[postgis-users] RE: Getting the nearest street name from GPS point

[Luís Mota]

Hi.

I made a small function is plpgsql to do sth lik you say you are needing:
CREATE OR REPLACE FUNCTION pubPlaceNameOfClosestLineStr (GEOMETRY)

RETURNS TEXT AS '

DECLARE

closestLinestring RECORD;

BEGIN

IF srid($1) != 20790 THEN

RAISE EXCEPTION ''SRID must be 20790'';

END IF;

FOR closestLinestring IN EXECUTE

''SELECT arteria, distance(line,geomfromtext(''''''||

substring(astext($1) from 1 for char_length(astext($1))-1)

|| '''''',20790)) AS distance FROM eixos_ctt''||

'' ORDER BY distance ASC LIMIT 1''

LOOP

IF closestLinestring.distance < 50 THEN

RETURN closestLinestring.arteria;

ELSE

RETURN NULL;

END IF;

END LOOP;

RETURN NULL;

END

' LANGUAGE 'plpgsql';



In this case, the maximum tolerance is 50 m. Note that the street name is
the field "arteria" in the eixos_ctt' table.



Bye, Luís

----- Original