最近邻居的Postgis SQL [英] Postgis SQL for nearest neighbors

问题描述

我正在尝试计算最近的邻居.为此，我需要传递一个参数来限制到邻居的最大距离.例如，半径1000米以内的最近邻居是哪些?

I'm trying to calculate the nearest neighbors. For that, I need to pass a parameter to limit the maximum distance from the neighbors. For example, which are the nearest neighbors within a radius of 1000 meters?

我做了以下事情:

我用数据创建了表格:

id | name | latitude | longitude

之后，我执行了以下查询:

After that, I executed the following query :

SELECT AddGeometryColumn ( 'public' , ' green ', ' geom ' , 4326 , ' POINT' , 2 );

UPDATE season
SET geom = ST_Transform(ST_PointFromText ('POINT (' || longitude || ' ' || latitude || ')', 4269), 4326);

第一个问题，巴西的SRID是4326吗?会是4269吗?

First question, Is the SRID of Brazil 4326? What would be 4269 ?

第二个问题，通过执行以下SQL

Second question, by doing the following SQL

SELECT id, name
FROM season
WHERE ST_DWithin (
                 geom ,
                 ST_GeomFromText ('POINT(-49.2653819 -25.4244287 )', 4326),
                 1000
                 );

这不返回任何内容.据我了解，此SQL会进一步指出最大距离的半径，对吧?

This returns nothing. From what I understand, this SQL would further point the radius of the maximum distance, right?

如果您为1000000000放入1000个结果，则会显示所有条目.

It appears if you put 1000 results for 100000000, all my entries appear .

所以，我想知道这里出什么问题了吗?

So, I wonder what is wrong here?

推荐答案

首先，如果您使用的是纬度，经度，则需要使用4326.

First, If you are using latitude, longitude, you need to use 4326.

UPDATE season SET geom = ST_PointFromText ('POINT(' || longitude || ' ' || latitude || ')' , 4326 ) ;

然后您在geom字段上创建索引

Then you create an index on the geom field

CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] ); 

然后您将获得kNN的倍数:

Then you get the kNN neightbors:

SELECT *,ST_Distance(geom,'SRID=4326;POINT(newLon newLat)'::geometry) 
FROM yourDbTable
ORDER BY
yourDbTable.geom <->'SRID=4326;POINT(newLon newLat)'::geometry
LIMIT 10;

该查询将利用gist索引的kNN功能( http://workshops.boundlessgeo.com/postgis-intro/knn.html ).

This query will take advantage of kNN functionality of the gist index (http://workshops.boundlessgeo.com/postgis-intro/knn.html).

仍然返回的距离将以度为单位，而不是以米为单位(投影4326使用度).

Still the distance returned will be in degrees, not meters (projection 4326 uses degrees).

要解决此问题:

SELECT *,ST_Distance(geography(geom),ST_GeographyFromText('POINT(newLon newLat)') 
FROM yourDbTable
ORDER BY
yourDbTable.geom <->'SRID=4326;POINT(newLon newLat)'::geometry
LIMIT 10;

计算ST_distance时，请使用地理类型.距离始终以米为单位:

When you calculate the ST_distance use the geography type. There distance is always in meters:

http://workshops.boundlessgeo.com/postgis-intro/geography.html

所有这些功能可能都需要最新的Postgis版本(2.0+).不过我不确定.

All this functionality will probably need a recent Postgis version (2.0+). I am not sure though.

选中此作为参考 https://gis.stackexchange. com/questions/91765/improve-speed-of-postgis-nearest-neighbor-query/

这篇关于最近邻居的Postgis SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！