Build MVP process for creating grid-based segments from HERE links #40
Assignees
Comments
2. Grid Definition (NC supported by RD) - 7.0 person-days
func_class in 1,2,3,4
Create HERE network with minor arterial or above
What major arterial is not included in select * from _centreline where linear_name_full in ('Birchmount Road','Midland Ave',
'Overlea Blvd','Martin Grove Rd', 'Evans Ave', 'Davenport Rd', 'Church St', 'Bay St',
'Carlton St', 'Queen St W', 'Queen St E', 'King St E', 'King St W'..............)HERE's street name attribute create table matched_w_stname as (
with centreline as (
SELECT centreline_id, linear_name_full, geom, feature_code_desc FROM _centreline
where feature_code_desc in ('Expressway','Expressway Ramp',
'Major Arterial','Major Arterial Ramp','Minor Arterial'))
,here as (
select * from here_gis.streets_18_3
join here_gis.streets_att_18_3 using (link_id))
select link_id, centreline_id, linear_name_full, st_name, here.geom as here_geom, centreline.geom as centreline_geom, feature_code_desc
from centreline
join here on UPPER(linear_name_full) = st_name)(orange lines are centreline that is minor arterial or above, purple lines are here links that matched using st_name) -- insert missing roads
INSERT INTO matched_w_stname(link_id, st_name, here_geom)
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', 'HWY-27', 'BROWNS LINE', 'N QUEEN ST','S KINGSWAY', 'BRIDLETOWNE CIR', 'SEWELLS RD');
-- insert func
insert into matched_w_stname(link_id, st_name, here_geom)
select * from (
select link_id, st_name, geom from here_gis.streets_18_3
join here_gis.streets_att_18_3 using (link_id)
where func_class in ('1', '2', '3')
except
select link_id, st_name, here_geom from natalie.matched_w_stname )aWhat the layer looks like after inserting missing some ramps on the 401 but we are getting rid of it anyways, wondering if i should include func_class 4 so it will include all the ramps create table raw_grid as (
select distinct link_id, st_name, geom from (
select link_id, st_name, geom from here_gis.streets_18_3 join here_gis.streets_att_18_3 using (link_id) where func_class = '4'
union
select link_id, st_name, here_geom from matched_w_stname)a)Links to add 1146580128, 1146580126, 1146580127, 1146580125, 29574890, 1134192879, 1134192878,792773745
,29600671 , 29568321, 133791150, 29525028, 802896579, 802896580, 802896578, 802896574,
802896575, 133791101, 133788561, 29566646, 29551312, 1148921948, 1148921947, 29597674,
29500609, 29500616, 29500634, 29554390, 29554383link_dir segment lengths
If I can find the node_id for start int to end int, then i can use this, to get the seq of link_dir from one int to another, but how can i find all the start and end int? with base (origin_source, end_target) as
(values (30415801, 883770922))
, result as (
SELECT * FROM base
cross join lateral pgr_dijkstra('SELECT id, source::int, target::int, length::int as cost from here.routing_streets_18_3b', origin_source::int, end_target::int))
select *
from result inner join here.routing_streets_18_3b on edge=idcreate table test_int as
with temp as (select * from (
select from_intersection_id as int_id
from _centreline
where feature_code_desc in ('Expressway','Expressway Ramp','Major Arterial','Major Arterial Ramp','Minor Arterial')
union all
select to_intersection_id as int_id
from _centreline
where feature_code_desc in ('Expressway','Expressway Ramp','Major Arterial','Major Arterial Ramp','Minor Arterial'))a
group by int_id
having count(int_id)>2 )
select * from gis.centreline_intersection inner join temp using(int_id)
Thoughts:
https://gis.stackexchange.com/questions/94203/grouping-connected-linestrings-in-postgis http://postgis.net/docs/manual-2.2/ST_LocateBetween.html Next steps:
Attempting to get here intersection nodes... --here intersection nodes
create table here_node as
with int as (select * from (
select ref_in_id from grid_updated
inner join here_gis.streets_att_18_3 using (link_id)
union all
select nref_in_id from grid_updated
inner join here_gis.streets_att_18_3 using (link_id))a
group by ref_in_id
having count(ref_in_id) >=3 )
select distinct ref_in_id, geom from int inner join here_gis.zlevels_18_3 on node_id = ref_in_idMISSING cul-de-sac create table here_int as
with int as (
select * from (
select ref_in_id from grid_updated
inner join here_gis.streets_att_18_3b using (link_id)
union all
select nref_in_id from grid_updated
inner join here_gis.streets_att_18_3b using (link_id))a
group by ref_in_id
having count(ref_in_id) >=3
union all
select * from (
select ref_in_id from grid_updated
inner join here_gis.streets_att_18_3b using (link_id)
union all
select nref_in_id from grid_updated
inner join here_gis.streets_att_18_3b using (link_id))b
group by ref_in_id
having 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
Weirdly missing one intersection??????? Whatever, will do cleaning later, should try to figure out if routing works first
create materialized view congestion.routing_grid as
select link_dir, id, source, target, length, routing_streets_18_3b.geom, edge_id from grid_updated
inner join here.routing_streets_18_3b using (link_id)Testing with small set --testing with small set
with int_node as (
select ref_in_id from here_int
where ref_in_id in (30473958,30475352,30475820,30479243,30479656,30480150,30480151,30480170,30480316, 30480325,30474992,30478533,30479115, 30473872,30474577 )
)
, results AS (SELECT *
FROM pgr_dijkstra('SELECT id, source::int, target::int, length::int as cost FROM congestion.routing_grid', ARRAY[30473958,30475352,30475820,30479243,30479656,30480150,30480151,30480170,30480316, 30480325,30474992,30478533,30479115, 30473872,30474577],
ARRAY[30473958,30475352,30475820,30479243,30479656,30480150,30480151,30480170,30480316, 30480325,30474992,30478533,30479115, 30473872,30474577]) 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 nodes, ST_LINEMERGE(ST_UNION(results.geom))
FROM results
LEFT OUTER JOIN int_node ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid
HAVING COUNT(ref_in_id) = 0
i am so happy Need to find a way to intelligently find which sets of routes are better, e.g. if the link length between a set of intersections are less than 20m then merge it to the next intersection to create a longer line, and stop doing that till it reaches some sensible length. Can test with the above set since it has two sets of intersections that is of a median street --try out small sets of ints
with int_node as (
select ref_in_id from here_int
where ref_in_id in (30473958,30475352,30475820,30479243,30479656,30480150,30480151,30480170,30480316, 30480325,30474992,30478533,30479115, 30473872,30474577 )
)
, results AS (SELECT *
FROM pgr_dijkstra('SELECT id, source::int, target::int, length::int as cost FROM congestion.routing_grid', ARRAY[30473958,30475352,30475820,30479243,30479656,30480150,30480151,30480170,30480316, 30480325,30474992,30478533,30479115, 30473872,30474577],
ARRAY[30473958,30475352,30475820,30479243,30479656,30480150,30480151,30480170,30480316, 30480325,30474992,30478533,30479115, 30473872,30474577]) results
INNER JOIN congestion.routing_grid ON id = edge
ORDER BY start_vid, end_vid, path_seq)
, routed as (
SELECT start_vid, end_vid, array_agg(id) as link_set, ST_LINEMERGE(ST_UNION(results.geom)),
ST_length(ST_transform(ST_LINEMERGE(ST_UNION(results.geom)), 2952)) as length, COUNT(ref_in_id) as int_count
FROM results
LEFT OUTER JOIN int_node ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid)
, next_best as (
select a.start_vid, a.end_vid, a.length, a.link_set, b.start_vid as new_start_vid, b.end_vid as new_end_vid, b.length as new_length, b.link_set as new_link_set from routed b
cross join lateral (select * from routed
where int_count = 1 and b.link_set <@ routed.link_set
order by length
limit 1)a
where b.int_count = 0)
select case when length >100 then start_vid else new_end_vid end as start_vid,
case when length >100 then end_vid else new_end_vid end as end_vid,
case when length >100 then length else new_length end as length,
case when length >100 then link_set else new_link_set end as link_set
from next_bestThis gets the next routed set.........should make it into a loop so it does that till everything is at least 200m, but lets try to scale it up a little and see if it works first WAIT, there are duplicated links because I didnt get rid of the set of intersection that the smaller set merged in to. I will have to filter it out by looking at which node_set is the same and select the longer one OMG WAIT, I also have to append the node to the merged ones....... or do i? no i dont, its not merged its just selecting the next routed version. wait then how are the node_list the same? because it is not the same....
they now return no duplicates, with this super inefficient query --try out small sets of ints
--create materialized view test_route as
with int_node as (
select ref_in_id from here_int
where ref_in_id in
(30473958,30475352,30475820,30479243,30479656,30480150,30480151,30480170,30480316, 30480325,30474992,
30478533,30479115, 30473872,30474577, 30478705,30479940,30480134,30480135,30487503,30487867,252330781,30488286,30488333,30488618 )
)
, results AS (SELECT *
FROM pgr_dijkstra('SELECT id, source::int, target::int, length::int as cost FROM congestion.routing_grid', ARRAY[30473958,30475352,30475820,30479243,30479656,30480150,30480151,30480170,30480316, 30480325,30474992,
30478533,30479115, 30473872,30474577, 30478705,30479940,30480134,30480135,30487503,30487867,252330781,30488286,30488333,30488618],
ARRAY[30473958,30475352,30475820,30479243,30479656,30480150,30480151,30480170,30480316, 30480325,30474992,
30478533,30479115, 30473872,30474577, 30478705,30479940,30480134,30480135,30487503,30487867,252330781,30488286,30488333,30488618]) results
INNER JOIN congestion.routing_grid ON id = edge
ORDER BY start_vid, end_vid, path_seq)
, routed as (
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, COUNT(ref_in_id) as int_count
FROM results
LEFT OUTER JOIN int_node ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid)
, next_best as (
select b.start_vid, b.end_vid, b.length, b.node_set, b.link_set, b.geom,
a.start_vid as new_start_vid, a.end_vid as new_end_vid, a.length as new_length, a.node_set as new_node_set, a.link_set as new_link_set, a.geom as new_geom
from routed b
cross join lateral (select * from routed
where int_count = 1 and b.link_set <@ routed.link_set
order by length
limit 1)a
where b.int_count = 0)
, result as (
select case when length >100 then start_vid else new_start_vid end as start_vid,
case when length >100 then end_vid else new_end_vid end as end_vid,
case when length >100 then length else new_length end as length,
case when length >100 then node_set else new_node_set end as node_set,
case when length >100 then link_set else new_link_set end as link_set,
case when length >100 then geom else new_geom end as geom
from next_best)
select * from result
except
select a.* from result a, result b where a.link_set <@ b.link_set and (a.start_vid != b.start_vid or a.end_vid != b.end_vid)
time to scale up moreeeeeeee with int_node as (
select array_agg(ref_in_id::int) as ref_in_id from here_int
)
SELECT results.* from int_node
, lateral pgr_dijkstra('SELECT id, source::int, target::int, length::int as cost FROM congestion.routing_grid',ref_in_id,ref_in_id ) results
INNER JOIN congestion.routing_grid ON id = edge
ORDER BY start_vid, end_vid, path_seqcreate materialized view congestion.routed_int2int as
with int_node as (
select array_agg(ref_in_id::int) as ints from here_int
), 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
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--sets of int to try out
(30458305,30458307,30458408,30458435,30458436,30458451,30458470,30459481,30549485,30459492,30459499,30459506,30459552,30459553,30459557,30459558,30459561,30459606,30459621,30459622,30459625,30459652)
)Things to figure out:
Examples of potential problematic sets of intersection
Added directionality based on Could try to order it by azimuth or other types of direction based parameter, and then order it by length. We should weigh direction heavier than length. Used with int_node as (
select ref_in_id, geom from here_int
where ref_in_id in
(30414610,30414755,30414771,30414830,30414866,30414892,30414894,30414896,30414899,30414902,30414915,30414916,30414918,30414919,30414921,30414922,30414923,30414926,30414928,30414932,30414958,30414959,30414968,30414969,30414972,30414973,30414993,30414994,30415032,30415036,30415038,30415047,30415051,30415106,30415111,30415115,30415150,30415158,30415164,30415168,30415170,30415175,30415182,30415192,30415330,30415343,30415357,30415730,30415731,30415781,30415782,30415784,30415785)
), results AS (SELECT *
FROM pgr_dijkstra('SELECT id, source::int, target::int, length::int as cost FROM congestion.routing_grid', ARRAY[30414610,30414755,30414771,30414830,30414866,30414892,30414894,30414896,30414899,30414902,30414915,30414916,30414918,30414919,30414921,30414922,30414923,30414926,30414928,30414932,30414958,30414959,30414968,30414969,30414972,30414973,30414993,30414994,30415032,30415036,30415038,30415047,30415051,30415106,30415111,30415115,30415150,30415158,30415164,30415168,30415170,30415175,30415182,30415192,30415330,30415343,30415357,30415730,30415731,30415781,30415782,30415784,30415785],
ARRAY[30414610,30414755,30414771,30414830,30414866,30414892,30414894,30414896,30414899,30414902,30414915,30414916,30414918,30414919,30414921,30414922,30414923,30414926,30414928,30414932,30414958,30414959,30414968,30414969,30414972,30414973,30414993,30414994,30415032,30415036,30415038,30415047,30415051,30415106,30415111,30415115,30415150,30415158,30415164,30415168,30415170,30415175,30415182,30415192,30415330,30415343,30415357,30415730,30415731,30415781,30415782,30415784,30415785]) results
INNER JOIN congestion.routing_grid ON id = edge
ORDER BY start_vid, end_vid, path_seq)
, routed as (
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, COUNT(ref_in_id) as int_count
FROM results
LEFT OUTER JOIN int_node ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid)
, next_best as (
select b.start_vid, b.end_vid, b.length, b.node_set, b.link_set, b.geom,
a.start_vid as new_start_vid, a.end_vid as new_end_vid, a.length as new_length, a.node_set as new_node_set, a.link_set as new_link_set, ST_HausdorffDistance, a.geom as new_geom
from routed b
cross join lateral (
select *, ST_HausdorffDistance(b.geom, routed.geom) from routed
where int_count = 1 and b.link_set <@ routed.link_set
and gis.direction_from_line(b.geom) = gis.direction_from_line(routed.geom)
--and ST_HausdorffDistance(b.geom, routed.geom) < 1
order by ST_HausdorffDistance(b.geom, routed.geom) desc, length
limit 1)a
where b.int_count = 0)
, result as (
select case when length >100 then start_vid else new_start_vid end as start_vid,
case when length >100 then end_vid else new_end_vid end as end_vid,
case when length >100 then length else new_length end as length,
case when length >100 then node_set else new_node_set end as node_set,
case when length >100 then link_set else new_link_set end as link_set,
case when length >100 then geom else new_geom end as geom
from next_best)
select * from result
except
select a.* from result a, result b where a.link_set <@ b.link_set and (a.start_vid != b.start_vid or a.end_vid != b.end_vid)
Another problem, if I rank by ST_HausdorffDistance, segment get merge to the red line but i want it to merge with blue. Should try to do something like, if the ST_HausdorffDistance is between x and y then choose the one that is shorter in length to merge. Trying to get the next three best choice, and do something with that later, perhaps a ratio of hausdorffdistance? with int_node as (
select ref_in_id, geom from here_int
where ref_in_id in
(30414610,30414755,30414771,30414830,30414866,30414892,30414894,30414896,30414899,30414902,30414915,30414916,30414918,30414919,30414921,30414922,30414923,30414926,30414928,30414932,30414958,30414959,30414968,30414969,30414972,30414973,30414993,30414994,30415032,30415036,30415038,30415047,30415051,30415106,30415111,30415115,30415150,30415158,30415164,30415168,30415170,30415175,30415182,30415192,30415330,30415343,30415357,30415730,30415731,30415781,30415782,30415784,30415785)
), results AS (SELECT *
FROM pgr_dijkstra('SELECT id, source::int, target::int, length::int as cost FROM congestion.routing_grid', ARRAY[30414610,30414755,30414771,30414830,30414866,30414892,30414894,30414896,30414899,30414902,30414915,30414916,30414918,30414919,30414921,30414922,30414923,30414926,30414928,30414932,30414958,30414959,30414968,30414969,30414972,30414973,30414993,30414994,30415032,30415036,30415038,30415047,30415051,30415106,30415111,30415115,30415150,30415158,30415164,30415168,30415170,30415175,30415182,30415192,30415330,30415343,30415357,30415730,30415731,30415781,30415782,30415784,30415785],
ARRAY[30414610,30414755,30414771,30414830,30414866,30414892,30414894,30414896,30414899,30414902,30414915,30414916,30414918,30414919,30414921,30414922,30414923,30414926,30414928,30414932,30414958,30414959,30414968,30414969,30414972,30414973,30414993,30414994,30415032,30415036,30415038,30415047,30415051,30415106,30415111,30415115,30415150,30415158,30415164,30415168,30415170,30415175,30415182,30415192,30415330,30415343,30415357,30415730,30415731,30415781,30415782,30415784,30415785]) results
INNER JOIN congestion.routing_grid ON id = edge
ORDER BY start_vid, end_vid, path_seq)
, routed as (
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, COUNT(ref_in_id) as int_count
FROM results
LEFT OUTER JOIN int_node ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid)
select rank() over (partition by b.start_vid, b.end_vid, b.length, b.node_set, b.link_set, b.geom order by ST_HausdorffDistance, b.length), b.start_vid, b.end_vid, b.length, b.node_set, b.link_set, b.geom,
a.start_vid as new_start_vid, a.end_vid as new_end_vid, a.length as new_length, a.node_set as new_node_set, a.link_set as new_link_set, ST_HausdorffDistance, a.geom as new_geom
from routed b
cross join lateral (
select *, ST_HausdorffDistance(b.geom, routed.geom) from routed
where int_count = 1 and b.link_set <@ routed.link_set
and gis.direction_from_line(b.geom) = gis.direction_from_line(routed.geom)
order by ST_HausdorffDistance(b.geom, routed.geom) desc, length
limit 3)a
where b.int_count = 0
what i have for meow, but it doesn't work --try out small sets of ints
drop materialized view test_route;
create materialized view test_route as
with int_node as (
select ref_in_id, geom from here_int
where ref_in_id in
(30414610,30414755,30414771,30414830,30414866,30414892,30414894,30414896,30414899,30414902,30414915,30414916,30414918,30414919,30414921,30414922,30414923,30414926,30414928,30414932,30414958,30414959,30414968,30414969,30414972,30414973,30414993,30414994,30415032,30415036,30415038,30415047,30415051,30415106,30415111,30415115,30415150,30415158,30415164,30415168,30415170,30415175,30415182,30415192,30415330,30415343,30415357,30415730,30415731,30415781,30415782,30415784,30415785)
), results AS (SELECT *
FROM pgr_dijkstra('SELECT id, source::int, target::int, length::int as cost FROM congestion.routing_grid', ARRAY[30414610,30414755,30414771,30414830,30414866,30414892,30414894,30414896,30414899,30414902,30414915,30414916,30414918,30414919,30414921,30414922,30414923,30414926,30414928,30414932,30414958,30414959,30414968,30414969,30414972,30414973,30414993,30414994,30415032,30415036,30415038,30415047,30415051,30415106,30415111,30415115,30415150,30415158,30415164,30415168,30415170,30415175,30415182,30415192,30415330,30415343,30415357,30415730,30415731,30415781,30415782,30415784,30415785],
ARRAY[30414610,30414755,30414771,30414830,30414866,30414892,30414894,30414896,30414899,30414902,30414915,30414916,30414918,30414919,30414921,30414922,30414923,30414926,30414928,30414932,30414958,30414959,30414968,30414969,30414972,30414973,30414993,30414994,30415032,30415036,30415038,30415047,30415051,30415106,30415111,30415115,30415150,30415158,30415164,30415168,30415170,30415175,30415182,30415192,30415330,30415343,30415357,30415730,30415731,30415781,30415782,30415784,30415785]) results
INNER JOIN congestion.routing_grid ON id = edge
ORDER BY start_vid, end_vid, path_seq)
, routed as (
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, COUNT(ref_in_id) as int_count
FROM results
LEFT OUTER JOIN int_node ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid)
, next_best as (
select rank() over (partition by b.start_vid, b.end_vid, b.length, b.node_set, b.link_set, b.geom order by ST_HausdorffDistance desc, b.length), b.start_vid, b.end_vid, b.length, b.node_set, b.link_set, b.geom,
a.start_vid as new_start_vid, a.end_vid as new_end_vid, a.length as new_length, a.node_set as new_node_set, a.link_set as new_link_set, ST_HausdorffDistance, a.geom as new_geom
from routed b
cross join lateral (
select *, ST_HausdorffDistance(b.geom, routed.geom) from routed
where int_count = 1 and b.link_set <@ routed.link_set and gis.direction_from_line(b.geom) = gis.direction_from_line(routed.geom)
order by ST_HausdorffDistance(b.geom, routed.geom) desc, length
limit 2)a
where b.int_count = 0)
-- still
, result as (
select *,
case when st_hausdorffdistance/lag(st_hausdorffdistance, -1) over (partition by start_vid, end_vid, length, node_set, link_set, geom order by rank) < 5
and new_length-lag(new_length, -1) over (partition by start_vid, end_vid, length, node_set, link_set, geom order by rank) > 20 then 2 else 1 end as selection
from next_best
where length <= 100
union all select *, 1 as selection from next_best where length >100)
, final as (
select case when length >100 then start_vid else new_start_vid end as start_vid,
case when length >100 then end_vid else new_end_vid end as end_vid,
case when length >100 then length else new_length end as length,
case when length >100 then node_set else new_node_set end as node_set,
case when length >100 then link_set else new_link_set end as link_set,
case when length >100 then geom else new_geom end as geom
from result
where rank = selection)
select * from final
except
select a.* from final a, result b where a.link_set <@ b.link_set and (a.start_vid != b.start_vid or a.end_vid != b.end_vid)Made a temp fix to selection with this code --try out small sets of ints
drop materialized view test_route;
create materialized view test_route as
with int_node as (
select ref_in_id, geom from here_int
where ref_in_id in
(30362979,30362987,30362990,30363018,30363068,30363077,30363091,30363105,30363474,30363537,30363545,30412386,30414528,30414531,30414534,30414540,30414551,30414573,30414575,30414576,30414577,30414578,30414610,30414649,30414650,30414684,30414685,30414690,30414695,30414702,30414716,30414755,30414771,30414817,30414830,30414866,30414892,30414894,30414896,30414899,30414902,30414915,30414916,30414918,30414919,30414921,30414922,30414923,30414926,30414928,30414932,30414958,30414959,30414968,30414969,30414972,30414973,30414993,30414994,30415032,30415036,30415038,30415047,30415051,30415066,30415070,30415081,30415106,30415111,30415115,30415150,30415158,30415164,30415168,30415170,30415175,30415182,30415192,30415330,30415343,30415357,30415380,30415399,30415730,30415731,30415781,30415782,30415784,30415785,30415791,30415792,30415793,30415797,30415799,30415966,30415967,30415970,832199856,862292856,863218141,863218143)), results AS (SELECT *
FROM pgr_dijkstra('SELECT id, source::int, target::int, length::int as cost FROM congestion.routing_grid', ARRAY[330362979,30362987,30362990,30363018,30363068,30363077,30363091,30363105,30363474,30363537,30363545,30412386,30414528,30414531,30414534,30414540,30414551,30414573,30414575,30414576,30414577,30414578,30414610,30414649,30414650,30414684,30414685,30414690,30414695,30414702,30414716,30414755,30414771,30414817,30414830,30414866,30414892,30414894,30414896,30414899,30414902,30414915,30414916,30414918,30414919,30414921,30414922,30414923,30414926,30414928,30414932,30414958,30414959,30414968,30414969,30414972,30414973,30414993,30414994,30415032,30415036,30415038,30415047,30415051,30415066,30415070,30415081,30415106,30415111,30415115,30415150,30415158,30415164,30415168,30415170,30415175,30415182,30415192,30415330,30415343,30415357,30415380,30415399,30415730,30415731,30415781,30415782,30415784,30415785,30415791,30415792,30415793,30415797,30415799,30415966,30415967,30415970,832199856,862292856,863218141,863218143],
ARRAY[30362979,30362987,30362990,30363018,30363068,30363077,30363091,30363105,30363474,30363537,30363545,30412386,30414528,30414531,30414534,30414540,30414551,30414573,30414575,30414576,30414577,30414578,30414610,30414649,30414650,30414684,30414685,30414690,30414695,30414702,30414716,30414755,30414771,30414817,30414830,30414866,30414892,30414894,30414896,30414899,30414902,30414915,30414916,30414918,30414919,30414921,30414922,30414923,30414926,30414928,30414932,30414958,30414959,30414968,30414969,30414972,30414973,30414993,30414994,30415032,30415036,30415038,30415047,30415051,30415066,30415070,30415081,30415106,30415111,30415115,30415150,30415158,30415164,30415168,30415170,30415175,30415182,30415192,30415330,30415343,30415357,30415380,30415399,30415730,30415731,30415781,30415782,30415784,30415785,30415791,30415792,30415793,30415797,30415799,30415966,30415967,30415970,832199856,862292856,863218141,863218143]) results
INNER JOIN congestion.routing_grid ON id = edge
ORDER BY start_vid, end_vid, path_seq)
, routed as (
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, COUNT(ref_in_id) as int_count
FROM results
LEFT OUTER JOIN int_node ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid)
, next_best as (
select rank() over (partition by b.start_vid, b.end_vid, b.length, b.node_set, b.link_set, b.geom order by ST_HausdorffDistance desc, b.length), b.start_vid, b.end_vid, b.length, b.node_set, b.link_set, b.geom,
a.start_vid as new_start_vid, a.end_vid as new_end_vid, a.length as new_length, a.node_set as new_node_set, a.link_set as new_link_set, ST_HausdorffDistance, a.geom as new_geom
from routed b
cross join lateral (
select *, ST_HausdorffDistance(b.geom, routed.geom) from routed
where int_count = 1 and b.link_set <@ routed.link_set and gis.direction_from_line(b.geom) = gis.direction_from_line(routed.geom)
order by ST_HausdorffDistance(b.geom, routed.geom) desc, length
limit 2)a
where b.int_count = 0)
-- still
, result as (
select *,st_hausdorffdistance/lag(st_hausdorffdistance, -1) over (partition by start_vid, end_vid, length, node_set, link_set, geom order by rank) as hausdorffdistance_diff
,new_length-lag(new_length, -1) over (partition by start_vid, end_vid, length, node_set, link_set, geom order by rank) as length_diff,
case when
length <100 and
st_hausdorffdistance/lag(st_hausdorffdistance, -1) over (partition by start_vid, end_vid, length, node_set, link_set, geom order by rank) < 5 and
new_length-lag(new_length, -1) over (partition by start_vid, end_vid, length, node_set, link_set, geom order by rank) > 20
then 2 else 1 end as selection
from next_best
)
, final as (
select case when length >100 then start_vid else new_start_vid end as start_vid,
case when length >100 then end_vid else new_end_vid end as end_vid,
case when length >100 then length else new_length end as length,
case when length >100 then node_set else new_node_set end as node_set,
case when length >100 then link_set else new_link_set end as link_set,
case when length >100 then geom else new_geom end as geom
from (select *, case when rank = 2 and hausdorffdistance_diff is null and
length_diff is null
then lag(selection, 1) over (partition by start_vid, end_vid order by rank) else selection end as fin_selection
from result)d
where rank = fin_selection )
select * from final
except
select a.* from final a, result b where a.link_set <@ b.link_set and (a.start_vid != b.start_vid or a.end_vid != b.end_vid)Another problem segment 1 being shorter than 100, got merged with 2, and 2 being shorter than 100 got merged with 3 but it should really get merge to 1 too It is because the haudorffdistance is more than 5, it is 23 for some reason. Maybe I shouldnt use haudorffdistance....? could also try to say, if a shorter segment got merge to you, you don't have to find a merge anymore. (rank by length AND priority) but how? for each sets of intersection, scan through the table, find the set of intersection that was chosen to be the additional segment for merging, and get rid of it. Could use the same logic to prioritize which sets of intersection gets to merge and which doesn't ok before i do anything im going route every intersection and figure out quantity of corner cases :) WITH intersections as(
SELECT area_short_code, array_agg(ref_in_id::int) AS ints
FROM congestion.here_intersection_nodes xsections
INNER JOIN gis.city_ward area ON ST_Contains(ST_transform(ST_Buffer(ST_Transform(area.geom,98012),5), 4326) , xsections.geom)
GROUP BY area_short_code
), results AS(
SELECT results.*, routing_grid.id, routing_grid.geom
FROM intersections
, LATERAL pgr_dijkstra('SELECT id, source, target, length FROM congesting.routing_grid',
ints, ints) results
INNER JOIN congestion.routing_grid ON id = edge
)
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
INTO congestion.route_int2int
FROM results
LEFT OUTER JOIN congestion.here_intersection_nodes ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid
HAVING COUNT(ref_in_id) =0the buffer is not big enough, i have created my own routing boundary in WITH intersections as(
SELECT id as area_id, array_agg(ref_in_id::int) AS ints
FROM congestion.here_intersection_nodes xsections
INNER JOIN congestion.routing_boundary area ON ST_Contains(ST_transform(ST_Buffer(ST_Transform(area.geom,98012),15), 4326) , xsections.geom)
GROUP BY area_id
), results AS(
SELECT results.*, routing_grid.id, routing_grid.geom
FROM intersections
, LATERAL pgr_dijkstra('SELECT id, source::int, target::int, length as cost FROM congestion.routing_grid',
ints, ints) results
INNER JOIN congestion.routing_grid ON id = edge
)
SELECT start_vid, end_vid,
array_agg(node) as node_set, array_agg(id) as link_set,
ST_Linemerge(St_union(results.geom)) geom
INTO table congestion.route_int2int2
FROM results
LEFT OUTER JOIN congestion.here_intersection_nodes ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid
HAVING COUNT(ref_in_id) =0
actually missing some segments but whatever for now There are: <200m: 1237/4510 (~30%) <100m: 750/4510 (~17%)
find all possible combinations and then filter the ones that is not in a sequence?
the highest number of combination is "382460951663844400" three hundred eighty-two quadrillion four hundred sixty trillion nine hundred fifty-one billion six hundred sixty-three million eight hundred forty-four thousand four hundred Decided to move things to python
--ran in 39 mins
WITH intersections as(
SELECT area_short_code, array_agg(ref_in_id::int) AS ints
FROM congestion.here_intersection_nodes xsections
INNER JOIN gis.city_ward area ON ST_Contains(ST_transform(ST_Buffer(ST_Transform(area.geom,98012),5), 4326) , xsections.geom)
GROUP BY area_short_code
), results AS(
SELECT results.*, routing_grid.id, routing_grid.geom
FROM intersections
, 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
)
, routed as (
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, COUNT(ref_in_id) as int_count
FROM results
LEFT OUTER JOIN congestion.here_intersection_nodes ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid)
, next_best as (
select rank() over (partition by b.start_vid, b.end_vid, b.length, b.node_set, b.link_set, b.geom order by ST_HausdorffDistance, b.length), b.start_vid, b.end_vid, b.length, b.node_set, b.link_set, b.geom,
a.start_vid as new_start_vid, a.end_vid as new_end_vid, a.length as new_length, a.node_set as new_node_set, a.link_set as new_link_set, ST_HausdorffDistance, a.geom as new_geom
from routed b
cross join lateral (
select *, ST_HausdorffDistance(st_transform(b.geom, 2952), st_transform(routed.geom, 2952)) from routed
where int_count = 1 and b.link_set <@ routed.link_set and gis.direction_from_line(b.geom) = gis.direction_from_line(routed.geom)
order by ST_HausdorffDistance(st_transform(b.geom, 2952), st_transform(routed.geom, 2952)) , length
limit 2)a
where b.int_count = 0)
select * into congestion.route_nextbest from next_bestdrop materialized view congestion.pythonmerge_results
create materialized view congestion.pythonmerge_results as
select segment_id::text||b.id::text as id, array_agg(unnest), b.length, st_linemerge(st_union(geom)) as geom from (
select segment_id, id, unnest(link_set), length from congestion.test_pythonmerge)b
inner join congestion.routing_grid on unnest = link_dir
group by segment_id, b.id, b.length
union all
select segment_id::text||0::text as id, array_agg(link_dir) as link_set, st_length as length, geom from (
select route_int2int.segment_id, unnest(route_int2int.link_set), ST_length(ST_transform(route_int2int.geom, 2952))
from congestion.route_int2int
left join congestion.test_pythonmerge using (segment_id)
where test_pythonmerge.segment_id is null )a
INNER JOIN congestion.routing_grid ON unnest=id
group by segment_id, st_length, geom
CREATE MATERIALIZED VIEW congestion.route_nextbest_ordered
TABLESPACE pg_default
AS
WITH rank AS (
SELECT a.start_vid,
a.end_vid,
sum(linkdir_obs.total_count) AS sum
FROM ( SELECT DISTINCT route_nextbest_1.start_vid,
route_nextbest_1.end_vid,
unnest(route_nextbest_1.link_set) AS unnest
FROM congestion.route_nextbest route_nextbest_1) a
JOIN congestion.routing_grid ON a.unnest = routing_grid.id
JOIN congestion.linkdir_obs USING (link_dir)
GROUP BY a.start_vid, a.end_vid
)
SELECT route_nextbest.rank,
route_nextbest.start_vid,
route_nextbest.end_vid,
route_nextbest.length,
route_nextbest.node_set,
route_nextbest.link_set,
route_nextbest.geom,
route_nextbest.new_start_vid,
route_nextbest.new_end_vid,
route_nextbest.new_length,
route_nextbest.new_node_set,
route_nextbest.new_link_set,
route_nextbest.st_hausdorffdistance,
route_nextbest.new_geom,
rank.sum
FROM congestion.route_nextbest
JOIN rank USING (start_vid, end_vid)
ORDER BY route_nextbest.length, rank.sum, route_nextbest.rank-- create route_nextbest
WITH intersections as(
SELECT id as area_id, array_agg(ref_in_id::int) AS ints
FROM congestion.here_intersection_nodes xsections
INNER JOIN congestion.routing_boundary area ON ST_Contains(ST_transform(ST_Buffer(ST_Transform(area.geom,98012),15), 4326) , xsections.geom)
GROUP BY area_id
), results AS(
SELECT results.*, routing_grid.id, routing_grid.geom
FROM intersections
, 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
), routed as (
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, COUNT(ref_in_id) as int_count
FROM results
LEFT OUTER JOIN congestion.here_intersection_nodes ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid)
, int2int as (
select distinct start_vid, end_vid, length, node_set, link_set, geom
from routed
where int_count = 0 )
select row_number() over (partition by b.start_vid, b.end_vid, b.length, b.node_set, b.link_set, b.geom order by ST_HausdorffDistance , b.length),b.start_vid, b.end_vid, b.length, b.node_set, b.link_set, b.geom,
a.start_vid as new_start_vid, a.end_vid as new_end_vid, a.length as new_length, a.node_set as new_node_set, a.link_set as new_link_set, ST_HausdorffDistance, a.geom as new_geom
from int2int b
left join lateral(
select *, ST_HausdorffDistance(st_transform(b.geom, 2952), st_transform(routed.geom, 2952)) from routed
where int_count = 1 and b.link_set <@ routed.link_set
order by ST_HausdorffDistance(b.geom, routed.geom), length
limit 2)a on TRUE--for testing route_nextbes
with int_node as (
select ref_in_id, geom from congestion.here_intersection_nodes
where ref_in_id in
(30363590,30363603,30363604,30363638,30363655,30363662,30365106,30365186,30365317,30365326,30365333,30365346,30365555,30365582,30415214,30415237,30415282,30415301,30415512,30417181,30417266,30417480,30417673,30417871,30417897,30418029,30418035,30418243,30418658,30418842,30419317,801643126,839843782,839843786,858685559))
, results AS (
SELECT * FROM pgr_dijkstra('SELECT id, source::int, target::int, length::int as cost FROM congestion.routing_grid',
ARRAY[30363590,30363603,30363604,30363638,30363655,30363662,30365106,30365186,30365317,30365326,30365333,30365346,30365555,30365582,30415214,30415237,30415282,30415301,30415512,30417181,30417266,30417480,30417673,30417871,30417897,30418029,30418035,30418243,30418658,30418842,30419317,801643126,839843782,839843786,858685559 ],
ARRAY[30363590,30363603,30363604,30363638,30363655,30363662,30365106,30365186,30365317,30365326,30365333,30365346,30365555,30365582,30415214,30415237,30415282,30415301,30415512,30417181,30417266,30417480,30417673,30417871,30417897,30418029,30418035,30418243,30418658,30418842,30419317,801643126,839843782,839843786,858685559 ])results
INNER JOIN congestion.routing_grid ON id = edge
ORDER BY start_vid, end_vid, path_seq)
, routed as (
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, COUNT(ref_in_id) as int_count
FROM results
LEFT OUTER JOIN int_node ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid)
, int2int as (
select start_vid, end_vid, length, node_set, link_set, geom
from routed
where int_count = 0 )
select rank() over (partition by b.start_vid, b.end_vid, b.length, b.node_set, b.link_set, b.geom order by ST_HausdorffDistance desc, b.length),b.start_vid, b.end_vid, b.length, b.node_set, b.link_set, b.geom,
a.start_vid as new_start_vid, a.end_vid as new_end_vid, a.length as new_length, a.node_set as new_node_set, a.link_set as new_link_set, ST_HausdorffDistance, a.geom as new_geom
from int2int b
left join lateral(
select *, ST_HausdorffDistance(st_transform(b.geom, 2952), st_transform(routed.geom, 2952)) from routed
where int_count = 1 and b.link_set <@ routed.link_set
order by ST_HausdorffDistance(b.geom, routed.geom) desc, length
limit 2)a on TRUE
select * from congestion.route_int2int limit 10
--should do a filter on duplicated linkscreate table congestion.segment_link_allint as
select segment_id, link_dir from (select segment_id, unnest(link_set) from
congestion.route_int2int)a
inner join congestion.routing_grid on id = unnest
order by segment_idAfter first use of merge_shortsegments These are all the segments in route_nextbest that does not have a next best mapped by length
CREATE MATERIALIZED VIEW congestion.pythonmerge_results
TABLESPACE pg_default
AS
SELECT row_number() OVER () AS new_id,
array_agg(b.link_dir) AS link_set,
b.length,
st_linemerge(st_union(grid.geom)) AS geom
FROM ( SELECT test_pythonmerge.segment_id,
test_pythonmerge.id,
unnest(test_pythonmerge.link_set) AS link_dir,
test_pythonmerge.length
FROM congestion.test_pythonmerge) b
JOIN congestion.routing_grid grid USING (link_dir)
GROUP BY b.segment_id, b.id, b.length
UNION ALL
SELECT 9977 + row_number() OVER () AS new_id,
array_agg(routing_grid.link_dir) AS link_set,
a.st_length AS length,
routing_grid.geom
FROM ( SELECT route_int2int.segment_id,
unnest(route_int2int.link_set) AS unnest,
st_length(st_transform(route_int2int.geom, 2952)) AS st_length
FROM congestion.route_int2int
LEFT JOIN congestion.test_pythonmerge USING (segment_id)
WHERE test_pythonmerge.segment_id IS NULL) a
JOIN congestion.routing_grid ON a.unnest = routing_grid.id
GROUP BY a.segment_id, a.st_length, routing_grid.geom
WITH DATA;create materialized view congestion.route_int2int_nextbest as
with temp as (
SELECT segment_id, start_vid, end_vid, link_set, geom, ST_length(ST_transform(geom, 2952)) as length,
degrees(ST_Azimuth(ST_startpoint(ST_line_substring(geom, 0, 2/ST_length(ST_transform(geom, 2952)))),ST_endpoint(ST_line_substring(geom, 0, 2/ST_length(ST_transform(geom, 2952)))))) as start_subsec,
degrees(ST_Azimuth(ST_startpoint(ST_line_substring(geom, (ST_length(ST_transform(geom, 2952))-2)/ST_length(ST_transform(geom, 2952)), 1)), ST_endpoint(ST_line_substring(geom, (ST_length(ST_transform(geom, 2952))-2)/ST_length(ST_transform(geom, 2952)), 1)))) as end_subsec
FROM congestion.route_int2int
)
, cal as (
select pot.segment_id as pot_seg, pot.start_vid as pot_start_vid, pot.end_vid as pot_end_vid, pot.link_set as pot_link_set, pot.geom as pot_geom, ST_length(ST_transform(pot.geom, 2952)) as pot_length
,case when pot.start_vid = temp.end_vid then
degrees(
ST_Azimuth(ST_startpoint(ST_line_substring(pot.geom, 0, 2/ST_length(ST_transform(pot.geom, 2952)))),ST_endpoint(ST_line_substring(pot.geom, 0, 2/ST_length(ST_transform(pot.geom, 2952))))))
else
degrees(
ST_Azimuth(
ST_startpoint(ST_line_substring(pot.geom, (ST_length(ST_transform(pot.geom, 2952))-2)/ST_length(ST_transform(pot.geom, 2952)), 1)),
ST_endpoint(ST_line_substring(pot.geom, (ST_length(ST_transform(pot.geom, 2952))-2)/ST_length(ST_transform(pot.geom, 2952)), 1))))
end as pot_az
, temp.* from congestion.route_int2int pot, temp
where (pot.start_vid = temp.end_vid or pot.end_vid = temp.start_vid)
and not (pot.start_vid = temp.end_vid and pot.end_vid = temp.start_vid))
, next_best as (
select pot_seg, pot_start_vid, pot_end_vid, pot_link_set, pot_geom, case when pot_start_vid = end_vid then abs(pot_az - end_subsec) when pot_end_vid = start_vid then abs(pot_az - start_subsec)
end as ang_diff, pot_length, length, segment_id, start_vid, end_vid, link_set, geom
from cal
order by ang_diff)
select row_number() over(partition by a.segment_id, a.length, a.start_vid, a.end_vid, a.link_set, a.geom order by ang_diff) as rank, a.segment_id, a.start_vid, a.end_vid, a.link_set, a.length, a.geom, b.pot_seg, b.pot_start_vid,b.pot_end_vid, b.pot_link_set, b.pot_length, b.pot_geom, b.ang_diff
from temp a
left join lateral (select pot_seg, pot_length, pot_start_vid, pot_end_vid, pot_link_set, pot_geom, ang_diff from next_best where a.start_vid = start_vid and a.end_vid = end_vid
limit 2)b on TRUEcreate materialized view congestion.route_int2int_nextbest as
with temp as (
SELECT segment_id, start_vid, end_vid, link_set, geom, ST_length(ST_transform(geom, 2952)) as length,
degrees(ST_Azimuth(ST_startpoint(ST_line_substring(geom, 0, 2/ST_length(ST_transform(geom, 2952)))),ST_endpoint(ST_line_substring(geom, 0, 2/ST_length(ST_transform(geom, 2952)))))) as start_subsec,
degrees(ST_Azimuth(ST_startpoint(ST_line_substring(geom, (ST_length(ST_transform(geom, 2952))-2)/ST_length(ST_transform(geom, 2952)), 1)), ST_endpoint(ST_line_substring(geom, (ST_length(ST_transform(geom, 2952))-2)/ST_length(ST_transform(geom, 2952)), 1)))) as end_subsec
FROM congestion.route_int2int
)
, cal as (
select pot.segment_id as pot_seg, pot.start_vid as pot_start_vid, pot.end_vid as pot_end_vid, pot.link_set as pot_link_set, pot.geom as pot_geom, ST_length(ST_transform(pot.geom, 2952)) as pot_length
,case when pot.start_vid = temp.end_vid then
degrees(
ST_Azimuth(ST_startpoint(ST_line_substring(pot.geom, 0, 2/ST_length(ST_transform(pot.geom, 2952)))),ST_endpoint(ST_line_substring(pot.geom, 0, 2/ST_length(ST_transform(pot.geom, 2952))))))
else
degrees(
ST_Azimuth(
ST_startpoint(ST_line_substring(pot.geom, (ST_length(ST_transform(pot.geom, 2952))-2)/ST_length(ST_transform(pot.geom, 2952)), 1)),
ST_endpoint(ST_line_substring(pot.geom, (ST_length(ST_transform(pot.geom, 2952))-2)/ST_length(ST_transform(pot.geom, 2952)), 1))))
end as pot_az
, temp.* from congestion.route_int2int pot, temp
where (pot.start_vid = temp.end_vid or pot.end_vid = temp.start_vid)
and not (pot.start_vid = temp.end_vid and pot.end_vid = temp.start_vid))
, next_best as (
select pot_seg, pot_start_vid, pot_end_vid, pot_link_set, pot_geom, case when pot_start_vid = end_vid then abs(pot_az - end_subsec) when pot_end_vid = start_vid then abs(pot_az - start_subsec)
end as ang_diff, pot_length, length, segment_id, start_vid, end_vid, link_set, geom
from cal
order by ang_diff)
select row_number() over(partition by a.segment_id, a.length, a.start_vid, a.end_vid, a.link_set, a.geom order by ang_diff) as rank, a.segment_id, a.start_vid, a.end_vid, a.link_set, a.length, a.geom, b.pot_seg, b.pot_start_vid,b.pot_end_vid, b.pot_link_set, b.pot_length, b.pot_geom, b.ang_diff
from temp a
left join lateral (select pot_seg, pot_length, pot_start_vid, pot_end_vid, pot_link_set, pot_geom, ang_diff from next_best where a.start_vid = start_vid and a.end_vid = end_vid
limit 2)b on TRUEcreate table congestion.route_int2int_distinct as
with temp as (
select distinct * from (select segment_id, start_vid, end_vid,unnest(link_set) as links from congestion.route_int2int)a )
select segment_id, start_Vid, end_vid, array_agg(link_dir) as link_set, ST_linemerge(st_union(geom)) as geom, ST_length(St_transform(ST_linemerge(st_union(geom)), 2952)) as length
from temp
join congestion.routing_grid on links=id
group by segment_id, start_vid, end_vidselect * from congestion.mergedshort_result
create table congestion.mergedshort_result3 as
with temp as (
select row_number() over () as segment_id, start_vid, end_vid, link_set, length from congestion.mergedshort_result )
select segment_id, start_vid, end_vid, array_agg(link_dir) as link_set, a.length as length, st_linemerge(st_union(geom)) as geom from
(select segment_id, start_vid, end_vid, unnest(link_set), length from temp)a
join congestion.routing_grid on unnest = link_dir
group by segment_id, start_vid, end_vid, a.length
select * from congestion.partitioned_segments order by length desc
select * from speeds_links_30min_v1 limit 20
create table congestion.mergedshort_result2 as
select row_number() over () as segment_id, start_vid, end_vid, array_agg(link_dir) as link_set, a.length as length, st_linemerge(st_union(geom)) as geom from
(select start_vid, end_vid, unnest(link_set), length from congestion.mergedshort_result)a
join congestion.routing_grid on unnest = link_dir
group by start_vid, end_vid, a.length
order by length
limit 70
SELECT geom FROM (select unnest(link_set) as link_dir from congestion.mergedshort_result3)a
GROUP BY link_dir, geom
HAVING COUNT (link_dir) > 1
delete from select * from congestion.mergedshort_result
select *, unnest(link_set) as link_dir from congestion.mergedshort_result1
select * from congestion.mergedshort_result1_ordered
where start_vid = 863442614 and end_vid = 30338711
select * from congestion.mergedshort_result3 order by length
SELECT segment_id, geom FROM (select *, unnest(link_set) as link_dir from congestion.route_int2int)a
GROUP BY segment_id, link_dir, geom
HAVING COUNT (link_dir) > 1
select * from congestion.route_int2int where segment_id = 20-- distinct intersections for route_int2int2
WITH intersections as(
SELECT id as area_id, array_agg(ref_in_id::int) AS ints
FROM congestion.here_intersection_nodes xsections
INNER JOIN congestion.routing_boundary area ON ST_Contains(ST_transform(ST_Buffer(ST_Transform(area.geom,98012),15), 4326) , xsections.geom)
GROUP BY area_id
), results AS(
SELECT results.*, routing_grid.id, routing_grid.geom
FROM intersections
, LATERAL pgr_dijkstra('SELECT id, source::int, target::int, st_length(st_transform(geom, 2952)) as cost FROM congestion.routing_grid',
ints, ints) results
INNER JOIN congestion.routing_grid ON id = edge
)
SELECT row_number() over () as segment_id, start_vid, end_vid, array_agg(id order by path_seq) as link_set, array_agg(cost order by path_seq) as length_set, st_linemerge(st_union(s.geom)) as geom, sum(cost) as length
into congestion.route_int2int3
FROM (select distinct path_seq, start_vid, end_vid, edge, node, cost, agg_cost, id, geom from results)s
LEFT OUTER JOIN congestion.here_intersection_nodes ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid
HAVING COUNT(ref_in_id) =0
order by start_vid, end_vid--route next best ordered for first time merging
WITH temp AS (
SELECT route_int2int.segment_id,
route_int2int.start_vid,
route_int2int.end_vid,
route_int2int.link_set,
route_int2int.length_set,
route_int2int.geom,
route_int2int.length,
degrees(st_azimuth(st_startpoint(st_linesubstring(route_int2int.geom, 0::double precision, 2::double precision / length)), st_endpoint(st_linesubstring(route_int2int.geom, 0::double precision, 2::double precision / length)))) AS start_subsec,
degrees(st_azimuth(st_startpoint(st_linesubstring(route_int2int.geom, (length - 2::double precision) / length, 1::double precision)), st_endpoint(st_linesubstring(route_int2int.geom, (length - 2::double precision) / length, 1::double precision)))) AS end_subsec
FROM (select * from congestion.route_int2int3
union all
select *from congestion.route_int2int_missing )route_int2int
), cal AS (
SELECT pot.segment_id AS pot_seg,
pot.start_vid AS pot_start_vid,
pot.end_vid AS pot_end_vid,
pot.link_set AS pot_link_set,
pot.length_set AS pot_length_set,
pot.geom AS pot_geom,
pot.length AS pot_length,
CASE
WHEN pot.start_vid = temp.end_vid THEN degrees(st_azimuth(st_startpoint(st_linesubstring(pot.geom, 0::double precision, 2::double precision / pot.length)), st_endpoint(st_linesubstring(pot.geom, 0::double precision, 2::double precision / pot.length))))
ELSE degrees(st_azimuth(st_startpoint(st_linesubstring(pot.geom, (pot.length - 2::double precision) / pot.length, 1::double precision)), st_endpoint(st_linesubstring(pot.geom, (pot.length - 2::double precision) / pot.length, 1::double precision))))
END AS pot_az,
temp.segment_id,
temp.start_vid,
temp.end_vid,
temp.link_set,
temp.length_set,
temp.geom,
temp.length,
temp.start_subsec,
temp.end_subsec
FROM (select * from congestion.route_int2int3
union all
select *from congestion.route_int2int_missing ) pot,
temp
WHERE (pot.start_vid = temp.end_vid OR pot.end_vid = temp.start_vid) AND NOT (pot.start_vid = temp.end_vid AND pot.end_vid = temp.start_vid)
), next_best AS (
SELECT cal.pot_seg,
cal.pot_start_vid,
cal.pot_end_vid,
cal.pot_link_set,
cal.pot_length_set,
cal.pot_geom,
CASE
WHEN cal.pot_start_vid = cal.end_vid THEN abs(cal.pot_az - cal.end_subsec)
WHEN cal.pot_end_vid = cal.start_vid THEN abs(cal.pot_az - cal.start_subsec)
ELSE NULL::double precision
END AS ang_diff,
cal.pot_length,
cal.length,
cal.segment_id,
cal.start_vid,
cal.end_vid,
cal.link_set,
cal.length_set,
cal.geom
FROM cal
ORDER BY (
CASE
WHEN cal.pot_start_vid = cal.end_vid THEN abs(cal.pot_az - cal.end_subsec)
WHEN cal.pot_end_vid = cal.start_vid THEN abs(cal.pot_az - cal.start_subsec)
ELSE NULL::double precision
END), cal.length
), selections as (
SELECT row_number() OVER (PARTITION BY a.segment_id, a.length, a.start_vid, a.end_vid, a.link_set, a.geom ORDER BY b.ang_diff) AS rank,
a.segment_id,
a.start_vid,
a.end_vid,
a.link_set,
a.length_set,
a.length,
a.geom,
b.pot_seg,
b.pot_start_vid,
b.pot_end_vid,
b.pot_link_set,
b.pot_length_set ,
b.pot_length,
b.pot_geom,
b.ang_diff
FROM temp a
LEFT JOIN LATERAL ( SELECT next_best.pot_seg,
next_best.pot_length,
next_best.pot_start_vid,
next_best.pot_end_vid,
next_best.pot_link_set,
next_best.pot_length_set,
next_best.pot_geom,
next_best.ang_diff
FROM next_best
WHERE a.start_vid = next_best.start_vid AND a.end_vid = next_best.end_vid
LIMIT 2) b ON true),
rank AS (
SELECT a.start_vid,
a.end_vid,
sum(linkdir_obs.total_count) AS sum
FROM (select *, unnest(link_set) from congestion.route_int2int3
union all
select *, unnest(link_set)from congestion.route_int2int_missing ) a
JOIN congestion.routing_grid ON a.unnest = routing_grid.id
JOIN congestion.linkdir_obs USING (link_dir)
GROUP BY a.start_vid, a.end_vid
), prep AS (
SELECT
CASE
WHEN selections.length < 50::double precision THEN 1
WHEN selections.length >= 50::double precision AND selections.length < 100::double precision THEN 2
WHEN selections.length >= 100::double precision AND selections.length <= 200::double precision THEN 3
ELSE 4
END AS length_rank,
selections.start_vid,
selections.end_vid,
selections.rank,
selections.segment_id,
selections.link_set,
selections.length_set,
selections.length,
selections.geom,
selections.pot_seg,
selections.pot_start_vid,
selections.pot_end_vid,
selections.pot_link_set,
selections.pot_length_set,
selections.pot_length,
selections.pot_geom,
selections.ang_diff,
rank.sum
FROM selections
JOIN rank USING (start_vid, end_vid)
)
SELECT prep.start_vid,
prep.end_vid,
prep.rank,
prep.segment_id,
prep.link_set,
prep.length_set,
prep.length,
prep.geom,
prep.pot_seg,
prep.pot_start_vid,
prep.pot_end_vid,
prep.pot_link_set,
prep.pot_length_set,
prep.pot_length,
prep.pot_geom,
prep.ang_diff,
prep.sum
FROM prep
ORDER BY prep.length_rank, prep.sum DESC, prep.rankwith greedy as ( -- WHICH ONE IS DA BEST?
with all_results as (
select segment_id, link_set ,id, length_set, length, 'v4' as version, abs(200-length) as diff
from congestion.partition_all_possibility_v4
union
select segment_id, link_set ,id, length_set, length, 'v3' as version, abs(200-length) as diff
from congestion.partition_all_possibility_v3
union
select segment_id, link_set ,id, length_set, length, 'v2' as version, abs(200-length) as diff
from congestion.partition_all_possibility_v2
union
select segment_id, link_set ,id, length_set, length, 'anneal' as version, abs(200-length) as diff
from congestion.partition_result
union
select segment_id, link_set ,id, length_set, length, 'greedy' as version, abs(200-length) as diff
from congestion.partition_simanneal
order by version, segment_id, id)
, evaluate as (select row_number() over (partition by segment_id order by sum(diff)) as rank, segment_id, version, sum(diff) as error
from all_results
group by segment_id, version)
select segment_id, id, link_set, length_set, length, version from evaluate
join all_results using (segment_id, version)
where rank = 1
order by length descCreate corridor 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))
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_namethe result looks pretty good, just needed to add some streets with int_set as (
select * from congestion.corridor_int)
,intersections as(
SELECT array_agg( distinct ref_in_id::int) AS ints
FROM int_set xsections
), results AS(
SELECT results.*, routing_grid.corridor_id, routing_grid.geom
FROM intersections
, LATERAL pgr_dijkstra('select corridor_id as id, start_vid as source, end_vid as target, length as cost from congestion.route_corridor ',
ints, ints) results
INNER JOIN congestion.route_corridor routing_grid ON corridor_id = edge
)
SELECT row_number() over () as corridor_id, start_vid, end_vid, array_agg(s.corridor_id order by path_seq) as link_set, array_agg(cost order by path_seq) as length_set, st_linemerge(st_union(s.geom)) as geom, sum(cost) as length
into congestion.route_corridor2
FROM (select distinct path_seq, start_vid, end_vid, edge, node, cost, agg_cost, corridor_id, geom from results)s
LEFT OUTER JOIN int_set ON node = ref_in_id AND node != start_vid
GROUP BY start_vid, end_vid
HAVING COUNT(ref_in_id) =0
order by start_vid, end_vidwith unnested as (
select segment_id, unnest(link_set) as link_dirs
from congestion.partitioned_segments_v4)
, ordered as (select row_number() over (partition by segment_id), segment_id, source, target, id from
unnested
inner join congestion.routing_grid on id = link_dirs
)
, iwant as (
select min(row_number) min, max(row_number) max,segment_id from ordered
group by segment_id
order by segment_id)
, stuff as (
select case when min = row_number then source end as start_vid, case when max = row_number then target end as end_vid,
segment_id from ordered
inner join iwant using (segment_id))
, wut as (select start_vid, end_vid, segment_id
from stuff
where (start_vid is not null or end_vid is not null))
, ende as (select start_vid, case when end_vid is null then lag(end_vid, -1) over (order by segment_id) else end_vid end as end_vid, segment_id
from wut)
select * from ende where start_vid is not nullwith unnested as (
select segment_id, unnest(link_set) as link_dirs
from congestion.partitioned_segments_v4)
, ordered as (select row_number() over (partition by segment_id), segment_id, source, target, id from
unnested
inner join congestion.routing_grid on id = link_dirs
)
, iwant as (
select min(row_number) min, max(row_number) max, segment_id from ordered
group by segment_id
order by segment_id)
, stuff as (
select * from ordered
inner join iwant using (segment_id))
,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))
, uniona as(select segment_id from stuff
inner join int_set on ref_in_id = source
where min != row_number
union all
select segment_id from stuff
inner join int_set on ref_in_id = target
where max != row_number)
select distinct segment_id from unionanot being matched with segment_id 100 with stuff as (select corridor_id, link_set, array_agg(unnest) as seg_set, geom
from (select cor.*, unnest(seg_link_set) from congestion.corridor_segments_v4 cor
join congestion.segments_v4 seg using (segment_id))a
group by corridor_id, link_set, geom)
, test as (select corridor_id, array_length(link_set, 1) as cor_len, array_length(seg_set, 1) as seg_len,
array_length(link_set, 1) - array_length(seg_set, 1) as diff, geom from stuff
)
, match as (select *,(cor_len-seg_len)/cor_len::numeric from test
where diff = 0
order by diff desc)
select * from congestion.route_corridor
left join match using (corridor_id)
where match.corridor_id is nullnot being matched with segment_id and longer than 250m with stuff as (select corridor_id, link_set, array_agg(unnest) as seg_set, geom
from (select cor.*, unnest(seg_link_set) from congestion.corridor_segments_v4 cor
join congestion.segments_v4 seg using (segment_id))a
group by corridor_id, link_set, geom)
, test as (select corridor_id, array_length(link_set, 1) as cor_len, array_length(seg_set, 1) as seg_len,
array_length(link_set, 1) - array_length(seg_set, 1) as diff, geom from stuff
)
, match as (select *,(cor_len-seg_len)/cor_len::numeric from test
where diff = 0
order by diff desc)
select * from congestion.route_corridor
left join match using (corridor_id)
where match.corridor_id is null and length >250---------------
-- matched corridors (100%)
create materialized view congestion.matched_corridors as
with stuff as (select corridor_id, link_set, array_agg(unnest) as seg_set, geom
from (select cor.*, unnest(seg_link_set) from congestion.corridor_segments_v4 cor
join congestion.segments_v4 seg using (segment_id))a
group by corridor_id, link_set, geom)
, test as (select corridor_id, array_length(link_set, 1) as cor_len, array_length(seg_set, 1) as seg_len,
array_length(link_set, 1) - array_length(seg_set, 1) as diff, geom from stuff
)
, match as (select * from test
where diff = 0
order by diff desc)
select corridor_segments_v4.* from congestion.corridor_segments_v4
inner join match using (corridor_id)
order by corridor_id
---------------------
-- not matched and length > 250
create materialized view congestion.partitioned_corridors as
with longer as (select route_corridor.corridor_id, route_corridor.start_vid,
route_corridor.end_vid, route_corridor.link_set, route_corridor.length_set, route_corridor.length from congestion.route_corridor
left join congestion.matched_corridors match using (corridor_id)
where match.corridor_id is null and route_corridor.length >250)
-- not matched and shorter 250 -- use as its own
, shorter_ones as (
select route_corridor.corridor_id, 0 as id, route_corridor.link_set, route_corridor.length
from congestion.route_corridor
left join longer using (corridor_id)
left join congestion.matched_corridors using (corridor_id)
where longer.corridor_id is null and matched_corridors is null )
--partitioned segments
, corrected as (
select segment_id as corridor_id, id
, link_set::bigint[], length from congestion.test_pythonmerge_probs_cor)
, alla as (select * from shorter_ones
union all
select * from corrected)
, again as (select corridor_id, 20000 + row_number () over(order by corridor_id, id) as segment_id, link_set, length from alla)
select corridor_id, segment_id, array_agg(link_dir) as link_set, a.length from (
select corridor_id, segment_id, unnest(link_set), length from again)a
join congestion.routing_grid on id = unnest
group by corridor_id, segment_id, a.length
create materialized view congestion.corridors_v1 as
select * from congestion.partitioned_corridors
union all
select corridor_id, segment_id, link_set, length from congestion.matched_corridors
order by corridor_id
select *
from congestion.corridors_v1
inner join congestion.route_corridor using (corridor_id)
-- figure out which segment_id to ditch
create materialized view congestion.segment_links_v5 as
with ditch as (
select unnest(link_set) as link_dir from congestion.corridors_v1
where segment_id > 20000)
, byebye as (select * from congestion.segment_links_v4
join ditch using (link_dir))
, old as (select segment_links_v4.* from congestion.segment_links_v4
left join byebye using (segment_id)
where byebye.segment_id is null)
, new as (select segment_id, unnest(link_set) as link_dir from congestion.corridors_v1
where segment_id > 20000
union all
select segment_id, link_dir from old
)
select * from new
order by segment_id
create materialized view congestion.segments_v5 as
select segment_id, st_linemerge(st_union(geom)) as geom, sum(length) as length from congestion.segment_links_v5
inner join congestion.routing_grid using (link_dir)
group by segment_id
with asas as (
select link_dir from congestion.segment_links_v4
except
select link_dir from congestion.segment_links_v5)
select link_dir, geom from asas
inner join congestion.routing_grid using (link_dir)
select * from (
select *, unnest(link_set) as link_dir from congestion.corridors_v1
where corridor_id = 666)a
inner join congestion.routing_grid using (link_dir)
select * from congestion.segments_v4 where segment_id = 6906
select * from (
select *, unnest(link_set) from congestion.test_pythonmerge_probs_cor2 where segment_id = 666)a
join congestion.routing_grid on id = unnest::bigint
select unnest(link_Set) from congestion.route_corridor where corridor_id = 666
select * from congestion.test_pythonmerge
delete from congestion.test_pythonmerge_probs_cor2
with stuff as (select corridor_id, link_set, array_agg(unnest) as seg_set, geom
from (select cor.*, unnest(seg_link_set) from congestion.corridor_segments_v4 cor
join congestion.segments_v4 seg using (segment_id))a
group by corridor_id, link_set, geom)
, test as (select corridor_id, array_length(link_set, 1) as cor_len, array_length(seg_set, 1) as seg_len,
array_length(link_set, 1) - array_length(seg_set, 1) as diff, geom from stuff
)
, match as (select *,(cor_len-seg_len)/cor_len::numeric from test
where diff = 0
order by diff desc)
, ass as (select route_corridor.corridor_id as segment_id, route_corridor.start_vid,
route_corridor.end_vid, route_corridor.link_set, route_corridor.length_set, route_corridor.length from congestion.route_corridor
left join match using (corridor_id)
where match.corridor_id is null and corridor_id = 666)
select unnest(length_set) from ass
with sets as (
select * from congestion.merged_segments_v2
)
select * from sets
where array_length(length_set, 1) >=5
with stuff as (select corridor_id, link_set, array_agg(unnest) as seg_set, geom
from (select cor.*, unnest(seg_link_set) from congestion.corridor_segments_v4 cor
join congestion.segments_v4 seg using (segment_id))a
group by corridor_id, link_set, geom)
, test as (select corridor_id, array_length(link_set, 1) as cor_len, array_length(seg_set, 1) as seg_len,
array_length(link_set, 1) - array_length(seg_set, 1) as diff, geom from stuff
)
, match as (select *,(cor_len-seg_len)/cor_len::numeric from test
where diff = 0
order by diff desc)
select route_corridor.corridor_id as segment_id, route_corridor.start_vid,
route_corridor.end_vid, route_corridor.link_set, route_corridor.length_set, route_corridor.length from congestion.route_corridor
left join match using (corridor_id)
where match.corridor_id is null and length > 250 |
|
With the creation of |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Create process to populate lookup table for aggregating HERE links to grid-based segments
The text was updated successfully, but these errors were encountered: