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.


1 Answer 1


There are various methods for determining nearest neighbor - I provide several examples in this blogpost (pre-2.0, however).

Here is an example using a postgres window function to rank the distances between points. Grab the highest - and thus closest - rank and recalculate the distance. If you have a general sense of how clustered your points are, you can throw a ST_DWithin() in your query to limit the initial distance calculations.

    ,ST_Distance(i.geom, i.b_geom) AS dist
    ,b.gid AS b_gid
    ,b.geom AS b_geom
    ,rank() OVER (PARTITION BY a.gid ORDER BY ST_Distance(a.centroid_geom, b.the_geom)) AS pos
FROM points a, points b 
WHERE a.gid <> b.gid) i
WHERE pos = 1
  • Very helpful, thank you. I ended up using the following, which works but is rather slow. Not a problem for this application though. Nov 3, 2012 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.