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

[Daniel Kastl]

Hi Chintan,

For the problem of the "flipped" edges, which could be the cause of your
problem, you can take a look at the workshop example:
https://workshop.pgrouting.org/2.5/en/chapters/SQLfunction.html#geometry-handling
In the example you see that the reverse geometry is taken, if the edge is
in the opposite direction.

I hope this helps,
Daniel


On Tue, Sep 17, 2019 at 4:18 PM chintan pathak <chintan.pathak at gmail.com>
wrote:

> 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>
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/pgrouting-users



-- 
Georepublic UG & Georepublic Japan
eMail: daniel.kastl at georepublic.de
Web: https://georepublic.info
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20190917/a654fcc7/attachment-0001.html>