[postgis-users] update / closest polygon

Josh Livni josh at livniconsulting.com
Fri Mar 3 09:09:52 PST 2006

  I have a basic 'update' question.  For a simple example, given a bunch 
of points, I'd like to give them attributes based on the closest polygon.

Let's say I try this:

UPDATE centroids
SET p_distance = distance(centroids.the_geom, polygons.the_geom),
FROM polygons
WHERE expand(centroids.the_geom,1000) && polygons.the_geom
   and Distance(centroids.the_geom, polygons.the_geom) < 1000

This associates any polygon within 1k to the point -- but not 
necessarily the closest.  Is there an easy way to bring in an 'order by 
distance' and 'limit 1' kind of idea to the UPDATE query?  Do I need to 
do that in a separate select, make a temp table, and then join?

Thanks in advance for any pointers.


More information about the postgis-users mailing list