Skip to content

Latest commit

 

History

History
264 lines (229 loc) · 26.2 KB

Data_Dictionary.md

File metadata and controls

264 lines (229 loc) · 26.2 KB
Raw

LAMP Data Exports

Access instructions for all LAMP public data exports are available at https://performancedata.mbta.com.

LAMP currently produces the following sets of public data exports:

Subway Performance Data

Each row represents a unique trip_id-stop_id pair for each service_date of revenue rail service.

field name type description source
service_date int64 equivalent to GTFS-RT start_date value in Trip Descriptor as int instead of string GTFS-RT
start_time int64 equivalent to GTFS-RT start_time value in Trip Descriptor converted to seconds after midnight GTFS-RT
route_id string equivalent to GTFS-RT route_id value in Trip Descriptor GTFS-RT
branch_route_id string equivalent to GTFS-RT route_id value in Trip Descriptor for lines with multiple routes, NULL if line has single route, e.g. Green-B for Green-B route, NULL for Blue route. EXCEPTION: LAMP Inserts Red-A or Red-B to indicate Red-line Ashmont or Braintree branch if trip stops at station south of JFK/UMass. GTFS-RT
trunk_route_id string line if multiple routes exist on line, otherwise route_id, e.g. Green for Green-B route, Blue for Blue route GTFS-RT
trip_id string equivalent to GTFS-RT trip_id value in Trip Descriptor GTFS-RT
direction_id bool equivalent to GTFS-RT direction_id value in Trip Descriptor as bool instead of int GTFS-RT
direction string equivalent to GTFS direction value from directions.txt for route_id-direction_id pair GTFS
direction_destination string equivalent to GTFS direction_destination value from directions.txt for route_id-direction_id pair GTFS
stop_count int16 number of stops recorded on trip LAMP Calculated
vehicle_id string equivalent to GTFS-RT id value in VehicleDescriptor GTFS-RT
vehicle_label string equivalent to GTFS-RT label value in VehicleDescriptor. GTFS-RT
vehicle_consist string Pipe separated concatenation of multi_carriage_details labels in CarriageDetails GTFS-RT
stop_id string equivalent to GTFS-RT stop_id value in VehiclePosition GTFS-RT
parent_station string stop_name of the parent_station associated with the stop_id from stops.txt GTFS
stop_sequence int16 equivalent to GTFS-RT current_stop_sequence value in VehiclePosition GTFS-RT
move_timestamp int64 earliest "IN_TRANSIT_TO" or "INCOMING_AT" status timestamp for a trip-stop pair from GTFS-RT VehiclePosition GTFS-RT
stop_timestamp int64 earliest "STOPPED_AT" status timestamp for a trip-stop pair from GTFS-RT VehiclePosition or last arrival timestamp from GTFS-RT StopTimeUpdate if VehiclePosition value is not available GTFS-RT
travel_time_seconds int64 seconds the vehicle spent traveling to the stop_id of trip-stop pair from previous stop_id on trip LAMP Calculated
dwell_time_seconds int64 seconds the vehicle spent stopped at stop_id of trip-stop pair LAMP Calculated
headway_branch_seconds int64 seconds between consecutive vehicles departing parent_station on branch_route_id LAMP Calculated
headway_trunk_seconds int64 seconds between consecutive vehicles departing parent_station on trunk_route_id LAMP Calculated
scheduled_arrival_time int64 arrival_time of this trip-stop pair at stop_id for matched planned trip from stop_times.txt GTFS
scheduled_departure_time int64 departure_time of this trip-stop pair at stop_id for matched planned trip from stop_times.txt GTFS
scheduled_travel_time int64 planned seconds a vehicle spent traveling to the stop_id of trip-stop pair from previous stop_id on trip, derived from from stop_times.txt LAMP Calculated
scheduled_headway_branch int64 planned seconds between consecutive vehicles departing parent_station on branch_route_id, derived from from stop_times.txt LAMP Calculated
scheduled_headway_trunk int64 planned seconds between consecutive vehicles departing parent_station on trunk_route_id, derived from from stop_times.txt LAMP Calculated

OPMI Tableau Exports

The following LAMP data exports are used by OPMI for Tableau dashboarding:

LAMP_ALL_RT_fields

Each row represents a unique trip_id-stop_id pair for each service_date of rail service.

field name type description source
service_date date equivalent to GTFS-RT start_date value in Trip Descriptor as date instead of string GTFS-RT
start_datetime datetime equivalent to GTFS-RT start_time added to start_date from Trip Descriptor LAMP Calculated
static_start_datetime datetime equivalent to start_datetime if planned trip, otherwise GTFS-RT start_date added to static_start_time LAMP Calculated
stop_sequence int16 equivalent to GTFS-RT current_stop_sequence value in VehiclePosition GTFS-RT
canonical_stop_sequence int16 stop sequence based on "canonical" route trip as defined in route_patterns.txt table LAMP Calculated
previous_canonical_stop_sequence int16 canonical_stop_sequence for previous stop on trip LAMP Calculated
sync_stop_sequence int16 stop sequence that is consistent across all branches of a trunk_route_id for a particular parent_station LAMP Calculated
previous_sync_stop_sequence int16 sync_stop_sequence for previous stop on trip LAMP Calculated
stop_id string equivalent to GTFS-RT stop_id value in VehiclePosition GTFS-RT
previous_stop_id string stop_id for previous stop on trip GTFS-RT
parent_station string stop_name of the parent_station associated with the stop_id from stops.txt GTFS
previous_parent_station string parent_station for previous stop on trip GTFS
stop_name string equivalent to GTFS stop_name from stops.txt for stop_id GTFS
previous_stop_name string stop_name for previous stop on trip GTFS
previous_stop_departure_datetime datetime earliest "IN_TRANSIT_TO" OR "INCOMING_AT" status timestamp for a trip-stop pair from GTFS-RT VehiclePosition as an Eastern datetime GTFS-RT
stop_arrival_datetime datetime earliest "STOPPED_AT" status timestamp for a trip-stop pair from GTFS-RT VehiclePosition or last arrival timestamp from GTFS-RT StopTimeUpdate if VehiclePosition value is not available as an Eastern datetime GTFS-RT
stop_departure_datetime datetime equivalent to previous_stop_departure_datetime for next stop on trip GTFS-RT
previous_stop_departure_sec int64 previous_stop_departure_datetime as seconds after midnight LAMP Calculated
stop_arrival_sec int64 stop_arrival_datetime as seconds after midnight LAMP Calculated
stop_departure_sec int64 stop_departure_datetime as seconds after midnight LAMP Calculated
is_revenue boolen equivalent to MBTA GTFS-RT revenue value in Trip Descriptor as only bool GTFS-RT
direction_id int8 equivalent to GTFS-RT direction_id value in Trip Descriptor GTFS-RT
route_id string equivalent to GTFS-RT route_id value in Trip Descriptor GTFS-RT
branch_route_id string equivalent to GTFS-RT route_id value in Trip Descriptor for lines with multiple routes, NULL if line has single route, e.g. Green-B for Green-B route, NULL for Blue route_id. EXCEPTION: LAMP Inserts Red-A or Red-B to indicate Red-line Ashmont or Braintree branch if trip stops at station south of JFK/UMass. GTFS-RT
trunk_route_id string line if multiple routes exist on line, otherwise route_id, e.g. Green for Green-B route, Blue for Blue route GTFS-RT
start_time int64 equivalent to GTFS-RT start_time value in Trip Descriptor converted to seconds after midnight GTFS-RT
vehicle_id string equivalent to GTFS-RT id value in VehicleDescriptor GTFS-RT
stop_count int16 number of stops recorded on trip LAMP Calculated
trip_id string equivalent to GTFS-RT trip_id value in Trip Descriptor GTFS-RT
vehicle_label string equivalent to GTFS-RT label value in VehicleDescriptor. GTFS-RT
vehicle_consist string Pipe separated concatenation of multi_carriage_details labels in CarriageDetails GTFS-RT
direction string equivalent to GTFS direction value from directions.txt for route_id-direction_id pair GTFS
direction_destination string equivalent to GTFS direction_destination value from directions.txt for route_id-direction_id pair GTFS
static_trip_id_guess string GTFS trip_id from trips.txt, will match GTFS-RT trip_id if trip is not ADDED, if trip is ADDED will be closest matching GTFS trip_id based on start_time LAMP Calculated
static_start_time int64 earliest arrival_time from stop_times.txt for static_trip_id_guess GTFS
static_stop_count int64 planned stop count from stop_times.txt of static_trip_id_guess trip GTFS
exact_static_trip_match bool false if trip_id is unplanned, otherwise true LAMP Calculated
static_version_key int64 internal LAMP foreign-key linking real-time events to static tables in Database Schema LAMP Calculated
travel_time_seconds int64 seconds the vehicle spent traveling to the stop_id of trip-stop pair from previous stop_id on trip LAMP Calculated
dwell_time_seconds int64 seconds the vehicle spent stopped at stop_id of trip-stop pair LAMP Calculated
headway_branch_seconds int64 seconds between consecutive vehicles departing parent_station on branch_route_id LAMP Calculated
headway_trunk_seconds int64 seconds between consecutive vehicles departing parent_station on trunk_route_id LAMP Calculated

LAMP_service_id_by_date_and_route

LAMP calculated dataset containing planned route_id and service_id combinations for each service_date in GTFS Schedules.

field name type description
pk_id int64 LAMP primary key
route_id string route_id from routes.txt
service_id string service_id from trips.txt
service_date int64 date of service as int in "YYYYMMDD" format
service_date_calc date date of service
static_version_key int64 key used to link GTFS static schedule versions between tables

LAMP_static_calendar_dates

field name type description
pk_id int64 LAMP primary key
service_id string service_id from calendar_dates.txt
date int64 date from calendar_dates.txt as int in "YYYYMMDD" format
calendar_date date date from calendar_dates.txt
exception_type int8 exception_type from calendar_dates.txt
holiday_name string holiday_name from calendar_dates.txt
static_version_key int64 key used to link GTFS static schedule versions between tables

LAMP_static_calendar

field name type description
pk_id int64 LAMP primary key
service_id string service_id from calendar.txt
monday bool monday from calendar.txt as bool
tuesday bool tuesday from calendar.txt as bool
wednesday bool wednesday from calendar.txt as bool
thursday bool thursday from calendar.txt as bool
friday bool friday from calendar.txt as bool
saturday bool saturday from calendar.txt as bool
sunday bool sunday from calendar.txt as bool
start_date date start_date from calendar.txt
end_date date end_date from calendar.txt
static_version_key int64 key used to link GTFS static schedule versions between tables

LAMP_static_feed_info

field name type description
pk_id int64 LAMP primary key
feed_start_date date feed_start_date from feed_info.txt
feed_end_date date feed_end_date from feed_info.txt
feed_version string feed_version from feed_info.txt
feed_active_date date date extracted from feed_version
static_version_key int64 key used to link GTFS static schedule versions between tables

LAMP_static_routes

field name type description
pk_id int64 LAMP primary key
route_id string route_id from routes.txt
agency_id int8 agency_id from routes.txt
route_short_name string route_short_name from routes.txt
route_long_name string route_long_name from routes.txt
route_desc string route_desc from routes.txt
route_type int8 route_type from routes.txt
route_sort_order int32 route_sort_order from routes.txt
route_fare_class string route_fare_class from routes.txt
line_id string line_id from routes.txt
static_version_key int64 key used to link GTFS static schedule versions between tables

LAMP_static_stop_times

field name type description
pk_id int64 LAMP primary key
trip_id string trip_id from stop_times.txt
arrival_time int32 arrival_time from stop_times.txt as seconds after midnight
departure_time int32 departure_time from stop_times.txt as seconds after midnight
schedule_travel_time_seconds int64 (calculated) planned seconds the vehicle spent traveling to the stop_id of trip-stop pair from previous stop_id on trip
schedule_headway_branch_seconds int64 (calculated) planned seconds between consecutive vehicles departing stop_id on branch_route_id
schedule_headway_trunk_seconds int64 (calculated) planned seconds between consecutive vehicles departing stop_id on trunk_route_id
stop_id string stop_id from stop_times.txt
stop_sequence int16 stop_sequence from stop_times.txt
static_version_key int64 key used to link GTFS static schedule versions between tables

LAMP_static_stops

field name type description
pk_id int64 LAMP primary key
stop_id string stop_id from stops.txt
stop_name string stop_name from stops.txt
stop_desc string stop_desc from stops.txt
platform_code string platform_code from stops.txt
platform_name string platform_name from stops.txt
parent_station string parent_station from stops.txt
static_version_key int64 key used to link GTFS static schedule versions between tables

LAMP_static_trips

field name type description
pk_id int64 LAMP primary key
route_id string route_id from trips.txt
branch_route_id string route_id for lines with multiple routes, NULL if line has single route, e.g. Green-B for Green-B route, NULL for Blue route. EXCEPTION: LAMP Inserts Red-A or Red-B to indicate Red-line Ashmont or Braintree branch if trip stops at station south of JFK/UMass.
trunk_route_id string line if multiple routes exist on line, otherwise route_id, e.g. Green for Green-B route, Blue for Blue route_id
service_id string service_id from trips.txt
trip_id string trip_id from trips.txt
direction_id int8 direction_id from trips.txt
block_id string block_id from trips.txt
static_version_key int64 key used to link GTFS static schedule versions between tables

LAMP_RT_ALERTS

The MBTA GTFS Realtime Alerts feed is archived in this dataset.

Each row of this dataset represents an entry from the informed_entity and active_period fields of the Alert message being exploded.

In generating this dataset, translation string fields contain only the English translation. All timestamp fields are in POSIX Time, the integer number of seconds since 1 January 1970 00:00:00 UTC. These are converted to datetimes in are Eastern Standard Time for user convenience.

field name type description
id int64 Unique identifier for this Alert. Subsequent updates to it will have the same ID.
cause string Equivalent to cause in GTFS-RT Alert message.
cause_detail string Equivalent to cause_detail in GTFS-RT Alert message.
effect string Equivalent to effect in GTFS-RT Alert message.
effect_detail string Equivalent to effect_detail in GTFS-RT Alert message.
severity_level string Equivalent to severity_level in GTFS-RT Alert message.
severity int8 Equivalent to Alert-severity in MBTA Enhance GTFS-RT Message.
alert_lifecycle string Equivalent to Alert-alert_lifecycle in MBTA Enhance GTFS-RT Message.
duration_certainty string Equivalent to Alert-duration_certainty in MBTA Enhance GTFS-RT Message.
header_text.translation.text string Equivalent to header_text[n][text] in Alert message where n is the index of the English Translation.
description_text.translation.text string Equivalent to description_text[n][text] in Alert message where n is the index of the English Translation.
service_effect_text.translation.text string Equivalent to Alert-service_effect_text[n][text] in MBTA Enhance GTFS-RT Message where n is the index of the English Translation.
timeframe_text.translation.text string Equivalent to Alert-timeframe_text[n][text] in MBTA Enhance GTFS-RT Message where n is the index of the English Translation.
recurrence_text.translation.text string Equivalent to Alert-recurrence_text[n][text] in MBTA Enhance GTFS-RT Message where n is the index of the English Translation.
created_timestamp uint64 Equivalent to Alert-created_timestamp in MBTA Enhance GTFS-RT Message.
created_datetime datetime created_timestamp as EST Datetime.
last_modified_timestamp uint64 Equivalent to Alert-last_modified_timestamp in MBTA Enhance GTFS-RT Message.
last_modified_datetime datetime last_modified_timestamp as EST Datetime.
last_push_notification_timestamp uint64 Equivalent to Alert-last_push_notification_timestamp in MBTA Enhance GTFS-RT Message.
last_push_notification_datetime datetime last_push_notification_timestamp as EST Datetime.
closed_timestamp uint64 Equivalent to Alert-closed_timestamp in MBTA Enhance GTFS-RT Message.
closed_datetime datetime closed_timestamp as EST Datetime.
active_period.start_datetime datetime Equivalent to active_period[n][start] in Alert message as EST Datetime. A record is produced for every index n.
active_period.start_timestamp uint64 Equivalent to active_period[n][start] in Alert message as POSIX Timestamp. A record is produced for every index n.
active_period.end_datetime datetime Equivalent to active_period[n][end] in Alert message as EST Datetime. A record is produced for every index n.
active_period.end_timestamp uint64 Equivalent to active_period[n][end] in Alert message as POSIX Timestamp. A record is produced for every index n.
informed_entity.route_id string Equivalent to informed_entity[n][route_id] in Alert. A record is produced for every index n.
informed_entity.route_type int8 Equivalent to informed_entity[n][route_type] in Alert. A record is produced for every index n.
informed_entity.direction_id int8 Equivalent to informed_entity[n][direction_id] in Alert. A record is produced for every index n.
informed_entity.stop_id string Equivalent to informed_entity[n][stop_id] in Alert. A record is produced for every index n.
informed_entity.facility_id string Equivalent to informed_entity[n][faciliy_id] in Alert. A record is produced for every index n.
informed_entity.activities string Equivalent to informed_entity[n][activities] as a | delimitated string. All potential values are defined in the Activity enum.