[postgis-users] using dynamic tables in plpgsql nearest neighbour query

[Ben Madin]

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