[pgrouting-users] Reg: Get nearest node in osm data

[Stephen Woodbridge]

On 6/23/2015 3:56 PM, Manikanta Kondeti wrote:
> Hi,
>
>   I am writing a small application to get the route on the map. I am
> using openlayers.js on client and pgrouting as a backend routing library
> for my project. When a click is triggered I get a lat, lon, and I have
> the osm data in postgres which is loaded using osm2pgrouting. I'll pass
> this lat,lon to server and  I have to find the nearest node id from this
> lat,lon. Can I query the database and get it done?  Help me out.

Probably you want to do this in two steps:

1. find the nearest edge
2. find the nearest node along the edge

Anyway your queries will be something like:

-- get the closest edge
select * from edges where st_dwithin(geom, st_setsrid(st_makepoint(long, 
lat), 4326), maxdist) order by st_distance(geom, 
st_setsrid(st_makepoint(long, lat), 4326)) asc limit 1;

-- get closest node along edge
select case when st_line_locate_point(geom,st_setsrid(st_makepoint(long, 
lat), 4326))  < 0.5 then source else target from edges edge_id=id



-- or search to just get the closest node
select * from nodes where st_dwithin(geom, st_setsrid(st_makepoint(long, 
lat), 4326), maxdist) order by st_distance(geom, 
st_setsrid(st_makepoint(long, lat), 4326)) asc limit 1;


where:

maxdist - maximum distance to search for geometries in db units probably 
degrees

long, lat - values for the location in question

id - is the id of the closest edge found in the first query