gis.road_graph

Parameters

Name	Type	Mode
_road_code	text	IN
_point	geometry	IN
_use_cache	boolean	IN
id	integer	TABLE
road_code	text	TABLE
code	integer	TABLE
abscissa	real	TABLE
geom	geometry	TABLE
road_linestring_from_marker_to_point	geometry	TABLE
road_linestring_from_start_to_point	geometry	TABLE
closing_multilinestring	geometry	TABLE
is_next_edge_start_marker	boolean	TABLE
cumulative	real	TABLE

Definition

DECLARE
    _road_info record;
    _run_build_cache boolean;
    _previous_marker record;
    _point_is_between_edges boolean;
    _closest_edge_at_5m record;
    _next_edge_record record;
    -- timing variables
   _timing1  timestamptz;
   _start_ts timestamptz;
   _end_ts   timestamptz;
   _overhead numeric;     -- in ms
BEGIN

-- timing
    _timing1  := clock_timestamp();
    _start_ts := clock_timestamp();
    _end_ts   := clock_timestamp();
    -- take minimum duration as conservative estimate
    _overhead := 1000 * extract(epoch FROM LEAST(_start_ts - _timing1, _end_ts   - _start_ts));
    _start_ts := clock_timestamp();

-- If there is less than 2 edges, we do not use cache
    -- since it will not be useful and will raise an exception ( by function get_ordered_edges)
    SELECT INTO _use_cache
        CASE
            WHEN (SELECT COUNT(*) FROM road_graph.edges  AS e WHERE e.road_code = _road_code) < 2 THEN False
            ELSE _use_cache
        END
    ;

-- The road must be in the cache (see build_road_cached_objects).
    -- If not, the calculation is done without cache
    -- Two separate tests: the query on road_cache cannot be planned if the table does not exist
    IF _use_cache AND to_regclass('road_cache') IS NULL THEN
        _use_cache = False;
    END IF;
    IF _use_cache THEN
        IF NOT EXISTS (SELECT 1 FROM road_cache AS r WHERE r.road_code = _road_code) THEN
            _use_cache = False;
        END IF;
    END IF;

-- Retrieve cached objects such as road simple linestring and closing multilinestring
    -- and also the road markers locations agains the simple road linestring
    -- from the temporary table generated beforehand (see update_edge_references)
    IF _use_cache THEN

-- Get given _point location against the road simple linestring
        -- And retrieve data from the temporary table road_cache
        SELECT INTO _road_info
            r.simple_linestring,
            r.closing_multilinestring,
            r.edges_multilinestring,
            r.marker_locations,
            ST_LineLocatePoint(r.simple_linestring, _point) AS point_location
        FROM road_cache AS r
        WHERE r.road_code = _road_code
        ;

-- RAISE NOTICE '% = %' , 'get road info from cache', 1000 * (extract(epoch FROM clock_timestamp() - _start_ts)) - _overhead;

-- Get the previous marker
        -- which is the one with the location just before the _point location
        -- We also compute the linestring from the marker to the end of the road
        -- since we can use it to simplify the calcution of the reference from the point
        SELECT
        INTO _previous_marker
            m.id, m.road_code, m.code, m.abscissa, m.geom,
            -- Linestring (with no gaps) between the marker and the point
            ST_LineSubstring(
                _road_info.simple_linestring,
                (_road_info.marker_locations->>(m.id::text))::float8,
                _road_info.point_location
            ) AS road_linestring_from_marker_to_point,
            -- Linestring (with no gaps) between the start of the road and the point
            ST_LineSubstring(
                _road_info.simple_linestring,
                0,
                _road_info.point_location
            ) AS road_linestring_from_start_to_point,
            -- MultiLinestring of the linestrings generated to close the gaps between edges
            -- used in other functions to remove them from the road linestring parts
            _road_info.closing_multilinestring,
            -- MultiLinestring of the edges (with gaps between edges)
            _road_info.edges_multilinestring,
            -- road simple linestring : the single linestring (with no gaps) made by merging all edges linestrings and closing linestrings
            _road_info.simple_linestring AS road_simple_linestring,
            False AS is_next_edge_start_marker,
            NULL::real as cumulative
        FROM
            road_graph.markers AS m
        WHERE True
        -- Only the markers of the road
        AND m.road_code = _road_code
        AND (_road_info.marker_locations->>(m.id::text))::float8 <= _road_info.point_location
        ORDER BY (_road_info.marker_locations->>(m.id::text))::float8 DESC
        LIMIT 1
        ;
    -- RAISE NOTICE '% = %' , 'get previous marker from cache', 1000 * (extract(epoch FROM clock_timestamp() - _start_ts)) - _overhead;
    ELSE
        -- Use plain query to avoid creating temporary tables
        WITH
        -- get road ordered edges (use previous and next edge ids)
        ordered_edges AS (
            SELECT DISTINCT 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 (
            SELECT
                a.*,
                -- Calculate the location of the given point against this generated linestring
                ST_LineLocatePoint(a.geom, _point) AS point_location
            FROM (
                -- 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
            ) AS a
        ),
        marker_position AS (
            -- For each marker of the road, get its fractionnal location
            -- against the single merged linestring
            -- and all other needed columns values
            SELECT
                m.id, m.road_code, m.code, m.abscissa, m.geom,
                ST_LineLocatePoint(l.geom, m.geom) AS marker_location,
                l.geom AS road_simple_linestring
            FROM
                road_line as l,
                road_graph.markers AS m
            WHERE True
            AND m.road_code = _road_code
            -- No need to add filter 'AND m.road_code = l.road_code' since there is only one linestring
            -- No need to order data either
            -- ORDER BY m.code, m.abscissa
        )
        -- Get the previous marker
        -- which is the one with the location just before the _point location
        -- We also compute the linestring from the marker to the end of the road
        -- since we can use it to simplify the calcution of the reference from the point
        SELECT
        INTO _previous_marker
            m.id, m.road_code, m.code, m.abscissa, m.geom,
            -- Linestring (with no gaps) between the marker and the point
            ST_LineSubstring(
                l.geom,
                m.marker_location,
                point_location
            ) AS road_linestring_from_marker_to_point,
            -- Linestring (with no gaps) between the start of the road and the point
            ST_LineSubstring(
                l.geom,
                0,
                point_location
            ) AS road_linestring_from_start_to_point,
            -- MultiLinestring of the linestrings generated to close the gaps between edges
            -- used in other functions to remove them from the road linestring parts
            l.closing_multilinestring,
            -- MultiLinestring of the edges (with gaps between edges)
            l.edges_multilinestring,
            -- road simple linestring : the single linestring (with no gaps) made by merging all edges linestrings and closing linestrings
            l.geom AS road_simple_linestring,
            False AS is_next_edge_start_marker,
            NULL::real as cumulative
        FROM
            marker_position AS m,
            road_line AS l
        WHERE True
        AND m.marker_location <= l.point_location
        ORDER BY m.marker_location DESC
        LIMIT 1
        ;
    -- RAISE NOTICE '% = %' , 'get road info without cache', 1000 * (extract(epoch FROM clock_timestamp() - _start_ts)) - _overhead;
    END IF;

-- Check if the _point is between 2 real edges or close to the edges
    _point_is_between_edges =
    CASE
        WHEN ST_Distance(
                _point,
                _previous_marker.road_simple_linestring
            ) != ST_Distance(
                _point,
                _previous_marker.edges_multilinestring
            )
            THEN True
        ELSE False
    END
    ;
    -- RAISE NOTICE '% = %' , 'check point between edges', 1000 * (extract(epoch FROM clock_timestamp() - _start_ts)) - _overhead;
    -- RAISE NOTICE '_point_is_between_edges: %', _point_is_between_edges;

-- If the point is between two edges (floating in the air),
    -- we get the closest edge start, and replace the return references if needed
    --  |---ID:5-----(4)---------|     there is a gap    X |(7+60)----ID:10-----------|
    --                         4+180                      7+60
    -- the point X should have the references 7+60 if it is close enough to the edge ID 10
    IF _point_is_between_edges IS TRUE THEN
        -- Get the closest edge (tolerance 5m)
        SELECT INTO _closest_edge_at_5m
            e.id,
            e.start_marker, e.start_abscissa, e.start_cumulative,
            _point <-> ST_StartPoint(e.geom) AS distance,
            ST_StartPoint(e.geom) AS geom
        FROM road_graph.edges AS e
        WHERE e.road_code = _road_code
        AND ST_DWithin(ST_StartPoint(e.geom), _point, 5)
        ORDER BY distance
        LIMIT 1
        ;

IF _closest_edge_at_5m.id IS NOT NULL
            AND Coalesce(_closest_edge_at_5m.start_marker * 10000 + _closest_edge_at_5m.start_abscissa, 0.0)
                > Coalesce(_previous_marker.code * 10000 + _previous_marker.abscissa, 0.0)
        THEN
            -- RAISE NOTICE 'We should use the next edge start_marker instead of the previous marker code';
            _previous_marker.id := _closest_edge_at_5m.id;
            _previous_marker.code := Coalesce(_closest_edge_at_5m.start_marker, 0);
            _previous_marker.abscissa := Coalesce(_closest_edge_at_5m.start_abscissa, 0.0);
            _previous_marker.geom := _closest_edge_at_5m.geom;
            -- Linestring (with no gaps) between the marker and the point
            -- In this case, we adapt and return the same geometry as the one between the start and the point
            _previous_marker.road_linestring_from_marker_to_point := _previous_marker.road_linestring_from_start_to_point;
            -- Declare we have not taken the previous marker, but the start marker of the first next edge
            _previous_marker.is_next_edge_start_marker = True;
            -- we also need the start_cumulative
            _previous_marker.cumulative = _closest_edge_at_5m.start_cumulative;
        END IF;
    END IF;

-- Return the previous marker data
    RETURN QUERY
    SELECT
        _previous_marker.id, _previous_marker.road_code,
        _previous_marker.code, _previous_marker.abscissa,
        _previous_marker.geom,
        _previous_marker.road_linestring_from_marker_to_point,
        _previous_marker.road_linestring_from_start_to_point,
        _previous_marker.closing_multilinestring,
        _previous_marker.is_next_edge_start_marker,
        _previous_marker.cumulative
    ;

END;

get_road_previous_marker_from_point(_road_code text, _point geometry, _use_cache boolean DEFAULT false)

Description

Parameters

Definition