Pre-aggregate derived arrival times so these analyses can run interactively

[NoamGaash]

⚠️ AI-generated draft — needs human validation

This issue was written by an AI agent from materials produced during the
hasadna Open Bus hackathon (July 2026). The underlying analysis was built fast,
under hackathon conditions, and has not been peer-reviewed. Figures,
endpoint behaviour and conclusions all need independent verification before
anyone acts on them or quotes them publicly.

Please validate before implementing. Corrections very welcome.

The one-sentence version

Nobody can tell you what time a bus actually reached a given stop — the API doesn't
store it — so every analysis re-derives it from raw GPS by hand, at ~1–2 minutes per
line, and they all derive it slightly differently. Compute it once in the ETL and
store it.

Background — why this is a problem

The Stride API has a gtfs_ride_stop__arrival_time field, and a
/siri_ride_stops/list endpoint with a nearest_siri_vehicle_location_* block that
looks exactly like "where the bus was when it reached this stop". These are empty.

Checked live on 2026-08-16, 1,000 consecutive rows from /siri_ride_stops/list for
service date 2026-08-10:

column	populated
nearest_siri_vehicle_location_id	0 / 1000
nearest_siri_vehicle_location__recorded_at_time	0 / 1000
gtfs_stop_id	0 / 1000
gtfs_ride_stop__arrival_time	0 / 1000

Reproduce:

curl -s "https://open-bus-stride-api.hasadna.org.il/siri_ride_stops/list?limit=1000\
&siri_rides__scheduled_start_time_from=2026-08-10T00:00:00%2B03:00\
&siri_rides__scheduled_start_time_to=2026-08-10T23:59:59%2B03:00"

So the only actual-position data available is the raw GPS ping stream from
/siri_vehicle_locations/list — a location roughly once a minute per vehicle. To
find out when a bus reached a stop, you have to take the stop's coordinates, find the
vehicle's closest approach, and interpolate between the two bracketing pings.

Every analysis in this milestone that talks about time at a stop does this
independently. Segment reliability (#1782), the Marey diagram (#1783), the
segment×hour heatmap (#1784), headway decay (#1789) and bunching (#1790) all
re-implement the same interpolation. The hackathon write-up reports this costs ~1–2
minutes per line and forces aggressive caching — (this figure is from the write-up
and was not re-measured here).

Two consequences, and the second is worse than the first:

1. It's slow. Nothing built on it can be interactive.
2. Everyone's numbers differ. Each consumer picks its own match-distance
   threshold and its own handling of the awkward cases, so two דאטאבוס charts can
   legitimately disagree about the same bus.

What to build

Compute derived stop arrival times once, in the ETL, and store them. Per
(ride, stop):

• derived_arrival_time — interpolated moment of closest approach
• match_distance_m — how close the vehicle actually got to the stop
• ping_gap_s — seconds between the two bracketing GPS pings

The last two are not optional extras. They are what let a consumer decide whether to
trust a value. The hackathon work used 150 m as a loose-match warning, 300 m
as "drop this stop entirely", and a 2-minute ping gap as a coarse-timing warning.
Storing the raw measurements rather than a boolean lets consumers set their own bar.

Two artifacts that must be handled explicitly

Both of these were found the hard way during the hackathon and will silently corrupt
results if ignored:

• Terminal dwell. At the origin stop the bus is parked at the kerb for several
  minutes. "Closest approach" lands in the middle of that idle period, which inflates
  the first segment's duration. The origin should resolve to departure — the last
  ping while still in the stop's vicinity — not to closest approach.
• Coincident junction stops. Stops metres apart with identical scheduled times can
  match the same ping, or match out of order. Needs a forward-constrained monotonic
  search: stop k may only match a ping at or after the one matched for stop k−1.
  Non-positive segment durations get dropped.

Why it's worth your time

This is the shared bottleneck under most of the analysis tickets in this batch. It is
the difference between "a notebook someone ran once during a hackathon" and "a chart
that loads when a journalist opens the page". It also makes every downstream metric
consistent, which matters more than speed if these numbers are ever quoted publicly.

Arguably it should be built before the other scale-up tickets rather than after —
they are all waiting on it.

Getting started

• Where the code goes: this is upstream of this repo — the ETL lives in
  hasadna/open-bus-stride-etl and
  the API in hasadna/open-bus-stride-api.
  Worth opening a discussion there before writing code.
• Reference implementation of the interpolation:
  analyses/bus_arrival_reliability.py,
  with the method written up in
  algorithms/bus-arrival-reliability.md.
• Skills: Python, SQL, some geospatial comfort (point-to-point distance,
  interpolation between timestamps). No ML, no heavy maths.
• Good first step that isn't the whole thing: confirm on a handful of lines that
  the derived arrival times match reality — e.g. against a timetable app or a manual
  GPS trace read. Nobody has validated the interpolation against ground truth, and
  everything else rests on it.

Related tickets

Blocks or would speed up: #1782, #1783, #1784, #1789, #1790, #1799, #1800
Related data-quality issues: #1775 (SIRI→GTFS matching), #1778 (first ping ≠ departure)

---

Credit & provenance
Found during the hasadna Open Bus hackathon, July 2026 — analysis by the hackathon team.
· Method, evidence and caveats: algorithms/upstream-issues.md
· Original work: https://github.com/hasadna/open-bus-hackathon-26
· Issue drafts and the full defect list: algorithms/upstream-issues.md

Rewritten 2026-08-16 to be readable without the hackathon materials. The null-column
table was measured live against the production API on that date; the ~1–2 min/line
cost figure is from the hackathon write-up and was not re-measured.

[navesh43]

The cost of compute line

That derivation costs ~1–2 minutes per line

I don't think it takes that long. Still, the feature is important for the other reasons listed (uniformity of information, and its easy accessibility).

Errors

I think there is a lot of interest in estimating computational errors. This is important because some of the effects in the data may be at the noise floor. For example, if the errors are 1-2 minutes, they could have an unintended effect on wait times and possibly even make the data worse than it really is.

Levels of implementation

I propose to implement 3 levels of calculating arrival times to the stop, for each level there is a different approach to calculating errors:

1. The simple approach:

take the last ping time in the stop area, and treat it as the real stop time.
Error calculation: We estimate the error by taking the distance between the ping and the stop and dividing it by the speed (it seems that the ping speed can be taken for estimating the speed)

2. Calculate the travel time:

Take the last ping in the stopping area, and calculate the time the bus was at the stopping point. This operation uses the speed present in the ping and the velocity-time relationship: $v\cdot t = d$, to find the actual stopping time.
Error calculation: There is uncertainty in the travel speed, we can take a range around the ping speed ($\pm 30 km/h$ or another range) and get the uncertainty extremes.
Note: This approach should be taken by route and not by air distance.

3. Interpolation from 2 pings:

Take the ping before the stop and the ping after the stop and calculate the arrival time from them. The principle is to use the previous method on the ping after the stop and the ping before the stop and take the average of the arrival time from both as the desired value.
Error calculation: Errors can be inferred from the previous method along with the possibility of slightly reducing the required range, but also using the difference between the arrival times of the two pings to estimate the errors (perhaps taking the difference between them as an error, or simply using this difference to decide whether these pings are reliable or not).
Note: Check that the pings are close enough to the stopping point (no more than 300-500 meters), otherwise, this method should not be used, it may result in a worse value than the first method.

The reason why all approaches need to be implemented:

Not every method can be used at all times, for example, if the speed fails to be measured or the measurement is incorrect, methods 2 and 3 are not suitable (and accordingly, to calculate the errors in method 1, a standard value for speed must be assumed, which can also be done in methods 2 and 3 in principle), and not for every stop there will be 2 close pings to use in method 3.

I suggest creating automatic rules to decide which method is suitable for each stop and saving the errors and the error evaluation method with the value (stop arrival time).

[NoamGaash]

Thank you @navesh43 ! I'll assume you've wrote your comment as a human and remove the "needs verification" label 🙏