HERE Map Refresh 2021

[radumas]

• [x] List things that need to be changed
• [x] Pull in new map
• [ ] validate new map
• [ ] refresh data to new map
• [ ] document this annual process

[chmnata]

Things that need to be changed:

Functions that rely on old map:

• [x] covid.generate_citywide_tti(),
• [x] congestion.generate_citywide_tti_daily()
• [x] congestion.generate_segments_tti_weekly()
• [x] congestion.generate_segments_bi_monthly()

Webapp that rely on old map:

• [ ] tt request app

Mat view and view rely on old map:

• [ ] aharpal.monthly_tti_by_ward
• [ ] aharpal.monthly_tti_by_ward
• [ ] atp.neighbourhood_tti_sept
• [ ] atp.neighbourhood_tti_sept
• [ ] rapidto.rapidto_report_monthly_summary
• [ ] rapidto.rapidto_report_monthly_summary
• [ ] congestion.tti_map_am
• [ ] congestion.tti_map_pm

Things to create:

• [x] Sharedstreet lookup table for new map <-> old map,

• [x] Sharedstreet lookup table for new map <-> centreline

• [x] Congestion segments link lookup table

• [ ] Segment level aadt

• [ ] Baseline TTI

• [ ] Create new routing related functions and tables:

• [x] 1. routing_streets,

• [x] 2. routing nodes,

• [x] 3. traffic patterns,

• [ ] 4. routing function (get_links_btwn_px_19_4_tc())

Things to check:

• [x] If congestion.highway_segments_v5 missing any segments because of conflation
• [x] How many segments are successfully conflated through sharedstreets
• [x] Func class 1 to 3

[chmnata]

Will put this in readme after everything is done Steps:

• [x] Track down new map

• [x] Pull new map + other gis layers to our database with batch_upload.sh

• [x] Create new routing_streets and traffic_patterns

• [x] Match new map version to sharedstreets :pencil2: Writing 136184 matched edges: here_routing_streets_21_1.json.matched.geojson :pencil2: Writing 10305 unmatched lines: here_routing_streets_21_1.json.unmatched.geojson

[chmnata]

Total number of links, by functional class, for the last 3 versions

Versions	1	2	3	4	5
18_3	605	1166	3542	26894	145376
19_4	609	1197	3952	30772	271745
21_1	635	1253	3958	31457	272851

Total length of links in km, by functional class, for the last 3 versions

Versions	1	2	3	4	5
18_3	107	175.6	295.5	1599	12741.5
19_4	107	175.6	295.6	1596.8	17630.3
21_1	107	175.6	295.5	1596.6	17677.5

--SQL to generate summary of length and link count
select sum(case when dir_travel in ('B') then 2 else 1 end) as sum_links, 
		sum(case when dir_travel = 'B' then 2* st_length(ST_transform(geom, 98012)) 
			else st_length(ST_transform(geom, 98012)) end)/1000  as sum_length,  func_class,'18_3' as version
from here_gis.streets_att_18_3
join here_gis.streets_18_3 using (link_id) 
group by func_class, version

union all 

select sum(case when dir_travel in ('B') then 2 else 1 end) as sum_links, 
		sum(case when dir_travel = 'B' then 2* st_length(ST_transform(geom, 98012)) 
			else st_length(ST_transform(geom, 98012)) end)/1000 as sum_length,  func_class, '19_4' as version
from here_gis.streets_att_19_4 
join here_gis.streets_19_4 using (link_id) 
group by func_class, version

union all 

select sum(case when dir_travel in ('B') then 2 else 1 end) as sum_links, 
		sum(case when dir_travel = 'B' then 2* st_length(ST_transform(geom, 98012)) 
			else st_length(ST_transform(geom, 98012)) end)/1000 as sum_length,  func_class, '21_1' as version
from here_gis.streets_att_21_1 
join here_gis.streets_21_1 using (link_id) 
group by func_class, version
order by version, func_class

[radumas]

Interesting that Class 1 & 2 have significant change in number of links in this edition vs. last upgrade.

[chmnata]

Noticing some issues while conflating the new map version to congestion segments. We got a lot of change in func class 1 and 2 in this map version update, so there are a lot of changes in the congestion segments. I realized there are some lines are cut and merged in a way that doens't fit with the defined segments_v5. Example below: Segment in question (1532) 21_1 version links segmentation 19_4_tc version links segmentation Difference between 21_1 and 19_4_tc version

So now the links making up a segment doesn't necessarily starts and ends at the same node.

I discovered this when specifically looking at the kqqr conflation and the maximum difference in segment length between 2 version is ~50m. It is not a HUGE difference but based on the average length of segments (200m), missing 25% of length in a segment could mean a lot when we are aggregating up to a smaller corridor (with 80% length filter in place). Judging by this I think this map version update needs a rerun of a couple of stuff, e.g. baseline tti, and past tti data.

[aharpalaniTO]

ah - interesting. Any idea how many segments have a length difference of >5%? Definitely makes a strong case for re-generating our baselines as part of the overall refresh.

[chmnata]

For the kqqr stuff, 34/408 (8.3%) segment_ids have a length difference of >5%. I will check the entire map as well.

[chmnata]

a total of 192 unmatched links, mostly on Eglinton. Going to try a endcap=flat spatial join and see if that works.

These are the links with no match after spatial join, why is eglinton here????

Apparently they redraw eglinton into two lines for each direction :meow_dio:

[radumas]

Because the LRT is running down the middle of the street

[chmnata]

For the old links with no match in sharedstreet lookup, I did a spatial join with endcap=flat buffers ranging from 5 - 30m. These are the 60 links that did not get matched after spatial joins. Some of them are on eglinton, some at 6 point, and weirdly on a local road (the pond road) Checked here.routing_streets_21_1, and the pond road is missing 🤔 Which is weird because here_gis.streets_21_1 do have those links. Turns out I did something wrong in here.routing_streets_21_1 🤦🏼🤦🏼🤦🏼

Which means I have to rematch them in sharedstreets and re-do the above steps. 🤦🏼🤦🏼🤦🏼

[chmnata]

Recreated here.routing_streets_21_1, the pond road is still missing. It is also not a part of here_gis.traffic_streets_21_1. Checked here_gis.streets_att_21_1 and it is a func_class 5 road, not sure why this is included in the segment_v5 in the first place...

[chmnata]

Turns out I did not do something wrong in here.routing_streets_21_1, just forgot to add a suffix to here.routing_nodes`, no need to rematch.

[chmnata]

Summary one

For the old links with no match in sharedstreet lookup, I did a spatial join with endcap=flat buffers ranging from 5 - 30m. These are the 60 links that did not get matched after spatial joins. Some of them are on eglinton, some at 6 point, and on the pond road. This road was a part of the congestion segments because I used routing_streets_18_3b to create it before. It was included because it was tagged as Minor Arterial in the centreline, even though its a func_class 5 in here.

For the links that matched with sharedstreets and matched using spatial join, 16% of the segment_id has a length difference of more than 5% of its original length. However, glancing at the conflated segment at this point, you can see gaps between segments with no matching link_dirs. This is probably the result of old links that partly got matched to a new link. When I was doing the first round of spatial joins, I only included links that does not have a match, so for links thats partly matched, it was not included as a candidate for spatial join. I believe this can be improved by another round of spatial join for segments with gaps. Example: Segment_id with a length difference of more than 5% of its own length.

with match_result as (
	select segment_id, sum(ST_length(ST_transform(geom, 2952)) ) as total_length from congestion.segment_links_lookup_21_1_prep
	inner join here.routing_streets_21_1 using (link_dir)
	group by segment_id)

select segment_id, length, total_length, abs(total_length-length), abs(total_length-length)/length , geom
from match_result
inner join congestion.segments_v5 using (segment_id)
where abs(total_length-length)/length >= 0.005
order by abs(total_length-length) desc

This is what the new conflated segment looks like so far

Next steps

• Do another round of spatial joins with segments that has a length difference of more or equal 2%. Hopefully this can give us a more complete network by matching the links that only got partly matched.

[chmnata]

Did another round of spatial joins + manual updates of visible gaps in the network. Investigated missing segment_id, and dealt with them accordingly. Below is a map of all 38 missing segments that I'm going to leave out for the next version of congestion network. They are excluded either because they 1) don't exist anymore, 2) shouldn't be included to begin with, 3) merged with another segment, 4) changed completely and new segment_id was added to replace it (e.g. six point)

Note: The eglinton ones are merged with others, six point was replaced, pond road does not exist in routing_streets (Another thing to investigate after, I feel like they should have traffic as a minor arterial)

Next Step: Make sure Gardiner and Lakeshore are coded correctly. (Totally forgot about this during spatial join, will figure out a way to eliminate most of them automagically using street name)

[chmnata]

Got rid of double matched link_dir for gardiner and lakeshore with an sql (hyperlink later). Will now do a final check on complete segments_v6 network.

[chmnata]

Version 6 of congestion network is finally ready for review @aharpalaniTO ! It is conflated with a combination of sharedstreets matcher and spatial join, process found in sql/conflate_segments_21_1.sql. Completed several checks including:

1. Making sure each link_dir is matched to only 1 segment_id,
2. Manually checked segment_ids with more than 5% of length change ,
3. Check geometries with gaps by looking at ST_geometrytype,
4. Gardiner and Lakeshore only has link with its respective street name.

Segment table: congestion.segments_v6 Link lookup table: congestion.segment_links_v6_21_1 Highway table: congestion.highway_segments_v6

Note: I did also got rid of some segments that shouldn't be included to begin with (e.g. merged absurdly short ones), and included a few segments that was missing one direction. Overall should be minimal changes and shouldn't affect citywide tti if we re run the functions.

[chmnata]

Total number of links, by functional class, for the last 3 versions Versions 1 2 3 4 5 18_3 605 1166 3542 26894 145376 19_4 609 1197 3952 30772 271745 21_1 635 1253 3958 31457 272851

Total length of links in km, by functional class, for the last 3 versions Versions 1 2 3 4 5 18_3 107 175.6 295.5 1599 12741.5 19_4 107 175.6 295.6 1596.8 17630.3 21_1 107 175.6 295.5 1596.6 17677.5

--SQL to generate summary of length and link count
select sum(case when dir_travel in ('B') then 2 else 1 end) as sum_links, 
		sum(case when dir_travel = 'B' then 2* st_length(ST_transform(geom, 98012)) 
			else st_length(ST_transform(geom, 98012)) end)/1000  as sum_length,  func_class,'18_3' as version
from here_gis.streets_att_18_3
join here_gis.streets_18_3 using (link_id) 
group by func_class, version

union all 

select sum(case when dir_travel in ('B') then 2 else 1 end) as sum_links, 
		sum(case when dir_travel = 'B' then 2* st_length(ST_transform(geom, 98012)) 
			else st_length(ST_transform(geom, 98012)) end)/1000 as sum_length,  func_class, '19_4' as version
from here_gis.streets_att_19_4 
join here_gis.streets_19_4 using (link_id) 
group by func_class, version

union all 

select sum(case when dir_travel in ('B') then 2 else 1 end) as sum_links, 
		sum(case when dir_travel = 'B' then 2* st_length(ST_transform(geom, 98012)) 
			else st_length(ST_transform(geom, 98012)) end)/1000 as sum_length,  func_class, '21_1' as version
from here_gis.streets_att_21_1 
join here_gis.streets_21_1 using (link_id) 
group by func_class, version
order by version, func_class

This is WRONG, I was using 19_4 instead of 19_4_tc. There are actually a lot of new local roads , I think the version 19_4 is more similar to 21_1 than 19_4. Updated tables below: Total number of links, by functional class, for the last 3 versions Total length of links in km, by functional class, for the last 3 versions New links Retired links

[chmnata]

I am here a year later to investigate the mystery of Pond Road. We do indeed have data in here.ta for the Pond Road. :meow_dio: The reason why the Pond Road is not a part of routing_streets_21_1 is because ar_traff = 'N' This section of The Pond Road has a value of N in ar_traff, which was used in creating routing_streets. The filters for routing_streets are :

• paved= 'Y'
• poiaccess= 'N'
• ar_auto= 'Y'
• ar_traff= 'Y'

It fits all the other criteria, just not the ar_traff :meow_dundundun:

However, there aren't that much data for these link_dir, averaging around 5 to 11 5 min observation per day in 2022 May.