美文网首页开源
PostGIS 查询某点周围指定范围内的兴趣点

PostGIS 查询某点周围指定范围内的兴趣点

作者: giscoder | 来源:发表于2020-07-15 17:27 被阅读0次

问题描述

在我们生活中,想必很多人都使用过一个功能就是查找附近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等效

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

微信公众号

相关文章

  • PostGIS 查询某点周围指定范围内的兴趣点

    问题描述 在我们生活中,想必很多人都使用过一个功能就是查找附近XX米内的美食、景点等信息。那么该功能是如何实现的呢...

  • Springboot+JPA+PostGIS经纬度查询范围

    前言 web应用中很多查找某点范围内的poi功能,可以通过PostGIS实现。 安装postgis 通过docke...

  • postgis

    官网 postgis查询版本: 参考: 获取PostGIS版本?

  • Mysql——查询

    创建表 插入数据 where 条件判断符 查询价格小于10.2的水果 IN查询 查询指定范围内的条件记录,将所有的...

  • PostGIS导出指定范围内的栅格数据

    针对已经通过PostGIS导入到PostgreSQL中的栅格数据,给定经纬度范围,实现栅格数据的导出。查询SQL语...

  • PostGIS函数应用(一)

    先展示查询的效果: (1)几何图形空间查询: (2)Buffer空间查询: (3)查询函数 PostGIS空间查询...

  • PostGIS 查找最近点

    从PostGIS 2.0开始,geometry 类型的数据支持KNN算法。下面的例子示范查找最近点关于<->,详见...

  • PostGIS特性

    PostGIS特性 1、PostGIS支持所有的空间数据类型,这些类型包括:点(POINT)、线(LINESTRI...

  • Excel使用vlookup将表格数据合并

    查询3个表格,把相同的值进行合并。 一、Vlookup函数:功能及语法结构。 功能:在指定的数据范围内返回符合查询...

  • oracle数据操作(四)

    基本查询语句 from 子句 指定从哪张表范围内查找数据select 字段名from 表名查询对应字段的数据 wh...

网友评论

    本文标题:PostGIS 查询某点周围指定范围内的兴趣点

    本文链接:https://www.haomeiwen.com/subject/eothxktx.html