Create corridors from major arterial to major arterial

[chmnata]

To get all link_dir that are Major arterial, I tried to join them using st_name and UPPER(lf_name)

with centreline as (
SELECT geo_id, lf_name, geom, fcode_desc FROM gis.centreline 
where fcode_desc in ('Major Arterial'))
,here as (                          
select * from here_gis.streets_18_3 
join here_gis.streets_att_18_3 using (link_id))
select link_id, geo_id, lf_name, st_name, here.geom as here_geom, centreline.geom as centreline_geom, fcode_desc
from centreline
join here on UPPER(lf_name) = st_name

Includes almost everything, just need to add and remove a few streets.

[chmnata]

Include missing street, similar sql was used when creating the initial grid

with centreline as (
 SELECT geo_id, lf_name, geom, fcode_desc FROM gis.centreline 
 where fcode_desc in ('Major Arterial'))
 ,here as (                         
 select * from here_gis.streets_18_3 
 join here_gis.streets_att_18_3 using (link_id))
 , include as (select link_id, st_name, here.geom 
 from centreline
 join here on UPPER(lf_name) = st_name)

 select link_id, st_name, geom  from include
 union
 SELECT link_id, st_name, geom from here_gis.streets_18_3 
 JOIN here_gis.streets_att_18_3 using (link_id)
 WHERE st_name IN ('MT PLEASANT RD','ALLEN RD', 'LEASIDE BRG', 'BROWNS LINE', 'N QUEEN ST','S KINGSWAY', 'BRIDLETOWNE CIR', 'SEWELLS RD', 'HWY-27') 

[chmnata]

Using the following sql to find the Major Arterial intersections of centreline

with temp as (select * from (
select from_intersection_id as int_id
from _centreline
where feature_code_desc in ('Major Arterial')
union all
select to_intersection_id as int_id
from _centreline
where feature_code_desc in ('Major Arterial'))a
group by int_id
having count(int_id)>2)
, cul as (
select * from (
select from_intersection_id as int_id
from _centreline
where feature_code_desc in ('Major Arterial')
union all
select to_intersection_id as int_id
from _centreline
where feature_code_desc in ('Major Arterial'))a
group by int_id
having count(int_id)=1)

select * from gis.centreline_intersection 
inner join (select * from temp 
            union 
            select * from cul )a using(int_id)

[chmnata]

However, to find the here equivalent nodes with this sql will not return all necessary nodes, as streets with medians will only have the closest node returned.

SELECT centreline_int.int_id::integer AS int,
    centreline_int.geom,
    nodes.node_id,
    nodes.node_geom
   FROM centreline_int
     CROSS JOIN LATERAL ( SELECT node_id,
            z.geom AS node_geom
           FROM here_gis.zlevels_18_3 z
            inner join congestion.corridor_routing using (link_id)
          ORDER BY (z.geom <-> centreline_int.geom)
         LIMIT 1) nodes;

One approach is to find all here intersections with congestion.corridor_routing and then filter it with a buffer of centreline's intersection.

[chmnata]

Cleaned congestion.corridor_routing in congestion.corridor_routing_cleaned. Now we can get a more accurate sets of here intersection with

with int as (
    select * from (                                                                        
    select ref_in_id from (select distinct link_id from congestion.corridor_routing_cleaned)a
    inner join here_gis.streets_att_18_3 using (link_id)
    union all
    select nref_in_id  from (select distinct link_id from congestion.corridor_routing_cleaned)b
    inner join here_gis.streets_att_18_3 using (link_id)
                 )a
group by ref_in_id                                          
having count(ref_in_id) >=3 or count(ref_in_id) = 1)
select distinct ref_in_id, geom 
from int inner join here_gis.zlevels_18_3 on node_id = ref_in_id

Would ideally also clean the intersection a little bit, or I could just the merging function to merge "smaller" up to a certain length, though this might mess up corridors in downtown.

-- updated sql that includes 2 additional intersections with count(ref_in_id) = 2
with int as (
    select * from (                                                                        
    select ref_in_id from (select distinct link_id from congestion.corridor_routing_cleaned)a
    inner join here_gis.streets_att_18_3 using (link_id)
    union all
    select nref_in_id  from (select distinct link_id from congestion.corridor_routing_cleaned)b
    inner join here_gis.streets_att_18_3 using (link_id)
                 )a
group by ref_in_id                                          
having count(ref_in_id) >=3 or count(ref_in_id) = 1 )
select distinct ref_in_id, geom 
from int inner join here_gis.zlevels_18_3 on node_id = ref_in_id
union all
select node_id, geom
from here_gis.zlevels_18_3 where node_id in (30326082,30362000)

[chmnata]

trying to route with this

with int as (
    select * from (                                                                        
    select ref_in_id from (select distinct link_id from congestion.corridor_routing_cleaned)a
    inner join here_gis.streets_att_18_3 using (link_id)
    union all
    select nref_in_id  from (select distinct link_id from congestion.corridor_routing_cleaned)b
    inner join here_gis.streets_att_18_3 using (link_id)
                 )a
group by ref_in_id                                          
having count(ref_in_id) >=3 or count(ref_in_id) = 1 )
, int_set as (select distinct ref_in_id, geom 
from int inner join here_gis.zlevels_18_3 on node_id = ref_in_id
union all
select node_id, geom
from here_gis.zlevels_18_3 where node_id in (30326082,30362000))
,int_node as (
select array_agg(ref_in_id::int) as ints from int_set
), results as(
SELECT * from int_node
, lateral pgr_dijkstra('SELECT id, source::int, target::int, length::int as cost FROM congestion.routing_grid',ints,ints ) results
INNER JOIN congestion.routing_grid ON id = edge
ORDER BY start_vid, end_vid, path_seq)
SELECT start_vid, end_vid, array_agg(node) as node_set, array_agg(id) as link_set, ST_LINEMERGE(ST_UNION(results.geom)) as geom,
    ST_length(ST_transform(ST_LINEMERGE(ST_UNION(results.geom)), 2952)) as length
    INTO congestion.route_corridor 
FROM results
LEFT OUTER JOIN here_int ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid
HAVING COUNT(ref_in_id) = 0

[chmnata]

trying to route with this

with int as (
  select * from (                                                                        
  select ref_in_id from (select distinct link_id from congestion.corridor_routing_cleaned)a
  inner join here_gis.streets_att_18_3 using (link_id)
  union all
  select nref_in_id  from (select distinct link_id from congestion.corridor_routing_cleaned)b
  inner join here_gis.streets_att_18_3 using (link_id)
               )a
group by ref_in_id                                            
having count(ref_in_id) >=3 or count(ref_in_id) = 1   )
, int_set as (select distinct ref_in_id, geom 
from int inner join here_gis.zlevels_18_3 on node_id = ref_in_id
union all
select node_id, geom
from here_gis.zlevels_18_3 where node_id in (30326082,30362000))
,int_node as (
select array_agg(ref_in_id::int) as ints from int_set
), results as(
SELECT * from int_node
, lateral pgr_dijkstra('SELECT id, source::int, target::int, length::int as cost FROM congestion.routing_grid',ints,ints ) results
INNER JOIN congestion.routing_grid ON id = edge
ORDER BY start_vid, end_vid, path_seq)
SELECT start_vid, end_vid, array_agg(node) as node_set, array_agg(id) as link_set, ST_LINEMERGE(ST_UNION(results.geom)) as geom,
  ST_length(ST_transform(ST_LINEMERGE(ST_UNION(results.geom)), 2952)) as length
  INTO congestion.route_corridor 
FROM results
LEFT OUTER JOIN here_int ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid
HAVING COUNT(ref_in_id) = 0

ERROR:  invalid memory alloc request size 2971266928
CONTEXT:  PL/pgSQL function pgr_dijkstra(text,anyarray,anyarray,boolean) line 3 at RETURN QUERY
SQL state: XX000

haha

[chmnata]

corridor routing result

[chmnata]

Cleaned up version, lookup in congestion.corridors_v1_merged_lookup corridor's geom and length in congestion.corridors_v1_merged