Calculate the average wait time at bus stops

[VMois]

Now that we have the necessary prod data after #21, we can calculate the average wait time at bus stops. The end goal for this issue is to have a final table in BigQuery that contains stop_id, date, direction_id, and line_id (bus number). We might also split the day into a few chunks as frequently bus service is reduced at night.

[VMois]

I assume sometimes buses will not stop because no passengers are at a stop. This means that having only a radius of 10-20 meters to detect when a bus is near a stop is not enough. If buses drive fast enough, we might miss a "near-stop" event completely.

Manually testing for route_id "24" and stop_id = 2795 ("Etude Dr At Hermitage Rd" stop), the radius needs to be at 110 meters to capture all events that match a schedule.

Increasing the radius creates a problem as a "near-stop" event can be captured for a stop that is located in the opposite side of the road. We need a way to filter out stops on the opposite side.

[VMois]

Need also to investigate special stops that can be end points of travel as well as starting points. For example, bus hubs. Need to understand how trip_id and stop_id change (or not).

[VMois]

Below is an example of query to calculate avg wait times. It still has flaws but it covers multiple exceptions already.

Query:

DECLARE var_distance_from_stop INT64;
DECLARE var_today_est_limit TIMESTAMP;
DECLARE var_yesterday_est_limit TIMESTAMP;

SET var_today_est_limit = TIMESTAMP("2024-06-04 04:00:00 UTC");
SET var_yesterday_est_limit = TIMESTAMP("2024-06-03 04:00:00 UTC");
SET var_distance_from_stop = 120;  -- in meters


WITH selected_positions AS (
  SELECT * FROM `miwaitway.prod_miway_data.vehicle_position` WHERE timestamp BETWEEN var_yesterday_est_limit AND var_today_est_limit
)
, vehicles_near_stops AS (
  SELECT
      stop_id,
      stops.stop_name,
      vehicle.vehicle_id AS vehicle_id,
      vehicle.timestamp,
      vehicle.trip_id,
  FROM
      `miwaitway.prod_miway_data.stops` AS stops,
      selected_positions AS vehicle
  WHERE ST_DWithin(
          stops.location_point,
          vehicle.location_point,
          var_distance_from_stop)
)
, enriched AS (
  SELECT 
      vns.stop_id,
      vns.stop_name,
      vns.vehicle_id,
      vns.timestamp as timestamp,
      t.route_id,
      t.direction_id,
      vns.trip_id,
      LEAD(vns.timestamp) OVER (PARTITION BY stop_id, t.route_id, t.direction_id ORDER BY vns.timestamp DESC) as previous_timestamp,
      LEAD(vns.vehicle_id) OVER (PARTITION BY stop_id, t.route_id, t.direction_id ORDER BY vns.timestamp DESC) as previous_vehicle_id,
      LEAD(vns.trip_id) OVER (PARTITION BY stop_id, t.route_id, t.direction_id ORDER BY vns.timestamp DESC) as previous_trip_id,
  FROM vehicles_near_stops AS vns
  JOIN `miwaitway.raw_miway_data.trips` AS t ON vns.trip_id = t.trip_id
)
SELECT e.stop_id, route_id, ROUND(AVG(TIMESTAMP_DIFF(timestamp, previous_timestamp, SECOND) / 60)) as avg_wait_time
FROM enriched AS e
JOIN `miwaitway.raw_miway_data.stop_times` AS stop_times
ON stop_times.stop_id = SAFE_CAST(e.stop_id AS INT64) AND e.trip_id = stop_times.trip_id  -- make sure the correct direction is include only for an event
WHERE vehicle_id != previous_vehicle_id AND e.trip_id != previous_trip_id
GROUP BY e.stop_id, route_id
ORDER BY avg_wait_time DESC

For some reason, stop_id in stop_times table can be only INT64, but in stops, stop_id is a STRING. Need to investigate if data is properly ingested or it is an issue with source data.

[VMois]

Some lines operate only in the morning and then afternoon (e.g. route 49 at stop 4917) which means avg wait time becomes huge. One potential solution is to use the median instead of the average. Something to investigate