使用 PostGIS 查找给定点的 n 个最近邻居？

Question

我正在尝试解决使用 PostGIS 查找 n 个最近邻居的问题：

起点：

• 带有地理名称的表地理名称（来自 geonames.org）包含 纬度/经度 (WSG-84)
• 添加了 GeometryColumn 几何对象 srid=4326 和 datatype=POINT
• 填充几何值：更新地理名 设置几何= ST_SetSRID(ST_Point(经度,纬度), 第4326章）；
• 为 geom 创建了 GIST 索引（CREATE INDEX geom_index ON geoname USING GIST (geom);) / 集群geom_index: CLUSTER geom_index ON geoname;)
• 为 geonameid 创建了 PRIMARY KEY UNIQUE BTREE 索引

问题： 在 id (geoname.geonameid) 表示的表 geoname 中查找给定点的 n 个（例如 5）个最近邻居。

可能的解决方案：

受到 http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor，我尝试了以下查询：

"SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance " +
"FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid <> ende.geonameid " +
"AND ST_DWithin(start.geom, ende.geom, 300) order by distance limit 5"

处理时间：大约60秒

还尝试了基于EXPAND的方法：

"SELECT start.asciiname, ende.asciiname, distance_sphere(start.geom, ende.geom) as distance " +
"FROM geoname As start, geoname As ende WHERE start.geonameid = 2950159 AND start.geonameid <> ende.geonameid AND expand(start.geom, 300) && ende.geom " +
"order by distance limit 5"

处理时间：大约 120 秒

预期的应用程序是某种自动完成功能，因此，任何花费时间超过 1 秒的方法都不适用。使用 PostGIS 通常是否可以实现 < 1 秒的响应时间？

Answer 1

现在，从 PostGIS 2.0 开始，就有了可用的几何类型的 KNN 索引。
这会为您提供最近的 5 条记录，说明它们距离“您的位置...”有多远。

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

请参阅 PostgreSQL 手册中的 <-> 运算符 /a>.

Answer 2

正如我认为你在列表中得到的答案，单位是度，所以你几乎用 st_dwithin 中的 300 度搜索整个世界。

如果您的数据集很大，因此您无法在基于米的投影中工作（计算速度更快且占用更少的 CPU 资源），您应该考虑使用 geographphy 类型。然后您可以将 st_dwithin 与meter一起使用。

让事情变得更快，我应该创建一个新表，将几何图形转换为地理图形。

但为了测试它，你可以即时投射：

SELECT start.asciiname, ende.asciiname, 
ST_Distance(start.geom::geography, ende.geom::geography) as distance 
FROM geoname As start, geoname As ende 
WHERE start.geonameid = 2950159 AND start.geonameid <> ende.geonameid AND
ST_DWithin(start.geom::geography, ende.geom::geography, 300) 
order by distance 
limit 5;

HTH
尼克拉斯