It uses a pretty simple SQL statement:
SELECT gtfs_stops.stop_name, gtfs_stops.stop_lat, gtfs_stops.stop_lon,
gtfs_stop_times.departure_time, gtfs_stop_times.trip_id, gtfs_stop_times.stop_headsign,
gtfs_trips.route_id, gtfs_trips.block_id FROM gtfs_trips
LEFT JOIN gtfs_stop_times ON gtfs_trips.trip_id = gtfs_stop_times.trip_id
LEFT JOIN gtfs_stops on gtfs_stops.stop_id = gtfs_stop_times.stop_id
LEFT JOIN gtfs_calendar ON gtfs_trips.service_id = gtfs_calendar.service_id
WHERE route_id = '(route number)'
AND departure_time >= CURTIME()
AND departure_time <= ADDTIME(CURTIME(),'0:05:00') AND gtfs_calendar.(day of the week) = 1 AND start_date <= CURDATE() AND end_date >= CURDATE()
GROUP BY block_id;
This SQL statement will show the next timepoints for (route number) on the the specified (day of the week). Yes, I know it's pretty crude, but it works.
You can play with this toy at:
http://st.recnet.com/metro_next_bus.php
There is a pull down that shows all of the SFV sector routes (the descriptions were manually entered), an issue that I have with the routes.txt file. But you can send a value of rte and a valid route_id using a GET statement and it will return routes outside the SFV. It does not work for rail (801-805) because the Metro Rail trips are showin without a block_id. Rail will return just a single trip on the route.
I could use an SQL sort to rearrange the headsigns in order so you have an idea what direction the buses are going. I would have preferred if Metro was to include the direction_id in trips.txt which is a simple 0 or 1 value that would indicate a direction. In this case, I could just make two SQL calls and get each direction listed separately. Using direction_id would also make the data compatible with other tools including TriMet's Time Table Maker.
Without the direction_id data, I would have to manually build a table based on headsign readings what direction a bus is going and even with that, there may be a few possibilities that buses going in two different directions can have the same headsign (such as a mid-route terminal).
This little app does take into consideration that some trips only operate certain days of the week (school trippers) but I do not believe that the calendar.txt gives the ranges of the school year. Therefore, there's a possibility that it will display a trip that may not actually be running since it's a school holiday. MTA also has school holiday trips "H", which may be the school trip with some deviation on the schedule. These are very complex, but on some routes with infrequent service and school trippers (645 comes to mind) could cause confusion. Right now, I am not calling to the calendar_dates file to get the exceptions (holiday service). Therefore, transit holidays right now will show just like regular weekdays.
With that said, my wish list for Metro GTFS data improvements include:
- Having the block_id be the known line-run numbers instead of a distinct number.
- Include run numbers for the trains in block_id.
- Use the direction_id field in trips.txt.
- Move headsign data to trips.txt and only use stop_times.txt when there is a mid-line sign change. (such as the 242/243)
- At the end of a trip at the last timepoint in stop_times.txt, set drop_off_type to 1. This will indicate the end of the route. It will also prevent apps from identifying that timepoint as a pick-up and showing two times at a particular stop (one for when the bus arrives from the previous trip and the time it departs for the next trip, hence, recovery time).
- Better descriptions of the routes in routes.txt
