问题描述

在我们生活中，想必很多人都使用过一个功能就是查找附近XX米内的美食、景点等信息。那么该功能是如何实现的呢？本文将带你一探究竟。
具体问题：查询给定点（如：113.678 34.796）周围1000米内的学校。

数据库

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);

官方说明是：

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.

翻译过来大致是说：
（1）对于geometry第三个距离参数单位和数据的坐标系有关，如果数据使用的是地理坐标系，则距离单位是度。
（2）对于geography第三个距离参数单位是米。

Geometry与Geography

GIS坐标系简介

GIS中坐标系统有两种，一种是球面坐标，也叫地理坐标；一种是平面坐标，也叫投影坐标。在应用时，球面坐标通常用于计算，平面坐标通常用于展示（当然也可以用于计算）。
投影坐标是从球坐标投影后展开得来(用一个圆柱将地球包起来，把地球当成会发光的光源，投影后，将圆柱展开得到)，投影的范围越大，精度就越低。范围越小，精度就越高。除了投影扇形的大小有区别，在不同的行业，也有不同的坐标系，例如用于测绘的坐标系。

Geometry与Geography支持的坐标系

Geometry支持地理坐标和平面坐标，Geometry支持更多的函数，一些几何计算的代价更低；
Geography仅支持地理坐标，Geography支持的函数略少，计算代价更高。

那为什么还需要Geography呢，请看官方给出的解释：

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)

说明：语句中“XXXX”，需根据项目的精度要求，选择不同的投影以及所在的条度带。该方法适合小区域范围的查询，如果区域范围横跨两个条度带的时候，查出的内容会出错。
解决方法：在精度要求不高的时候，可使用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)

与我国相关的坐标系

Geographic Coordinate System 地理坐标，参考资料：https://developers.arcgis.com/javascript/3/jshelp/gcs.html

WKID	名称
4214	GCS_Beijing_1954
4326	GCS_WGS_1984
4490	GCS_China_Geodetic_Coordinate_System_2000
4555	GCS_New_Beijing
4610	GCS_Xian_1980

Projected Coordinate System 投影坐标，参考资料：https://developers.arcgis.com/javascript/3/jshelp/pcs.html

WKID	名称
2327	Xian_1980_GK_Zone_13
2328	Xian_1980_GK_Zone_14
2329	Xian_1980_GK_Zone_15
2330	Xian_1980_GK_Zone_16
2331	Xian_1980_GK_Zone_17
2332	Xian_1980_GK_Zone_18
2333	Xian_1980_GK_Zone_19
2334	Xian_1980_GK_Zone_20
2335	Xian_1980_GK_Zone_21
2336	Xian_1980_GK_Zone_22
2337	Xian_1980_GK_Zone_23
2338	Xian_1980_GK_CM_75E
2339	Xian_1980_GK_CM_81E
2340	Xian_1980_GK_CM_87E
2341	Xian_1980_GK_CM_93E
2342	Xian_1980_GK_CM_99E
2343	Xian_1980_GK_CM_105E
2344	Xian_1980_GK_CM_111E
2345	Xian_1980_GK_CM_117E
2346	Xian_1980_GK_CM_123E
2347	Xian_1980_GK_CM_129E
2348	Xian_1980_GK_CM_135E
2349	Xian_1980_3_Degree_GK_Zone_25
2350	Xian_1980_3_Degree_GK_Zone_26
2351	Xian_1980_3_Degree_GK_Zone_27
2352	Xian_1980_3_Degree_GK_Zone_28
2353	Xian_1980_3_Degree_GK_Zone_29
2354	Xian_1980_3_Degree_GK_Zone_30
2355	Xian_1980_3_Degree_GK_Zone_31
2356	Xian_1980_3_Degree_GK_Zone_32
2357	Xian_1980_3_Degree_GK_Zone_33
2358	Xian_1980_3_Degree_GK_Zone_34
2359	Xian_1980_3_Degree_GK_Zone_35
2360	Xian_1980_3_Degree_GK_Zone_36
2361	Xian_1980_3_Degree_GK_Zone_37
2362	Xian_1980_3_Degree_GK_Zone_38
2363	Xian_1980_3_Degree_GK_Zone_39
2364	Xian_1980_3_Degree_GK_Zone_40
2365	Xian_1980_3_Degree_GK_Zone_41
2366	Xian_1980_3_Degree_GK_Zone_42
2367	Xian_1980_3_Degree_GK_Zone_43
2368	Xian_1980_3_Degree_GK_Zone_44
2369	Xian_1980_3_Degree_GK_Zone_45
2370	Xian_1980_3_Degree_GK_CM_75E
2371	Xian_1980_3_Degree_GK_CM_78E
2372	Xian_1980_3_Degree_GK_CM_81E
2373	Xian_1980_3_Degree_GK_CM_84E
2374	Xian_1980_3_Degree_GK_CM_87E
2375	Xian_1980_3_Degree_GK_CM_90E
2376	Xian_1980_3_Degree_GK_CM_93E
2377	Xian_1980_3_Degree_GK_CM_96E
2378	Xian_1980_3_Degree_GK_CM_99E
2379	Xian_1980_3_Degree_GK_CM_102E
2380	Xian_1980_3_Degree_GK_CM_105E
2381	Xian_1980_3_Degree_GK_CM_108E
2382	Xian_1980_3_Degree_GK_CM_111E
2383	Xian_1980_3_Degree_GK_CM_114E
2384	Xian_1980_3_Degree_GK_CM_117E
2385	Xian_1980_3_Degree_GK_CM_120E
2386	Xian_1980_3_Degree_GK_CM_123E
2387	Xian_1980_3_Degree_GK_CM_126E
2388	Xian_1980_3_Degree_GK_CM_129E
2389	Xian_1980_3_Degree_GK_CM_132E
2390	Xian_1980_3_Degree_GK_CM_135E
2401	Beijing_1954_3_Degree_GK_Zone_25
2402	Beijing_1954_3_Degree_GK_Zone_26
2403	Beijing_1954_3_Degree_GK_Zone_27
2404	Beijing_1954_3_Degree_GK_Zone_28
2405	Beijing_1954_3_Degree_GK_Zone_29
2406	Beijing_1954_3_Degree_GK_Zone_30
2407	Beijing_1954_3_Degree_GK_Zone_31
2408	Beijing_1954_3_Degree_GK_Zone_32
2409	Beijing_1954_3_Degree_GK_Zone_33
2410	Beijing_1954_3_Degree_GK_Zone_34
2411	Beijing_1954_3_Degree_GK_Zone_35
2412	Beijing_1954_3_Degree_GK_Zone_36
2413	Beijing_1954_3_Degree_GK_Zone_37
2414	Beijing_1954_3_Degree_GK_Zone_38
2415	Beijing_1954_3_Degree_GK_Zone_39
2416	Beijing_1954_3_Degree_GK_Zone_40
2417	Beijing_1954_3_Degree_GK_Zone_41
2418	Beijing_1954_3_Degree_GK_Zone_42
2419	Beijing_1954_3_Degree_GK_Zone_43
2420	Beijing_1954_3_Degree_GK_Zone_44
2421	Beijing_1954_3_Degree_GK_Zone_45
2422	Beijing_1954_3_Degree_GK_CM_75E
2423	Beijing_1954_3_Degree_GK_CM_78E
2424	Beijing_1954_3_Degree_GK_CM_81E
2425	Beijing_1954_3_Degree_GK_CM_84E
2426	Beijing_1954_3_Degree_GK_CM_87E
2427	Beijing_1954_3_Degree_GK_CM_90E
2428	Beijing_1954_3_Degree_GK_CM_93E
2429	Beijing_1954_3_Degree_GK_CM_96E
2430	Beijing_1954_3_Degree_GK_CM_99E
2431	Beijing_1954_3_Degree_GK_CM_102E
2432	Beijing_1954_3_Degree_GK_CM_105E
2433	Beijing_1954_3_Degree_GK_CM_108E
2434	Beijing_1954_3_Degree_GK_CM_111E
2435	Beijing_1954_3_Degree_GK_CM_114E
2436	Beijing_1954_3_Degree_GK_CM_117E
2437	Beijing_1954_3_Degree_GK_CM_120E
2438	Beijing_1954_3_Degree_GK_CM_123E
2439	Beijing_1954_3_Degree_GK_CM_126E
2440	Beijing_1954_3_Degree_GK_CM_129E
2441	Beijing_1954_3_Degree_GK_CM_132E
2442	Beijing_1954_3_Degree_GK_CM_135E
21413	Beijing_1954_GK_Zone_13
21414	Beijing_1954_GK_Zone_14
21415	Beijing_1954_GK_Zone_15
21416	Beijing_1954_GK_Zone_16
21417	Beijing_1954_GK_Zone_17
21418	Beijing_1954_GK_Zone_18
21419	Beijing_1954_GK_Zone_19
21420	Beijing_1954_GK_Zone_20
21421	Beijing_1954_GK_Zone_21
21422	Beijing_1954_GK_Zone_22
21423	Beijing_1954_GK_Zone_23
21473	Beijing_1954_GK_Zone_13N
21474	Beijing_1954_GK_Zone_14N
21475	Beijing_1954_GK_Zone_15N
21476	Beijing_1954_GK_Zone_16N
21477	Beijing_1954_GK_Zone_17N
21478	Beijing_1954_GK_Zone_18N
21479	Beijing_1954_GK_Zone_19N
21480	Beijing_1954_GK_Zone_20N
21481	Beijing_1954_GK_Zone_21N
21482	Beijing_1954_GK_Zone_22N
21483	Beijing_1954_GK_Zone_23N
3395	WGS_1984_World_Mercator

EPSG:3857与EPSG:900913

EPSG:3857与EPSG:900913等效

扫描下方二维码，关注微信公众号，精彩内容同步更新，有问题可随时交流

微信公众号