Congestion Network 2.0: Add street name, from street and to street to segments

[chmnata]

The latest centreline intersection has a lot more info! a from and to centreline_id + name , and turn direction!!!

[chmnata]

street_name could be derived with lf_name, by unnesting the centreline and concatenating distinct street_name 🤔

[chmnata]

First get the street_name for all segments, and then get the intersection name by filtering the one that is already in the street name

[chmnata]

WITH prep as (
select segment_set, start_vid, end_vid, start_int, end_int, unnest(geo_id_set) as geo_id
from congestion.segment_centreline)

, first_pass as (
    select segment_set, start_vid, end_vid, start_int, end_int, array_agg(distinct lf_name) as st_name , 
        array_length(array_agg(distinct lf_name), 1) 
    from prep
    inner join gis.centreline_20220705 using (geo_id)
    group by segment_set, start_vid, end_vid, start_int, end_int
    order by array_length(array_agg(distinct lf_name), 1) desc)
select  array_length, count(1)
from    first_pass
group by array_length

Around 100 segments/segment_sets came by with more than 1 street_name 3 16 2 82 1 6024

[chmnata]

An example of how to get intersection name

select segment_set, start_vid, end_vid, start_int, end_int, array_agg(distinct lf_name) as st_name , 
        array_length(array_agg(distinct lf_name), 1) 
from prep
inner join gis.centreline_20220705 using (geo_id)
group by segment_set, start_vid, end_vid, start_int, end_int

If one of the intersection name is in the st_name array then the other one will be selected as the intersection name. In the highlighted case, from_int_name is Lake Shore Blvd W which is the same as the st_name for segment, so Brown's Line will be selected as the intersection name

[chmnata]

with ints as (
select distinct on (int_id) int_id, linear_name_full_from , linear_name_full_to
from gis.centreline_intersection_20220705
order by int_id, linear_name_full_from , linear_name_full_to)
select segment_set, start_int, st_name, 
        coalesce(case when s.linear_name_full_from = ANY(st_name) then null  else s.linear_name_full_from end, 
        case when s.linear_name_full_to = ANY(st_name) then null  else s.linear_name_full_to end) as start_int_name, 
        coalesce(case when e.linear_name_full_from = ANY(st_name) then null  else e.linear_name_full_from end, 
        case when e.linear_name_full_to = ANY(st_name) then null  else e.linear_name_full_to end) as end_int_name 
from congestion.temp_street_name_segments seg
left join ints s on s.int_id = start_int
left join  ints e on e.int_id = end_int

Did some random check, some is good, some is not. e.g.

Technically right but we would likely want the 404 Steeles Woodbine Ramp to be just Woodbine Avenue

[chmnata]

For some reason this intersection 14228233 is not in the intersection table, but the centreline does stop there and has the intersection information. On top of that the traffic signal also has a int_id of 14228233. But since this intersection is not in the intersection layer, we couldn't get the intersection name information for it

[chmnata]

with ints as (
select distinct on (int_id) int_id, linear_name_full_from , linear_name_full_to
from gis.centreline_intersection_20220705
order by int_id, linear_name_full_from , linear_name_full_to)

, temp as (select segment_set, start_int, end_int, st_name, 
        coalesce(case when s.linear_name_full_from = ANY(st_name) then null  else s.linear_name_full_from end, 
        case when s.linear_name_full_to = ANY(st_name) then null  else s.linear_name_full_to end) as start_int_name, 
        coalesce(case when e.linear_name_full_from = ANY(st_name) then null  else e.linear_name_full_from end, 
        case when e.linear_name_full_to = ANY(st_name) then null  else e.linear_name_full_to end) as end_int_name 
from congestion.temp_street_name_segments seg
left join ints s on s.int_id = start_int
left join  ints e on e.int_id = end_int)
select * from temp 
where start_int_name is null or end_int_name is null

There are only 55 rows where a start or end int name is missing, which is not that bad.

However, some of those also got matched to Planning Boundary...

There are a total of 86 rows without int name after excluding Planning Boundaries.

There are a total of 30 distinct int_ids causing no int_name

with ints as (
select distinct on (int_id) int_id, linear_name_full_from , linear_name_full_to
from gis.centreline_intersection_20220705
where linear_name_full_from != 'Planning Boundary' and linear_name_full_to != 'Planning Boundary'
order by int_id, linear_name_full_from , linear_name_full_to)

, temp as (select segment_set, start_int, end_int, st_name, 
        coalesce(case when s.linear_name_full_from = ANY(st_name) then null  else s.linear_name_full_from end, 
        case when s.linear_name_full_to = ANY(st_name) then null  else s.linear_name_full_to end) as start_int_name, 
        coalesce(case when e.linear_name_full_from = ANY(st_name) then null  else e.linear_name_full_from end, 
        case when e.linear_name_full_to = ANY(st_name) then null  else e.linear_name_full_to end) as end_int_name 
from congestion.temp_street_name_segments seg
left join ints s on s.int_id = start_int
left join  ints e on e.int_id = end_int)
select start_int from temp 
where start_int_name is null 
union  
select end_int from temp
where end_int_name is null

Out of those 30int, 7 of them are not in the intersection layer..... thats why we couldn't get the intersection name. As for the other 23ints. Some of them have the same from_int name and to int name. e.g. Fairview Mall Dr on Fairview Mall Dr e.g. University Ave on University Ave e.g. O'Connor on O'Connor

Other ones is an intersection of a road and the planning boundary..... should've excluded these ones when I choose intersections.

[chmnata]

Lots of the remaining ones didnt get a name because its the same as the street_name. I will manually update those since there are not a lot.

[chmnata]

Added all the other ones except these ones because I found some problem with either the centreline routing or the actual segment routing.... which is sad because I also need to change the queens park segments and change the baseline and backfill all daily numbers and monthly numbers and all matches and queens park dr :cry_cat: To be more specific, it routed through a minor arterial intersection which should've been used as an intersection but was not.