[postgis-users] update / closest polygon
josh at livniconsulting.com
Fri Mar 3 15:44:35 PST 2006
Thanks all for the enlightening responses.
I like David's idea of just adding min(..) but unfortunately I forgot to
mention I actually need attributes from the parcel it was joined to, and not
just the distance.
So, from what I gather here, I think I will need to follow Paul's helpful
advice [thanks also to Brent for interesting method of calculating the
expand value] and wrap the select statement in 'something procedural'. I'll
set my python script to loop through each point and I'll be all set.
Thanks again for the insights.
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of David
Sent: Friday, March 03, 2006 1:43 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] update / closest polygon
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:
SET p_distance = min(distance(centroids.the_geom, polygons.the_geom)),
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.
> postgis-users mailing list
> postgis-users at postgis.refractions.net
postgis-users mailing list
postgis-users at postgis.refractions.net
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 3/3/2006
More information about the postgis-users