gis.road_graph

Parameters

Name	Type	Mode
_params	jsonb	IN

Definition

DECLARE
    node record;
    edges_data record;
    kept_edge record;
BEGIN
    -- The node may have been deleted by a previous fix
    SELECT n.*
    INTO node
    FROM road_graph.nodes AS n
    WHERE n.id = (_params->>'node_id')::integer
    ;
    IF node.id IS NULL THEN
        RETURN jsonb_build_object(
            'status', 'skipped',
            'message', format('The node %s does not exist anymore', _params->>'node_id')
        );
    END IF;

-- Edges of the node: the edge arriving at the node and the edge leaving it.
    -- An edge starting and ending at the node (loop) is neither arriving nor leaving
    SELECT
        count(e.id) AS edge_count,
        count(DISTINCT e.road_code) AS road_code_count,
        count(DISTINCT Coalesce(e.no_intersection_cutting, False)) AS cutting_count,
        (array_agg(e.id) FILTER (WHERE x.is_arriving))[1] AS arriving_edge_id,
        (array_agg(e.start_node) FILTER (WHERE x.is_arriving))[1] AS start_node_id,
        (array_agg(e.geom) FILTER (WHERE x.is_arriving))[1] AS arriving_geom,
        (array_agg(e.id) FILTER (WHERE x.is_leaving))[1] AS leaving_edge_id,
        (array_agg(e.end_node) FILTER (WHERE x.is_leaving))[1] AS end_node_id,
        (array_agg(e.geom) FILTER (WHERE x.is_leaving))[1] AS leaving_geom
    INTO edges_data
    FROM road_graph.edges AS e,
    LATERAL (
        SELECT
            e.end_node = node.id AND e.start_node != node.id AS is_arriving,
            e.start_node = node.id AND e.end_node != node.id AS is_leaving
    ) AS x
    WHERE node.id IN (e.start_node, e.end_node)
    ;

-- Node without edge: delete it
    IF edges_data.edge_count = 0 THEN
        DELETE FROM road_graph.nodes AS n
        WHERE n.id = node.id
        ;
        RETURN jsonb_build_object(
            'status', 'success',
            'message', format('The node %s without edge has been deleted', node.id)
        );
    END IF;

-- The node may have been connected to other edges by a previous fix
    IF NOT (
        edges_data.edge_count = 2
        AND edges_data.road_code_count = 1
        AND edges_data.cutting_count = 1
        AND edges_data.arriving_edge_id IS NOT NULL
        AND edges_data.leaving_edge_id IS NOT NULL
        AND edges_data.start_node_id != edges_data.end_node_id
    ) THEN
        RETURN jsonb_build_object(
            'status', 'skipped',
            'message', format('The node %s is not useless anymore', node.id)
        );
    END IF;

-- The two edges must only touch each other at the node:
    -- an edge crossing the other one cannot be merged
    IF NOT ST_Touches(edges_data.arriving_geom, edges_data.leaving_geom) THEN
        RETURN jsonb_build_object(
            'status', 'error',
            'message', format(
                'The edges %s and %s cross each other and cannot be merged: '
                || 'fix the issues of the check G01 first',
                edges_data.arriving_edge_id, edges_data.leaving_edge_id
            )
        );
    END IF;

-- Delete the node: the trigger before_node_delete merges the two edges into the edge with the lowest id
    -- An error of the triggers, for example a node still used by an edge, cancels the changes of this fix
    BEGIN
        DELETE FROM road_graph.nodes AS n
        WHERE n.id = node.id
        ;
    EXCEPTION WHEN foreign_key_violation THEN
        RAISE EXCEPTION
            'The edges % and % cannot be merged: fix the issues of the checks G01 and G03 first',
            edges_data.arriving_edge_id, edges_data.leaving_edge_id
        ;
    END;

-- Check the merged edge goes from the start node of the arriving edge to the end node of the leaving edge.
    -- If not, for example if the merged edge has been split by the triggers at a crossing or near another node,
    -- the exception cancels the changes of this fix (see fix_graph_issue)
    SELECT e.id, e.start_node, e.end_node, count(*) OVER () AS edge_count
    INTO kept_edge
    FROM road_graph.edges AS e
    WHERE e.id IN (edges_data.arriving_edge_id, edges_data.leaving_edge_id)
    LIMIT 1
    ;
    IF kept_edge.id IS NULL
        OR kept_edge.edge_count != 1
        OR kept_edge.start_node IS DISTINCT FROM edges_data.start_node_id
        OR kept_edge.end_node IS DISTINCT FROM edges_data.end_node_id
    THEN
        -- The message is split in string constants on separate lines, which are concatenated
        RAISE EXCEPTION
            'The edges % and % cannot be merged without other changes of the graph, '
            'for example because they cross other edges or pass near other nodes: '
            'fix the issues of the checks G01 and G03 first',
            edges_data.arriving_edge_id, edges_data.leaving_edge_id
        ;
    END IF;

RETURN jsonb_build_object(
        'status', 'success',
        'message', format(
            'The node %s has been deleted, and the edges %s and %s have been merged into the edge %s',
            node.id, edges_data.arriving_edge_id, edges_data.leaving_edge_id, kept_edge.id
        )
    );
END;

fix_graph_delete_node(_params jsonb)

Description

Parameters

Definition