shortest_path with bus lines and time tables

Question

I'm looking for a solution since days but can't find a way to solve it.

My goal is to find the shortest way between 2 bus stops, based on the time the bus takes between them.

So I have bus lines, and time tables for each of them. The cost is represented by the difference of time between the actual bus stop and the next bus stop (in seconds). the source and targets are the ID's of the bus stops

The problem is: I have some parallel links because each bus do his line many times a day, running the same way everytime.

I've tried with pgrouting's shortest_path function, but itt returns many times a wrong solution because of parallel links.

I've seen about shooting_star, but I don't think I can use it in my case, without geometry.

I've got PostGreSQL 9.1.9 with PostGIS 2.0.1. Here are an example of my database extraction:

        id        |      idcourse     |    source    |    target    |    cost    |
        1         |           1       |       62     |      34      |      60    |
        2         |           1       |       34     |      16      |     360    |
        3         |           1       |       16     |      61      |      60    |
        4         |           1       |       61     |      60      |      120   |
        5         |           2       |       62     |      34      |      60    |

the last row here is the same bus line as others (with idcourse = 1) but one hour later

and here is the request to get this:

select hc.idhorairecourse as id, c.idcourse,
hc.idarret as source,
(select hc2.idarret from horairecourse hc2 where hc2.idcourse = c.idcourse and hc2.heure > hc.heure order by hc2.heure limit 1) as target,
(extract(epoch from ((select horairecourse.heure from horairecourse where horairecourse.idcourse = c.idcourse and horairecourse.heure > hc.heure order by horairecourse.heure limit 1) - hc.heure))) as cost
from course c
inner join horairecourse hc on c.idcourse = hc.idcourse
where (select horairecourse.idarret from horairecourse where horairecourse.idcourse = c.idcourse and horairecourse.heure > hc.heure order by horairecourse.heure limit 1) is not null
order by c.idcourse, hc.heure

Nothing in your question says that "parallel links" are relevant to your problem at all. Since no absolute timestamp is given, you only need *one* copy of each bus route. Just eliminate duplicates ... — Erwin Brandstetter, Jul 22 '13 at 20:03
No, because I have to know at what time people must take this bus to arrive on time at a specific bus stop... — theplayer777, Jul 22 '13 at 20:54

score 1 · Answer 1 · answered Jul 22 '13 at 20:53

The question of multiple instances for one bus line aside, this query with a rCTE (recursive Common Table Expression) solves the problem as stated:

My goal is to find the shortest way between 2 bus stops, based on the time the bus takes between them.

WITH RECURSIVE
   from_to AS (SELECT 34 AS _from, 60 AS _to)  -- insert from & to once

,  route AS (
   SELECT target, ARRAY[_from, target] AS stops, cost
       , (target = _to) AS arrived
   FROM   from_to, bus
   WHERE  source = _from

   UNION ALL
   SELECT b.target, r.stops || b.target, r.cost + b.cost
       , (b.target = _to) AS arrived
   FROM   from_to, route r
   JOIN   bus   b ON b.source = r.target
   WHERE  b.target <> ALL(stops) -- don't circle
   AND    r. target <> _to       -- we arrived
   )
SELECT stops, cost
FROM   route
WHERE  arrived
ORDER  BY cost
LIMIT  1;

-> SQLfiddle demo.

You can easily gather more information along the way.

The algorithm traverses each connection and checks if it has been there before (give up) or if it has arrived (succeed). Then the shortest route to success is picked.

This works fine for small to medium cardinality. It does not scale very well for big tables, though, since every possible route (without going in circles) is tried. The recursive CTE cannot check whether another route has succeeded in a shorter time already. A specialized algorithm could perform much better by eliminating all routes that have taken too long already. You could do that with a plpgsql function, but it would be much faster implemented in C.

yes your solution works great for your example, but I have more than 500 relations... is there any solution with shortest_path? — theplayer777, Jul 23 '13 at 08:42

shortest_path with bus lines and time tables

1 Answers1