Congestion Network 2.0: Aggregate free flow baseline for each segment

[chmnata]

There are a total of 46 rows in the network_segments layer that do not have a baseline. Will investigate why, I'm guessing its because of map version difference..

[chmnata]

Data availability in here.ta for those missing segments

For example, segment 230 on Vic Park consist of link_dir ('948716059F','948716058F','995640093F','1291426834F','1206431575F','1291426833F')

when checking availability for each link_dir in 2019

select link_dir, count(distinct tx::date) 
from here.ta_201901
where link_dir in ('948716059F','948716058F','995640093F','1291426834F','1206431575F','1291426833F')
group by link_dir

Turns out only 4/6 have data in 2019 Jan. link_dir	count
1206431575F	31
948716058F	31
948716059F	31
995640093F	31

Check to see if we are getting data for link_dirs from another version

Using the start_vid and end_vid of this segment 230, I tried to route it with the 19_4_tc version.

SELECT results.*, routing_grid.id, routing_grid.link_dir, routing_grid.geom
    FROM pgr_dijkstra('SELECT id, source::int, target::int,
                           st_length(st_transform(geom, 2952)) as cost
                           FROM here.routing_streets_19_4_tc routing_grid
                           ',
                30437324, 30437307) results
    INNER JOIN here.routing_streets_19_4_tc routing_grid ON id = edge

Resulted in a new set of link_dirs ('995640093F','1206431575F','948716059F','948716058F','1206431576F') Seems like 1206431576F got split into 1291426834F and 1291426833F

And we indeed have data for link 1206431576F :ok_fine:

select link_dir, count(distinct tx::date) 
from here.ta_201901
where link_dir in ('1206431576F')
group by link_dir

[chmnata]

Tried routing the missing segments with routing_streets_19_4_tc

with missing as (
select * from congestion.network_segments
left join congestion.network_baseline using (segment_id)
where network_baseline.segment_id is null)

, new_route as (
    SELECT results.*, routing_grid.id, routing_grid.link_dir, routing_grid.geom, 
           missing.start_vid, missing.end_vid, missing.segment_id
    FROM missing
    , LATERAL pgr_dijkstra('SELECT id, source::int, target::int,
                           st_length(st_transform(geom, 2952)) as cost
                           FROM here.routing_streets_19_4_tc routing_grid
                           ',
                start_vid, end_vid) results
    INNER JOIN here.routing_streets_19_4_tc routing_grid ON id = edge)

select distinct segment_id from new_route

42 out of 46 missing segment can be routed. Out of those 42 that can be routed, only 25 of them have more than 80% of links length with data in 2019 Jan.

with missing as (
select * from congestion.network_segments
left join congestion.network_baseline using (segment_id)
where network_baseline.segment_id is null)

, new_route as (
    SELECT results.*, routing_grid.id, routing_grid.link_dir, routing_grid.geom, 
           missing.start_vid, missing.end_vid, missing.segment_id, 
            ST_length(ST_Transform(routing_grid.geom, 2952)) as link_length
    FROM missing
    , LATERAL pgr_dijkstra('SELECT id, source::int, target::int,
                           st_length(st_transform(geom, 2952)) as cost
                           FROM here.routing_streets_19_4_tc routing_grid
                           ',
                start_vid, end_vid) results
    INNER JOIN here.routing_streets_19_4_tc routing_grid ON id = edge)

, count_info as (
    select segment_id, count(1) as num_link_dir, sum(link_length) as total_length
    from new_route
    group by segment_id)

, link_info as (
    select segment_id, link_dir, count(distinct tx::date), link_length 
    from here.ta_201901
    inner join new_route using (link_dir)
    group by segment_id, link_dir,link_length)

, results as (
    select  segment_id, 
            count(distinct link_dir) as links_with_data, 
            num_link_dir, 
            sum(link_length), 
            total_length, 
            sum(link_length)/total_length as links_w_data
    from link_info
    inner join count_info using (segment_id)
    group by segment_id, num_link_dir, total_length
    order by segment_id)
select count(1)
from results
where links_w_data >= 0.8

[chmnata]

when checking here.ta_201901_OLD instead of here.ta_201901, all 42 segments that got routed have enouf data to aggregation baseline :meow_dio: I'm glad _old is still here. I will insert the 42 missing segment's baseline to the baseline table. Will investigate further as to the 4/46 segments that did not get routed, probably just need a new node to route.

[chmnata]

whelp, the 4 segments that could get routed are from six point

The routing streets for map version 19_4_tc is missing a section.

It doesn't exist in here_gis.streets_19_4_tc as well

[chmnata]

Segment 128 and segment 130, can use the following section to get baseline tt.

For segment 129 and 3522, we can use the highlighted portion's tt as baseline tt for the whole segment. The free flow speed for that section and the entire segment should be similar anyways. We just won't have actual aggregated data (segment level travel time or travel time index) for these segments for 2019 as they do not exist then.