[pgrouting-users] trsp and shortest route order

Tao Romera Martinez taoromera at gmail.com
Tue Jun 4 20:32:07 PDT 2013


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



-- 
Tao Romera Martínez

---------------------------------------------------------
Tel: 080-6805-0945
Address: Koganei-shi, Tokyo, Japan

Look for me on Facebook and LinkedIn
---------------------------------------------------------


More information about the Pgrouting-users mailing list