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 first marker and the start of the road to move the marker automatically
    tolerance real = Coalesce((_options->>'tolerance')::real, 1.0);
    -- Distance under which the marker is at the start of the road: the coordinates are rounded to 10 cm
    start_tolerance real = 0.05;
BEGIN
    RETURN QUERY
    WITH
    -- First marker of each road, except the roundabouts: the marker with the lowest code and abscissa,
    -- which can be virtual
    first_markers AS (
        SELECT DISTINCT ON (m.road_code)
            m.id, m.road_code, m.code, m.abscissa, m.is_virtual, m.geom
        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, m.code, m.abscissa, m.id
    ),
    -- First edge of each road: the edge without previous edge, with the lowest start cumulative
    first_edges AS (
        SELECT DISTINCT ON (e.road_code)
            e.id, e.road_code, e.geom, e.start_node, e.start_marker
        FROM road_graph.edges AS e
        WHERE _road_codes IS NULL OR e.road_code = ANY(_road_codes)
        ORDER BY e.road_code, (e.previous_edge_id IS NOT NULL), e.start_cumulative NULLS LAST, e.id
    ),
    -- First markers which are not at the start of the first edge of their road
    issues AS (
        SELECT
            m.id AS marker_id, m.road_code, m.code, m.abscissa, m.is_virtual, m.geom AS marker_geom,
            e.id AS edge_id, e.start_node, e.start_marker,
            ST_StartPoint(e.geom) AS start_point,
            ST_Distance(m.geom, ST_StartPoint(e.geom)) AS distance,
            -- Position of the marker along the first edge, from its start
            ST_LineLocatePoint(e.geom, m.geom) * ST_Length(e.geom) AS position
        FROM first_markers AS m
        JOIN first_edges AS e ON e.road_code = m.road_code
        WHERE NOT ST_DWithin(m.geom, ST_StartPoint(e.geom), start_tolerance)
    )
    SELECT
        'G09'::text,
        'First marker not at the start of the road'::text,
        -- The start of the first edge has no reference when the marker is after it
        CASE WHEN i.start_marker IS NULL THEN 'error' ELSE 'warning' END,
        i.road_code,
        ARRAY[i.edge_id],
        ARRAY[i.start_node],
        ARRAY[i.marker_id],
        jsonb_build_object(
            'marker_code', i.code,
            'marker_abscissa', i.abscissa,
            'is_virtual', i.is_virtual,
            'distance', round(i.distance::numeric, 2),
            'position_on_edge', round(i.position::numeric, 2),
            'has_start_reference', i.start_marker IS NOT NULL
        ),
        CASE WHEN i.distance <= tolerance THEN 'move_marker' ELSE NULL END,
        CASE
            WHEN i.distance <= tolerance THEN jsonb_build_object(
                'marker_id', i.marker_id,
                'edge_id', i.edge_id,
                'point', ST_AsEWKT(i.start_point)
            )
            ELSE NULL
        END,
        i.marker_geom,
        ST_MakeLine(i.marker_geom, i.start_point)
    FROM issues AS i
    ORDER BY i.road_code
    ;
END;

check_graph_first_marker(_road_codes text[], _options jsonb)

Description

Parameters

Definition