Trips Database

Note: live_date corresponds to the date for which the corresponding GTFS version went active. For example, at the TTC (Toronto), stop/route/service changes are made every board period, for which a corresponding GTFS is generated.

Version:
version_id: int
name: string -- ie. Nov18Jan5BoardPeriod
start_date: DateTime
end_date: DateTime
gtfs_link: String

Route:
route_id: string
name: string
version_id: int

RouteStop:
route_id: string
stop_id: string
version_id: int

Stops:
agency: string
stop_id: string
stop_short_name: string
stop_long_name: string
location: coordinate
arrivals: [TripStops]
version_id: int

TripStops:
trip_stop_id: string
stop_id: string
intersection_id: string
arrival: trip
departure: trip
skipped: bool
rule: int -- version of rules used to generate these from Trips

(only do when it's needed - write S3 trips for now)
Trips:
trip_id: string
location: coordinate
route: string
pattern: string
heading: string
agency: string
time: timestamp
vehicle_id: string
source: string ("nextbus", "avl", etc.)

Usage

StopReliability

(in-progress) Given: stop_id, routes, time interval Return: relevant TripStops Note: can only encompass one version

API

(Currently not scheduled to complete) Given: requested_time, routes Return: trips grouped by vehicle_id with difference between time and requested_time minimized

Trips DB:

• cost of about $200/month (have to justify somehow)
• reasons where it would be needed are a) used for playback (has to be fast, but client could cache 1-2 hrs) b) need live trips (ie. wait-times/slowdowns/travel-times are shown live, rather than waiting for trip to end)