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

Manikanta_Kondeti · June 23, 2015, 7:56pm

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.

Thank you,
Mani

Stephen_Woodbridge1 · June 23, 2015, 8:25pm

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

Manikanta_Kondeti · June 23, 2015, 9:03pm

Hi Steve,

Thanks a lot for your response.

Screen Shot 2015-06-24 at 2.30.06 AM.png

Screen Shot 2015-06-24 at 2.33.45 AM.png

···

On Wed, Jun 24, 2015 at 1:55 AM, Stephen Woodbridge <woodbri@swoodbridge.com> wrote:

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:

find the nearest edge

find the nearest node along the edge

Anyway your queries will be something like:

This is working perfect and found the nearest edge.

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

This is giving me syntax error. I’ve added the screenshot

– 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

This is not giving me any nearest nodes. I’ve this screenshot as well.

– 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

Let me know if I miss something.

Thanks,
Mani

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

Manikanta_Kondeti · June 23, 2015, 11:32pm

Sorted it.

After small correction:
select case when st_line_locate_point(the_geom,st_setsrid(st_makepoint(67.338263167225, -17.1189713223), 4326)) < 0.5 then (source) else (target) END AS node FROM ways WHERE gid=400280;