Skip to content

Instantly share code, notes, and snippets.

@engelmav

engelmav/buses_to_pabt.sql

Created November 22, 2012 16:28
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save engelmav/4131983 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save engelmav/4131983 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @invalid-email-address Anonymous created this gist Nov 22, 2012.
    34 changes: 34 additions & 0 deletions buses_to_pabt.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,34 @@
    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