Skip to content

Instantly share code, notes, and snippets.

@engelmav
Created November 22, 2012 16:39
Show Gist options
  • Save engelmav/4132050 to your computer and use it in GitHub Desktop.
Save engelmav/4132050 to your computer and use it in GitHub Desktop.
All 113 Buses (to PABT)
SELECT
BusRoute.route_id
,bustrip.trip_id
,bustrip.trip_headsign
,BusStopTime.arrival_time
,busstopTime.stop_sequence
,busstop.stop_name
--,BusTripShape.shape_pt_sequence
FROM routes BusRoute
LEFT JOIN trips BusTrip
ON BusRoute.route_id = BusTrip.route_id
LEFT JOIN stop_times BusStopTime
ON BusTrip.trip_id = BusStopTime.trip_id
LEFT JOIN stops BusStop
ON BusStopTime.stop_id = BusStop.stop_id
WHERE
BusTrip.direction_id = '0'
and
bustrip.trip_id IN (
SELECT
StopTime.trip_id
FROM stops StopName
LEFT JOIN stop_times StopTime
ON StopName.stop_id = StopTime.stop_id
WHERE
StopName.stop_name like '%AUTHORITY%')
AND
BusTrip.trip_headsign ~ '^113.*'
--AND BusStopTime.arrival_time ~ '^05.*'
ORDER BY
BusRoute.route_id
,BusTrip.trip_id
,BusStopTime.stop_sequence
ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment