|
|
- Tuesday, January 13
- ===================
-
- rtd / bus schedules / transit data
- ----------------------------------
-
- I'm taking the bus today, so I got to thinking about bus schedules. I use
- Google Calendar a little bit (out of habit and convenience more than any
- particular love), and I was thinking "why doesn't my calendar just know the
- times of transit routes I use?"
-
- I thought maybe there'd be, say, iCal (CalDAV? What is actually the thing?)
- data somewhere for a given RTD schedule, or failing that, maybe JSON or TSV or
- something. A cursory search doesn't turn up much, but I did find these:
-
- * http://www.rtd-denver.com/Developer.shtml
- * https://developers.google.com/transit/gtfs/reference?csw=1
- * http://www.rtd-denver.com/GoogleFeeder/
- * http://www.rtd-denver.com/GoogleFeeder/google_transit_Jan15_Runboard.zip
-
- I grabbed that last one.
-
- brennen@desiderata 16:16:43 /home/brennen ★ mkdir rtd && mv google_transit_Jan15_Runboard.zip rtd
- brennen@desiderata 16:16:51 /home/brennen ★ cd rtd
- brennen@desiderata 16:16:53 /home/brennen/rtd ★ unzip google_transit_Jan15_Runboard.zip
- Archive: google_transit_Jan15_Runboard.zip
- inflating: calendar.txt
- inflating: calendar_dates.txt
- inflating: agency.txt
- inflating: shapes.txt
- inflating: stop_times.txt
- inflating: trips.txt
- inflating: stops.txt
- inflating: routes.txt
-
- Ok, so this is pretty minimalist CSV stuff from the look of most of it.
-
- brennen@desiderata 16:22:12 /home/brennen/rtd ★ grep Lyons stops.txt
- 20921,Lyons PnR,Vehicles Travelling East, 40.223979,-105.270174,,,0
-
- So it looks like stops have an individual id?
-
- brennen@desiderata 16:24:41 /home/brennen/rtd ★ grep '20921' ./*.txt | wc -l
- 87
-
- A lot of this is noise, but:
-
- brennen@desiderata 16:26:23 /home/brennen/rtd ★ grep 20921 ./stop_times.txt
- 8711507,12:52:00,12:52:00,20921,43,,1,0,
- 8711508,11:32:00,11:32:00,20921,43,,1,0,
- 8711509,07:55:00,07:55:00,20921,43,,1,0,
- 8711512,16:41:00,16:41:00,20921,43,,1,0,
- 8711519,05:37:00,05:37:00,20921,3,,0,1,
- 8711517,16:47:00,16:47:00,20921,1,,0,1,
- 8711511,17:58:00,17:58:00,20921,43,,1,0,
- 8711514,13:02:00,13:02:00,20921,1,,0,1,
- 8711516,07:59:00,07:59:00,20921,1,,0,1,
- 8711515,11:42:00,11:42:00,20921,1,,0,1,
- 8711510,19:10:00,19:10:00,20921,43,,1,0,
- 8711513,18:05:00,18:05:00,20921,1,,0,1,
- 8711518,06:47:00,06:47:00,20921,1,,0,1,
- brennen@desiderata 16:26:57 /home/brennen/rtd ★ head -1 stop_times.txt
- trip_id,arrival_time,departure_time,stop_id,stop_sequence,stop_headsign,pickup_type,drop_off_type,shape_dist_traveled
-
- So:
-
- brennen@desiderata 16:41:47 /home/brennen/code/rtd-tools (master) ★ grep ',20921,' ./stop_times.txt | cut -d, -f1,3 | sort -n
- 8711507,12:52:00
- 8711508,11:32:00
- 8711509,07:55:00
- 8711510,19:10:00
- 8711511,17:58:00
- 8711512,16:41:00
- 8711513,18:05:00
- 8711514,13:02:00
- 8711515,11:42:00
- 8711516,07:59:00
- 8711517,16:47:00
- 8711518,06:47:00
- 8711519,05:37:00
-
- That first number is a `trip_id`, the second one departure time. Trips
- are provided in `trips.txt`:
-
- brennen@desiderata 16:54:56 /home/brennen/code/rtd-tools (master) ★ head -2 trips.txt
- route_id,service_id,trip_id,trip_headsign,direction_id,block_id,shape_id
- 0,SA,8690507,Union Station,0, 0 2,793219
-
- I don't usually use `join` very much, but this seems like a logical place for
- it. It turns out that `join` wants its input sorted on the join field, so I do
- this:
-
- brennen@desiderata 16:54:38 /home/brennen/code/rtd-tools (master) ★ sort -t, -k1 stop_times.txt > stop_times.sorted.txt
- brennen@desiderata 16:54:38 /home/brennen/code/rtd-tools (master) ★ sort -t, -k3 trips.txt > trips.sorted.txt
-
- And then:
-
- brennen@desiderata 16:51:07 /home/brennen/code/rtd-tools (master) ★ join -t, -1 1 -2 3 ./stop_times.sorted.txt ./trips.sorted.txt | grep 20921
- ,Y,WK,Lyons PnR,0, Y 16,79481043,,1,0,
- ,Y,WK,Lyons PnR,0, Y 16,79481043,,1,0,
- ,Y,WK,Lyons PnR,0, Y 15,79481043,,1,0,
- ,Y,WK,Lyons PnR,0, Y 41,79480943,,1,0,
- ,Y,WK,Lyons PnR,0, Y 41,79481043,,1,0,
- ,Y,WK,Lyons PnR,0, Y 41,79481043,,1,0,
- ,Y,WK,Boulder Transit Center,1, Y 41,794814
- ,Y,WK,Boulder Transit Center,1, Y 16,794812
- ,Y,WK,Boulder Transit Center,1, Y 16,794814
- ,Y,WK,Boulder Transit Center,1, Y 15,794812
- ,Y,WK,Boulder Transit Center,1, Y 41,794813
- ,Y,WK,Boulder Transit Center,1, Y 15,794813
- ,Y,WK,Boulder Transit Center,1, 206 1,794816
-
- Ok, waitasec. What the fuck is going on here? The string `20921` appears
- nowhere in these lines. It takes me too long to figure out that the
- text files have CRLF line-endings and this is messing with something in
- the chain (probably just output from `grep`, since it's obviously
- finding the string). So:
-
- brennen@desiderata 16:59:35 /home/brennen/code/rtd-tools (master) ★ dos2unix *.sorted.txt
- dos2unix: converting file stop_times.sorted.txt to Unix format ...
- dos2unix: converting file trips.sorted.txt to Unix format ...
-
- Why does `dos2unix` operate in-place on files instead of printing to STDOUT?
- It beats me, but I sure am glad I didn't run it on anything especially
- breakable. It _does_ do what you'd expect when piped to, anyway, which is
- probably what I should have done.
-
- So this seems to work:
-
- brennen@desiderata 17:04:45 /home/brennen/code/rtd-tools (master) ★ join -t, -1 1 -2 3 ./stop_times.sorted.txt ./trips.sorted.txt | grep 20921
- 8711507,12:52:00,12:52:00,20921,43,,1,0,,Y,WK,Lyons PnR,0, Y 16,794810
- 8711508,11:32:00,11:32:00,20921,43,,1,0,,Y,WK,Lyons PnR,0, Y 16,794810
- 8711509,07:55:00,07:55:00,20921,43,,1,0,,Y,WK,Lyons PnR,0, Y 15,794810
- 8711510,19:10:00,19:10:00,20921,43,,1,0,,Y,WK,Lyons PnR,0, Y 41,794809
- 8711511,17:58:00,17:58:00,20921,43,,1,0,,Y,WK,Lyons PnR,0, Y 41,794810
- 8711512,16:41:00,16:41:00,20921,43,,1,0,,Y,WK,Lyons PnR,0, Y 41,794810
- 8711513,18:05:00,18:05:00,20921,1,,0,1,,Y,WK,Boulder Transit Center,1, Y 41,794814
- 8711514,13:02:00,13:02:00,20921,1,,0,1,,Y,WK,Boulder Transit Center,1, Y 16,794812
- 8711515,11:42:00,11:42:00,20921,1,,0,1,,Y,WK,Boulder Transit Center,1, Y 16,794814
- 8711516,07:59:00,07:59:00,20921,1,,0,1,,Y,WK,Boulder Transit Center,1, Y 15,794812
- 8711517,16:47:00,16:47:00,20921,1,,0,1,,Y,WK,Boulder Transit Center,1, Y 41,794813
- 8711518,06:47:00,06:47:00,20921,1,,0,1,,Y,WK,Boulder Transit Center,1, Y 15,794813
- 8711519,05:37:00,05:37:00,20921,3,,0,1,,Y,WK,Boulder Transit Center,1, 206 1,794816
-
- Which seems kind of right for the [South][southbound] &
- [Northbound][northbound] schedules, but they're weirdly intermingled. I think
- this pulls departure time and a `direction_id` field:
-
- brennen@desiderata 17:15:12 /home/brennen/code/rtd-tools (master) ★ join -t, -1 1 -2 3 ./stop_times.sorted.txt ./trips.sorted.txt | grep 20921 | cut -d, -f3,13 | sort -n
- 05:37:00,1
- 06:47:00,1
- 07:55:00,0
- 07:59:00,1
- 11:32:00,0
- 11:42:00,1
- 12:52:00,0
- 13:02:00,1
- 16:41:00,0
- 16:47:00,1
- 17:58:00,0
- 18:05:00,1
- 19:10:00,0
-
- So southbound, I guess:
-
- brennen@desiderata 17:15:59 /home/brennen/code/rtd-tools (master) ★ join -t, -1 1 -2 3 ./stop_times.sorted.txt ./trips.sorted.txt | grep 20921 | cut -d, -f3,13 | grep ',1' | sort -n
- 05:37:00,1
- 06:47:00,1
- 07:59:00,1
- 11:42:00,1
- 13:02:00,1
- 16:47:00,1
- 18:05:00,1
-
- This should probably be where I think oh, right, this is a Google spec - maybe
- there's [already some tooling](https://github.com/google/transitfeed). Failing
- that, slurping them into SQLite or something would be a lot less painful. Or
- at least using csvkit.
-
- [southbound]: http://www3.rtd-denver.com/schedules/getSchedule.action?runboardId=151&routeId=Y&routeType=12&direction=S-Bound&serviceType=3
- [northbound]: http://www3.rtd-denver.com/schedules/getSchedule.action?runboardId=151&routeId=Y&routeType=12&direction=N-Bound&serviceType=3
|
