G'day all,
I am trying to sort out a routing problem over much of South-East Asia, and
trying to fill gaps using a number of different data sources.
I have a plpgsql function which works - with a static table name etc. When I
tried to change it to a dynamic name using the execute 'query' using variables;
statement, it doesn't work.
I am suspicious that the geometry type might be the cause
Anyways, this works : (using a multilinestring table with vertices assigned
using the pgrouting function.
CREATE OR REPLACE FUNCTION find_nearest_road(tabname varchar,
point geometry, sf varchar(6), OUT value int
) AS
$BODY$
DECLARE
max_search_radius real := 5.0; -- this is assuming working in degrees I
guess!
search_radius real := 0.01;
rec record; -- this has to match your lookup table
BEGIN
LOOP
SELECT gid, source, target INTO rec
FROM road m -- and you might want to change this
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, but
I kind of like it.
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;
and this doesn't :- the first 5 lines after loop above have been replaced with
the 7 lines below, but I haven't even quoted the tablename - it's still
hardcoded.
CREATE OR REPLACE FUNCTION find_nearest_road(tabname varchar,
point geometry, sf varchar(6), OUT value int
) AS
$BODY$
DECLARE
max_search_radius real := 5.0; -- this is assuming working in degrees I
guess!
search_radius real := 0.01;
rec record; -- this has to match your lookup table
BEGIN
LOOP
EXECUTE 'SELECT gid, source, target
FROM road m -- and you might want to change this
WHERE st_expand($1, $2) && m.the_geom
ORDER BY st_distance($1, m.the_geom)
LIMIT 1'
INTO rec
USING point, search_radius;
IF FOUND THEN -- you could really simplify this, it might speed it up, but
I kind of like it.
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 VOLATILE STRICT;
the first function returns :
prices=# select
find_nearest_road('road','0101000020E610000052FC3DCF94A459409734BBCFC2243240'::geometry,'s');
find_nearest_road
-------------------
1507
(1 row)
and the second version of the function :
prices=# select
find_nearest_road('road','0101000020E610000052FC3DCF94A459409734BBCFC2243240'::geometry,'s');
find_nearest_road
-------------------
(1 row)
So I guess it comes down to what's wrong between :
SELECT gid, source, target INTO rec FROM road m WHERE st_expand(point,
search_radius) && m.the_geom ORDER BY st_distance(point, m.the_geom) LIMIT 1;
EXECUTE
'SELECT gid, source, target FROM road m WHERE st_expand($1, $2) && m.the_geom
ORDER BY st_distance($1, m.the_geom) LIMIT 1'
INTO rec USING point, search_radius;
Any advice gratefully received,
Ben
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
