Skip to content

Instantly share code, notes, and snippets.

@engelmav

engelmav/buses_to_pabt.sql

Created November 22, 2012 16:28
Show Gist options
  • Save engelmav/4131983 to your computer and use it in GitHub Desktop.
Save engelmav/4131983 to your computer and use it in GitHub Desktop.
Download ZIP
Buses to Port Authority
Raw
buses_to_pabt.sql
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 = '1'
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
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