[postgis-users] How to find the nearest road

[Ben Madin]

Julian,

I have been trying to modify the function to take a tablename when it is called, and although I can't see why it wouldn't work, it doesn't.

This is the one that does work - pretty similar I think to the one online.

I normally create a function to find the nearest geography of interest, and then feed that location (as geometry) into this function. In most cases I only want the start or finish node (for pgrouting) but sometimes it helps to have the record id for sorting out bad topography (like OSM data).

There are people on the postgis list who have a much better handle on this stuff, so I am copying this back to that list as well.

Good luck

cheers

Ben



CREATE OR REPLACE FUNCTION find_nearest_road(
    point geometry, sf varchar(6), OUT value int
) AS
$BODY$
DECLARE
    max_search_radius real := 5.0; -- working in degrees
    search_radius real := 0.01;
    rec record;
BEGIN
LOOP
    SELECT gid, source, target INTO rec
    FROM road m -- matches the table with the topography in it.
    WHERE st_expand(point, search_radius) && m.the_geom
    ORDER BY st_distance(point, m.the_geom)
    LIMIT 1;
    IF FOUND THEN -- you could really simplify this, it might speed it up
        IF substring(sf from 1 for 1) iLIKE 's' --start or Source
            THEN
            value := rec.source;
        ELSIF substring(sf from 1 for 1) iLIKE 't' --target
           OR substring(sf from 1 for 1) iLIKE 'f' --finish
           THEN
            value := rec.target;
        ELSIF substring(sf from 1 for 1) iLIKE 'g' --gid
           OR substring(sf from 1 for 1) iLIKE 'i' --id
            THEN
            value := rec.gid;
        END IF;
        EXIT;
    END IF;
    search_radius := search_radius * 2.0;
    EXIT WHEN search_radius > max_search_radius;
END LOOP;
END;
$BODY$ LANGUAGE plpgsql STABLE STRICT;

On 24/04/2010, at 5:02 , julian andres montes galvis wrote:

> Hi  ben, I am julian  from Colombia.
> 
> googling in the web and found  your postgres function find_nearest_road
> 
> do you have a new  version  from this ?
> 
> thanks and advanced
> 
> 
> Julian
> 
> -- 
> Julian Montes
> 
> 
> AZ LOGICA LTDA
> Telefax (+57 1) 800 1228
> Carrera 50 # 103B - 15
> Bogotá-Colombia
> 
> 
>