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 the free end of an edge and another edge
    tolerance real = Coalesce((_options->>'tolerance')::real, 2.0);
    -- Distance used by the triggers to split an edge by a node, and to reuse an existing node
    node_tolerance real = 0.50;
    -- Maximum distance between two connected edges
    connected_tolerance real = 0.001;
BEGIN
    RETURN QUERY
    WITH
    -- Start and end points of the edges
    ends AS (
        SELECT e.id, e.road_code, e.geom AS edge_geom, 'start'::text AS side, e.start_node AS node_id,
            ST_StartPoint(e.geom) AS point, ST_EndPoint(e.geom) AS opposite_point
        FROM road_graph.edges AS e
        WHERE _road_codes IS NULL OR e.road_code = ANY(_road_codes)
        UNION ALL
        SELECT e.id, e.road_code, e.geom AS edge_geom, 'end'::text AS side, e.end_node AS node_id,
            ST_EndPoint(e.geom) AS point, ST_StartPoint(e.geom) AS opposite_point
        FROM road_graph.edges AS e
        WHERE _road_codes IS NULL OR e.road_code = ANY(_road_codes)
    ),
    -- Free ends: no other edge is connected to them
    free_ends AS (
        SELECT f.*
        FROM ends AS f
        WHERE NOT EXISTS (
            SELECT 1
            FROM road_graph.edges AS o
            WHERE o.id != f.id
            AND ST_DWithin(o.geom, f.point, connected_tolerance)
        )
    ),
    -- Closest other edge less than the tolerance from each free end. Are ignored:
    -- * the edges connected to the opposite end of the edge, for example its next edge if it is very short,
    -- * the edges crossed by the edge near its free end: the edge goes beyond the other edge,
    --   the crossing is found by the check G01
    gaps AS (
        SELECT DISTINCT ON (f.id, f.side)
            f.*,
            o.id AS other_id, o.road_code AS other_road_code,
            ST_Distance(o.geom, f.point) AS distance,
            ST_ClosestPoint(o.geom, f.point) AS closest_point,
            ST_StartPoint(o.geom) AS other_start, ST_EndPoint(o.geom) AS other_end
        FROM free_ends AS f
        JOIN road_graph.edges AS o
            ON o.id != f.id
            AND ST_DWithin(o.geom, f.point, tolerance)
            AND NOT ST_DWithin(o.geom, f.opposite_point, connected_tolerance)
            AND NOT (
                ST_Intersects(o.geom, f.edge_geom)
                AND ST_DWithin(ST_Intersection(o.geom, f.edge_geom), f.point, tolerance)
            )
        ORDER BY f.id, f.side, ST_Distance(o.geom, f.point)
    ),
    -- Point to join: the closest point of the other edge,
    -- or its start or end point if it is closer than the distance used by the triggers to split it
    targets AS (
        SELECT
            g.*,
            CASE
                WHEN ST_DWithin(g.closest_point, g.other_start, node_tolerance)
                    AND ST_Distance(g.closest_point, g.other_start) <= ST_Distance(g.closest_point, g.other_end)
                    THEN g.other_start
                WHEN ST_DWithin(g.closest_point, g.other_end, node_tolerance)
                    THEN g.other_end
                ELSE g.closest_point
            END AS target
        FROM gaps AS g
    ),
    -- Two free ends close to each other: keep only one issue
    issues AS (
        SELECT t.*
        FROM targets AS t
        WHERE NOT EXISTS (
            SELECT 1
            FROM free_ends AS f
            WHERE f.id = t.other_id
            AND ST_Equals(f.point, t.target)
            AND f.id < t.id
            AND ST_DWithin(f.point, t.point, tolerance)
        )
    )
    SELECT
        'G02'::text,
        'Edge ending near another edge without being connected'::text,
        'error'::text,
        i.road_code,
        ARRAY[i.id, i.other_id],
        CASE WHEN i.node_id IS NULL THEN NULL ELSE ARRAY[i.node_id] END,
        NULL::integer[],
        jsonb_build_object(
            'road_codes', jsonb_build_array(i.road_code, i.other_road_code),
            'distance', round(ST_Distance(i.point, i.target)::numeric, 2),
            -- The free end joins the other edge in its middle, or at its start or end point
            'join_type', CASE WHEN ST_Equals(i.target, i.closest_point) THEN 'edge' ELSE 'edge_end' END
        ),
        'extend_edge'::text,
        jsonb_build_object(
            'edge_id', i.id,
            'side', i.side,
            'point', ST_AsEWKT(i.point),
            'target', ST_AsEWKT(i.target)
        ),
        i.point,
        ST_MakeLine(i.point, i.target)
    FROM issues AS i
    ORDER BY i.road_code, i.id, i.side
    ;
END;

check_graph_t_junction_gaps(_road_codes text[], _options jsonb)

Description

Parameters

Definition