[pgrouting-users] trsp and shortest route order

[Stephen Woodbridge]

Hi Tao,

Yes, I have just run into this problem myself wit other non pgrouting 
code tht I moved from pg8.3 to pg9.2 and started getting back results in 
different order which was causing issues for me.

Regarding trsp, in the 2.0 we have changed all the results from all the 
functions to be something like:

seq | id1 | id2 | cost

where seq can be used to maintain the order of the results if you join 
them. This is more convenient that having to add the:

    WITH route AS (SELECT ROW_NUMBER() OVER (PARTITION BY 1) as rank, ...

This is a tip for others on using pgr-1.x

Thank you for reporting the problem and the solution.

Best,
   -Steve

On 6/4/2013 11:32 PM, Tao Romera Martinez wrote:
> Hi Steve,
>
> Normally trsp gives back the shortest route as an ordered list of
> ways, let's say A, B, C, D, E.
> The problem is trsp was giving back, for a specific set of data, the
> shortest route in a random order: C, D, A, E, B.
>
> I found out what was causing this problem, so I post it here to help
> people with the same problem.
> The problem was not with trsp, but with postgresql.
> This was my original SQL query:
>
> SELECT * FROM turn_restrict_shortest_path('#{query}',#{source_id},
> #{target_id}, true, true, '')) JOIN #{table_name} ON edge_id = id;
>
> The problem was caused by the JOIN operation. Without the JOIN, the
> route was in the correct order, but the JOIN altered it. I'm not an
> SQL expert, so I don't know why this was happening in only this case.
> The original query above was working fine with the other tables.
>
> Anyway, this is what I did to solve the problem:
>
> WITH route AS (SELECT ROW_NUMBER() OVER (PARTITION BY 1) as rank, *
> FROM turn_restrict_shortest_path('#{query}',#{source_id},
> #{target_id}, true, true, '')) SELECT * FROM route JOIN #{table_name}
> ON edge_id = id ORDER BY RANK;
>
> I created a column with the number of the row, and reordered it back
> after the JOIN. I read somewhere that this fix was used to solve a
> similar problem (sorry, don't have the link).
>
> Best regards,
>
> Tao
>
> 2013/6/5 Stephen Woodbridge <woodbri at swoodbridge.com>:
>> On 6/4/2013 10:42 AM, Tao Romera Martinez wrote:
>>>
>>> Dear all,
>>>
>>> We have been using the trsp function for a while now, and yesterday we
>>> had strange results for the first time: the shortest route given as
>>> result was in total disorder.
>>
>>
>> Hi Tao,
>>
>> Can you explain more exactly what you mean by "result was in total
>> disorder"?
>>
>> I hear something is not as you would expect it, but I'm not sure exactly
>> what is wrong.
>>
>> Thanks,
>>    -Steve
>>
>>> We work with the OpenStreeMap dataset for the whole planet, but we
>>> have it split in countries. Thus, we have a table with the routable
>>> network for France, one for Japan, etc. trsp has been working nicely
>>> for a bunch of countries, but yesterday someone tried our app
>>> (SweetWay, for iPhone, as a matter of fact) from Bahrain for the first
>>> time, and by checking the results I realized the route was in total
>>> disorder. I was puzzled, because the function has worked without
>>> problems so far in many other countries.
>>>
>>> Has anyone experienced this problem before? I made some research with
>>> Google, but couldn't find anything relevant.
>>>
>>> Thanks for your help,
>>>
>>> Tao
>>>
>>> --
>>> Tao Romera Martínez
>>>
>>> ---------------------------------------------------------
>>> Tel: 080-6805-0945
>>> Address: Koganei-shi, Tokyo, Japan
>>>
>>> Look for me on Facebook and LinkedIn
>>> ---------------------------------------------------------
>>> _______________________________________________
>>> Pgrouting-users mailing list
>>> Pgrouting-users at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>>>
>>
>> _______________________________________________
>> Pgrouting-users mailing list
>> Pgrouting-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>
>
>