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