[postgis-users] nearest neighbor search

[collin]

Rob,
I have been using the following SQL to update a point table with the 
nearest distance to a line table. I have not optimized it or analyzed 
it, but it does work.
SELECT
    pt.point_geom,
    min(distance(pt.point_geom,lt.line_geom)) as minimum_distance
FROM point_table as pt, line_table as lt
GROUP BY pt.point_geom;

The key to this is using minimum distance and grouping the results by 
point this will ensure you get minimum distance as opposed to first 
distance in a list.
hope this helps,
Collin Bode
NCALM, UC Berkeley

Robert Helmer wrote:

>Hello,
>
>I have been trying to find info on how to do a "nearest neighbor"
>search on point data I have loaded into my PostGIS/PostgreSQL
>database.. the only thing I've found so far is messages in
>the archive of this mailing list, suggesting using Distance()
>and a temp table.
>
>Does anyone have any pointers for how to do this? I'd sure
>appreciate it!
>
>
>Thanks,
>Rob Helmer
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>  
>