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

Daniel Kastl daniel at georepublic.de
Tue Sep 17 15:38:10 PDT 2019


Hi Chintan,

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.

Best regards,
Daniel


On Wed, Sep 18, 2019 at 3:36 AM chintan pathak <chintan.pathak at gmail.com>
wrote:

> 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>
> _______________________________________________
> 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/20190918/218cbbfc/attachment-0001.html>


More information about the Pgrouting-users mailing list