First of all, a little background.

I work for a regional transit agency. We are making a "diagnostic" about our feeder bus service. We'd like to know what proportion of our users could take the bus to go to the train station instead of taking their car. It's been done several time in the pass, but we are now using gtfs as our main datasource so we have to rethink our methodology.

typical feeder bus service

To be considered "feeding" the train, a bus route must have a stop within a certain distance from a train station (red buffers). Also, synchronicity with the train service is very important because if your bus arrives at the train station half an hour before the train, the waiting time is too long and you'll want to sleep 20 more minutes in the morning and take your car.

Let say you take Line A (Blue) at stop 12. You step off the bus at stop 13. The bus arrives at stop 13, which is the stop to go to the Train Station #1 5 minutes before the train. That's very good. That would mean that everybody that takes that bus route at a stop from 1 to 13 included would arrive 5 minutes before that train.

Then, the train, going through a very densely populated area with lots a schools and crossings is forced to reduce its speed a lot. In the meantime, the bus picks up passengers at stop 14 to 17 and arrives at Train Station #2 10 minutes before that train. So passenger taking the bus at stops 14 to 17 would all have a waiting time of 10 minutes once arrived a the train station. So, along that bus line, passenger taking the bus at stop 1 to 13 have a waiting time of 5 minutes when those taking the bus at stops 14 to 17 have a waiting time of 10 minutes.

Line B, on the other side of the track, pass near Train Station #1, but its stops are too far to be considering "feeding" Train Station #1. It arrives at Train Station #2 7 minutes before the train (doest it for every train during the morning rush hour; it's very well synchronized). So passengers along Line B, taking the bus everywhere from stop 1 to 59 would have a waiting time of 7 minutes.

Now, my question. Once I have determined that stops LineA.13 and LineA.17 are feeding my train (it has been done spatially, in PostGIS), and that the waiting time when taking the bus at a stop before #13 is 5 minutes but those after have a waiting time of 10 minutes, how can I assign the waiting time to all the stop before them?

I'd like to do it in Postgres/PostGIS (pl/pgsql or pl/python), but I can use pure python (OS or arcpy) as well.

I could, I think, loop backward. So, once I found a stop that fits (here LineA.17), assign the same waiting time to stop 16, then 15... until I found another stop that fits my criteria (LineA.13) and then assign the the rest of the stops, the same waiting time as 13.

I have no idea how to create such a loop though. I don't think that I can do it in SQL so I'd have to use a procedural language in PostgreSQL.

I had an idea of using pgRouting to find the route between each feeder stops so that way, Line A would be split in two (stops 1 to 13 and then 13 to 17). Would that be easier?

The next step, will be to use pgRouting to calculate the driving time from all the stops that have a waiting time (sorry to LineA.18 and over!) and compare that with the schedule of the bus to calculate competitivity (is it taking 5 minutes more in bus that in car?)

Any ideas? I normally post a long work-in-progress script to show the effort I've made so far, but I'm stuck!

  • Due to other weird cases, I now consider cutting the routes where the passengers can get off the bus. So, each route "segments" will be independent. I still need to figure out how I can cut my shapes in PostGIS when my stops aren't topologically correct (the shapes follow the street and the stops are on the pole)... ;)
    – fgcarto
    Commented Oct 10, 2013 at 17:21

2 Answers 2


In last year's Google Summer of Code program a student implemented a pgRouting function for multi-modal routing. It didn't make it into the new 2.0 release, so it probably doesn't work right now, but you may want to take a look at the available resources to see if it's helpful or not:

It would be nice to get this function into the next release, so please contact the developer mailing list to coordinate the necessary work in case you're interested: http://pgrouting.org/support.html


Actually creating the loop you want is really easy with SQL:

SELECT DISTINCT ON (b1.line, b1.number) b1.line,b1.number,b2.waiting
FROM busstops AS b1
  LEFT JOIN busstops AS b2
    ON b1.line = b2.line
      AND b1.number<=b2.number
      AND b2.waiting IS NOT NULL
ORDER BY b1.line,b1.number,b2.number;


It'd also be easy to, let's say, sum transfer times from stop to stop.

And you could use regular pgRouting if only you manage to transform the routes into temporal graph (with nodes representing times of departure and time for route cost).

  • Woot! It works... There's still parts that I don't understand, but I get the idea... Could I be bold enough as to ask you could override the waiting time if a later stop have a smaller waiting time? The theory is that you could skip the first train station if you have to wait on the platform for 20 minutes and stay in the bus until Train station 2 if the wait is only 4 minutes... ;) Thanks a million times!!!
    – fgcarto
    Commented Oct 15, 2013 at 16:15
  • 1
    Just change the ORDER BY clause. The first two columns must stay since they're in the DISTINCT ON clause but otherwise than that all is allowed: sqlfiddle.com/#!1/24fab/2 Commented Oct 15, 2013 at 22:07
  • You are a wizard!! :) Thanks! I've been struggling with that for too long!!
    – fgcarto
    Commented Oct 16, 2013 at 14:46
  • Just make sure that 4 minutes ain't for a train that's an hour later :) Commented Oct 16, 2013 at 20:39
  • Nah! The maximal waiting time allowed is 30 minutes and even so, there's a big red symbol saying that's it's really not good! ;) Thanks again! Also, I've won my point with my boss that nobody is really going to stay on a bus maybe 20 minutes longer to go to a train station farther than the first one, even if the waiting time is really longer. ;)
    – fgcarto
    Commented Oct 17, 2013 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.