Running Boards

boards GTFS running

This SQL will allow you to make a running board of a particular block.

SELECT route_id, gtfs_trips.trip_id, departure_time, stop_name, stop_headsign
FROM gtfs_trips
LEFT JOIN gtfs_stop_times ON gtfs_stop_times.trip_id = gtfs_trips.trip_id
LEFT JOIN gtfs_stops ON gtfs_stops.stop_id = gtfs_stop_times.stop_id
WHERE block_id = {block_id}
GROUP BY gtfs_trips.trip_id
ORDER BY departure_time, stop_sequence;
On my original application "Where's the Bus", if you now click on the block ID, it will return the running board for a particular bus.  The running board is the overall assignment for a particular piece of equipment from the time it pulls out of the garage (Division) to the time it pulls in.  It's an interesting way to see how Metro schedules their buses.  Over the past decade, Metro has been doing a lot more of scheduling between different lines.
This task also gave me a little more understanding why Metro did the block_id's the way they did.  This is because they did a very smart thing. They put the schedule data of the current schedules as well as the schedules for the next shakeup (schedule change) in July.  In order to do this, the trip_id and block_id's had to be distinctive. 
My idea to Metro is to still use the line and run numbers but to put a static value as the first digit of the block_id.  For example in the January shake up,  Line 150 bus run 12 could be encoded as 2150012 and then in the next shakeup, it can be 3150012.  Just my suggestion here. 
I do see some bugs in my program where it comes to handling of school trippers (same bus run doing different things different days).. this could be fixed with bringing the calendar functionality in. More fun to come!