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 length of a short edge
    min_length real = Coalesce((_options->>'min_length')::real, 3.0);
    -- Length of an edge reduced to a point: the coordinates are rounded to 10 cm
    zero_length real = 0.10;
BEGIN
    RETURN QUERY
    WITH
    -- Short edges, with the number of other edges at their start and end nodes
    short_edges AS (
        SELECT
            e.id, e.road_code, e.start_node, e.end_node, e.geom,
            ST_Length(e.geom) AS edge_length,
            (
                SELECT count(*)
                FROM road_graph.edges AS o
                WHERE o.id != e.id
                AND e.start_node IN (o.start_node, o.end_node)
            ) AS start_edge_count,
            (
                SELECT count(*)
                FROM road_graph.edges AS o
                WHERE o.id != e.id
                AND e.end_node IN (o.start_node, o.end_node)
            ) AS end_edge_count,
            (
                SELECT count(*)
                FROM road_graph.edges AS o
                WHERE o.road_code = e.road_code
            ) AS road_edge_count,
            -- The edges of the roundabouts, and the edges arriving at a roundabout or leaving it
            r.road_type = 'roundabout' OR EXISTS (
                SELECT 1
                FROM road_graph.edges AS o
                JOIN road_graph.roads AS ro ON ro.road_code = o.road_code
                WHERE ro.road_type = 'roundabout'
                AND o.id != e.id
                AND ARRAY[o.start_node, o.end_node] && ARRAY[e.start_node, e.end_node]
            ) AS is_roundabout_edge
        FROM road_graph.edges AS e
        JOIN road_graph.roads AS r ON r.road_code = e.road_code
        WHERE ST_Length(e.geom) < min_length
        AND (_road_codes IS NULL OR e.road_code = ANY(_road_codes))
    ),
    -- The edges of the roundabouts, or arriving at a roundabout, are often short: they are ignored,
    -- unless they are reduced to a point.
    -- The node to keep is the node with the most edges, or else the node with the lowest id
    issues AS (
        SELECT
            s.*,
            s.edge_length < zero_length AS is_zero_length,
            CASE
                WHEN s.start_edge_count > s.end_edge_count THEN True
                WHEN s.start_edge_count < s.end_edge_count THEN False
                ELSE s.start_node <= s.end_node
            END AS keep_start
        FROM short_edges AS s
        WHERE NOT s.is_roundabout_edge OR s.edge_length < zero_length
    )
    SELECT
        'G08'::text,
        'Short edge'::text,
        CASE WHEN i.is_zero_length THEN 'error' ELSE 'warning' END,
        i.road_code,
        ARRAY[i.id],
        CASE WHEN i.keep_start THEN ARRAY[i.start_node, i.end_node] ELSE ARRAY[i.end_node, i.start_node] END,
        NULL::integer[],
        jsonb_build_object(
            'length', round(i.edge_length::numeric, 2),
            'start_edge_count', i.start_edge_count,
            'end_edge_count', i.end_edge_count,
            'is_roundabout_edge', i.is_roundabout_edge
        ),
        -- The only edge of a road is not removed: the road would not have any edge anymore
        CASE WHEN i.road_edge_count > 1 THEN 'collapse_edge' ELSE NULL END,
        CASE
            WHEN i.road_edge_count = 1 THEN NULL
            ELSE jsonb_build_object(
                'edge_id', i.id,
                'keep_node_id', CASE WHEN i.keep_start THEN i.start_node ELSE i.end_node END,
                'remove_node_id', CASE WHEN i.keep_start THEN i.end_node ELSE i.start_node END
            )
        END,
        ST_LineInterpolatePoint(i.geom, 0.5),
        i.geom
    FROM issues AS i
    ORDER BY i.road_code, i.id
    ;
END;

check_graph_short_edges(_road_codes text[], _options jsonb)

Description

Parameters

Definition