gis.road_graph

Parameters

Name	Type	Mode
_multilinestring	geometry	IN
_road_code	text	IN

Definition

DECLARE
    _result_multilinestring geometry(MULTILINESTRING, 2154);
BEGIN

-- Return untouched geometry if road code is not given
    IF _road_code IS NULL OR trim(_road_code) = '' THEN
        RETURN _multilinestring;
    END IF;

-- Return untouched geometry if NULL or not a MultiLineString
    IF Coalesce(ST_GeometryType(_multilinestring), '') != 'ST_MultiLineString' THEN
        RETURN _multilinestring;
    END IF;

-- Return untouched geometry if there is only one part
    IF ST_NumGeometries(_multilinestring) <= 1 THEN
        RETURN _multilinestring;
    END IF;

BEGIN
        -- Reorder the geometry parts
        WITH
        -- Split the multilinestring into parts
        d AS (
            SELECT ST_Dump(_multilinestring) AS dump
        ),
        -- Get the part geometries
        parties AS (
            SELECT (dump).geom
            FROM d
        ),
        -- Calculate the references of the parts start & end points
        get_refs AS (
            SELECT
                geom,
                road_graph.get_reference_from_point(
                    ST_StartPoint(geom),
                    _road_code
                ) AS start_refs,
                road_graph.get_reference_from_point(
                    ST_EndPoint(geom),
                    _road_code
                ) AS end_refs
            FROM parties
        ),
        -- Extract the references and calculate a cost which will be used to order the parts
        extract_values AS (
            SELECT
                geom,
                (start_refs->>'marker_code')::int AS start_marker_code,
                (start_refs->>'abscissa')::real AS start_abscissa,
                (start_refs->>'marker_code')::int * 10000 + (start_refs->>'abscissa')::real AS start_part_order,
                (end_refs->>'marker_code')::int AS end_marker_code,
                (end_refs->>'abscissa')::real AS end_abscissa,
                (end_refs->>'marker_code')::int * 10000 + (end_refs->>'abscissa')::real AS end_part_order
            FROM get_refs
        )
        -- Reassemble the parts order by the calculated cost
        SELECT INTO _result_multilinestring
            ST_Multi(
                ST_Collect(
                    -- Revert the geometry if needed
                    CASE
                        WHEN start_part_order > end_part_order
                            THEN ST_Reverse(geom)
                        ELSE geom
                    END
                    -- Order the parts based on the parts order
                    ORDER BY end_part_order
                )
            )
        FROM extract_values
        ;
    EXCEPTION WHEN OTHERS THEN
        _result_multilinestring = _multilinestring;
    END;

RETURN _result_multilinestring;

END;

reorder_multilinestring_parts(_multilinestring geometry, _road_code text)

Description

Parameters

Definition