gis.road_graph

Parameters

Name	Type	Mode
_point	geometry	IN
_road_code	text	IN
_use_cache	boolean	IN

Definition

DECLARE
    closest_edge record;
    closest_edge_marker record;
    previous_marker record;
    merged_upstream_edges record;
    upstream_road_from_marker record;
    upstream_road_from_start record;
    found_road_code text;
    found_marker_code integer;
    -- double precision and not real: a real converted to numeric only keeps 6 significant digits
    -- for example a cumulative distance of 50955.53 m was rounded to 50955.5 m
    found_abscissa double precision;
    found_offset double precision;
    found_side text;
    found_cumulative double precision;
    cached_edge_orders jsonb;
    raise_notice text;
BEGIN
    raise_notice = road_graph.get_current_setting('road.graph.raise.notice', 'no');
    IF raise_notice IN ('info', 'debug') THEN
        RAISE NOTICE '% get_reference_from_point - _point = % & _road_code = %',
            REPEAT('    ', pg_trigger_depth()::INTEGER),
            ST_AsText(_point),
            _road_code
        ;
    END IF;

-- Get the edge order of the road from the cache, if it has been built beforehand
    -- (see build_road_cached_objects)
    IF _use_cache AND _road_code IS NOT NULL AND to_regclass('road_cache') IS NOT NULL THEN
        SELECT INTO cached_edge_orders r.edge_orders
        FROM road_cache AS r
        WHERE r.road_code = _road_code
        ;
    END IF;

-- Get the closest edge depending on given road_code
    IF _road_code IS NOT NULL THEN
        WITH ordered_ids AS (
            -- Edge order calculated only if not found in the cache
            SELECT o.id, o.edge_order
            FROM road_graph.get_ordered_edges(_road_code, -1, 'downstream') AS o
            WHERE cached_edge_orders IS NULL
            UNION ALL
            SELECT j.key::integer AS id, j.value::integer AS edge_order
            FROM jsonb_each_text(cached_edge_orders) AS j
        )
        SELECT INTO closest_edge
            e.*,
            -- Calculate the distance between the edge and the point
            -- Do not use e.geom <-> _point AS distance
            -- since it can lead to some PostGIS errors if used in the ORDER BY
            -- such as "ERROR:  index returned tuples in wrong order"
            ST_Distance(e.geom, _point) AS distance
        FROM
            road_graph.edges AS e
        INNER JOIN ordered_ids AS o ON e.id = o.id
        -- Limit search for the given road code
        WHERE e.road_code = _road_code
        -- Limit search to 50m
        AND ST_DWithin(e.geom, _point, 50)
        -- we must also order by edge_order, start_cumulative
        -- in case the point catches multiple edges (start and end points)
        ORDER BY distance, o.edge_order, e.start_cumulative
        -- Get only the closest edge, with the least edge_order -- start cumulative
        LIMIT 1
        ;
    ELSE
        SELECT INTO closest_edge
            e.*,
            -- Calculate the distance between the edge and the point
            -- Do not use e.geom <-> _point AS distance
            -- since it can lead to some PostGIS errors if used in the ORDER BY
            -- such as "ERROR:  index returned tuples in wrong order"
            ST_Distance(e.geom, _point) AS distance
        FROM
            road_graph.edges AS e
            -- LIMIT search without road_code TO 50m
        WHERE ST_DWithin(e.geom, _point, 50)
        ORDER BY distance
        -- Get only the closest edge
        LIMIT 1
        ;
    END IF;
    IF closest_edge IS NULL THEN
        IF raise_notice = 'debug' THEN
            RAISE NOTICE 'CLOSEST_EDGE is NULL';
        END IF;
        RETURN NULL;
    END IF;
    IF raise_notice = 'debug' THEN
        RAISE NOTICE 'CLOSEST_EDGE %', to_json(closest_edge);
    END IF;

-- Get road_code
    found_road_code = closest_edge.road_code;

WITH
    get_previous_marker AS (
        SELECT *
        FROM road_graph.get_road_previous_marker_from_point(
            found_road_code,
            _point,
            _use_cache
        )
    ),
    marker_data AS (
        SELECT
            -- marker data
            m.*,
            -- Multilinestring from the marker to the point
            ST_Difference(
                -- linestring between the marker and _point
                m.road_linestring_from_marker_to_point,
                -- multilinestring made with connectors between edges end points and start points
                m.closing_multilinestring,
                -- grid size to avoid rounding issues
                0.01
            ) AS upstream_road_from_marker,
            -- Multilinestring from the road start to the point
            ST_Difference(
                -- linestring between the road start and the point
                m.road_linestring_from_start_to_point,
                -- multilinestring made with connectors between edges end points and start points
                m.closing_multilinestring,
                -- grid size to avoid rounding issues
                0.01
            ) AS upstream_road_from_start
        FROM
            get_previous_marker AS m
    )
    SELECT INTO closest_edge_marker
        m.*
    FROM marker_data AS m
    ;

IF raise_notice = 'debug' THEN
        RAISE NOTICE 'CLOSEST_EDGE_MARKER %', to_json(closest_edge_marker);
        RAISE NOTICE '-';
    END IF;

-- Calculate values to return based on the generated geometries
    -- If the marker is the next edge start marker, because the point
    -- was not on an edge but between two unconnected edges,
    -- we should take information from this edge.
    found_marker_code = closest_edge_marker.code;
    found_abscissa =
        CASE
            WHEN closest_edge_marker.is_next_edge_start_marker IS NOT TRUE
                THEN ST_Length(closest_edge_marker.upstream_road_from_marker) + closest_edge_marker.abscissa
            ELSE closest_edge_marker.abscissa
        END
    ;
    found_cumulative =
        CASE
            WHEN closest_edge_marker.is_next_edge_start_marker IS NOT TRUE
                THEN Coalesce(ST_Length(closest_edge_marker.upstream_road_from_start), 0)
            ELSE closest_edge_marker.cumulative
        END
    ;
    found_offset =
        CASE
            WHEN closest_edge_marker.is_next_edge_start_marker IS NOT TRUE
                THEN closest_edge.distance
            ELSE 0.0
        END
    ;
    found_side = (
        CASE
            WHEN ST_Contains(
                ST_Buffer(
                    closest_edge.geom,
                    closest_edge.distance +1,
                    'side=left'
                ), _point
            ) THEN 'left'
            ELSE 'right'
        END
    );

-- Build the JSON to return
    RETURN json_build_object(
        'road_code', found_road_code,
        'marker_code', found_marker_code,
        'abscissa', round(found_abscissa::numeric, 2),
        'cumulative', round(found_cumulative::numeric, 2),
        'offset', round(found_offset::numeric, 2),
        'side', found_side
    );

END;

get_reference_from_point(_point geometry, _road_code text DEFAULT NULL::text, _use_cache boolean DEFAULT false)

Description

Parameters

Definition