[postgis-users] update / closest polygon

[David Bitner]

Off the top of my head, I can't quite remember if you can throw an
aggregate into an update statement like this, but have you tried:

UPDATE centroids
SET p_distance = min(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

On 3/3/06, Josh Livni <josh at livniconsulting.com> wrote:
>   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.
>
>    -Josh
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>