gis.road_graph

Parameters

Name	Type	Mode
_line	geometry	IN
_tolerance	real	IN
road_order	integer	TABLE
road_code	text	TABLE
start_marker	integer	TABLE
start_abscissa	real	TABLE
start_cumulative	real	TABLE
end_marker	integer	TABLE
end_abscissa	real	TABLE
end_cumulative	real	TABLE
direction	text	TABLE
length	real	TABLE
geom	geometry	TABLE

Definition

DECLARE
    line geometry;
BEGIN
    IF _line IS NULL OR ST_IsEmpty(_line) THEN
        RETURN;
    END IF;

-- Use the road graph projection
    line = ST_Force2D(_line);
    IF ST_SRID(line) = 0 THEN
        line = ST_SetSRID(line, 2154);
    ELSIF ST_SRID(line) != 2154 THEN
        line = ST_Transform(line, 2154);
    END IF;

RETURN QUERY
    WITH
    -- Points of each part of the line, densified
    -- to avoid long segments if the line has not been snapped on the edges
    line_points AS (
        SELECT
            coalesce(d.path[1], 1) AS part,
            coalesce(d.path[array_length(d.path, 1)], 1) AS point_index,
            d.geom
        FROM ST_DumpPoints(ST_Segmentize(ST_Multi(line), 5)) AS d
    ),
    segments AS (
        SELECT
            lp.part,
            row_number() OVER (ORDER BY lp.part, lp.point_index) AS segment_index,
            ST_MakeLine(lp.geom, lead(lp.geom) OVER (PARTITION BY lp.part ORDER BY lp.point_index)) AS geom
        FROM line_points AS lp
    ),
    -- Find the road of each segment, with its middle point
    -- which avoids the ambiguity of the points located on the intersection nodes
    segment_roads AS (
        SELECT
            sg.part, sg.segment_index, sg.geom, r.road_code
        FROM segments AS sg
        CROSS JOIN LATERAL (
            SELECT e.road_code
            FROM road_graph.edges AS e
            WHERE ST_DWithin(e.geom, ST_LineInterpolatePoint(sg.geom, 0.5), _tolerance)
            ORDER BY ST_Distance(e.geom, ST_LineInterpolatePoint(sg.geom, 0.5)), e.road_code
            LIMIT 1
        ) AS r
        WHERE sg.geom IS NOT NULL
        AND ST_Length(sg.geom) > 0
    ),
    -- Group the consecutive segments on the same road
    groups AS (
        SELECT
            sr.*,
            sr.segment_index - row_number() OVER (PARTITION BY sr.part, sr.road_code ORDER BY sr.segment_index) AS group_id
        FROM segment_roads AS sr
    ),
    road_lines AS (
        SELECT
            row_number() OVER (ORDER BY min(g.segment_index))::integer AS road_order,
            g.road_code,
            ST_LineMerge(ST_MakeLine(g.geom ORDER BY g.segment_index)) AS geom
        FROM groups AS g
        GROUP BY g.part, g.road_code, g.group_id
    ),
    road_references AS (
        SELECT
            rl.*,
            -- For roundabouts, the start and end points of the closed road are the same:
            -- use points 1cm inside the line to avoid any ambiguity
            -- and correct the references afterwards with this shift
            CASE
                WHEN ro.road_type = 'roundabout' AND ST_Length(rl.geom) > 0.1 THEN 0.01
                ELSE 0
            END AS shift,
            road_graph.get_reference_from_point(
                CASE
                    WHEN ro.road_type = 'roundabout' AND ST_Length(rl.geom) > 0.1
                        THEN ST_LineInterpolatePoint(rl.geom, 0.01 / ST_Length(rl.geom))
                    ELSE ST_StartPoint(rl.geom)
                END,
                rl.road_code,
                False
            ) AS start_ref,
            road_graph.get_reference_from_point(
                CASE
                    WHEN ro.road_type = 'roundabout' AND ST_Length(rl.geom) > 0.1
                        THEN ST_LineInterpolatePoint(rl.geom, 1 - 0.01 / ST_Length(rl.geom))
                    ELSE ST_EndPoint(rl.geom)
                END,
                rl.road_code,
                False
            ) AS end_ref
        FROM road_lines AS rl
        LEFT JOIN road_graph.roads AS ro
            ON ro.road_code = rl.road_code
    ),
    road_directions AS (
        SELECT
            rr.*,
            CASE
                WHEN (rr.end_ref->>'cumulative')::numeric >= (rr.start_ref->>'cumulative')::numeric THEN 1
                ELSE -1
            END AS sign
        FROM road_references AS rr
    )
    SELECT
        rd.road_order,
        rd.road_code,
        (rd.start_ref->>'marker_code')::integer,
        ((rd.start_ref->>'abscissa')::numeric - rd.sign * rd.shift)::real,
        ((rd.start_ref->>'cumulative')::numeric - rd.sign * rd.shift)::real,
        (rd.end_ref->>'marker_code')::integer,
        ((rd.end_ref->>'abscissa')::numeric + rd.sign * rd.shift)::real,
        ((rd.end_ref->>'cumulative')::numeric + rd.sign * rd.shift)::real,
        CASE WHEN rd.sign = 1 THEN 'downstream' ELSE 'upstream' END,
        round(ST_Length(rd.geom)::numeric, 2)::real,
        rd.geom::geometry(LineString, 2154)
    FROM road_directions AS rd
    ORDER BY rd.road_order
    ;
END;

get_road_sections_from_snapped_line(_line geometry, _tolerance real DEFAULT 1)

Description

Parameters

Definition