如何在PGRouting的pgr_dijkstra中计算有序节点序列的总距离？

Absolutely! You can automate this process in PostgreSQL using a PL/pgSQL function that iterates over your node array, computes the shortest path for each consecutive pair, and sums up the total distance. Here's a step-by-step implementation:

First, let's create a reusable function that accepts your node array and returns the total shortest path distance:

CREATE OR REPLACE FUNCTION calculate_total_shortest_path(node_array INT[])
RETURNS NUMERIC AS $$
DECLARE
    total_distance NUMERIC := 0;
    i INT;
    source_node INT;
    target_node INT;
    path_cost NUMERIC;
BEGIN
    -- Handle edge cases: empty array or single node (no paths to calculate)
    IF array_length(node_array, 1) <= 1 THEN
        RETURN 0;
    END IF;

    -- Iterate over each consecutive pair in the input array
    FOR i IN 1..array_length(node_array, 1)-1 LOOP
        source_node := node_array[i];
        target_node := node_array[i+1];

        -- Calculate the total cost of the shortest path between the current pair
        SELECT SUM(cost) INTO path_cost
        FROM pgr_dijkstra(
            'SELECT gid as id, source, target, rcost_len AS cost FROM finalroads',
            source_node,
            target_node,
            false
        );

        -- Add the path cost to the total (use COALESCE to handle missing paths)
        total_distance := total_distance + COALESCE(path_cost, 0);
    END LOOP;

    RETURN total_distance;
END;
$$ LANGUAGE plpgsql;

How It Works: #

• Edge Case Handling: If the input array has 0 or 1 nodes, the function returns 0 immediately since there are no paths to compute.
• Loop Through Pairs: The FOR loop runs from the first element to the second-to-last element, grabbing each consecutive source and target node pair.
• Path Calculation: For each pair, we call pgr_dijkstra and sum the cost column from its result to get the total distance of the shortest path between the two nodes.
• Sum Total: We accumulate the path costs into total_distance, using COALESCE to handle cases where no path exists between a pair (it adds 0 instead of NULL).

Using the Function: #

Once the function is created, you can call it with your node array like this:

SELECT calculate_total_shortest_path(ARRAY[2,3,4,5,6,8]);

Optional Adjustments: #

• If you want to raise an error when a path between a pair doesn't exist (instead of adding 0), replace the COALESCE line with:

  IF path_cost IS NULL THEN
      RAISE EXCEPTION 'No path exists between nodes % and %', source_node, target_node;
  END IF;
  total_distance := total_distance + path_cost;
  

内容的提问来源于stack exchange，提问作者Ayaz49