I've been looking over the PostGIS "API" of functions, and I notice that most of them take two elements to compare. For example, the ST_Distance function takes two geometry/geography elements to find the distance.

There is no function to do something like: "Given a geometry G, give me the closest geometry GClosest in Table T where G.id <> GClosest.id"

I realize I could write a PL/PgSQL function to iterate over the table and call ST_Distance on each element, but I'm hoping there's a better, more efficient, solution.

  • 1
    If you are interested in the distance to the closest geometry, check gis.stackexchange.com/questions/11979/…
    – underdark
    Commented Sep 8, 2011 at 20:04
  • let me know if i understood right... you want the next feature that have the same distance than the most closest?
    – falcacibar
    Commented Sep 9, 2011 at 15:45

2 Answers 2


Your question can be also answered by a single (albeit complex) query like the following which returns the whole record and the distance to the reference geometry. Please note that if more than one record matches the min distance they are all returned.

  address AS i, 
     min( ST_Distance(
        ) AS min_distance
     address AS ga,
     address AS gb 
     ga.id <> gb.id 
     ga.id = 3
  ) AS md 
  ST_Distance( i.address_geom, md.address_geom) = md.min_distance;

I have tested this query on table of addresses and it works. In the query above I am looking for nearest point to that with id=3.

  • This is good information - thank you... I understand the min(..) aggregate function by definition, but I'm confused on how it's being used in your example. st_distance(X,Y) takes two geometry types and returns a distance between them, which is a single value. Why are you then calling an aggregate function on that single value result? Maybe I'm misinterpreting the inside select statement...
    – Jmoney38
    Commented Sep 9, 2011 at 14:41
  • The group by is on the ga geometry which is a constant for the whole result set (remember ga is selected by id=3) so it basically does nothing. That is just a trick to have the ga geometry available in the outer query's st_distance without joining the table twice again. Today I was thinking that maybe I could get away with the innner query altogether by using the partition clause. That should also improve performance. I will give it a try and let you know.
    – unicoletti
    Commented Sep 9, 2011 at 16:08
  • Unfortunately window functions were introduced in 8.4 and now I don't have access to a server that has both postgis and that version so I can't test a query rewritten with the partion clause.
    – unicoletti
    Commented Sep 12, 2011 at 13:18

George MacKerron has written a simple Nearest Neighbor function which I've found quite useful. This function returns the ID of the nearest neighbor to a given feature:

create or replace function 
  nn(nearTo                   geometry
   , initialDistance          real
   , distanceMultiplier       real 
   , maxPower                 integer
   , nearThings               text
   , nearThingsIdField        text
   , nearThingsGeometryField  text)
 returns integer as $$
  sql     text;
  result  integer;
  sql := ' select ' || quote_ident(nearThingsIdField) 
      || ' from '   || quote_ident(nearThings)
      || ' where st_dwithin($1, ' 
      ||   quote_ident(nearThingsGeometryField) || ', $2 * ($3 ^ $4))'
      || ' order by st_distance($1, ' || quote_ident(nearThingsGeometryField) || ')'
      || ' limit 1';
  for i in 0..maxPower loop
     execute sql into result using nearTo              -- $1
                                , initialDistance     -- $2
                                , distanceMultiplier  -- $3
                                , i;                  -- $4
    if result is not null then return result; end if;
  end loop;
  return null;
$$ language 'plpgsql' stable;

Usage example:

SELECT id, nn(pt_geom,0.00001,2,100,'nw_node','node_id','node_geom') FROM my_point_table;

... selects the nearest node in nw_node table for every entry in my_point_table.

There is also a more generic function on the Boston GIS site.

  • I'm more concerned with how to create queries 1:N queries in a more general sense. For example, instead of finding the closest element to geometry G, I might want to find the first element that overlaps G. Thank you for the information, regardless. The link to Boston GIS was very useful! I've already printed some of the cheat sheets :-)
    – Jmoney38
    Commented Sep 9, 2011 at 14:45
  • Maybe you could rephrase your question to make it a little clearer, @Jmoney48. So you are not interested in the nearest neighbor problem specifically but rather in how to compare one geometry with all geometries in a table?
    – underdark
    Commented Sep 10, 2011 at 8:25
  • ALWAYS use the generic function of the Boston GIS site, the simple one is incredibly slow for big tables and the effort to apply it is not bigger.
    – Vladtn
    Commented Apr 4, 2012 at 18:25

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.