Congestion network 2.0: Route segments

[chmnata]

I created a congestion.routing_grid for routing the first MVP version of congestion segment based on routing_streets_18_3.

 with temp as (
 select * from here.routing_streets_18_3
 left join  congestion.routing_grid using (link_dir)
where routing_grid.link_dir is null )
select * 
from here.routing_streets_21_1
left join temp using (link_dir)
where  temp.link_dir is null 

By excluding the link_dirs that were excluded before, is gonna save me some time for cleaning the new routing network.

Will also get rid of some link_dir by street name, mostly the ones with null for names in streets_att_21_1 (those are ramps)

[chmnata]

looooooks generally fine

[chmnata]

Was gonna use the old routing_boundary for routing but ended up creating a new one and made smaller sections cause I got a memory error. Re-used the code from last time and changed the base routing network and the nodes.

[chmnata]

Rationale for merging small segments

During the segment cleaning process (aka removing incorrectly routed segment + add in missing segments), I was combining smol segments with largely ones ( Combine A + B to make C) because: 1) No use for smol segments as they have a higher chance of not having enough data for the 80% rule 2) We don't use smol segments for reporting

But then I realized by combining smaller segments, it means they are less routable because some nodes will not be available after merging :meow_dundundun: . However: 1) we very rarely create segments that "turns", and mostly route corridors that are straight and on the same street 2) This version does not have ramps for its not good for getting travel times for the shortest path anyways

Discussion with @radumas :

• The small segments aren't really used for reporting but would be used for routing purposes. However, there is a distinction between straight line routing and routing routing (aka routing for OD to get travel time for shortest path). Raph also mentioned that it would be GREAT if the congestion network solves the performance issues with the travel time request app, though not having the smol segments might lead to some weird bugs when creating segments in the app. Based on past experience of what people created in the app for data requests, they are mostly straight line corridors. So we could have a warning to only draw straight line corridors in the app to reduce potential weirdness in drawing "turning segments".

SO for the purposes of this version of the network, the use cases we are mainly trying to tackle is to produce aggregated product for data requests and projects. As data request and projects usually calls for straight line corridors, we will continue to merge smaller segments to bigger ones. This network will not support routing routing since there are no ramps and some nodes will not be available for routing after merging segments. :meow_salute:

[chmnata]

Segments were routed and some cleaning were done manually, as well as QC. Inserted new nodes and deleted new nodes where necessary, and rerouted segments where nodes changed. Also got rid of unwanted segments like this (the red one) . QC:

• Checked the network for duplicate use of link_dir and took care of those.
• Checked the network_nodes layer against the final routed layer to see if there are nodes that were not used
• Checked the segment lengths and validate the segments that are v smol and v long.
• Also essentially checked the segments 1 by 1 manually when merging little segments

Final network segment