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 a marker and the edges of its road
    marker_tolerance real = Coalesce((_options->>'marker_tolerance')::real, 50.0);
    -- Tolerance on the positions of the markers: two markers at the same point can have
    -- slightly different positions, for example at the end of an edge and at the start of the next edge
    position_tolerance real = 1.0;
    road record;
    -- Markers of the road, sorted by code and abscissa
    marker_ids_list integer[];
    marker_codes integer[];
    marker_abscissas real[];
    marker_virtuals boolean[];
    marker_geoms geometry[];
    marker_edge_ids integer[];
    marker_positions double precision[];
    marker_count integer;
    -- Longest sequence of markers in the order of their position, ending at each marker
    sequence_lengths integer[];
    previous_indexes integer[];
    best_index integer;
    in_order boolean[];
    -- Previous and next markers in the order of the road, around a marker out of this order
    previous_index integer;
    next_index integer;
    i integer;
    j integer;
BEGIN
    -- Roads with markers, except the roundabouts
    FOR road IN
        SELECT DISTINCT m.road_code
        FROM road_graph.markers AS m
        JOIN road_graph.roads AS r ON r.road_code = m.road_code
        WHERE r.road_type != 'roundabout'
        AND (_road_codes IS NULL OR m.road_code = ANY(_road_codes))
        ORDER BY m.road_code
    LOOP
        -- Markers of the road in the order of their code and abscissa, with their position along the road:
        -- the cumulative length at their closest edge of the road
        SELECT
            array_agg(x.id ORDER BY x.code, x.abscissa, x.id),
            array_agg(x.code ORDER BY x.code, x.abscissa, x.id),
            array_agg(x.abscissa ORDER BY x.code, x.abscissa, x.id),
            array_agg(x.is_virtual ORDER BY x.code, x.abscissa, x.id),
            array_agg(x.geom ORDER BY x.code, x.abscissa, x.id),
            array_agg(x.edge_id ORDER BY x.code, x.abscissa, x.id),
            array_agg(x.position ORDER BY x.code, x.abscissa, x.id)
        INTO
            marker_ids_list, marker_codes, marker_abscissas, marker_virtuals,
            marker_geoms, marker_edge_ids, marker_positions
        FROM (
            SELECT
                m.id, m.code, m.abscissa, m.is_virtual, m.geom,
                c.id AS edge_id, c.distance,
                c.start_cumulative + ST_LineLocatePoint(c.geom, m.geom) * ST_Length(c.geom) AS position
            FROM road_graph.markers AS m
            LEFT JOIN LATERAL (
                SELECT e.id, e.geom, e.start_cumulative, ST_Distance(e.geom, m.geom) AS distance
                FROM road_graph.edges AS e
                WHERE e.road_code = m.road_code
                AND ST_DWithin(e.geom, m.geom, marker_tolerance)
                ORDER BY ST_Distance(e.geom, m.geom), e.id
                LIMIT 1
            ) AS c ON True
            WHERE m.road_code = road.road_code
        ) AS x
        ;
        marker_count = cardinality(marker_ids_list);

-- Longest sequence of markers whose positions increase with their code and abscissa:
        -- the markers out of this sequence are not in the order of the road
        sequence_lengths = array_fill(0, ARRAY[marker_count]);
        previous_indexes = array_fill(0, ARRAY[marker_count]);
        in_order = array_fill(False, ARRAY[marker_count]);
        best_index = 0;
        FOR i IN 1..marker_count LOOP
            CONTINUE WHEN marker_positions[i] IS NULL;
            sequence_lengths[i] = 1;
            FOR j IN 1..i - 1 LOOP
                IF marker_positions[j] IS NOT NULL
                    -- Two markers can be at the same position, for example a virtual marker
                    -- and the marker of the next code, with the same reference
                    AND marker_positions[j] <= marker_positions[i] + position_tolerance
                    AND sequence_lengths[j] + 1 > sequence_lengths[i]
                THEN
                    sequence_lengths[i] = sequence_lengths[j] + 1;
                    previous_indexes[i] = j;
                END IF;
            END LOOP;
            IF best_index = 0 OR sequence_lengths[i] > sequence_lengths[best_index] THEN
                best_index = i;
            END IF;
        END LOOP;
        i = best_index;
        WHILE i > 0 LOOP
            in_order[i] = True;
            i = previous_indexes[i];
        END LOOP;

-- One issue for each marker far from the road, on an edge without reference,
        -- or out of the order of the road
        FOR i IN 1..marker_count LOOP
            CONTINUE WHEN in_order[i];
            previous_index = (SELECT k FROM generate_series(i - 1, 1, -1) AS k WHERE in_order[k] LIMIT 1);
            next_index = (SELECT k FROM generate_series(i + 1, marker_count) AS k WHERE in_order[k] LIMIT 1);
            check_code = 'G10';
            check_title = 'Markers not in the order of the road';
            severity = 'error';
            road_code = road.road_code;
            edge_ids = CASE WHEN marker_edge_ids[i] IS NULL THEN NULL ELSE ARRAY[marker_edge_ids[i]] END;
            node_ids = NULL;
            marker_ids = ARRAY[marker_ids_list[i]];
            details = jsonb_build_object(
                'issue_type', CASE
                    WHEN marker_edge_ids[i] IS NULL THEN 'far_from_road'
                    WHEN marker_positions[i] IS NULL THEN 'edge_without_reference'
                    ELSE 'order'
                END,
                'marker_code', marker_codes[i],
                'marker_abscissa', marker_abscissas[i],
                'is_virtual', marker_virtuals[i],
                'position', round(marker_positions[i]::numeric, 1),
                -- Previous and next markers by code which are in the order of the road
                'previous_marker_code', marker_codes[previous_index],
                'previous_marker_position', round(marker_positions[previous_index]::numeric, 1),
                'next_marker_code', marker_codes[next_index],
                'next_marker_position', round(marker_positions[next_index]::numeric, 1)
            );
            fix_action = NULL;
            fix_params = NULL;
            geom = marker_geoms[i];
            geom_detail = NULL;
            RETURN NEXT;
        END LOOP;
    END LOOP;
    RETURN;
END;

check_graph_markers_order(_road_codes text[], _options jsonb)

Description

Parameters

Definition