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.
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!