Research Request - stop-to-stop segments, does stop_sequence vary?

[tiffanychu90]

Complete the below when receiving a research request, and continue to add to this issue as you receive additional details and produce deliverables. Be sure to also add the appropriate project-level label to this issue (eg gtfs-rt, DLA).

Research Question

Single sentence description: Does stop_sequence (found at trip-level) vary for the same route-direction?

• Exploratory work related this Jarvus ticket: User Story: Python dbt model to produce shape-stop grain table data-infra#2237
• Epic - Daskify RT segment speeds #592

Detailed description:

• Goal: cut stop-to-stop segments and calculate stop-level metrics
• Constraint: if stop_sequence is at the trip-level, can we figure out any version of a table that is more condensed than what is in stop_times?
• Initial thought: see if stop_sequence is consistent within route_id-direction_id level, and if it is, we just have to have 1 set of stop-to-stop segments per route-direction.
• For stop-level metrics, we would want to calculate delay (actual_arrival_time - scheduled_arrival_time) and probably speed (speed_mph it was travling from previous stop to this current stop)
  • Endpoint delay compares the actual_arrival_time (pin the last vehicle position we know to the nearest stop) and compare that to that stop_id's scheduled_arrival_time.
• If we can cut stop-to-stop segments even by shape_id, we get some aggregation, and retain nuances of bus deviating to serve high school during particular hours. Need a good method to link back to stop_times...but without traversing a very large table to do so.

[tiffanychu90]

Conceptual illustration: why stop_sequence would differ....if shape_id is all we can aggregate to, let's aggregate to it and start calculating stop-level metrics.

[tiffanychu90]

Plan to deal with missing the curves and bends when we cut stop-to-stop segments.
Bring shape's line geometry back in and fill in the stuff in between segment start/end