gis.road_graph

Parameters

Name	Type	Mode
_road_code	text	IN

Definition

BEGIN

-- The cache contains one row per road. It is dropped at the end of the transaction.
    -- Test before to avoid the notice raised by CREATE TABLE IF NOT EXISTS
    IF to_regclass('road_cache') IS NULL THEN
        CREATE TEMPORARY TABLE road_cache (
            road_code text PRIMARY KEY,
            simple_linestring geometry,
            closing_multilinestring geometry,
            edges_multilinestring geometry,
            marker_locations jsonb,
            edge_orders jsonb
        ) ON COMMIT DROP;
    END IF;

-- Remove the previous cache of the road, which could be outdated
    -- if the road graph has been modified since in the same transaction
    DELETE FROM road_cache AS r
    WHERE r.road_code = _road_code
    ;

-- The cache is not used for roads with less than 2 edges
    IF (SELECT count(*) FROM road_graph.edges AS e WHERE e.road_code = _road_code) < 2 THEN
        RETURN TRUE;
    END IF;

INSERT INTO road_cache (
        road_code, simple_linestring, closing_multilinestring, edges_multilinestring,
        marker_locations, edge_orders
    )
        WITH
        ordered_edges AS (
            -- get road ordered edges (use previous and next edge ids)
            SELECT o.id, o.road_code, o.edge_order, o.geom
            FROM road_graph.get_ordered_edges(_road_code, -1, 'downstream') AS o
        ),
        touching_edges AS (
            -- For each edge, add a line at the end of it
            -- from the previous edge (lag) end point
            -- to the edge start_point
            SELECT
                o.id, o.road_code, o.edge_order,
                -- closing lines between last end point and edge start point
                ST_MakeLine(
                        Coalesce(
                            ST_EndPoint(LAG(o.geom) OVER(ORDER BY edge_order)),
                            ST_StartPoint(o.geom)
                        ),
                        ST_StartPoint(o.geom)
                ) AS closing_line,
                -- Merge closing lines and edge geometry
                ST_LineMerge(ST_Union(
                    ST_MakeLine(
                        Coalesce(
                            ST_EndPoint(LAG(o.geom) OVER(ORDER BY edge_order)),
                            ST_StartPoint(o.geom)
                        ),
                        ST_StartPoint(o.geom)
                    ),
                    o.geom
                )) AS geom,
                o.geom AS edge_geom
            FROM ordered_edges AS o
            ORDER BY o.edge_order
        ),
        road_line AS (
            -- Create a single linestring by merging all edge augmented linestrings
            SELECT
                max(t.road_code) AS road_code,
                ST_MakeLine(t.geom ORDER BY t.edge_order) AS geom,
                ST_CollectionExtract(
                    ST_MakeValid(
                        ST_Multi(ST_Collect(t.closing_line ORDER BY t.edge_order))
                    ),
                    2
                ) AS closing_multilinestring,
                ST_CollectionExtract(
                    ST_MakeValid(
                        ST_Multi(ST_Collect(t.edge_geom ORDER BY t.edge_order))
                    ),
                    2
                )  AS edges_multilinestring
            FROM touching_edges AS t
        )
        SELECT
            _road_code AS road_code,
            l.geom AS simple_linestring,
            l.closing_multilinestring,
            l.edges_multilinestring,
            jsonb_object_agg(
                m.id,
                ST_LineLocatePoint(l.geom, m.geom)
            ) AS marker_locations,
            -- Order of the edges, used to choose between two edges at the same distance from a point
            (
                SELECT jsonb_object_agg(eo.id, eo.edge_order)
                FROM (
                    SELECT o.id, min(o.edge_order) AS edge_order
                    FROM ordered_edges AS o
                    GROUP BY o.id
                ) AS eo
            ) AS edge_orders
        FROM
            road_line as l,
            road_graph.markers AS m
        WHERE TRUE
        AND m.road_code = _road_code
        AND l.road_code = _road_code
        GROUP BY l.geom, l.closing_multilinestring, l.edges_multilinestring
    ;

RETURN TRUE;

END;

build_road_cached_objects(_road_code text)

Description

Parameters

Definition