1

How do I calculate the path, pass coordinates and, if possible, in the return function, have the route coordinate list? (I'm trying to plot the route with the ipyleaflet library in a jupyter notebook, and I need the return of the route's coordinate list. I'm new to this and I don't know how to do it)

I tried with an example query that I searched for in a book but I got an empty answer

SELECT r.seq, r.node, w.name As street, w.x1, w.x2, r.cost::numeric(10,4), ( SUM(w.length_m) OVER(ORDER BY r.seq) )::numeric(10,2) As dist_m 
FROM pgr_dijkstra('SELECT gid AS id, source, target, cost, reverse_cost FROM ways', (SELECT id FROM ways_vertices_pgr ORDER BY the_geom <-> ST_SetSRID( ST_Point(-3.7482, -38.5486), 4326) limit 1 ) , (SELECT id FROM ways_vertices_pgr 
ORDER BY the_geom <-> ST_SetSRID( ST_Point(-3.7532, -38.5170), 4326) limit 1 ), true) AS r 
LEFT JOIN ways AS w ON r.edge = w.gid;
  • Have you tried anything? – nmtoken Nov 9 '19 at 7:59
  • yes, I tried with an example query that I searched for in a book. SELECT r.seq, r.node, w.name As street, w.x1, w.x2, r.cost::numeric(10,4), ( SUM(w.length_m) OVER(ORDER BY r.seq) )::numeric(10,2) As dist_m FROM pgr_dijkstra('SELECT gid AS id, source, target, cost, reverse_cost FROM ways', (SELECT id FROM ways_vertices_pgr ORDER BY the_geom <-> ST_SetSRID( ST_Point(-3.7482, -38.5486), 4326) limit 1 ) , (SELECT id FROM ways_vertices_pgr ORDER BY the_geom <-> ST_SetSRID( ST_Point(-3.7532, -38.5170), 4326) limit 1 ), true) AS r LEFT JOIN ways AS w ON r.edge = w.gid; – Henrique Nov 9 '19 at 18:07
  • What was the result of the query and how does it differ from what you expected. Are you wanting to run the SQL query as part of the Python code? Please edit the question to add more detail – nmtoken Nov 9 '19 at 18:16
  • I got an empty answer. ok i will add more information in the question – Henrique Nov 9 '19 at 19:11
  • There can be a lot of hickups. Is pgrouting extension correctly installed and enabled? Maybe your library silently ignore SQL errors and return just empty response instead of them. Pgrouting (and routing in general) also expect properly noded network dataset (junction on each intersection, start/end node ID on each segment and so on). So is your dataset a proper network? At last, it can also be a logical error in your SQL. – DavidP Nov 11 '19 at 6:34
1

Try this query if it works

     with source_tmp as (SELECT source 
            FROM ways order by st_distance(the_geom, ST_SetSRID(ST_MakePoint(-38.5486,-3.7482), 4326)) limit 1),
    target_tmp as (SELECT target 
            FROM ways order by st_distance(the_geom, ST_SetSRID(ST_MakePoint(-38.5170,-3.7532), 4326)) limit 1)

SELECT * 
FROM pgr_dijkstra('SELECT gid AS id, source, target, cost, reverse_cost FROM ways', (SELECT source from source_tmp) , 
                  (SELECT target from  target_tmp), true) AS r 
LEFT JOIN ways AS w ON r.edge = w.gid;

What I did here is selected source and target initially using lat long, you have to reverse the order in your st_makepoint function. Then I can the query

  • Yes, it worked!! thanks @Arun !! – Henrique Nov 17 '19 at 20:01

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.