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

BEGIN
    RETURN QUERY
    WITH
    -- Nodes with their edges. An edge starting and ending at the node (loop)
    -- is not counted as an edge arriving at or leaving the node
    nodes AS (
        SELECT
            n.id, n.geom,
            count(e.id) AS edge_count,
            array_agg(e.id ORDER BY e.id) FILTER (WHERE e.id IS NOT NULL) AS node_edge_ids,
            array_agg(DISTINCT e.road_code) FILTER (WHERE e.road_code IS NOT NULL) AS node_road_codes,
            count(e.id) FILTER (WHERE e.end_node = n.id AND e.start_node != n.id) AS arriving_count,
            count(e.id) FILTER (WHERE e.start_node = n.id AND e.end_node != n.id) AS leaving_count,
            (array_agg(e.geom) FILTER (WHERE e.end_node = n.id AND e.start_node != n.id))[1] AS arriving_geom,
            (array_agg(e.geom) FILTER (WHERE e.start_node = n.id AND e.end_node != n.id))[1] AS leaving_geom,
            count(DISTINCT Coalesce(e.no_intersection_cutting, False)) AS cutting_count,
            count(DISTINCT CASE WHEN e.start_node = n.id THEN e.end_node ELSE e.start_node END)
                AS other_node_count
        FROM road_graph.nodes AS n
        LEFT JOIN road_graph.edges AS e
            ON n.id IN (e.start_node, e.end_node)
        GROUP BY n.id, n.geom
    ),
    -- Nodes without edge, only when all the roads are checked,
    -- and nodes between two edges of the same road which can be merged:
    -- one edge arrives at the node and the other one leaves it,
    -- with the same value of no_intersection_cutting, they do not form a loop,
    -- and they only touch each other at the node: for example a loop ramp crossing itself cannot be merged
    issues AS (
        SELECT
            nd.*,
            CASE WHEN nd.edge_count = 0 THEN 'no_edge' ELSE 'two_edges' END AS node_type
        FROM nodes AS nd
        WHERE (nd.edge_count = 0 AND _road_codes IS NULL)
        OR (
            nd.edge_count = 2
            AND cardinality(nd.node_road_codes) = 1
            AND nd.arriving_count = 1
            AND nd.leaving_count = 1
            AND nd.cutting_count = 1
            AND nd.other_node_count = 2
            AND ST_Touches(nd.arriving_geom, nd.leaving_geom)
            AND (_road_codes IS NULL OR nd.node_road_codes && _road_codes)
        )
    )
    SELECT
        'G04'::text,
        'Useless node'::text,
        'warning'::text,
        i.node_road_codes[1],
        i.node_edge_ids,
        ARRAY[i.id],
        NULL::integer[],
        jsonb_build_object(
            'node_type', i.node_type,
            'road_codes', to_jsonb(Coalesce(i.node_road_codes, '{}'::text[]))
        ),
        'delete_node'::text,
        jsonb_build_object('node_id', i.id),
        i.geom,
        NULL::geometry
    FROM issues AS i
    ORDER BY i.node_road_codes[1], i.id
    ;
END;

check_graph_useless_nodes(_road_codes text[], _options jsonb)

Description

Parameters

Definition