[postgis-users] update / closest polygon

[Paul Ramsey]

"Nearest neighbor" is a whole wonderful special case to optimize, and  
we have never actually done the general optimization work in  
PostGIS.  That's because the nearest-neighbor "hack" works most of  
the time:

select a.id,a.distance from a, b where a && expand(b,<arbitrary  
distance>) and b.id = <id for one geometry> order by distance limit 1;

As long as <arbitrary distance> always hits something, you always get  
an answer, and pretty fast too.  Obviously, it is not a general  
solution :)

For your mass update, you would wrap the SQL above in something  
procedural to iterate through each record in b.  Again, note the  
ugliness of the solution.  With a real nearest-neighbor index, it  
could be one piece of SQL, and not involve a magic number distance  
filter.

P.

On Mar 3, 2006, at 9:09 AM, 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