3

I have a table of pgrouting nodes, structured like this:

CREATE TABLE ways_vertices_pgr
(
  id bigserial NOT NULL,
  cnt integer,
  chk integer,
  ein integer,
  eout integer,
  the_geom geometry(Point,4326),
  CONSTRAINT ways_vertices_pgr_pkey PRIMARY KEY (id)
)

PGrouting requires the node id, before it can route in-between two locations. My Input are two points(i.e. with Lat & long).

How do I get the node id from the Lat-long? How do I select the closest node?

1

I found this link: http://www.sqlexamples.info/SPAT/postgis_nearest_point.htm

based on which, I created the following function:

CREATE OR REPLACE FUNCTION get_nearest_node
(IN x_long double precision, IN y_lat double precision) -- input parameters
RETURNS TABLE -- structure of output
(
  node_id bigint ,
  dist integer -- distance to the nearest station
) AS $$

BEGIN

RETURN QUERY

  SELECT id as node_id,
     CAST
     (st_distance_sphere(the_geom, st_setsrid(st_makepoint(x_long,y_lat),4326)) AS INT)
     AS d
  FROM ways_vertices_pgr
  ORDER BY the_geom <-> st_setsrid(st_makepoint(x_long, y_lat), 4326)
  LIMIT 1;

  -- geometric operator <-> means "distance between"

END;
$$ LANGUAGE plpgsql;

This function gives me the node id & the distance

  • It should be noted that this function does give the closest node, which might not be what you want. You might want to get the closest segment, and the closest node on that segment. But that is an answer for a different question. – Devdatta Tengshe Dec 19 '14 at 12:50
1

And you can find a similar example including a shortest path query in the pgRouting workshop: http://workshop.pgrouting.org/chapters/wrapper.html#route-between-lat-lon-points-and-return-ordered-geometry-with-heading

Taken from within the function this is similar to yours without the distance:

SELECT id FROM ways_vertices_pgr 
    ORDER BY the_geom <-> ST_GeometryFromText('POINT(x y)',4326) LIMIT 1;

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.