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

[chintan pathak]

Dear Daniel,

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.

Thanks
Chintan Pathak


On Tue, Sep 17, 2019 at 12:42 AM Daniel Kastl <daniel at georepublic.de> wrote:

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



-- 


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/0c46acff/attachment-0001.html>