GTFS Database - SQL Queries for "Revenue Mileage" and "Revenue Hours"

Question

I'm trying to find the number of revenue miles/kilometers of a "Route" by: day, month, and year; by querying a GTFS database that has the structure described here:

https://developers.google.com/transit/gtfs/reference

And see here for a very clear sketch of the structure:

http://blog.openplans.org/2012/08/the-openplans-guide-to-gtfs-data/

"Revenue distance traveled" definition:

("Available for passengers to use" distance)

The number of miles/kilometers traveled from the first actual bus stop where a passenger can board, to the last drop-off at the last bus stop, for that particular route and bus run. (then aggregated together for all service runs taken by all buses for that particular route)

-

"Revenue hours" definition:

("Available for passengers to use" time span)

The number of hours from the moment the vehicle arrives at the first bus stop, until the moment it drops off its last passenger at the last bus stop. (then aggregated together for all service runs taken by all buses for that particular route)

I'm using SQL Server/MSSQL. Though SQL Lite, or MySQL, or any SQL examples would be perfectly fine.

Basically, I need to be able to SELECT a route, and then correlate the data in the routes, calendar_dates, calendar, stop-times, stops, and trips tables to find how many miles/kilometers were covered from the first stop (stop_times and stops tables) to the last, how many hours elapsed, and find this for a particular service_id (in trips and calendar tables), and then also for all service_ids for a particular route, and be able to get all this for a particular date (in calendar_dates table), or spans of dates (day, month, 3-month period, year, etc).

If a few different queries are needed, that's fine. The revenue distance traveled per route, and the revenue hours per route, can be separate queries.

Has anyone who has done this before be willing to share their query structure for this or has anyone figured this out? Are there any examples of how to write this query? I've been looking everywhere online for weeks.

Here is a diagram image of the database I have created with all relationships shown in detail:

Answer 1

I have done exactly this for scheduled kms, by:

1. Loading GTFS into a DB via GTFS SQL importer and PostGIS
2. Making the shape table spatial
3. Calculate distance for each shape
4. Aggregate as below (see note on service id).

---

select t.route_id as id, r.route_short_name as route, sum(l.shape_dist/1000) as sched_kms 
from gtfs_shape_lengths l

inner join gtfs_trips t on t.shape_id = l.shape_id
inner join gtfs_routes r on r.route_id = t.route_id
inner join gtfs_calendar c on t.service_id = c.service_id

where c.service_id ilike '%sat%'

group by t.route_id, r.route_short_name

union all

select 'total' as id, 'total_' as name,
sum(l.shape_dist/1000) as sched_kms

from gtfs_shape_lengths l

inner join gtfs_trips t on t.shape_id = l.shape_id
inner join gtfs_calendar c on t.service_id = c.service_id

where c.service_id ilike '%sat%'

order by sched_kms desc

Original writeup here: http://transitdata.net/using-gtfs-and-postgis-to-calculate-levels-of-scheduled-service/

Answer 2

Alright, I have come up with the following for getting the Service Hours. In my example, the arrival_time and departure_time columns in the stop_times table are an integer datatype with the number data stored representing "minutes since midnight" (e.g. "29 hours and 45 minutes since midnight" would be "1785 minutes"... Midnight being measured from noon on the service day, minus 12 hours—as the spec requires. Which is also the best way to do it). Also note: I added the column trip_date to the trips table because I'm using this GTFS database for operational/internal Federal reporting uses, and not just for service feeds to the public; so it's necessary to know the individual trip date (and I don't want to make an entry for each day in calendar_dates for this purpose, as some agencies do). This example is for MSSQL/SQL Server:

-- FIRST/LAST TRIPS OF THE DAY AND SPAN OF SERVICE

SELECT

    joinedTables.service_id                  AS 'Service Number',
    joinedTables.trip_date                   AS 'Date',
    joinedTables.route_id                    AS 'Route',

    MIN ( joinedTables.starting_departure )  AS 'First Departure in Minutes',
    MAX ( joinedTables.ending_arrival )      AS 'Last Departure in Minutes',

    -- Decimal hours of minutes integers.
    CAST (
                 (
                       (
                             MAX (ending_arrival) - MIN (starting_departure)
                       ) / 60.00
                 ) AS DECIMAL (9, 2)
         )                                 AS 'Service Hours'


FROM
    (
        SELECT
            SelectedTripsColumns.service_id,
            SelectedTripsColumns.trip_id,
            SelectedTripsColumns.route_id,
            SelectedTripsColumns.trip_date,
            MIN (departure_time) AS starting_departure,
            MAX (arrival_time) AS ending_arrival

        FROM
            stop_times AS stopTimesTable

        JOIN (
                 SELECT
                     service_id,
                     trip_id,
                     route_id,
                     trip_date
                 FROM
                     trips
             ) AS SelectedTripsColumns

        ON stopTimesTable.trip_id = SelectedTripsColumns.trip_id


        JOIN routes

        ON SelectedTripsColumns.route_id = routes.route_id


        GROUP BY
            SelectedTripsColumns.service_id,
            SelectedTripsColumns.trip_id,
            SelectedTripsColumns.route_id,
            SelectedTripsColumns.trip_date

    ) AS joinedTables

-- WHERE trip_date = '2015-07-27'

GROUP BY
    service_id,
    route_id,
    trip_date

ORDER BY
    service_id,
    route_id,
    trip_date;