pgr_withPointsCost - Proposed - pgRouting Manual (3.0)

Description

Modify the graph to include points defined by points_sql. Using Dijkstra algorithm, return only the aggregate cost of the shortest path(s) found.

The main characteristics are:

• It does not return a path.

• Returns the sum of the costs of the shortest path for pair combination of vertices in the modified graph.

• Vertices of the graph are:

  • positive when it belongs to the edges_sql

  • negative when it belongs to the points_sql

• Process is done only on edges with positive costs.

• Values are returned when there is a path.

  • The returned values are in the form of a set of (start_vid, end_vid, agg_cost) .

  • When the starting vertex and ending vertex are the same, there is no path.

    • The agg_cost in the non included values (v, v) is 0

  • When the starting vertex and ending vertex are the different and there is no path.

    • The agg_cost in the non included values (u, v) is \(\infty\)

• If the values returned are stored in a table, the unique index would be the pair: (start_vid, end_vid) .

• For undirected graphs, the results are symmetric .

  • The agg_cost of (u, v) is the same as for (v, u) .

• For optimization purposes, any duplicated value in the start_vids or end_vids is ignored.

• The returned values are ordered:

  • start_vid ascending

  • end_vid ascending

• Running time: \(O( start\_vids * (V \log V + E))\)

Signatures

Summary

pgr_withPointsCost(edges_sql, points_sql, from_vid,  to_vid  [, directed] [, driving_side])
pgr_withPointsCost(edges_sql, points_sql, from_vid,  to_vids [, directed] [, driving_side])
pgr_withPointsCost(edges_sql, points_sql, from_vids, to_vid  [, directed] [, driving_side])
pgr_withPointsCost(edges_sql, points_sql, from_vids, to_vids [, directed] [, driving_side])
RETURNS SET OF (start_vid, end_vid, agg_cost)

Using defaults

pgr_withPointsCost(edges_sql, points_sql, start_vid, end_vid)
RETURNS SET OF (start_vid, end_vid, agg_cost)

Example :

From point \(1\) to point \(3\)

• For a directed graph.

• The driving side is set as b both. So arriving/departing to/from the point(s) can be in any direction.

SELECT * FROM pgr_withPointsCost(
    'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
    'SELECT pid, edge_id, fraction, side from pointsOfInterest',
    -1, -3);
 start_pid  end_pid  agg_cost
-----------+---------+----------
        -1       -3       3.2
(1 row)

pgr_withPointsCost(edges_sql, points_sql, from_vid,  to_vid  [, directed] [, driving_side])
RETURNS SET OF (seq, node, edge, cost, agg_cost)

SELECT * FROM pgr_withPointsCost(
    'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
    'SELECT pid, edge_id, fraction, side from pointsOfInterest',
    -1, 3,
    directed := false);
 start_pid  end_pid  agg_cost
-----------+---------+----------
        -1        3       1.6
(1 row)

pgr_withPointsCost(edges_sql, points_sql, from_vid,  to_vids [, directed] [, driving_side])
RETURNS SET OF (start_vid, end_vid, agg_cost)

SELECT * FROM pgr_withPointsCost(
    'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
    'SELECT pid, edge_id, fraction, side from pointsOfInterest',
    -1, ARRAY[-3,5]);
 start_pid  end_pid  agg_cost
-----------+---------+----------
        -1       -3       3.2
        -1        5       1.6
(2 rows)

pgr_withPointsCost(edges_sql, points_sql, from_vids, to_vid  [, directed] [, driving_side])
RETURNS SET OF (start_vid, end_vid, agg_cost)

SELECT * FROM pgr_withPointsCost(
    'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
    'SELECT pid, edge_id, fraction, side from pointsOfInterest',
    ARRAY[-1,2], -3);
 start_pid  end_pid  agg_cost
-----------+---------+----------
        -1       -3       3.2
         2       -3       2.6
(2 rows)

pgr_withPointsCost(edges_sql, points_sql, from_vids, to_vids [, directed] [, driving_side])
RETURNS SET OF (start_vid, end_vid, agg_cost)

SELECT * FROM pgr_withPointsCost(
    'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
    'SELECT pid, edge_id, fraction, side from pointsOfInterest',
    ARRAY[-1,2], ARRAY[-3,7]);
 start_pid  end_pid  agg_cost
-----------+---------+----------
        -1       -3       3.2
        -1        7       3.6
         2       -3       2.6
         2        7         3
(4 rows)

Parameters

Inner query

Where:

ANY-INTEGER :

SMALLINT, INTEGER, BIGINT

ANY-NUMERICAL :

SMALLINT, INTEGER, BIGINT, REAL, FLOAT

Description of the Points SQL query

points_sql :

an SQL query, which should return a set of rows with the following columns:

Where:

ANY-INTEGER :

smallint, int, bigint

ANY-NUMERICAL :

smallint, int, bigint, real, float

Result Columns

Additional Examples

Example :

From point \(1\) and vertex \(2\) to point \(3\) and vertex \(7\) , with right side driving topology

SELECT * FROM pgr_withPointsCost(
    'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
    'SELECT pid, edge_id, fraction, side from pointsOfInterest',
    ARRAY[-1,2], ARRAY[-3,7],
    driving_side := 'l');
 start_pid  end_pid  agg_cost
-----------+---------+----------
        -1       -3       3.2
        -1        7       3.6
         2       -3       2.6
         2        7         3
(4 rows)

Example :

From point \(1\) and vertex \(2\) to point \(3\) and vertex \(7\) , with left side driving topology

SELECT * FROM pgr_withPointsCost(
    'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
    'SELECT pid, edge_id, fraction, side from pointsOfInterest',
    ARRAY[-1,2], ARRAY[-3,7],
    driving_side := 'r');
 start_pid  end_pid  agg_cost
-----------+---------+----------
        -1       -3         4
        -1        7       4.4
         2       -3       2.6
         2        7         3
(4 rows)

Example :

From point \(1\) and vertex \(2\) to point \(3\) and vertex \(7\) , does not matter driving side.

SELECT * FROM pgr_withPointsCost(
    'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
    'SELECT pid, edge_id, fraction, side from pointsOfInterest',
    ARRAY[-1,2], ARRAY[-3,7],
    driving_side := 'b');
 start_pid  end_pid  agg_cost
-----------+---------+----------
        -1       -3       3.2
        -1        7       3.6
         2       -3       2.6
         2        7         3
(4 rows)

The queries use the Sample Data network.

See Also

• withPoints - Family of functions

Indices and tables

• Index

• Search Page