gis.road_graph

Parameters

Name	Type	Mode
_road_codes	text[]	IN
_options	jsonb	IN
check_code	text	TABLE
check_title	text	TABLE
severity	text	TABLE
road_code	text	TABLE
edge_ids	integer[]	TABLE
node_ids	integer[]	TABLE
marker_ids	integer[]	TABLE
details	jsonb	TABLE
fix_action	text	TABLE
fix_params	jsonb	TABLE
geom	geometry	TABLE
geom_detail	geometry	TABLE

Definition

DECLARE
    -- Maximum distance between two nodes
    tolerance real = Coalesce((_options->>'tolerance')::real, 1.0);
BEGIN
    RETURN QUERY
    WITH
    -- Pairs of close nodes, not linked by an edge: a short edge is found by another check.
    -- The pairs are found in the nodes table, to use its spatial index
    close_nodes AS (
        SELECT a.id AS a_id, b.id AS b_id, a.geom AS a_geom, b.geom AS b_geom
        FROM road_graph.nodes AS a
        JOIN road_graph.nodes AS b
            ON a.id < b.id
            AND ST_DWithin(a.geom, b.geom, tolerance)
        WHERE NOT EXISTS (
            SELECT 1
            FROM road_graph.edges AS e
            WHERE (e.start_node = a.id AND e.end_node = b.id)
            OR (e.start_node = b.id AND e.end_node = a.id)
        )
    ),
    -- Edges and road codes of the nodes of the pairs
    pairs AS (
        SELECT
            c.*,
            Coalesce(ae.edge_ids, '{}') AS a_edge_ids, Coalesce(be.edge_ids, '{}') AS b_edge_ids,
            Coalesce(ae.road_codes, '{}') || Coalesce(be.road_codes, '{}') AS pair_road_codes
        FROM close_nodes AS c
        CROSS JOIN LATERAL (
            SELECT array_agg(e.id ORDER BY e.id) AS edge_ids, array_agg(DISTINCT e.road_code) AS road_codes
            FROM road_graph.edges AS e
            WHERE c.a_id IN (e.start_node, e.end_node)
        ) AS ae
        CROSS JOIN LATERAL (
            SELECT array_agg(e.id ORDER BY e.id) AS edge_ids, array_agg(DISTINCT e.road_code) AS road_codes
            FROM road_graph.edges AS e
            WHERE c.b_id IN (e.start_node, e.end_node)
        ) AS be
    ),
    -- Node to keep: the node with the most edges, so that the fewest edges are moved,
    -- or else the node with the lowest id (the first node of the pair), i.e. the oldest node
    issues AS (
        SELECT
            p.*,
            CASE WHEN keep_a THEN p.a_id ELSE p.b_id END AS keep_id,
            CASE WHEN keep_a THEN p.a_geom ELSE p.b_geom END AS keep_geom,
            CASE WHEN keep_a THEN p.b_id ELSE p.a_id END AS remove_id,
            CASE WHEN keep_a THEN p.b_geom ELSE p.a_geom END AS remove_geom,
            -- Road code of the issue: the first road code of the edges of the two nodes
            (SELECT min(c) FROM unnest(p.pair_road_codes) AS c) AS issue_road_code
        FROM pairs AS p,
        LATERAL (SELECT cardinality(p.a_edge_ids) >= cardinality(p.b_edge_ids) AS keep_a) AS k
        WHERE _road_codes IS NULL OR p.pair_road_codes && _road_codes
    )
    SELECT
        'G03'::text,
        'Nodes close to each other'::text,
        'error'::text,
        i.issue_road_code,
        (SELECT array_agg(DISTINCT x ORDER BY x) FROM unnest(i.a_edge_ids || i.b_edge_ids) AS x),
        ARRAY[i.keep_id, i.remove_id],
        NULL::integer[],
        jsonb_build_object(
            'road_codes', (SELECT jsonb_agg(DISTINCT c ORDER BY c) FROM unnest(i.pair_road_codes) AS c),
            'distance', round(ST_Distance(i.a_geom, i.b_geom)::numeric, 2)
        ),
        'merge_nodes'::text,
        jsonb_build_object(
            'keep_node_id', i.keep_id,
            'remove_node_id', i.remove_id
        ),
        i.keep_geom,
        CASE WHEN ST_Equals(i.a_geom, i.b_geom) THEN NULL ELSE ST_MakeLine(i.remove_geom, i.keep_geom) END
    FROM issues AS i
    ORDER BY i.issue_road_code, i.a_id, i.b_id
    ;
END;

check_graph_close_nodes(_road_codes text[], _options jsonb)

Description

Parameters

Definition