Skip to content

Instantly share code, notes, and snippets.

@luqmaan

luqmaan/active_trips_for_route.csv

Last active August 29, 2015 14:02
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save luqmaan/5ee4d3087fd6ec74ae17 to your computer and use it in GitHub Desktop.
Save luqmaan/5ee4d3087fd6ec74ae17 to your computer and use it in GitHub Desktop.
Download ZIP
Find the active trips for a route using CapMetro's Austin GTFS data. Unfortunately the GTFS doesn't seem to have hour precision, so many shapes are returned for a route that changes shapes throughout the day, e.g. the MetroRail.
Raw
active_trips_for_route.csv
shape_id shapes_count trip_id route_id trip_headsign shape_id shape_pt_lat shape_pt_lon shape_pt_sequence shape_dist_traveled route_id service_id trip_id trip_headsign trip_short_name direction_id block_id shape_id trip_type bikes_allowed wheelchair_accessible service_id:1 monday tuesday wednesday thursday friday saturday sunday start_date end_date
31794 1223 1303263 550 SOUTHBOUND 31794 30.264702 -97.737972 1223 <null> 550 1133 1303263 SOUTHBOUND <null> 0 550-30 31794 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31795 1223 1303278 550 SOUTHBOUND 31795 30.264702 -97.737972 1223 <null> 550 1133 1303278 SOUTHBOUND <null> 0 550-03 31795 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31807 1223 1303388 550 NORTHBOUND 31807 30.586524 -97.85580299999999 1223 <null> 550 1133 1303388 NORTHBOUND <null> 1 550-03 31807 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31808 1223 1303389 550 NORTHBOUND 31808 30.586524 -97.85580299999999 1223 <null> 550 1133 1303389 NORTHBOUND <null> 1 550-70 31808 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31797 964 1303271 550 SOUTHBOUND 31797 30.264702 -97.737972 964 <null> 550 1133 1303271 SOUTHBOUND <null> 0 550-03 31797 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31803 964 1303383 550 NORTHBOUND 31803 30.481022 -97.786478 964 <null> 550 1133 1303383 NORTHBOUND <null> 1 550-01 31803 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31804 695 1303380 550 NORTHBOUND 31804 30.443857 -97.70929700000001 695 <null> 550 1133 1303380 NORTHBOUND <null> 1 550-03 31804 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31790 661 1303277 550 SOUTHBOUND 31790 30.264702 -97.737972 661 <null> 550 1133 1303277 SOUTHBOUND <null> 0 550-70 31790 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31792 661 1303267 550 SOUTHBOUND 31792 30.264702 -97.737972 661 <null> 550 1133 1303267 SOUTHBOUND <null> 0 550-01 31792 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31805 661 1303370 550 NORTHBOUND 31805 30.440436 -97.701258 661 <null> 550 1133 1303370 NORTHBOUND <null> 1 550-30 31805 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31796 560 1303276 550 SOUTHBOUND 31796 30.44053 -97.701369 560 <null> 550 1133 1303276 SOUTHBOUND <null> 0 550-70 31796 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31809 560 1303371 550 NORTHBOUND 31809 30.586387 -97.855746 560 <null> 550 1133 1303371 NORTHBOUND <null> 1 550-30 31809 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31802 529 1303272 550 SOUTHBOUND 31802 30.264702 -97.737972 529 <null> 550 1133 1303272 SOUTHBOUND <null> 0 550-71 31802 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31813 529 1303377 550 NORTHBOUND 31813 30.396729 -97.715287 529 <null> 550 1133 1303377 NORTHBOUND <null> 1 550-30 31813 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31806 520 1303372 550 NORTHBOUND 31806 30.392779 -97.716419 520 <null> 550 1133 1303372 NORTHBOUND <null> 1 550-03 31806 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31791 469 1303274 550 SOUTHBOUND 31791 30.280164 -97.70890199999999 469 <null> 550 1133 1303274 SOUTHBOUND <null> 0 550-01 31791 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31798 303 1303279 550 SOUTHBOUND 31798 30.44053 -97.701369 303 <null> 550 1133 1303279 SOUTHBOUND <null> 0 550-03 31798 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31810 303 1303391 550 NORTHBOUND 31810 30.481022 -97.786478 303 <null> 550 1133 1303391 NORTHBOUND <null> 1 550-03 31810 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31801 192 1303275 550 SOUTHBOUND 31801 30.264702 -97.737972 192 <null> 550 1133 1303275 SOUTHBOUND <null> 0 550-01 31801 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
31811 141 1303373 550 NORTHBOUND 31811 30.440436 -97.701258 141 <null> 550 1133 1303373 NORTHBOUND <null> 1 550-03 31811 <null> 0 1 1133 1 1 1 1 0 0 0 2014-01-26 2014-06-07
Raw
asdf.sql
SELECT shapes.shape_id, count(*) as shapes_count, trip_id, route_id, trip_headsign, *
FROM shapes,
(
SELECT * FROM trips, calendar
WHERE route_id = 550
AND calendar.service_id = trips.service_id
AND calendar.tuesday = 1
GROUP BY shape_id
) as trips
WHERE shapes.shape_id = trips.shape_id
GROUP BY shapes.shape_id
ORDER BY shapes_count DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment