Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
NJ Buses to Port Authority b/w and 8:15 w/ Lat/Lon
SELECT
BusRoute.route_id
,bustrip.trip_id
,bustrip.trip_headsign
,BusStopTime.arrival_time
,busstopTime.stop_sequence
,busstop.stop_name
,busstop.stop_lat
,busstop.stop_lon
,busstop.stop_id
,StopAddr.addr
--,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
LEFT JOIN stop_addr StopAddr
ON BusStop.stop_id = StopAddr.stop_id
WHERE
BusTrip.direction_id = '0' --this is outbound to NY
AND BusTrip.service_id = '9' --this is the weekday service
and
bustrip.trip_id IN (
SELECT trip_id FROM stop_times
WHERE
to_timestamp(arrival_time, 'HH24:MI:SS') < to_timestamp('08:15:00','HH24:MI:SS')
AND
to_timestamp(arrival_time, 'HH24:MI:SS') > to_timestamp('08:00:00','HH24:MI:SS')
AND stop_id = '3511' -- Port Authority
)
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