1

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;
2

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.

3
  • Thanks for your answwer.Sorry , it seems that i did not select all the code . I've already corrected it. I changed the limit to three to verify that at least one is picked . I tried the code but still does not return any results .
    – user19566
    Feb 7 '15 at 11:50
  • I have run into a new SQL window postgis SELECT id::integer, the_geom FROM jaen_vertices_pgr ORDER BY the_geom <-> ST_GeometryFromText('POINT(-3.7894 37.7733)',4326) LIMIT 1; and it works fine.
    – user19566
    Feb 7 '15 at 12:20
  • 1
    It has finally been solved. Due to the final objetive is to get the id node from coordinates,i have not considered to record the out: CREATE OR REPLACE FUNCTION pgr_nearest_node(IN tbl character varying, IN x1 double precision, IN y1 double precision, OUT source integer,OUT geom Geometry) RETURNS SETOF record AS $BODY$ DECLARE source integer; BEGIN RETURN QUERY -- Find nearest node SELECT id,the_geom FROM jaen_vertices_pgr ORDER BY the_geom <-> ST_GeometryFromText('POINT('||x1||''||y1||' )',4326)LIMIT 1; END; $BODY$ LANGUAGE plpgsql VOLATILE STRICT; Thank you very much for your help.
    – user19566
    Feb 9 '15 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.