Temporary fix to make 23_4 and 22_2 comparable

[chmnata]

Issue

The congestion network congestion.network_segments is currently based on map version 22_2. However, as of 2024-02-27, ta data (here.ta) has been based on the new map version 23_4. Due to the significant changes in this new map version and the ongoing development of our aggregation method, we haven't switched to the most up-to-date version yet. This version discrepancy means that the congestion network data may no longer fully align with the HERE data, leading to potential issues in our aggregated products.

A total of 1865 segments have changed link_dir in the 23_4 version.

SELECT distinct segment_id 
from 
    (select segment_id, link_dir from congestion.network_links_22_2 old
    left join congestion.network_links_23_4_geom new using (segment_id, link_dir)
    where new.link_dir is null) outdated

Implications

The mismatch between the map versions could result in less data or missing data in the following congestion network aggregated products:

• congestion.network_segments_daily
• congestion.network_segments_monthly

This happens because when the map version changes, certain link_dir values get replaced, causing outdated references in the congestion network. At the same time, the HERE data we receive might not match to the congestion network fully.

Temporary Solution

Create a new table for daily network segment data using the 23_4 map version links and segments to calculate travel time. After calculating the travel time, transform these back to align with the segment definitions (length) of the 22_2 map version. This will ensure that the data remains comparable before and after 2024-02-27, despite the map version change.

[chmnata]

Some validation checks:

• Make sure for segments with no length changes, the travel time is the same:

-- segment_id 60 has the same length in 22_2 and 23_4.

-- both returned 82.68 for travel time
select * from congestion.network_segments_daily
where  segment_id = 60 and dt = '2024-08-01' and hr = 0

select * from congestion.network_segments_daily_23_4
where  segment_id = 60 and dt = '2024-08-01' and hr = 0

• Make sure the speed of segments after adjustment is the same:

-- segment 3743
-- 22_2: 657.15m
-- 23_4: 699.69m

22_2 adjusted: travel time 23.15, length 699.69, speed = 108 km/h
23_4: travel time 21.74, length 657.15, speed = 108 km/h

• Make sure segments that is in 23_4 but not 22_2 still gets aggregated

-- example segment: 7184 still have data

• Segments that had is_valid = False because of map unmatch now has data with is_valid = True

-- is_valid = False
select * from congestion.network_segments_daily
where  segment_id = 4145 and dt = '2024-08-01' and hr = 0

-- is_valid = True
select * from congestion.network_segments_daily_23_4
where  segment_id = 4145 and dt = '2024-08-01' and hr = 0

[chmnata]

The adjusted segment daily hourly level travel time is now in congestion.network_segments_daily_23_4, it is being updated daily through a temporary dag in the ec2.

Usage

View with all data in congestion.network_segments_temp

You can use it by itself like the congestion.network_segments_daily table, but be aware that it only contains data after 2024-02-27 (map version change date). You could union them in your query if you want to access all dates.

SELECT * FROM congestion.network_segments_temp

OR

with all_dates as (
	select segment_id, dt, hr, tt, is_valid, num_bins from congestion.network_segments_daily
	WHERE dt < '2024-02-28'
	union all
	select segment_id, dt, hr, tt, is_valid, num_bins  from congestion.network_segments_daily_23_4)
select * From all_dates 
where  segment_id = 4145 and dt = '2024-08-01' and hr = 0

How its created

See function congestion.generate_network_daily_temp_23_4(date) for the sql.
Steps:

1. Calculate 23_4 link speed for daily hourly level
2. Calculate 23_4 segment travel time for daily hourly level
3. Adjust 23_4 segment travel time to 22_2 segment travel time using difference in 22_2 and 23_4 segment length (assuming speed is constant)

Caveats

1. This is a temporary fix, the segment definition of 23_4 have not been fully validated. It should be fine for city-wide or area wide calculation but do validation if you see outliers.

Related tables for validation:
23_4 segment definition : congestion.network_segments_23_4_geom
23_4 link lookup: congestion.network_links_23_4_geom

2. Some segments will not exist post 2024-02-27 as segments retires. When the map changes, or when there are new traffic signals, sometime segments will need to be broken into two new segments. In this case, the old segment_id would be retired and 2 new segment_ids would be created to replace the retired segments.
   Table containing retired segments information: congestion.network_segments_retired