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
    -- Distance used by the triggers to split an edge by a node, and to reuse an existing node
    tolerance real = 0.50;
    -- Action fixing the edges crossing each other, chosen by the administrator: NULL by default,
    -- since they may be on a bridge. The edges touching each other are always cut
    crossing_fix_action text = Nullif(_options->>'crossing_fix_action', '');
BEGIN
    IF crossing_fix_action NOT IN ('cut_edges', 'mark_as_bridge') THEN
        RAISE EXCEPTION
            'The option crossing_fix_action of the check G01 must be cut_edges or mark_as_bridge, not %',
            crossing_fix_action;
    END IF;

RETURN QUERY
    WITH
    -- Points where two edges cross or touch each other.
    -- The edges which must not be cut, for example on a bridge, are ignored
    crossings AS (
        SELECT
            a.id AS a_id, b.id AS b_id,
            a.road_code AS a_road_code, b.road_code AS b_road_code,
            a.geom AS a_geom, b.geom AS b_geom,
            (ST_Dump(ST_CollectionExtract(ST_Intersection(a.geom, b.geom), 1))).geom AS point
        FROM road_graph.edges AS a
        JOIN road_graph.edges AS b
            ON a.id < b.id
            AND ST_Intersects(a.geom, b.geom)
        WHERE NOT Coalesce(a.no_intersection_cutting, False)
        AND NOT Coalesce(b.no_intersection_cutting, False)
        AND (
            _road_codes IS NULL
            OR a.road_code = ANY(_road_codes)
            OR b.road_code = ANY(_road_codes)
        )
    ),
    -- Is the point at the start or end of each edge
    ends AS (
        SELECT
            c.*,
            ST_DWithin(c.point, ST_StartPoint(c.a_geom), tolerance)
                OR ST_DWithin(c.point, ST_EndPoint(c.a_geom), tolerance) AS is_a_end,
            ST_DWithin(c.point, ST_StartPoint(c.b_geom), tolerance)
                OR ST_DWithin(c.point, ST_EndPoint(c.b_geom), tolerance) AS is_b_end
        FROM crossings AS c
    ),
    -- Keep the points which are not at the start or end of both edges:
    -- at least one of the edges must be split there.
    -- If the point is at the start or end of one edge, the edges touch each other: they are surely connected.
    -- Else they cross each other, maybe on a bridge
    issues AS (
        SELECT
            e.*,
            e.is_a_end OR e.is_b_end AS is_touching
        FROM ends AS e
        WHERE NOT (e.is_a_end AND e.is_b_end)
    ),
    -- Direction of each edge at the crossing point, from its points 1 m before and after,
    -- to get the angle between the edges: it helps to find the bridges
    directions AS (
        SELECT
            i.*,
            degrees(ST_Azimuth(
                ST_LineInterpolatePoint(i.a_geom, greatest(a.position - 1 / ST_Length(i.a_geom), 0)),
                ST_LineInterpolatePoint(i.a_geom, least(a.position + 1 / ST_Length(i.a_geom), 1))
            )) AS a_azimuth,
            degrees(ST_Azimuth(
                ST_LineInterpolatePoint(i.b_geom, greatest(b.position - 1 / ST_Length(i.b_geom), 0)),
                ST_LineInterpolatePoint(i.b_geom, least(b.position + 1 / ST_Length(i.b_geom), 1))
            )) AS b_azimuth
        FROM issues AS i,
        -- Position of the crossing point along each edge, from 0 to 1
        LATERAL (SELECT ST_LineLocatePoint(i.a_geom, i.point) AS position) AS a,
        LATERAL (SELECT ST_LineLocatePoint(i.b_geom, i.point) AS position) AS b
    )
    SELECT
        'G01'::text,
        'Crossing edges without node'::text,
        'error'::text,
        d.a_road_code,
        ARRAY[d.a_id, d.b_id],
        -- Existing node close to the crossing point, not used by the edges
        (
            SELECT array_agg(n.id ORDER BY n.id)
            FROM road_graph.nodes AS n
            WHERE ST_DWithin(n.geom, d.point, tolerance)
        ),
        NULL::integer[],
        jsonb_build_object(
            'crossing_type', CASE WHEN d.is_touching THEN 'touching' ELSE 'crossing' END,
            'road_codes', jsonb_build_array(d.a_road_code, d.b_road_code),
            'road_classes', jsonb_build_array(ra.road_class, rb.road_class),
            -- Angle between the two edges, from 0 (parallel) to 90 degrees (perpendicular)
            'angle', round((90 - abs(90 - mod((abs(d.a_azimuth - d.b_azimuth))::numeric, 180)))::numeric, 1)
        ),
        CASE WHEN d.is_touching THEN 'cut_edges' ELSE crossing_fix_action END,
        jsonb_build_object(
            'point', ST_AsEWKT(d.point),
            'edge_ids', jsonb_build_array(d.a_id, d.b_id)
        ),
        d.point,
        ST_Collect(d.a_geom, d.b_geom)
    FROM directions AS d
    LEFT JOIN road_graph.roads AS ra ON ra.road_code = d.a_road_code
    LEFT JOIN road_graph.roads AS rb ON rb.road_code = d.b_road_code
    ORDER BY d.a_road_code, d.a_id, d.b_id
    ;
END;

check_graph_crossing_edges(_road_codes text[], _options jsonb)

Description

Parameters

Definition