Getting closest point from table in PostGIS?

Question

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?

Answer 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

Answer 2

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;