Identify impact of refreshed here map on congestion segments

[chmnata]

The refreshed here map here_gis.streets_19_4 has updated links and geometry, how does this effect our current usage of the congestion grid congestion.segments_v5?

• How many links are changed?
• How many segments are affected?
• Are the changes substantial?
• What is the simplest way to port changes over?

[chmnata]

There are 4254/38866 links affected (11%) in congestion.segment_links_v5, a total of 478 km.

WITH traffic_link_dirs_19_4 AS(
    SELECT link_id || 'F'::text AS link_dir
    FROM here_gis.traffic_streets_19_4
    WHERE dir_travel = ANY (ARRAY['F', 'B'])
    UNION ALL
    SELECT link_id || 'T'::text AS link_dir
    FROM here_gis.traffic_streets_19_4
    WHERE dir_travel = ANY (ARRAY['T', 'B'])
    )
    ,traffic_link_dirs_18_3 AS(
    SELECT link_id || 'F'::text AS link_dir
    FROM here_gis.traffic_streets_18_3
    WHERE dir_travel = ANY (ARRAY['F', 'B'])
    UNION ALL
    SELECT link_id || 'T'::text AS link_dir
    FROM here_gis.traffic_streets_18_3
    WHERE dir_travel = ANY (ARRAY['T', 'B']))

select segment_id, link_dir
from (select * from congestion.segment_links_v5 inner join traffic_link_dirs_18_3 using (link_dir)) old
left join traffic_link_dirs_19_4 here using (link_dir)
where here.link_dir is null

and as a result, 3391/13126 (26%) segments in congestion.segments_v5 are affected

Wondering if we can find the corresponding new link_dir using SharedStreets.

[radumas]

(as part of https://github.com/CityofToronto/bdit_data-sources/issues/285)

[radumas]

I think the numbers are wrong because I made a mistake when creating routing_streets, the materialized view uses the old nodes layer.

See my previous analysis where I estimated 2-3% missing links https://github.com/CityofToronto/bdit_traffic_evaluation/issues/33#issuecomment-604658774

[chmnata]

ran this json through sharedstreets with --follow-line-direction, --snap-intersection and planet-180430 Resulted in a 93% match rate

"\COPY (SELECT jsonb_build_object('type', 'FeatureCollection', 'features', jsonb_agg(feature))FROM (SELECT jsonb_build_object('type', 'Feature', 'id', link_dir, 'geometry', ST_AsGeoJSON(geom)::jsonb, 'properties', to_jsonb(row) - 'id' - 'geom')AS feature FROM (select link_dir, geom from ( SELECT traffic_streets_18_3.link_id || 'F'::text AS link_dir,
            traffic_streets_18_3.link_id,
            (to_char(traffic_streets_18_3.link_id, '0000000000'::text) || '0'::text)::bigint AS id,
            traffic_streets_18_3.ref_in_id AS source,
            traffic_streets_18_3.nref_in_id AS target,
            st_length(st_transform(streets_18_3.geom, 3857)) AS length,
            streets_18_3.geom
           FROM here_gis.traffic_streets_19_4 traffic_streets_18_3
             JOIN here_gis.streets_19_4 streets_18_3 USING (link_id)
          WHERE traffic_streets_18_3.dir_travel in  ('F', 'B')
        UNION ALL
         SELECT traffic_streets_18_3.link_id || 'T'::text AS link_dir,
            traffic_streets_18_3.link_id,
            (to_char(traffic_streets_18_3.link_id, '0000000000'::text) || '1'::text)::bigint AS id,
            traffic_streets_18_3.nref_in_id AS source,
            traffic_streets_18_3.ref_in_id AS target,
            st_length(st_transform(streets_18_3.geom, 3857)) AS length,
            st_reverse(streets_18_3.geom) AS geom
           FROM here_gis.traffic_streets_19_4 traffic_streets_18_3
             JOIN here_gis.streets_19_4 streets_18_3 USING (link_id)
          WHERE traffic_streets_18_3.dir_travel in  ('T', 'B')
          ) streets ) row) features )  TO 'here_19_4.json'" 

There are 63 links in here_18_3 that were failed to match through sharedstreets

WITH traffic_link_dirs_19_4 AS(
    SELECT link_id || 'F'::text AS link_dir
    FROM here_gis.traffic_streets_19_4
    WHERE dir_travel = ANY (ARRAY['F', 'B'])
    UNION ALL
    SELECT link_id || 'T'::text AS link_dir
    FROM here_gis.traffic_streets_19_4
    WHERE dir_travel = ANY (ARRAY['T', 'B'])
    )
    ,traffic_link_dirs_18_3 AS(
    SELECT link_id || 'F'::text AS link_dir
    FROM here_gis.traffic_streets_18_3
    WHERE dir_travel = ANY (ARRAY['F', 'B'])
    UNION ALL
    SELECT link_id || 'T'::text AS link_dir
    FROM here_gis.traffic_streets_18_3
    WHERE dir_travel = ANY (ARRAY['T', 'B']))
, missing_link as (
select segment_id, link_dir
from (select * from congestion.segment_links_v5 inner join traffic_link_dirs_18_3 using (link_dir)) old
left join traffic_link_dirs_19_4 here using (link_dir)
where here.link_dir is null)

select * from missing_link left join here_matched_180430 on pp_link_dir = link_dir
where pp_link_dir is null 

There are a total of 4180/4254 (98%) links that can be join with sharedstreets in here_18_3 and here_19_4

WITH traffic_link_dirs_19_4 AS(
    SELECT link_id || 'F'::text AS link_dir
    FROM here_gis.traffic_streets_19_4
    WHERE dir_travel = ANY (ARRAY['F', 'B'])
    UNION ALL
    SELECT link_id || 'T'::text AS link_dir
    FROM here_gis.traffic_streets_19_4
    WHERE dir_travel = ANY (ARRAY['T', 'B'])
    )
    ,traffic_link_dirs_18_3 AS(
    SELECT link_id || 'F'::text AS link_dir
    FROM here_gis.traffic_streets_18_3
    WHERE dir_travel = ANY (ARRAY['F', 'B'])
    UNION ALL
    SELECT link_id || 'T'::text AS link_dir
    FROM here_gis.traffic_streets_18_3
    WHERE dir_travel = ANY (ARRAY['T', 'B']))
, missing_link as (
select segment_id, link_dir
from (select * from congestion.segment_links_v5 inner join traffic_link_dirs_18_3 using (link_dir)) old
left join traffic_link_dirs_19_4 here using (link_dir)
where here.link_dir is null)
, here_18 as (
select * from missing_link
left join here_matched_180430 on here_matched_180430.pp_link_dir = link_dir
where here_matched_180430.pp_link_dir is not null)
select count(distinct link_dir) 
from here_18 
inner join natalie.here_19_4_matched on "shstReferenceId" = shstreferenceid

These are the links (74) that were not able to join using sharedstreets

[chmnata]

Since here links are usually shorter than an intersection defined by osm, multiple different links can be match with the same shstreferenceid (the unique identifier for each intersection), this makes matching here_18_3 and here_19_4 harder as we can't just join shstreferenceid. There is a start and end node column for each returned geometry but it might require some recursive joins to find out where these nodes are on a shstreferenceid. @radumas suggested that we can make our own section column with ST_linelocatepoint().

Sample sql of what that looks like

with target as (
select * from here_matched_180430 where "shstReferenceId" = '7789ab543373ae19f714da7e708eae1f'
)

,toronto_network as (
select reference_id, geom as full_geom, street_name, direction from gis_shared_streets.toronto_network_reference)
select pp_link_dir as link_dir, reference_id, array[round(ST_linelocatepoint(full_geom, ST_startpoint(geom))::numeric, 2), 
round(ST_linelocatepoint(full_geom, ST_endpoint(geom))::numeric,2)] as section, geom, full_geom from target 
inner join toronto_network on reference_id = "shstReferenceId"

, returns

I will go ahead and do this for both here_18_3 and here_19_4

[chmnata]

Created function gis_shared_streets.create_section for adding section column

CREATE OR REPLACE FUNCTION gis_shared_streets.create_section(_tablename text, _referencecolumn text) 
    RETURNS void 
    AS $$

    BEGIN
        EXECUTE FORMAT('ALTER TABLE %I ADD COLUMN section numrange', _tablename);
        EXECUTE FORMAT('UPDATE %I 
                      SET section = section1
                       from (
                        select id as pid, case 
                       when ST_linelocatepoint(network.geom, ST_endpoint(new.geom))::numeric = 0 and 
                            ST_linelocatepoint(network.geom, ST_startpoint(new.geom))::numeric > 
                            ST_linelocatepoint(network.geom, ST_endpoint(new.geom))::numeric
                       then numrange(round(ST_linelocatepoint(network.geom, ST_startpoint(new.geom))::numeric, 2),1.00)
                       when ST_linelocatepoint(network.geom, ST_startpoint(new.geom))::numeric > 
                            ST_linelocatepoint(network.geom, ST_endpoint(new.geom))::numeric and 
                            ST_linelocatepoint(network.geom, ST_endpoint(new.geom))::numeric !=0
                       then null 

                       else 
                        numrange(round(ST_linelocatepoint(network.geom, ST_startpoint(new.geom))::numeric, 2), 
                            round(ST_linelocatepoint(network.geom, ST_endpoint(new.geom))::numeric,2)) end  as section1
                        from %I new
                        inner join gis_shared_streets.toronto_network_reference network on reference_id = %I )temp
                       where pid = id '
                            , _tablename, _tablename, _referencecolumn);

    END;
    $$
    LANGUAGE plpgsql;

• changed to numrange instead of array, and took care of cases where lower bound < upper bound cause by circle line segments

[chmnata]

Result of joining two sharedstreets table with shstreferenceid and section

WITH traffic_link_dirs_19_4 AS(
    SELECT link_id || 'F'::text AS link_dir
    FROM here_gis.traffic_streets_19_4
    WHERE dir_travel = ANY (ARRAY['F', 'B'])
    UNION ALL
    SELECT link_id || 'T'::text AS link_dir
    FROM here_gis.traffic_streets_19_4
    WHERE dir_travel = ANY (ARRAY['T', 'B'])
    )
    ,traffic_link_dirs_18_3 AS(
    SELECT link_id || 'F'::text AS link_dir
    FROM here_gis.traffic_streets_18_3
    WHERE dir_travel = ANY (ARRAY['F', 'B'])
    UNION ALL
    SELECT link_id || 'T'::text AS link_dir
    FROM here_gis.traffic_streets_18_3
    WHERE dir_travel = ANY (ARRAY['T', 'B']))
, missing_link as (
select segment_id, link_dir
from (select * from congestion.segment_links_v5 inner join traffic_link_dirs_18_3 using (link_dir)) old
left join traffic_link_dirs_19_4 here using (link_dir)
where here.link_dir is null)
, here_18 as (
select * from missing_link
left join here_matched_180430 on here_matched_180430.pp_link_dir = link_dir
where here_matched_180430.pp_link_dir is not null)
select segment_id, link_dir, here_19_4_matched.pp_link_dir, here_18.geom,  here_19_4_matched.geom, here_18.section, here_19_4_matched.section
from here_18 
inner join natalie.here_19_4_matched on "shstReferenceId" = shstreferenceid and (here_18.section <@ here_19_4_matched.section
                                                                                 or here_19_4_matched.section <@ here_18.section)

Blue line showing the links that cannot be joined with sharedstreets, 4160/4254 (97.8%) can be joined

[chmnata]

Other metrics to measure conflation result:

Length

Total length of the network segments_v5: 2,769 km Total length of retired links: 478.3 km Total length of unmatched links: 9.7 km Unmatched links account for 0.35% of the total network length

VKT

Total VKT of the entire network: 60,626,387 km Total VKT of unmatched links: 43,221 km Unmatched links account for 0.07% of the total VKT of the network

Red line representing all unmatched links over the segments_v5 network. Mostly at intersections and ramps.