gis.road_graph

Parameters

Name	Type	Mode
_params	jsonb	IN

Definition

DECLARE
    keep_node record;
    remove_node record;
BEGIN
    -- Nodes to keep and to remove
    SELECT n.*
    INTO keep_node
    FROM road_graph.nodes AS n
    WHERE n.id = (_params->>'keep_node_id')::integer
    ;
    SELECT n.*
    INTO remove_node
    FROM road_graph.nodes AS n
    WHERE n.id = (_params->>'remove_node_id')::integer
    ;

-- The node may have been merged or deleted by a previous fix
    IF remove_node.id IS NULL THEN
        RETURN jsonb_build_object(
            'status', 'skipped',
            'message', format('The node %s does not exist anymore', _params->>'remove_node_id')
        );
    END IF;
    IF keep_node.id IS NULL THEN
        RETURN jsonb_build_object(
            'status', 'error',
            'message', format('The node to keep %s does not exist anymore', _params->>'keep_node_id')
        );
    END IF;

-- The nodes must not be linked by an edge, which would be reduced to a point
    IF EXISTS (
        SELECT 1
        FROM road_graph.edges AS e
        WHERE (e.start_node = keep_node.id AND e.end_node = remove_node.id)
        OR (e.start_node = remove_node.id AND e.end_node = keep_node.id)
    ) THEN
        RETURN jsonb_build_object(
            'status', 'error',
            'message', format('The nodes %s and %s are linked by an edge', keep_node.id, remove_node.id)
        );
    END IF;

-- Move the start and end points of the edges of the node to remove to the node to keep:
    -- the triggers use the node to keep for these edges
    UPDATE road_graph.edges AS e
    SET geom = ST_SetPoint(e.geom, 0, keep_node.geom)
    WHERE e.start_node = remove_node.id
    ;
    UPDATE road_graph.edges AS e
    SET geom = ST_SetPoint(e.geom, ST_NPoints(e.geom) - 1, keep_node.geom)
    WHERE e.end_node = remove_node.id
    ;

-- Delete the node to remove, which has no edge anymore
    DELETE FROM road_graph.nodes AS n
    WHERE n.id = remove_node.id
    AND NOT EXISTS (
        SELECT 1
        FROM road_graph.edges AS e
        WHERE remove_node.id IN (e.start_node, e.end_node)
    )
    ;

RETURN jsonb_build_object(
        'status', 'success',
        'message', format('The node %s has been merged into the node %s', remove_node.id, keep_node.id)
    );
END;

fix_graph_merge_nodes(_params jsonb)

Description

Parameters

Definition