gis.road_graph

Parameters

Name	Type	Mode
_road_code	text	IN

Definition

DECLARE
    check_record record;
BEGIN
    WITH node AS (
        -- Get the node at the higher position in the roundabout
    	SELECT n.id, n.geom, e.id AS edge_id
    	FROM road_graph.nodes AS n
    	JOIN road_graph.edges AS e
    		ON e.road_code = _road_code
    		AND n.id IN (e.start_node, e.end_node)
        -- Do not take into account nodes connected to other roads
        -- since they are not only part of the roundabout but must be kept
        WHERE NOT EXISTS (
            SELECT f.id
            FROM road_graph.edges AS f
            WHERE f.road_code != _road_code
            AND n.id IN (f.start_node, f.end_node)
        )
    	ORDER BY ST_Y(n.geom) DESC LIMIT 1
    ),
    check_node AS (
        -- Intersects a circle of radius 1m around the node
        -- with the related edges so that we can then compare
        -- the generated points Y to the node Y
    	SELECT
    		n.id, e.id AS edge_id,
    		n.geom,
    		ST_Intersection(ST_ExteriorRing(ST_Buffer(n.geom, 1)), e.geom) AS inter
    	FROM node AS n
    	JOIN road_graph.edges AS e
    		ON n.id IN (e.start_node, e.end_node)
    		AND e.road_code = _road_code
    )
    -- Get id
    SELECT INTO check_record
    	n.id,
        bool_and(ST_Y(inter) <= ST_Y(n.geom)) AS ok
    FROM check_node AS n
    GROUP BY n.id
    ;

IF check_record.ok IS TRUE
    THEN
        RETURN check_record.id;
    ELSE
        RETURN NULL::integer;
    END IF;

END;

get_upper_roundabout_node_to_delete(_road_code text)

Description

Parameters

Definition