Find real time stop arrivals [depends on #24, #26][advanced]

[daphshez]

The goal of this task is to build a table with at least the following fields:

• trip_id
• date
• stop_code
• actual_arrival_time

Write a script that outputs that to a CSV (no need to write to DB at this point). The script should go under /siri .

How to do this
Assuming

• siri_arrivals table has a trip_id field (Find trip id for SIRI arrival  #24) and route offset (Add route offset to siri_arrivals [depends on #29, #30] #26)
• gtfs_stop_times table has shape_dist_ratio (Add SHAPE_DIST_RATIO field to gtfs_stop_times [easy]  #33)

You should be able to do something like:

• For each trip_id in siri_arrivals, build:

  • planned stops: a list of all planned stops in the trip, from gtfs_stop_times (stop_code & shape dist ratio)
  • known locations: a list of know positions from siri_arrivals - route_offset & timestamp

• For each stop in planned stops, find the last know location before it, and first known location after it. If the previous know location was at time t₁ and offset x₁, and the next known location is at time t₂ and offset x₂, and the stop offset is x_s, then

  t_s = (x_s - x₁) / (x₂ - x₁) * (t₂ - t₁)

What do I need to know

• Python, basic SQL
• You'll need a good understanding of the GTFS tables and the siri_arrivals tables, including the extra fields added in Find trip id for SIRI arrival  #24, Add route offset to siri_arrivals [depends on #29, #30] #26 & Add SHAPE_DIST_RATIO field to gtfs_stop_times [easy]  #33. If you know enough to understand this task description, you're probably all right.
• You probably need a dev database, see Create a database dump for dev environment + docker? #32
• Clone the repository, and pull request your changes.

[AvivSela]

Done.
The script is in open-bus/siri/analyseRealTime/Main.py