Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
1  
If you are interested in the distance to the closest geometry, check gis.stackexchange.com/questions/11979/… –  underdark Sep 8 '11 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 Sep 9 '11 at 15:45

2 Answers 2

up vote 5 down vote accepted

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.

select i.*, md.min_distance
from
   address as i, (
       select ga.address_geom,
       min(st_distance(ga.address_geom,gb.address_geom)) as min_distance
       from
          address as ga, address as gb where ga.id<>gb.id and ga.id=3
          group by ga.address_geom
) as md where 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.

share|improve this answer
    
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 Sep 9 '11 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 Sep 9 '11 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 Sep 12 '11 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 $$
declare 
  sql     text;
  result  integer;
begin
  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;
end
$$ 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.

share|improve this answer
    
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 Sep 9 '11 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 Sep 10 '11 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 Apr 4 '12 at 18:25

Your Answer

 
discard

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.