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
>
>
>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
