🐛📄 – TODS and TIDES Alignment: Mid-Trip Reliefs

[wmata-eajensen]

Describe the problem TODS allows multiple runs per trip (particularly common on commuter rail, but also happens on bus service at CTA, NYCT, and WMATA), while TIDES specifies one operator in the trips_performed table. Much less important than ridership/runtime analysis, but supporting mid-trip reliefs and perhaps additional staff could enable other types of analyses using TIDES formatted data.

Possible Solutions

1. Move operator_id to the stop_visits table.
2. Specify that operator_id is always the first operator on the trip.
3. Remove operator_id from trips_performed and create a new trips_performed_crew table to support additional multiple operators and additional staff (e.g. conductors)

For 3, a possible format could be something like the following table, though it probably needs some refinement:

trip_id_performed	employee_id	role	start_time	end_time

Additional context TODS v2 is moving to a format where it is a supplement to GTFS. Since TIDES was built assuming some agencies would want to reference GTFS fields, most fields should be compatible, and it should be straightforward to build TIDES as if non-revenue information in TODS is just in the normal schedule.

TODS v2 isn't quite final yet, though the working group has voted to move to the GTFS supplement format.

[botanize]

We receive operator logon/logoff messages with vehicle, operator and timestamps (and duty number, though that isn't always accurate) , so another option that more closely matches our vendor data could be a vehicle_crew table, e.g.,

vehicle_id	employee_id	role	start_timestamp	end_timestamp
1234	8008	OPERATOR	2025-05-05T11:00:00Z	2025-05-05T18:00:00Z

Where role is an enum?

• OPERATOR
• CONDUCTOR
• …

Matching crew to trips would be fairly straightforward using the vehicle and time.

[gabriel-korbato]

My initial vote would have been for alternative 1 (for its simplicity and coverage of most but not all use cases), but the suggestion from @botanize would also work if we want to be able to track multiple staff, and I prefer it over alternative 3.

[jlstpaul]

I like the idea of an optional table to address complex situations like this. I also prefer to define this explicitly rather than implicitly (as in Option 1 using the stop_visits table).

Have we discussed what happens in the similar situation when two different vehicles operate portions of the same trip? Was the plan to have two records in trips_performed?

Could we combine @wmata-eajensen and @botanize suggestions into an optional vehicle_crew table with the following fields

trip_id_performed - optional vehicle_id employee_id employee_role start_time end_time

This would allow ingestion of raw data along the the lines @botanize suggests, without a trip_id, but it would also allow the explicit linkage of vehicle and crew information to trips_performed records when that is known.

Given that this would be an optional table, I would not remove the existing vehicle_id and operator_id fields from the trips_performed record. I would probably rename them as primary_vehicle_id and primary_operator_id. The vendor or agency could determine how to use these fields when there are multiple vehicles and/or multiple operators identified in the vehicle_crew table.

[botanize]

I think the idea behind trip_id_performed is that only one vehicle can perform a trip, so while two vehicles would share a trip_id_scheduled, they'd have different trip_id_performed ids.

The trips_performed table also includes service_date, vehicle_id, actual_trip_start and actual_trip_end, so matching to the proposed vehicle_crew table would be fairly trivial, though to make it more trivial I might modify my proposal to use service_date and integer times instead of timestamps.

If we add trip_id_performed to my proposal, there would no longer be a direct link to the logon/logoff messages in our system (and probably others?). A sophisticated system could also track crew on partial trips, for example, fare inspectors riding a rail car between two stops. I also see an issue with identifying trip boundaries. Do you leave out the layover/recovery? Do you set boundaries at trip start times? How do pull-ins, pull-outs and deadheads fit in?

[jlstpaul]

That all makes sense. Thank you. I agree that the proposed vehicle_crew table would address the need identified here.

For simplicity, I'd still like to consider keeping the operator_id field on the trips_performed record.

[jaygordon]

I like @wmata-eajensen 's option 1 (move operator_id to stop_visits) because it resolves this issue and is simple, and I also like @botanize 's idea of a vehicle_crew table because it covers other situations (like other roles, such as conductors; and handling start/end times between stop visits, such as if operators change at some place between two revenue stops but where passengers can't board or alight).

I see value in making vehicle_crew an optional table as @jlstpaul suggests, which as he says requires that operator_id still exist in one of the required tables. In that case my vote would be to add an optional vehicle_crew table but also move the operator_id column to from trips_performed to stop_visits. Then the only rule we need to specify for stop_visit.operator_id is, when operators change at a stop_visit, does this column specify the operator who's being releived of duty or who's starting duty.