Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
Check this question, the sql code in the question should be enough for your needs.… – Alexandre Neto Nov 3 '12 at 3:11

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
share|improve this answer
Very helpful, thank you. I ended up using the following, which works but is rather slow. Not a problem for this application though. – Andrew Perrin Nov 3 '12 at 10:22

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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