从PostGIS 2.0开始，geometry 类型的数据支持KNN算法。下面的例子示范查找最近点

关于<->,详见PostgreSQL manual

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

例：

1
2
3
//创建表
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… |

1
2
3
4
5
6
7
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等。

1
2
3
4
5
6
7
8
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)