# Query shortest path from coordinates with pgrouting

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? Commented Nov 9, 2019 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; Commented Nov 9, 2019 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 Commented Nov 9, 2019 at 18:16
• 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. Commented Nov 11, 2019 at 6:34

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 !! Commented Nov 17, 2019 at 20:01