Skip to content

Data_Dictionary.md

Latest commit

 

History

History
342 lines (303 loc) · 52.9 KB

Data_Dictionary.md

File metadata and controls

342 lines (303 loc) · 52.9 KB
Raw

LAMP Data Exports

LAMP provides public data exports to support transit performance analysis.
Access instructions for all LAMP public data exports
Some LAMP data exports are used by the MBTA departments Operations Analytics and OPMI for Tableau dashboarding.

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

Below are the following OPMI Tableau Exports.

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 boolean 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.

LAMP_Bus_Events

LAMP_ALL_Bus_Events & LAMP_RECENT_Bus_Events have the same data dictionary.
Each row represents a unique trip_id-stop_sequence-vehicle_label combination for each service_date of bus service.
The bus data incorporates an additional data source: TransitMaster. Buses have TransitMaster devices to keep track of their location.

Column Name Arrow type Is primary key Is nullable Definition Constraints
trip_id large_string true false trip_id
stop_id large_string false false stop_id
route_id large_string true false route_id
gtfs_stop_sequence int64 false true stop_sequence
checkpoint_id large_string false true checkpoint_id
block_id large_string false true block_id
service_id large_string false true service_id
route_pattern_id large_string false true route_pattern_id
route_pattern_typicality int64 false true route_pattern_typicality
direction_id int8 false true direction_id
direction large_string false true Human readable direction of travel for the trip.
direction_destination large_string false true Terminus of trip.
stop_name large_string false true stop_name
plan_stop_count uint32 false true Total number of scheduled stops for trip.
plan_start_time int64 false true start_time
plan_travel_time_seconds int64 false true Total travel time since previous stop based on scheduled departure times.
plan_route_direction_headway_seconds int64 false true Headway, based on planned departure times, between the current trip and the next trip in the same route and direction.
plan_direction_destination_headway_seconds int64 false true Headway, based on planned departure times, between the current trip and the next trip in the same direction and destination.
plan_start_dt timestamp[us, tz=UTC] false true start_date
plan_stop_departure_dt timestamp[us, tz=UTC] false false Scheduled departure time at stop.
service_date date32[day] true false One scheduled day of service starting at 3am and possibly extending into the next calendar day.
stop_sequence uint32 true false Rank of stop crossing for a given trip_id, tm_pullout_id, and service_date. Differs from GTFS and TM variants when sequences do not align. - min: 1
tm_pullout_id large_string true false Unique identifier for a trip & vehicle in TransitMaster. Distinguishes multiple vehicles on the same trip_id.
vehicle_label large_string true false vehicle.label
schedule_joined large_string false false Indicates which dataset(s) were used to form the schedule for a given trip_id, tm_pullout_id, and service_date. GTFS indicates GTFS-only data was used. TM indicates TransitMaster-only data was used. JOIN indicates both datasets were used.
tm_stop_sequence int64 false true Rank of stop crossing for a given TransitMaster trip.
pattern_id int64 false true TransitMaster pattern_id for a given trip_id and service_date. Joinable to TransitMaster schedule and timepoint datasets.
point_type large_string false true Indicates whether a given stop is the first stop on the route (start), the last stop on the route (end), or a mid-route stop (mid).
timepoint_id int64 false true TransitMaster timepoint_id for a given trip_id and service_date.
timepoint_abbr large_string false true TransitMaster timepoint_abbr for a given trip_id and service_date.
timepoint_name large_string false true TransitMaster timepoint_name for a given trip_id and service_date.
tm_planned_sequence_end int64 false true TransitMaster single tm_stop_sequence of the planned end timepoint for a given trip-route-pattern.
tm_planned_sequence_start int64 false true TransitMaster single tm_stop_sequence of the planned start timepoint for a given trip-route-pattern.
timepoint_order uint32 false true Ranked order of planned timepoints for a given trip-route-pattern.
plan_stop_departure_sam int64 false false plan_stop_departure_dt expressed as seconds after service_date.
waiver_remark large_string false true Bus Ops waiver code; free text entries are removed to avoid exposing identifying information. - regex: ^([[:alpha:]]{1,5}|Unrecognized Code)$
tm_actual_arrival_dt timestamp[us, tz=UTC] false true Actual arrival time at stop from TransitMaster STOP_CROSSING table.
tm_actual_departure_dt timestamp[us, tz=UTC] false true Actual departure time at stop from TransitMaster STOP_CROSSING table.
tm_actual_arrival_time_sam int64 false true tm_actual_arrival_dt expressed as seconds after service_date.
tm_actual_departure_time_sam int64 false true tm_actual_departure_dt expressed as seconds after service_date.
stop_count uint32 false true Total number of stops for a given trip.
start_time int64 false true start_time
start_dt timestamp[us] false true service_date + start_time
gtfs_first_in_transit_dt timestamp[us, tz=UTC] false true earliest 'IN_TRANSIT_TO' status timestamp for vehicle from GTFS-RT VehiclePosition
gtfs_last_in_transit_dt timestamp[us, tz=UTC] false true latest 'IN_TRANSIT_TO' status timestamp for vehicle from GTFS-RT VehiclePosition
gtfs_arrival_dt timestamp[us, tz=UTC] false true Timestamp of earliest STOPPED_AT message for vehicle from GTFS-RT VehiclePosition
gtfs_departure_dt timestamp[us, tz=UTC] false true earliest 'STOPPED_AT' status timestamp for a trip-stop pair from GTFS-RT VehiclePosition
latitude double false true Earliest IN_TRANSIT_TO latitude for vehicle from GTFS-RT VehiclePosition. - allow_inf: False
- allow_nan: False
longitude double false true Earliest IN_TRANSIT_TO longitude for vehicle from GTFS-RT VehiclePosition. - allow_inf: False
- allow_nan: False
trip_id_gtfs large_string false true GTFS-RT trip_id before removing overload and rare variant suffixes. Artifact of MBTA's GTFS-RT feed.
public_operator_id int64 false true Anonymized operator ID joinable to other internal datasets.
previous_stop_id large_string false true
stop_arrival_dt timestamp[us, tz=UTC] false true Estimate of when bus arrived at stop area. Uses gtfs_arrival_dt if available, then TransitMaster actual_arrival_dt and adjusts time to ensure stop_sequence order implies arrival order.
stop_departure_dt timestamp[us, tz=UTC] false true Estimate of when bus departed from stop area. Uses gtfs_departure_dt if available, then TransitMaster actual_departure_dt and adjusts time to ensure stop_sequence order implies departure order.
gtfs_first_in_transit_seconds int64 false true gtfs_first_in_transit_dt minus service_date in seconds.
stop_arrival_seconds int64 false true stop_arrival_dt minus service_date in seconds.
stop_departure_seconds int64 false true stop_departure_dt minus service_date in seconds.
travel_time_seconds int64 false true Time in seconds between the previous stop's departure and the current stop's arrival. Uses stop_departure_dt and stop_arrival_dt.
stopped_duration_seconds int64 false true Time in seconds the bus spent stopped at the current stop. Uses stop_arrival_dt and stop_departure_dt. As estimates, travel_time_seconds does not indicate door events or dwell time.
route_direction_headway_seconds int64 false true Time in seconds between the previous bus and the current bus at the same stop, route, and direction. Uses stop_arrival_dt and stop_departure_dt. - min: 0
direction_destination_headway_seconds int64 false true Time in seconds between the previous bus and the current bus at the same stop, direction, and destination. Uses stop_arrival_dt and stop_departure_dt. - min: 0
is_full_trip bool false true True if the trip has both a start and end point, otherwise False. Uses point_type.