問(wèn)題描述

在我們生活中，想必很多人都使用過(guò)一個(gè)功能就是查找附近XX米內(nèi)的美食、景點(diǎn)等信息。那么該功能是如何實(shí)現(xiàn)的呢？本文將帶你一探究竟。
具體問(wèn)題：查詢給定點(diǎn)（如：113.678 34.796）周圍1000米內(nèi)的學(xué)校。

數(shù)據(jù)庫(kù)

PostgresSQL/PostGIS

主要方法

boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);
boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters, boolean use_spheroid);

官方說(shuō)明是：

Returns true if the geometries are within the specified distance of one another.
For geometry: The distance is specified in units defined by the spatial reference system of the geometries. For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID.
For geography units are in meters and measurement is defaulted to use_spheroid=true, for faster check, use_spheroid=false to measure along sphere.

翻譯過(guò)來(lái)大致是說(shuō)：
（1）對(duì)于geometry第三個(gè)距離參數(shù)單位和數(shù)據(jù)的坐標(biāo)系有關(guān)，如果數(shù)據(jù)使用的是地理坐標(biāo)系，則距離單位是度。
（2）對(duì)于geography第三個(gè)距離參數(shù)單位是米。

Geometry與Geography

GIS坐標(biāo)系簡(jiǎn)介

GIS中坐標(biāo)系統(tǒng)有兩種，一種是球面坐標(biāo)，也叫地理坐標(biāo)；一種是平面坐標(biāo)，也叫投影坐標(biāo)。在應(yīng)用時(shí)，球面坐標(biāo)通常用于計(jì)算，平面坐標(biāo)通常用于展示（當(dāng)然也可以用于計(jì)算）。
投影坐標(biāo)是從球坐標(biāo)投影后展開得來(lái)(用一個(gè)圓柱將地球包起來(lái)，把地球當(dāng)成會(huì)發(fā)光的光源，投影后，將圓柱展開得到)，投影的范圍越大，精度就越低。范圍越小，精度就越高。除了投影扇形的大小有區(qū)別，在不同的行業(yè)，也有不同的坐標(biāo)系，例如用于測(cè)繪的坐標(biāo)系。

Geometry與Geography支持的坐標(biāo)系

Geometry支持地理坐標(biāo)和平面坐標(biāo)，Geometry支持更多的函數(shù)，一些幾何計(jì)算的代價(jià)更低；
Geography僅支持地理坐標(biāo)，Geography支持的函數(shù)略少，計(jì)算代價(jià)更高。

那為什么還需要Geography呢，請(qǐng)看官方給出的解釋：

4.2.2. When to use Geography Data type over Geometry data type    
    
The geography type allows you to store data in longitude/latitude coordinates,     
but at a cost: there are fewer functions defined on GEOGRAPHY than there are on GEOMETRY;     
those functions that are defined take more CPU time to execute.    
    
The type you choose should be conditioned on the expected working area of the application you are building.     
Will your data span the globe or a large continental area, or is it local to a state, county or municipality?    
    
If your data is contained in a small area, you might find that choosing an appropriate     
projection and using GEOMETRY is the best solution, in terms of performance and functionality available.    
    
If your data is global or covers a continental region, you may find that GEOGRAPHY     
allows you to build a system without having to worry about projection details.     
You store your data in longitude/latitude, and use the functions that have been defined on GEOGRAPHY.    
    
If you don't understand projections, and you don't want to learn about them,     
and you're prepared to accept the limitations in functionality available in GEOGRAPHY,     
then it might be easier for you to use GEOGRAPHY than GEOMETRY.     
Simply load your data up as longitude/latitude and go from there.      
    
Refer to Section 14.11, “PostGIS Function Support Matrix” for compare between     
what is supported for Geography vs. Geometry.     
For a brief listing and description of Geography functions,     
refer to Section 14.4, “PostGIS Geography Support Functions”   

完整示例

示例1：使用Geometry

SELECT *
FROM school_table t
WHERE ST_DWithin(ST_Transform(ST_GeomFromText('POINT(113.678425 34.796591666)',4326),XXXX),ST_Transform(t.geom,XXXX),1000)

說(shuō)明：語(yǔ)句中“XXXX”，需根據(jù)項(xiàng)目的精度要求，選擇不同的投影以及所在的條度帶。該方法適合小區(qū)域范圍的查詢，如果區(qū)域范圍橫跨兩個(gè)條度帶的時(shí)候，查出的內(nèi)容會(huì)出錯(cuò)。
解決方法：在精度要求不高的時(shí)候，可使用WGS_1984_World_Mercator投影。
示例2：使用Geography

SELECT *
FROM school_table t
WHERE ST_DWithin(ST_GeographyFromText('SRID=4326;POINT(113.678425 34.796591666)'),t.geom::geography,1000)

與我國(guó)相關(guān)的坐標(biāo)系

Geographic Coordinate System 地理坐標(biāo)，參考資料：https://developers.arcgis.com/javascript/3/jshelp/gcs.html

Projected Coordinate System 投影坐標(biāo)，參考資料：https://developers.arcgis.com/javascript/3/jshelp/pcs.html

EPSG:3857與EPSG:900913

EPSG:3857與EPSG:900913等效

掃描下方二維碼，關(guān)注微信公眾號(hào)，精彩內(nèi)容同步更新，有問(wèn)題可隨時(shí)交流

微信公眾號(hào)