[pgrouting-users] Regarding wrong stitching of pgr_dijkstra() output

[chintan pathak]

 Hi,

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_pathFROM 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 ptJOIN 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?

and 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
 but this doesnt seem trivial.

Any help in this regards is appreciated.

Thanks

-- 


Chintan Pathak
[image: https://]about.me/chintanpathak
<https://about.me/chintanpathak?promo=email_sig&utm_source=email_sig&utm_medium=email_sig&utm_campaign=external_links>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20190917/a9ffcaec/attachment.html>