Monday, 6 March 2017

nearest neighbor within table using PostGIS


using PostGIS 2.0 and PostgreSQL 9.1 I have a table of photographic observations, sco_photos. The location column contains a PostGIS geometry point specifying where the observation was made. The table is not enormous (about 7,000 rows).


I am trying to write a query that will return, for each observation, the nearest other observation in the same table, along with the distance between the two observations. I can easily do this for a fixed point, e.g., how far is each observation from some central point, but the trick is locating the nearest other point in the table and measuring the distance to it.



This is the closest I've come thus far:


SELECT a.photo_id, b.photo_id, st_distance(a.location, b.location) 
FROM sco_photos a, sco_photos b
WHERE st_distance(a.location, b.location) =
(select min(st_distance(a.location, c.location))
from sco_photos c where c.photo_id <> a.photo_id);

...but this seems just to hang. Performance for this application is not terribly important, as I need this for analysis not for real-time querying.


Thanks for any advice.




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...