postgresql 使用 PostGIS 查找给定点的 n 个最近邻？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5111843/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find n Nearest Neighbors for given Point using PostGIS?
提问by Scholle
I am trying to solve the problem of finding the n nearest neighbors using PostGIS:
我正在尝试解决使用 PostGIS 找到 n 个最近邻的问题：
Starting Point:
初始点：
- Table geoname with geonames (from geonames.org) containing latitude/longitude (WSG-84)
- Added a GeometryColumn geom with srid=4326 and datatype=POINT
- Filled geom with values: UPDATE geoname SET geom = ST_SetSRID(ST_Point(longitude,latitude), 4326);
- Created GIST index for geom (CREATE INDEX geom_index ON geoname USING GIST (geom);) / Clustered geom_index: CLUSTER geom_index ON geoname;)
- Created PRIMARY KEY UNIQUE BTREE index for geonameid
- 表 geoname 与 geonames（来自 geonames.org）包含纬度/经度（WSG-84）
- 添加了 srid=4326 和 datatype=POINT 的 GeometryColumn geom
- 用值填充 geom：UPDATE geoname SET geom = ST_SetSRID(ST_Point(longitude,latitude), 4326);
- 为 geom 创建 GIST 索引 (CREATE INDEX geom_index ON geoname USING GIST (geom);) / Clustered geom_index: CLUSTER geom_index ON geoname;)
- 为 geonameid 创建 PRIMARY KEY UNIQUE BTREE 索引
Problem: Find n (e.g. 5) nearest neighbors for a given Point in table geoname represented by id (geoname.geonameid.
问题：为表 geoname 中的给定点查找 n（例如 5）个最近邻居，由 id (geoname.geonameid.geoname) 表示。
Possible solution:
可能的解决方案：
Inspired by http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor, I tried the following query:
受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"
Processing time: about 60s
处理时间：约60s
Also tried an approach based on EXPAND:
还尝试了一种基于 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"
Processing time: about 120s
处理时间：约120s
The intended application is some kind of autocomplete. So, any approach taking longer than >1s is not applicable. Is it generally possible to achieve a response time of <1s with PostGIS?
预期的应用程序是某种自动完成。因此，任何耗时超过 1 秒的方法都不适用。使用 PostGIS 通常是否可以实现 <1s 的响应时间？
回答by Stefan
Now since PostGIS 2.0, there's a KNN index for geometry types available. This gives you nearest 5 records with regard to how far they are away from "your location...".
现在从 PostGIS 2.0 开始，有一个可用的几何类型的 KNN 索引。这为您提供了最近的 5 条记录，它们与“您的位置...”的距离有关。
SELECT *
FROM your_table
ORDER BY your_table.geom <-> "your location..."
LIMIT 5;
See <->operator in PostgreSQL manual.
请参阅PostgreSQL 手册中的<->运算符。
回答by Nicklas Avén
As I think you were answered at the list the unit is in degrees so you area almost searching the whole world with 300 degrees in st_dwithin.
我认为你在列表中得到了回答，单位是度，所以你几乎在 st_dwithin 中用 300 度搜索整个世界。
If your dataset is that big so you can't work in a projected meterbased projection instead (much faster and less cpu-intensive calculations) you should consider using the geograpphy type instead. Then you can use st_dwithin with meter.
如果您的数据集很大，因此您不能在基于米的投影投影中工作（更快且 CPU 密集度更低的计算），您应该考虑使用 geography 类型。然后您可以将 st_dwithin 与仪表一起使用。
The make things faster you should I would just create a new table with the geometry converted to geography.
为了让事情更快，你应该我只创建一个新表，将几何转换为地理。
But to just test it you can cast on the fly:
但是为了测试它，您可以即时投射：
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 Nicklas
HTH尼克拉斯
