1

I'm creating a table route_<route_id> for the route given by pgr_dijkstra, like this. In my application, I have a websocket with a mobile client that it's sending it's current position every x seconds. How can I find the nearest node from the table route_<route_id> and return a subset of the route, from the current position to the destination?

The SQL I'm using to generate the route table is:

CREATE TABLE route_route_id as SELECT seq, cost, 
             length::double precision, geom, 
             ST_AsGeoJSON(ST_LineMerge(geom))::json->'coordinates' as coordinates, 
             ST_AsGeoJSON(geom) 
             FROM pgr_dijkstra('
                SELECT gid AS id, source::integer, 
                       target::integer, length AS cost 
                           FROM "ways",
                               convert_to_lon_lat(lat, long, '"ways_vertices_pgr"'), 
                               convert_to_lon_lat(lat, long, '"ways_vertices_pgr"'), 
                               false, false) a LEFT JOIN ways b ON (a.id2 = b.gid);
1

It seems that

SELECT seq from ways order by geom <-> ST_SetSrid(ST_MakePoint(lat, long), 4326) limit 1;

did the trick

  • It should be ST_MakePoint(long, lat) – Mike T Feb 14 '17 at 2:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.