Postgis SQL for nearest neighbors

postgis, postgresql, sql

Solution

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

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

Then you create an index on the geom field

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

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;

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

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

To fix this:

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

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

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

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

Check this for reference https://gis.stackexchange.com/questions/91765/improve-speed-of-postgis-nearest-neighbor-query/

Problem

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? I did the following : I created my table with the data: ``` 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); ``` First question, Is the SRID of Brazil 4326? What would be 4269 ? 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 ); ``` This returns nothing. From what I understand, this SQL would further point the radius of the maximum distance, right? It appears if you put 1000 results for 100000000, all my entries appear . So, I wonder what is wrong here?

Original source