I am using pgr_dijkstra() to find the shortest path between origin and destination using a query like so:
CREATE OR REPLACE FUNCTION public.sp_od(
orig integer,
dest integer)
RETURNS TABLE(shortest_path geometry)
LANGUAGE 'sql'
AS $BODY$
SELECT st_makeline(geom) as shortest_path
FROM pgr_dijkstra(
'SELECT id, source, target, st_length(geom, true) as cost FROM public."WA_roads"',
(SELECT source FROM public."WA_roads"
ORDER BY ST_StartPoint(geom) <->
(select ST_SetSRID(ST_MakePoint(CAST(ocentx as double precision), CAST(ocenty as double precision)), 4326) from all_trips_non_zero where origin = orig LIMIT 1) ASC
LIMIT 1),
(SELECT source FROM public."WA_roads"
ORDER BY ST_StartPoint(geom) <->
(select ST_SetSRID(ST_MakePoint(CAST(dcentx as double precision), CAST(dcenty as double precision)), 4326) from all_trips_non_zero where destination = dest LIMIT 1) ASC
LIMIT 1), directed := false
) as pt
JOIN public."WA_roads" rd ON pt.edge = rd.id;
$BODY$;
However, the returned result is wacky, as in the geometries are not stitched in order. I tried using ST_Union instead of ST_Makeline to make the line, which results in the correct geometry. However, the returned geometry is a multilinestring which is not amenable to the rest of my analysis.
The question and my attempts are detailed here: [https://gis.stackexchange.com/questions/334302/pgr-dijkstra-gives-wacky-routes-sometimes-with-undirected-graph](https://gis.stackexchange.com/questions/334302/pgr-dijkstra-gives-wacky-routes-sometimes-with-undirected-graph)?
and [https://gis.stackexchange.com/questions/335892/combining-the-output-of-st-collect-st-union-to-create-linestring-from-multilines](https://gis.stackexchange.com/questions/335892/combining-the-output-of-st-collect-st-union-to-create-linestring-from-multilines)?
The only "solution" to the problem seems to be reversing the edges that are not in the correct direction, as described here: [https://lists.osgeo.org/pipermail/pgrouting-users/2010-April/000288.html](https://lists.osgeo.org/pipermail/pgrouting-users/2010-April/000288.html) but this doesnt seem trivial.
Any help in this regards is appreciated.
Thank you for your reply. Before I saw this, I had just come to the following solution involving a reversing logic when node <> source
select row_number() over (), st_makeline((case when node <> source then st_reverse(geom) else geom end)) FROM pgr_dijkstra(
‘SELECT id, source, target, st_length(geom) as cost FROM public.“WA_roads”’,
(SELECT source FROM public.“WA_roads”
ORDER BY ST_StartPoint(geom) ↔
(select ST_SetSRID(ST_MakePoint(CAST(ocentx as double precision), CAST(ocenty as double precision)), 4326) from all_trips_non_zero where origin = 98001 LIMIT 1) ASC
LIMIT 1),
(SELECT source FROM public.“WA_roads”
ORDER BY ST_StartPoint(geom) ↔
(select ST_SetSRID(ST_MakePoint(CAST(dcentx as double precision), CAST(dcenty as double precision)), 4326) from all_trips_non_zero where destination = 98647 LIMIT 1) ASC
LIMIT 1), directed := false
) as sp
JOIN public.“WA_roads” rd ON sp.edge= rd.id;
But I will go with the official solution. I feel though that this should be the standard way of making the line from shortest path geometries since one never knows in advance if and when the reversing will be needed.
By default pgRouting does not return the geometry. It isn’t even a requirement to have one, if you do a shortest path search.
So it’s a bit out of scope and lies in the user’s responsibility to care about this. And if the geometry is from source to target or the other way just depends on how the data was created.
However, it might be a good idea to give a hint in the documentation with a reference to the example in the workshop.