Efficient SQL Query for GTFS Departures in MySQL

How to combine scheduled and real-time data for departures in the next 24 hours

Key Highlights

Combine Static & Real-Time Data: Leverage joins between GTFS tables (stops, trips, stop_times, calendar/calendar_dates) and GTFS_RT tables (trip_updates, stop_time_updates, vehicles) to capture scheduled departure times enhanced by real-time updates.
Time Window and Service Validation: Filter departures based on the next 24 hours using both calendar validity and time arithmetic, handling GTFS times beyond the standard 24-hour range.
Optimized Performance: Index columns and use efficient time conversion strategies (e.g., seconds since midnight) while also incorporating composite indices to boost query performance.

Introduction

For any transit application involving scheduled journeys and real-time modifications, it is key to design a query that efficiently pulls all departures from a specific stop (in this case, stop_id "PR") within a defined future window. When dealing with both static GTFS data and dynamic GTFS real-time (GTFS_RT) components that follow protocol buffer specifications stored in MySQL, the query must not only join multiple tables but also ensure the accurate alignment of service dates and incorporate any delay or update information from real-time feeds.

The static GTFS data comprises tables that include stops, trips, services (depending on whether you use a separate services table or calendar/calendar_dates), and stop_times. The dynamic aspect is managed through tables such as trip_updates, stop_time_updates, and vehicles. Incorporating these will help ensure that the final departure times reflect both scheduled and updated information.

Understanding the Data Model

Static GTFS Data

In a typical GTFS setup, the core tables necessary for constructing a departure schedule are:

stops: Contains stop definitions, including a unique stop_id, stop_name, and geographic coordinates.
trips: Holds trip-level information, linking to routes and service definitions, and includes unique trip_id and trip_headsign attributes.
stop_times: Lists the scheduled arrival and departure times at each stop for a given trip. Time values might exceed 24:00:00.
calendar / calendar_dates: Define the running dates for each service. The calendar indicates the normal schedule (which days of the week a service runs), while calendar_dates provide exceptions (e.g., holiday adjustments).

The goal is to filter departures at a specific stop ("PR") that occur within a 24-hour window from the current time. This requires checking that the service for the trip is valid on the current date and that the departure time (even if it appears as a value above "24:00:00") falls within this range.

Dynamic GTFS_RT Data

The GTFS real-time database captures live updates, which are essential to account for delays or adjustments. These databases generally include:

trip_updates: Provides updates on any modifications to a trip's schedule, including delay information.
stop_time_updates: Contains updates for individual stop times, typically specifying a delay or revised time for a given stop sequence.
vehicles: (Optional and useful for tracking) details current vehicle status and location.

Incorporating GTFS_RT data ensures that if a vehicle is delayed or has a modified departure time, the query reflects this by joining the appropriate update fields, usually using the COALESCE function to replace null updates with the scheduled time.

Query Building Strategy

An efficient query design can be segmented into two primary parts:

Static Data Query: Construct a query to select all departures from stop "PR", validating that the service is active during the current day, and that the departure time falls into the next 24 hours, considering times over 24:00:00.
Integration of Real-Time Data: Extend the static query to join GTFS_RT tables. The real-time information is joined via trip_id and stop_sequence to update departure times using available delay values.

Step 1 – Handling the Time Window and Service Validity

Static Query for Scheduled Departures

The first part of the query must validate that the service is active by checking the current date against the start_date and end_date (if using the calendar table) or by checking specific day flags (monday, tuesday, etc.). It must also compute upcoming departures based on the current time.

A sample method involves converting the departure_time into a comparable format (such as seconds from midnight) to facilitate the handling of times above 24:00:00. For example, the SQL function UNIX_TIMESTAMP() can be concatenated with the current date to create a valid comparison between times.

Consider a query that extracts all departures for the current day and the following day within a 24-hour window:


-- Select static scheduled departures from stop "PR"
SELECT
    st.departure_time,
    r.route_long_name,
    t.trip_headsign,
    s.stop_name
FROM
    stop_times st
JOIN
    trips t ON st.trip_id = t.trip_id
JOIN
    stops s ON st.stop_id = s.stop_id
JOIN
    routes r ON t.route_id = r.route_id
JOIN
    calendar cal ON t.service_id = cal.service_id
WHERE
    s.stop_id = 'PR'
    AND cal.start_date <= CURDATE()
    AND cal.end_date >= CURDATE()
    -- Ensure the service is active on the current day (assuming day flags are used)
    AND (
         (cal.monday = 1 AND WEEKDAY(CURDATE()) = 0)
         OR (cal.tuesday = 1 AND WEEKDAY(CURDATE()) = 1)
         OR (cal.wednesday = 1 AND WEEKDAY(CURDATE()) = 2)
         OR (cal.thursday = 1 AND WEEKDAY(CURDATE()) = 3)
         OR (cal.friday = 1 AND WEEKDAY(CURDATE()) = 4)
         OR (cal.saturday = 1 AND WEEKDAY(CURDATE()) = 5)
         OR (cal.sunday = 1 AND WEEKDAY(CURDATE()) = 6)
    )
    -- Filter departures within the 24-hour window, taking into account times past 24:00:00
    AND (
         UNIX_TIMESTAMP(CONCAT(CURDATE(), ' ', st.departure_time))
         BETWEEN UNIX_TIMESTAMP(NOW()) AND UNIX_TIMESTAMP(NOW()) + 86400
         -- Include departures that might be in the early hours of the next day
         OR UNIX_TIMESTAMP(CONCAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), ' ', st.departure_time))
         BETWEEN UNIX_TIMESTAMP(NOW()) AND UNIX_TIMESTAMP(NOW()) + 86400
    )
ORDER BY st.departure_time;

This query performs the following:

Joins the static tables to retrieve the scheduled departure time, route and trip details, and the stop name.
Uses the calendar table to ensure that the trip is active on the current date, matching the correct day-of-week flag.
Applies UNIX_TIMESTAMP conversion to both the departure time (concatenated to the appropriate date) and the current time for proper 24-hour window filtering.
Orders the results by departure time to assist with further processing or display in an app.

Step 2 – Integrating GTFS-Realtime Updates

Merging Static and Real-Time Data

For many transit applications, acquiring only scheduled data is not enough; real-time updates are vital as they provide delays, cancellations, or modifications that occur after the static schedule is published. To incorporate this, you need to join GTFS_RT tables with the previously written query.

The updated query takes a LEFT JOIN approach with the GTFS_RT tables, ensuring that when no real-time update exists, the static schedule remains intact. The COALESCE() function is used to default delay to 0 if no update is present.

Below is an enhanced query that includes GTFS_RT, allowing you to compute updated departure times:


-- Select departures with real-time updates for stop "PR"
SELECT DISTINCT
    s.stop_id,
    t.trip_id,
    st.departure_time AS scheduled_departure,
    r.route_long_name,
    t.trip_headsign,
    s.stop_name,
    COALESCE(stu.departure_delay, 0) AS departure_delay,
    DATE_ADD(
        CONCAT(CURDATE(), ' ', st.departure_time),
        INTERVAL COALESCE(stu.departure_delay, 0) SECOND
    ) AS updated_departure_time
FROM
    stops s
JOIN
    stop_times st ON s.stop_id = st.stop_id
JOIN
    trips t ON st.trip_id = t.trip_id
JOIN
    routes r ON t.route_id = r.route_id
JOIN
    calendar cal ON t.service_id = cal.service_id
-- Incorporate calendar_dates if available (as an alternative or additional check)
LEFT JOIN
    calendar_dates cd ON t.service_id = cd.service_id
LEFT JOIN
    trip_updates tu ON t.trip_id = tu.trip_id
LEFT JOIN
    stop_time_updates stu ON tu.trip_update_id = stu.trip_update_id
         AND st.stop_sequence = stu.stop_sequence
WHERE
    s.stop_id = 'PR'
    AND cal.start_date <= CURDATE()
    AND cal.end_date >= CURDATE()
    AND (
         (cal.monday = 1 AND WEEKDAY(CURDATE()) = 0)
         OR (cal.tuesday = 1 AND WEEKDAY(CURDATE()) = 1)
         OR (cal.wednesday = 1 AND WEEKDAY(CURDATE()) = 2)
         OR (cal.thursday = 1 AND WEEKDAY(CURDATE()) = 3)
         OR (cal.friday = 1 AND WEEKDAY(CURDATE()) = 4)
         OR (cal.saturday = 1 AND WEEKDAY(CURDATE()) = 5)
         OR (cal.sunday = 1 AND WEEKDAY(CURDATE()) = 6)
         -- Optionally adjust based on calendar_dates exceptions as needed
    )
    AND (
         UNIX_TIMESTAMP(CONCAT(CURDATE(), ' ', st.departure_time))
         BETWEEN UNIX_TIMESTAMP(NOW()) AND UNIX_TIMESTAMP(NOW()) + 86400
         OR UNIX_TIMESTAMP(CONCAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), ' ', st.departure_time))
         BETWEEN UNIX_TIMESTAMP(NOW()) AND UNIX_TIMESTAMP(NOW()) + 86400
    )
ORDER BY updated_departure_time;

This revised query:

Remains primarily based on the static schedule by joining stops, stop_times, trips, routes, and calendars.
Employs LEFT JOINs to bring in trip updates and stop time updates so that any available delay is factored into the computed updated_departure_time.
Uses COALESCE() to safely default delays to 0 if there are no real-time updates.
Applies stringent filters based on the 24-hour window by converting the scheduled departure times with the applicable date.
Sorts by the updated departure time for proper chronological display.

Performance Optimization

Indexing and Query Efficiency

To ensure the query executes efficiently, especially with large datasets, careful planning of indexing is mandatory. Here are key recommendations:

Indexes on Key Columns: Ensure you have indexes on columns such as stop_id, trip_id, service_id, and departure_time. Composite indexes can be even more beneficial, for instance on (stop_id, departure_time) in the stop_times table.
Time Conversion Efficiency: When filtering on time, using functions like UNIX_TIMESTAMP or calculating seconds since midnight early in the query may improve performance by reducing string-based comparisons.
Use of Materialized Views: If the query is run repeatedly (for instance, in a transit app refreshing every minute), consider using a materialized view or a temporary table that pre-computes convergent metrics, and then update this cached query periodically.
Join Strategies: When joining a large number of tables, especially when merging static and real-time data, ensure that your join columns are indexed and use INNER or LEFT JOIN appropriately to prevent unnecessary data loads.

Handling GTFS Times That Exceed 24:00:00

Special Considerations

It is common in GTFS datasets to encounter times that exceed the normal 24-hour format (e.g., "25:30:00" meaning 1:30 AM the next day). Your query must account for these by concatenating the correct date component or converting the time appropriately.

One approach is to compute the total seconds of the time field. For example, if times go over 24 hours, a function that adjusts the date could be introduced. Although the example queries above use a straightforward concatenation method by switching dates, an alternative solution is to write a user-defined function (UDF) in MySQL to manage these conversions explicitly.

Integrating Both GTFS and GTFS_RT for a Unified Query

Finally, the entire process culminates in a unified solution where both scheduled and real-time data come together seamlessly. The following comprehensive query is designed to capture all departures for the next 24 hours for stop "PR". This example has been structured to be efficient, scalable, and flexible to different schema variations (i.e., the presence of either calendar or calendar_dates for service exceptions).

Combined Query Overview

The query below:

Starts by joining the fundamental GTFS tables (stops, stop_times, trips, routes, calendar).
Integrates real-time updates using LEFT JOINs with GTFS_RT tables (trip_updates, stop_time_updates) so that dynamic timing adjustments are applied.
Ensures that the service’s validity is checked against the current day, and optionally, calendar_dates may further refine this logic.
Filters the departures so that only entries in the next 24 hours are included by comparing both the scheduled departure and its adjusted time (with delay).
Orders the output by the updated departure time, which is essentially the time the user is expected to see.

Unified SQL Query Example


-- Comprehensive query for departures at stop "PR" within the next 24 hours
SELECT DISTINCT
    s.stop_id,
    t.trip_id,
    st.departure_time AS scheduled_departure,
    r.route_long_name,
    t.trip_headsign,
    s.stop_name,
    COALESCE(stu.departure_delay, 0) AS departure_delay,
    DATE_ADD(
        -- Assuming departure time is tied to the current date and possibly next day for times > "24:00:00"
        IF(
            UNIX_TIMESTAMP(CONCAT(CURDATE(), ' ', st.departure_time)) > UNIX_TIMESTAMP(NOW()),
            CONCAT(CURDATE(), ' ', st.departure_time),
            CONCAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), ' ', st.departure_time)
        ),
        INTERVAL COALESCE(stu.departure_delay, 0) SECOND
    ) AS updated_departure_time
FROM
    stops s
JOIN
    stop_times st ON s.stop_id = st.stop_id
JOIN
    trips t ON st.trip_id = t.trip_id
JOIN
    routes r ON t.route_id = r.route_id
JOIN
    calendar cal ON t.service_id = cal.service_id
LEFT JOIN
    calendar_dates cd ON t.service_id = cd.service_id
LEFT JOIN
    trip_updates tu ON t.trip_id = tu.trip_id
LEFT JOIN
    stop_time_updates stu ON tu.trip_update_id = stu.trip_update_id
         AND st.stop_sequence = stu.stop_sequence
WHERE
    s.stop_id = 'PR'
    AND cal.start_date <= CURDATE()
    AND cal.end_date >= CURDATE()
    AND (
         (cal.monday = 1 AND WEEKDAY(CURDATE()) = 0)
         OR (cal.tuesday = 1 AND WEEKDAY(CURDATE()) = 1)
         OR (cal.wednesday = 1 AND WEEKDAY(CURDATE()) = 2)
         OR (cal.thursday = 1 AND WEEKDAY(CURDATE()) = 3)
         OR (cal.friday = 1 AND WEEKDAY(CURDATE()) = 4)
         OR (cal.saturday = 1 AND WEEKDAY(CURDATE()) = 5)
         OR (cal.sunday = 1 AND WEEKDAY(CURDATE()) = 6)
    )
    AND (
         /* Check if scheduled departure falls within next 24 hours */
         UNIX_TIMESTAMP(
           IF(
             UNIX_TIMESTAMP(CONCAT(CURDATE(), ' ', st.departure_time)) > UNIX_TIMESTAMP(NOW()),
             CONCAT(CURDATE(), ' ', st.departure_time),
             CONCAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), ' ', st.departure_time)
           )
         ) BETWEEN UNIX_TIMESTAMP(NOW()) AND UNIX_TIMESTAMP(NOW()) + 86400
    )
ORDER BY updated_departure_time;

The logic in the IF() clause takes advantage of concatenating the departure_time with the correct date based on whether the scheduled time has already passed for today. It further adjusts the final departure time by adding any delay reported by GTFS_RT data.

Additional Considerations

Dealing with Complex Real-Time Scenarios

In scenarios where real-time data is extremely dynamic, you might need to refresh your query results frequently or incorporate caching strategies. Furthermore, if the GTFS_RT source reports cancellations or service disruptions, additional logic might be necessary to filter out cancelled trips or alert users accordingly.

Sample Table of Key Fields

Here is a concise overview of the key fields utilized in the query:

Column	Description
st.departure_time	Scheduled departure time from the stop
r.route_long_name	Human-readable route name
t.trip_headsign	Destination or headsign for the trip
stu.departure_delay	Real-time delay in seconds reported by GTFS_RT
updated_departure_time	Computed departure time incorporating any delay

Conclusion

To summarize, constructing a highly efficient query for all departures in the next 24 hours for stop_id "PR" in a MySQL GTFS database involves several deliberate steps:

Integrate the core static GTFS tables (stops, trips, stop_times, calendar/calendar_dates, and routes) to determine the scheduled departures based on valid service dates and current day's filters.
Adapt time comparisons by employing functions like UNIX_TIMESTAMP, ensuring that departures reported in times beyond 24:00:00 are correctly adjusted to the proper date.
Incorporate GTFS_RT data via LEFT JOINs with trip_updates and stop_time_updates to update departure times in real time, handling missing values with COALESCE.
Optimize overall database performance through strategic indexing (especially on key columns and composite indexes) and, if necessary, utilize caching mechanisms to speed up repeated queries.

The final unified query presented above meets these requirements, effectively returning a dynamic list of departures, along with any applicable delays. With careful consideration of data model intricacies and time complexities, the provided solution should prove robust and scalable for a variety of transit applications.

References

TransitWiki - Community-based Transit Knowledge
GTFS Static Reference - Google Developers
GTFS-Realtime Specification