[postgis-users] update / closest polygon

[Josh Livni]

Adam,

Thanks a lot for the response.  I only need to the distance query once, but
in any case, I unfortunately was unable to get your query to run.  I am no
expert with this type of join, and I kept running into a 'ERROR: missing
FROM-clause entry for table "polygons"' (or table centroids if I added
polygons to the from clause).

In any case, I will continue to research my options, but if anyone has a
followup for what is probably a simple question to most of you (find the
closest polygon to a given set of points), I would certainly appreciate.

I think my alternative now is to just use a python script and psycopg, and
query for the closest polygon each point at a time, and update one at a
time.  This would be quite slow...

Thanks again,

  -Josh

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Adam
Quiney
Sent: Friday, March 03, 2006 10:42 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] update / closest polygon

Hi Josh,

Though you could probably achieve this using nested queries, computing 
distance to and from each of your points/polygons could be an expensive 
operation.  If this operation will be performed more than once, then I 
would be inclined to create a table holding the distances between your 
points and polygons, then indexing based on this distance:

CREATE TABLE poly_point_distance AS
SELECT c.centroid_id, p.polygon_id, distance(c.centroids.the_geom, 
p.polygons.the_geom) AS pp_distance
FROM polygons AS p
INNER JOIN centroids AS c
	ON expand(centroids.the_geom,1000) && polygons.the_geom
	AND distance(centroids.the_geom, polygons.the_geom) < 1000

CREATE INDEX distance_idx ON poly_point_distance (pp_distance);

You would probably want to create indices on the foreign keys in this 
table as well.

The above query may be able to be optimized, this is just a quick jot.

You can then perform a join against this table during your update to 
pull out the minimum distance.

--Adam

Josh Livni 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
> 

-- 
Adam Quiney
Refractions Research Inc.
aquiney at refractions.net
(250) 383-3022
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 
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
 

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 3/3/2006