# Getting closest point from table in PostGIS?

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?

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. Dec 19, 2014 at 12:50

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