Reorganize realtime arrival times to show times by stop

[matikin9]

Currently, our Trip Updates endpoint is at:
/{agency_id}/trip_updates/...

and the data is returned in this structure:

• trip/vehicle
  • stop
    • arrival time

We need a new endpoint that returns the same data in this structure:

• stop
  • arrival time

[matikin9]

Return trip_updates data's departure times as an array of times (similar to route_stops_grouped) and have it returned in the websocket

[albertkun]

Update 5/19/2023

@matikin9 @albertkun met to discuss the re-organization of this endpoint.

Target JSON endpoint

Diagram

Next Steps

• Generate the line_detail_updates endpoint

[albertkun]

Notes 2023-09-21

• We might be aiming for a big chunk of effort
• We don't understand the edge cases enough
• We should step back from the prototype schedules page
• We need a way to look at the different trips

[matikin9]

Research:

• Start by doing a SQL query-based analysis of the number of distinct shapes per route, so we know the complexity of what we're working with
• From that, we can identify some of the more complex routes we are dealing with.
• Do a deeper dive on those routes, comparing the stops to see what the variations are.
• Bring in the Service Planners who have knowledge of those routes to get a better understanding.

After this we can strategize on what our next steps should be.

[matikin9]

This query will bring up a count of unique shape_ids for each route_id:

select route_id, count(distinct shape_id) as unique_shape_ids
from metro_api.trips
group by route_id
order by unique_shape_ids desc;

Example output

route_id	unique_shape_ids
10-13168	23
108-13168	20
51-13168	20
111-13168	18
115-13168	18

Initial findings

• Some, like the 10, are actually sisters lines (10/48).
• Some have Owl service.

[matikin9]

Todo List

• Put the list of route with counts of distinct shape_ids into a spreadsheet for tracking.
• Look at the routes that have 3 shape_ids and figure out what the difference in stop_sequence is.
  • Re-run processing to generate an updated trip_shapes table
  • Starting noting what the differences are for each of the lines in the spreadsheet. Note any questions we would want to ask the service planners.
• Talk to Service Planners to understand in more detail why there are more than 2 shapes.
• Continue with the routes that have more shapes.

[matikin9]

These are the routes that each have 3 distinct shape_ids:

select route_id, shape_id
from metro_api.trips 
where route_id in (
'94-13168',
'161-13168',
'232-13168',
'158-13168',
'662-13168',
'211-13168',
'DSE-US',
'205-13168',
'164-13168',
'344-13168',
'601-13168',
'244-13168'
)
group by route_id, shape_id
order by route_id, shape_id;

[matikin9]

This query should pull up the distinct sequence of stops and shape geometries for line 94

select direction_id, t.shape_id, stop_id, stop_sequence, sh.geometry
from metro_api.trips t, metro_api.stop_times st, metro_api.trip_shapes sh
where t.trip_id = st.trip_id and t.shape_id = sh.shape_id
and route_id in ('94-13168')
group by direction_id, t.shape_id, stop_id, stop_sequence, sh.geometry
order by shape_id, stop_sequence;

[albertkun]

trip_shapes was re-generated using the following sql script:

-- remove existing trip_shapes
truncate trip_shapes;

-- update trip_shapes
update trip_shapes
set geometry = sh.geometry
from (
    select
        shape_id,
        ST_MakeLine(
            shapes.geometry order by shape_pt_sequence
        ) as geometry
    from metro_api.shapes
    group by shape_id
) as sh
where trip_shapes.shape_id = sh.shape_id;