gis.road_graph

Parameters

Name	Type	Mode
_editing_session_id	integer	IN

Definition

WITH
    logged AS (
        SELECT i.item, l.key::integer AS id, l.value AS op
        FROM
            "road_graph".editing_sessions AS es,
            unnest(ARRAY['edges', 'markers']) AS i(item),
            jsonb_each_text(Coalesce(es.logged_ids->i.item, '{}'::jsonb)) AS l
        WHERE es.id = _editing_session_id
    ),
    -- Only the edges whose geometry or road has changed.
    -- The edges only updated by the calculation of their references are not taken into account
    modified_edges AS (
        SELECT l.id
        FROM logged AS l
        LEFT JOIN "road_graph".edges AS o
            ON o.id = l.id
        LEFT JOIN road_graph_editing.edges AS n
            ON n.id = l.id AND l.op != 'D'
        WHERE l.item = 'edges'
        AND (
            o.id IS NULL OR n.id IS NULL
            OR o.road_code != n.road_code
            OR NOT ST_Equals(o.geom, n.geom)
        )
    ),
    -- The modified edges before (production schema) and after (editing schema) the modifications
    all_edges AS (
        SELECT 'old' AS version, e.road_code, e.geom,
            e.start_marker, e.start_abscissa, e.end_marker
        FROM "road_graph".edges AS e
        JOIN modified_edges AS m ON m.id = e.id
        UNION ALL
        SELECT 'new' AS version, e.road_code, e.geom,
            e.start_marker, e.start_abscissa, e.end_marker
        FROM road_graph_editing.edges AS e
        JOIN modified_edges AS m ON m.id = e.id
    ),
    -- The modified markers before and after the modifications
    all_markers AS (
        SELECT m.road_code, m.code
        FROM "road_graph".markers AS m
        JOIN logged AS l ON l.item = 'markers' AND l.id = m.id
        UNION
        SELECT m.road_code, m.code
        FROM road_graph_editing.markers AS m
        JOIN logged AS l ON l.item = 'markers' AND l.id = m.id AND l.op != 'D'
    ),
    roads AS (
        SELECT DISTINCT a.road_code FROM all_edges AS a
        UNION
        SELECT DISTINCT m.road_code FROM all_markers AS m
    ),
    edges_impacts AS (
        SELECT
            r.road_code,
            (
                SELECT ST_Union(a.geom) FROM all_edges AS a
                WHERE a.road_code = r.road_code AND a.version = 'old'
            ) AS old_geom,
            (
                SELECT ST_Union(a.geom) FROM all_edges AS a
                WHERE a.road_code = r.road_code AND a.version = 'new'
            ) AS new_geom,
            (
                SELECT Coalesce(sum(ST_Length(a.geom)), 0) FROM all_edges AS a
                WHERE a.road_code = r.road_code AND a.version = 'old'
            ) AS old_length,
            (
                SELECT Coalesce(sum(ST_Length(a.geom)), 0) FROM all_edges AS a
                WHERE a.road_code = r.road_code AND a.version = 'new'
            ) AS new_length,
            -- First modified point: the least start references of the modified edges
            (
                SELECT jsonb_build_array(a.start_marker, a.start_abscissa) FROM all_edges AS a
                WHERE a.road_code = r.road_code
                ORDER BY a.start_marker, a.start_abscissa
                LIMIT 1
            ) AS start_reference,
            (
                SELECT max(a.end_marker) FROM all_edges AS a
                WHERE a.road_code = r.road_code
            ) AS end_marker_code,
            -- The references are not calculated for some edges, for example the roundabouts
            (
                SELECT bool_or(a.start_marker IS NULL OR a.start_abscissa IS NULL OR a.end_marker IS NULL)
                FROM all_edges AS a
                WHERE a.road_code = r.road_code
            ) AS null_references
        FROM roads AS r
    ),
    markers_impacts AS (
        -- For each modified marker: its code and the code of the previous marker
        SELECT
            m.road_code,
            jsonb_agg(
                jsonb_build_array(
                    Coalesce(
                        (
                            SELECT max(p.code) FROM (
                                SELECT x.code FROM "road_graph".markers AS x
                                WHERE x.road_code = m.road_code AND x.code < m.code
                                UNION ALL
                                SELECT x.code FROM road_graph_editing.markers AS x
                                WHERE x.road_code = m.road_code AND x.code < m.code
                            ) AS p
                        ),
                        -1
                    ),
                    m.code
                )
            ) AS marker_ranges
        FROM all_markers AS m
        GROUP BY m.road_code
    )
    SELECT
        jsonb_agg(
            jsonb_build_object(
                'road_code', e.road_code,
                'whole_road', Coalesce(e.null_references, false),
                -- The geometry of the road has not changed when the edges have only been split
                'geometry_changed', (
                    (e.old_geom IS NOT NULL OR e.new_geom IS NOT NULL)
                    AND NOT Coalesce(
                        abs(e.old_length - e.new_length) < 0.01
                        AND ST_HausdorffDistance(e.old_geom, e.new_geom) < 0.1,
                        false
                    )
                ),
                'start_marker_code', e.start_reference->0,
                'start_abscissa', e.start_reference->1,
                'end_marker_code', e.end_marker_code,
                'near_geom', encode(ST_AsEWKB(ST_Collect(ARRAY[e.old_geom, e.new_geom])), 'hex'),
                'marker_ranges', Coalesce(m.marker_ranges, '[]'::jsonb)
            )
        )
    FROM edges_impacts AS e
    LEFT JOIN markers_impacts AS m
        ON m.road_code = e.road_code
    ;

get_editing_session_impacts(_editing_session_id integer)

Description

Parameters

Definition