PostGIS 查找最近点

2018/04/20 PostGIS

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

SELECT *
FROM your_table 
ORDER BY your_table.geom <-> "your location..."
LIMIT 1;

例:

//创建表
CREATE TABLE "nts_io_postgis_2d"  (id serial  primary key, wkt text,name text);
SELECT AddGeometryColumn('nts_io_postgis_2d', 'the_geom',  4326 ,'GEOMETRY', 2)

插入数据如下:

id wkt name the_geom
1 POINT (4 4)   010100002…
2 POINT (6 6)   010100002…
3 LINESTRING (5 0, 5 10, 5 20)   010200002…
4 LINESTRING (5 0, 5 10, 5 20)   010200002…
5 LINESTRING (5 0, 5 10, 5 20)   010300000…
select ST_AsText(the_geom) 
from public.nts_io_postgis_2d  
order by the_geom <-> ST_PointFromText('POINT(2 2)',4326) 
limit 1

--returns
LINESTRING(5 0,5 10,5 20)

再比如,加入Geometry类型过滤条件,比如只查找距离目标地物最近的点,使用ST_GeometryType命令,返回ST_Point、ST_LineString、ST_Polygon等。

select ST_AsText(the_geom) 
from public.nts_io_postgis_2d 
where ST_GeometryType(the_geom) = 'ST_Point' 
order by the_geom <-> ST_PointFromText('POINT(0 0)',4326) 
limit 1

--returns
POINT(4 4)

站内搜索

    目录