pgr_withPointsCostMatrix  proposed¶
Name¶
pgr_withPointsCostMatrix  Calculates the shortest path and returns only the aggregate cost of the shortest path(s) found, for the combination of points given.
Warning
These are proposed functions for next mayor release.
 They are not officially in the current release.
 They will likely officially be part of the next mayor release:
 The functions make use of ANYINTEGER and ANYNUMERICAL
 Name might not change. (But still can)
 Signature might not change. (But still can)
 Functionality might not change. (But still can)
 pgTap tests have being done. But might need more.
 Documentation might need refinement.
Signature Summary¶
pgr_withPointsCostMatrix(edges_sql, points_sql, start_vids)
pgr_withPointsCostMatrix(edges_sql, points_sql, start_vids, directed, driving_side)
RETURNS SET OF (start_vid, end_vid, agg_cost)
Note
There is no details flag, unlike the other members of the withPoints family of functions.
Signatures¶
Minimal Signature¶
 The minimal signature:
 Is 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.
pgr_withPointsCostMatrix(edges_sql, points_sql, start_vid)
RETURNS SET OF (start_vid, end_vid, agg_cost)
Example: 

SELECT * FROM pgr_withPointsCostMatrix(
'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
'SELECT pid, edge_id, fraction from pointsOfInterest',
array[1, 3, 6, 6]);
start_vid  end_vid  agg_cost
++
6  1  1.3
6  3  4.3
6  6  1.3
1  6  1.3
1  3  5.6
1  6  2.6
3  6  1.7
3  1  1.6
3  6  1
6  6  1.3
6  1  2.6
6  3  3
(12 rows)
Complete Signature¶
pgr_withPointsCostMatrix(edges_sql, points_sql, start_vids,
directed:=true, driving_side:='b')
RETURNS SET OF (start_vid, end_vid, agg_cost)
Example:  returning a symmetrical cost matrix 

 Using the default side value on the points_sql query
 Using an undirected graph
 Using the default driving_side value
SELECT * FROM pgr_withPointsCostMatrix(
'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
'SELECT pid, edge_id, fraction from pointsOfInterest',
array[1, 3, 6, 6], directed := false);
start_vid  end_vid  agg_cost
++
6  1  1.3
6  3  1.7
6  6  1.3
1  6  1.3
1  3  1.6
1  6  2.6
3  6  1.7
3  1  1.6
3  6  1
6  6  1.3
6  1  2.6
6  3  1
(12 rows)
Description of the Signatures¶
Description of the edges_sql query¶
edges_sql:  an SQL query, which should return a set of rows with the following columns: 

Column  Type  Default  Description 

id  ANYINTEGER  Identifier of the edge.  
source  ANYINTEGER  Identifier of the first end point vertex of the edge.  
target  ANYINTEGER  Identifier of the second end point vertex of the edge.  
cost  ANYNUMERICAL 


reverse_cost  ANYNUMERICAL  1 

Where:
ANYINTEGER:  SMALLINT, INTEGER, BIGINT 

ANYNUMERICAL:  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: 

Column  Type  Description 

pid  ANYINTEGER 

edge_id  ANYINTEGER  Identifier of the “closest” edge to the point. 
fraction  ANYNUMERICAL  Value in <0,1> that indicates the relative postition from the first end point of the edge. 
side  CHAR 

Where:
ANYINTEGER:  smallint, int, bigint 

ANYNUMERICAL:  smallint, int, bigint, real, float 
Description of the parameters of the signatures¶
Parameter  Type  Description 

edges_sql  TEXT  Edges SQL query as described above. 
points_sql  TEXT  Points SQL query as described above. 
start_vids  ARRAY[ANYINTEGER]  Array of identifiers of starting vertices. When negative: is a point’s pid. 
directed  BOOLEAN  (optional). When false the graph is considered as Undirected. Default is true which considers the graph as Directed. 
driving_side  CHAR 

Description of the return values¶
Returns set of (start_vid, end_vid, agg_cost)
Column  Type  Description 

start_vid  BIGINT  Identifier of the starting vertex. 
end_vid  BIGINT  Identifier of the ending vertex. 
agg_cost  FLOAT  Aggregate cost of the shortest path from start_vid to end_vid. 
Examples¶
Example:  Use with tsp 

SELECT * FROM pgr_TSP(
$$
SELECT * FROM pgr_withPointsCostMatrix(
'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
'SELECT pid, edge_id, fraction from pointsOfInterest',
array[1, 3, 6, 6], directed := false);
$$,
randomize := false
);
seq  node  cost  agg_cost
+++
1  6  1.3  0
2  1  1.6  1.3
3  3  1  2.9
4  6  1.3  3.9
5  6  0  5.2
(5 rows)
See Also¶
 withPoints  Family of functions
 Cost Matrix
 Traveling Sales Person
 sampledata network.
Indices and tables