使用PostGIS查找给定点的n个最近邻居? [英] Find n Nearest Neighbors for given Point using PostGIS?

问题描述

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

I am trying to solve the problem of finding the n nearest neighbors using PostGIS:

起点:

• 具有地名的表地名(来自 geonames.org)包含 纬度/经度(WSG-84)
• 添加了带有以下内容的GeometryColumn几何图形: srid = 4326和datatype = POINT
• 具有以下值的填满的几何图形:UPDATE地理名称 SET geom = ST_SetSRID(ST_Point(经度，纬度)， 4326)；
• 为geom创建GIST索引(创建 INDEX geom_index ON地理名称USING GIST(geom);)/集群geom_index:CLUSTER geom_index ON 地名；)
• 为geonameid创建主键唯一BTREE索引

• 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

问题: 在由id(geoname.geonameid.)表示的表地理名称中，找到给定点的n个(例如5个)最近的邻居.

Problem: Find n (e.g. 5) nearest neighbors for a given Point in table geoname represented by id (geoname.geonameid.

可能的解决方案:

灵感来自 http://www.bostongis.com/PrinterFriendly.aspx?content_name= postgis_nearest_neighbor ，我尝试了以下查询:

Inspired by http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_nearest_neighbor, I tried the following query:

"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"

处理时间:大约60s

Processing time: about 60s

还尝试了一种基于EXPAND的方法:

Also tried an approach based on 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"

处理时间:大约120s

Processing time: about 120s

目标应用程序是某种自动完成功能.因此，任何花费时间大于1s的方法均不适用.使用PostGIS，通常是否可以达到< 1s的响应时间?

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?

推荐答案

自PostGIS 2.0以来，现在有一个适用于几何类型的KNN索引. 这将为您提供最近的5条记录，它们距您的位置..."有多远.

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...".

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

请参见PostgreSQL手册中的<->运算符 .

See <-> operator in PostgreSQL manual.

这篇关于使用PostGIS查找给定点的n个最近邻居?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！