Epic - Daskify RT segment speeds

[tiffanychu90]

After receiving a research request, use this template to plan and track your work. Be sure to also add the appropriate project-level label to this issue (eg gtfs-rt, DLA).

Epic Information - Daskify RT speeds

Summary

• Pilot the route segments concept to see how speeds get calculated at the by route segment-trip level
• Assemble all the tables that are needed for pipeline and decide priority of which ones can be moved to dbt first
• RT segment speeds pipeline Google Doc

Steps

1. cut segments (route segments or stop segments)
2. spatial join vehicle positions by route-direction to the segments (use delayed after looping within routes for operator) (A1)
3. pare down vehicle positions to just enter/exit within a segment + drop unusable trips (trips < 10 min of vp data). should exclude trips with too little info or trip_id is None at the start, then pare down (A2)
4. do linear referencing within segments to get distance_elapsed and time_elapsed, and calculate speeds. save as partitioned parquets (A3)

Issues

Set up tables for exploration

• 1. Pilot batch of speed by segment-trip tables #593
• 2. Segment speeds, fix sjoin, add trip diagnostics #600
• 3. Research Request - Initial road segments (Census/Caltrans LRS) #605
• 4. V2 segment speeds - intermediate outputs #643
• 5. Compare stop arrivals #662
• 6. run feb date through segment speeds workflow #670
• 7. Combine speeds service #674 (rerun Feb 2023 to fix UTC vs Pacific and download 2 dates of vp)
• 8. Stop segments pipeline refactor #743, Stop segments refactor 2 #765 refactor stop segments workflow

Diagnostics

• 1. Research Request - Segment-trip speed diagnostics #594: where are errors for too high or too low speeds coming from?
• 2a. Research Request - Trip-level diagnostics #595: generate trip-level diagnostics
• 2b. Research Request - RT trip diagnostics: thresholds for usable trips #604: visualize these in a notebook. goal: get thresholds for usable trips without sacrificing short trips that have valid and complete vehicle positions
• 3. Research Request - stop-to-stop segments, does stop_sequence vary? #648: find the most aggregation we can do for stop-level metrics and stop-to-stop segments.
• 4. Research Request - aggregate stop-level metrics by segment #663: how to/if we should aggregate stop-level metrics within route/stop segments -- skip this
• 5. Research Request - compare scheduled vs RT total trip time #668: compare RT trip time elapsed vs scheduled trip times and minutes of trips with RT vp data

Refine Data Processing

• 1. Research Request - "super" project to deal with inlining / looping routes #705: circular / loop / inlining of routes: carve out separate data processing
• 2. Research Request - review RT segments speeds preprocessing stages #751
• 3. Research Request - speeds by stop segments published to open data #761
• 4. Research Request - New speed tables: trip average speeds and average route speeds by time-of-day #790
• 5. Research Request - Speeds chart for 20th, mean, 80th percentile by shape #764, Research Request - Fix Certain Speeds #804
• 6. Research Request - Plug in gaps in stop segments #811
• 7. Sjoin performance #831

[tiffanychu90]

Speeds by segments analytics pipeline takes roughly ~1.5 hrs to create the 2 open data layers related to speeds.
Next: use new epic for road segments.