pgRouting – How to Get the Closest Point from a Table in PostGIS

pgroutingpostgis-2.0proximity

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?

Best Answer

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

Related Question