Найдите n ближайших соседей для данной точки, используя PostGIS?

Я пытаюсь решить проблему поиска n ближайших соседей с помощью PostGIS:

Начальная точка:

  • Таблица geoname с geonames (от geonames.org), содержащий широта/долгота (WSG-84)
  • Добавлена ​​геометрия GeometryColumn с srid = 4326 и datatype = POINT
  • Заполненная геометрия со значениями: UPDATE geoname SET geom = ST_SetSRID (ST_Point (долгота, широта), 4326);
  • Создан индекс GIST для геометрии (CREATE INDEX geom_index ON geoname ИСПОЛЬЗОВАНИЕ GIST (geom);)/Clustered geom_index: CLUSTER geom_index ON geoname;)
  • Создан PRIMARY KEY UNIQUE BTREE index для geonameid

Проблема: Найдите n (например, 5) ближайших соседей для данной точки в таблице geoname, представленной id (geoname.geonameid.

Возможное решение:

Вдохновленный 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 с

Запланированное приложение - это своего рода автозаполнение. Таким образом, любой подход, принимающий дольше > 1s, неприменим. Можно ли вообще достичь времени отклика < 1 с PostGIS?

Ответы

Ответ 1

Теперь, начиная с PostGIS 2.0, существует индекс KNN для доступных типов геометрии. Это дает вам ближайшие 5 записей, не учитывая, насколько далеко от "вашего местоположения...".

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

См. <-> operator в руководстве по PostgreSQL.

Ответ 2

Как я думаю, на вас ответили в списке, единица находится в градусах, поэтому вы почти просматриваете весь мир с 300 градусами в st_dwithin.

Если ваш набор данных настолько велик, что вы не можете работать в прогнозируемой проекции на основе измерителя (намного быстрее и меньше вычислений с использованием процессора), вы должны использовать вместо этого тип географии. Затем вы можете использовать st_dwithin с метром.

Делайте вещи быстрее, я должен просто создать новую таблицу с геометрией, преобразованной в географию.

Но просто проверить его можно на лету:

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;

НТН Никлас