Query shortest path from coordinates with pgrouting

Question

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;

Answer 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