Find nearest node function

Question

I am trying to get work the next function but i don't know if it is incomplete because the query SELECT * FROM pgr_nearest_node1('jaen',-3.75,37.779); does not provide any results.

Here is the function:(from pgr_fromAtoB):

CREATE OR REPLACE FUNCTION pgr_nearest_node1(IN tbl character varying, IN x1 double precision, IN y1 double precision, OUT id integer, OUT geom geometry)
  RETURNS SETOF record AS
    $BODY$
    DECLARE
            rec     record;

    BEGIN
        -- Find nearest node
        EXECUTE 'SELECT id::integer, the_geom FROM jaen_vertices_pgr 
                ORDER BY the_geom <-> ST_GeometryFromText(''POINT(' 
                || x1 || ' ' || y1 || ')'',4326) LIMIT 3' INTO rec;

            -- Return record
                    id     := rec.id;
                    geom   := rec.the_geom;

    END;
    $BODY$
    LANGUAGE 'plpgsql' VOLATILE STRICT;

Answer 1

You didn't post the whole function, so there could be several reasons, why it doesn't work. For example the declaration of output values could be wrong.

You need to have something like this, if you want to return id and geom:

CREATE OR REPLACE FUNCTION pgr_nearest_node1(
                IN tbl varchar,
                IN x1 double precision,
                IN y1 double precision,
                OUT id integer,
                OUT geom geometry
        )
        RETURNS SETOF record AS
$BODY$
[...]

And then the query selects all nodes from jaen_vertices_pgr and orders them by distance with the closest on the top. Therefore you do LIMIT 1, because you're only interested in the nearest point. Not sure, why you have set LIMIT 3. With 3 records returned you had to loop through them for example.

-- Find nearest node
EXECUTE 'SELECT id::integer, the_geom FROM jaen_vertices_pgr 
        ORDER BY the_geom <-> ST_GeometryFromText(''POINT(' 
        || x1 || ' ' || y1 || ')'',4326) LIMIT 1' INTO rec;

And finally you need to return ???; something, and you probably want to return record; (depends on what you have specified at the beginning).

If this still doesn't help, please post your complete function.