pgr_trsp - Proposed

pgr_trsp - routing vertices with restrictions.

_images/boost-inside.jpeg

Boost Graph Inside

Warning

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 ANY-INTEGER and ANY-NUMERICAL

    • 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.

Availability

  • Version 3.4.0

    • New proposed signatures:

    • Deprecated signatures:

      • pgr_trsp(text,integer,integer,boolean,boolean,text)

      • pgr_trsp(text,integer,float,integer,float,boolean,boolean,text)

      • pgr_trspViaVertices(text,anyarray,boolean,boolean,text)

      • pgr_trspviaedges(text,integer[],double precision[],boolean,boolean,text)

  • Version 2.1.0

    • New prototypes:

      • pgr_trspViaVertices

      • pgr_trspViaEdges

  • Version 2.0.0

    • Official function

Description

Turn restricted shortest path (TRSP) is an algorithm that receives turn restrictions in form of a query like those found in real world navigable road networks.

The main characteristics are:

  • It does no guarantee the shortest path as it might contain restriction paths.

The general algorithm is as follows:

  • Execute a Dijkstra.

  • If the solution passes thru a restriction then.

    • Execute the TRSP algorithm with restrictions.

Signatures

Proposed

pgr_trsp(Edges SQL, Restrictions SQL, start vid, end vid, [directed])
pgr_trsp(Edges SQL, Restrictions SQL, start vid, end vids, [directed])
pgr_trsp(Edges SQL, Restrictions SQL, start vids, end vid, [directed])
pgr_trsp(Edges SQL, Restrictions SQL, start vids, end vids, [directed])
pgr_trsp(Edges SQL, Restrictions SQL, Combinations SQL, [directed])
RETURNS SET OF (seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
OR EMPTY SET

One to One

pgr_trsp(Edges SQL, Restrictions SQL, start vid, end vid, [directed])

RETURNS SET OF (seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
OR EMPTY SET
Example:

From vertex \(6\) to vertex \(10\) on an undirected graph.

SELECT * FROM pgr_trsp(
  $$SELECT id, source, target, cost, reverse_cost FROM edges$$,
  $$SELECT path, cost FROM restrictions$$,
  6, 10,
  false);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         6 |      10 |    6 |    2 |    1 |        0
   2 |        2 |         6 |      10 |   10 |   -1 |    0 |        1
(2 rows)

One to Many

pgr_trsp(Edges SQL, Restrictions SQL, start vid, end vids, [directed])

RETURNS SET OF (seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
OR EMPTY SET
Example:

From vertex \(6\) to vertices \(\{10, 1\}\) on an undirected graph.

SELECT * FROM pgr_trsp(
  $$SELECT id, source, target, cost FROM edges$$,
  $$SELECT * FROM restrictions$$,
  6, ARRAY[10, 1],
  false);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         6 |       1 |    6 |    4 |    1 |        0
   2 |        2 |         6 |       1 |    7 |   10 |    1 |        1
   3 |        3 |         6 |       1 |    8 |   12 |    1 |        2
   4 |        4 |         6 |       1 |   12 |   11 |    1 |        3
   5 |        5 |         6 |       1 |   11 |    8 |    1 |        4
   6 |        6 |         6 |       1 |    7 |    7 |    1 |        5
   7 |        7 |         6 |       1 |    3 |    6 |    1 |        6
   8 |        8 |         6 |       1 |    1 |   -1 |    0 |        7
   9 |        1 |         6 |      10 |    6 |    4 |    1 |        0
  10 |        2 |         6 |      10 |    7 |    8 |    1 |        1
  11 |        3 |         6 |      10 |   11 |    5 |    1 |        2
  12 |        4 |         6 |      10 |   10 |   -1 |    0 |        3
(12 rows)

Many to One

pgr_trsp(Edges SQL, Restrictions SQL, start vids, end vid, [directed])

RETURNS SET OF (seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
OR EMPTY SET
Example:

From vertices \(\{6, 1\}\) to vertex \(8\) on a directed graph.

SELECT * FROM pgr_trsp(
  $$SELECT id, source, target, cost, reverse_cost FROM edges$$,
  $$SELECT path, cost FROM restrictions$$,
  ARRAY[6, 1], 8);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         1 |       8 |    1 |    6 |    1 |        0
   2 |        2 |         1 |       8 |    3 |    7 |    1 |        1
   3 |        3 |         1 |       8 |    7 |   10 |  101 |        2
   4 |        4 |         1 |       8 |    8 |   -1 |    0 |      103
   5 |        1 |         6 |       8 |    6 |    4 |    1 |        0
   6 |        2 |         6 |       8 |    7 |   10 |    1 |        1
   7 |        3 |         6 |       8 |    8 |   -1 |    0 |        2
(7 rows)

Many to Many

pgr_trsp(Edges SQL, Restrictions SQL, start vids, end vids, [directed])

RETURNS SET OF (seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
OR EMPTY SET
Example:

From vertices \(\{6, 1\}\) to vertices \(\{10, 8\}\) on an undirected graph.

SELECT * FROM pgr_trsp(
  $$SELECT id, source, target, cost, reverse_cost FROM edges$$,
  $$SELECT path, cost FROM restrictions$$,
  ARRAY[6, 1], ARRAY[10, 8],
  false);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         1 |       8 |    1 |    6 |    1 |        0
   2 |        2 |         1 |       8 |    3 |    7 |    1 |        1
   3 |        3 |         1 |       8 |    7 |    4 |    1 |        2
   4 |        4 |         1 |       8 |    6 |    2 |    1 |        3
   5 |        5 |         1 |       8 |   10 |    5 |    1 |        4
   6 |        6 |         1 |       8 |   11 |   11 |    1 |        5
   7 |        7 |         1 |       8 |   12 |   12 |    1 |        6
   8 |        8 |         1 |       8 |    8 |   -1 |    0 |        7
   9 |        1 |         1 |      10 |    1 |    6 |    1 |        0
  10 |        2 |         1 |      10 |    3 |    7 |    1 |        1
  11 |        3 |         1 |      10 |    7 |    4 |    1 |        2
  12 |        4 |         1 |      10 |    6 |    2 |    1 |        3
  13 |        5 |         1 |      10 |   10 |   -1 |    0 |        4
  14 |        1 |         6 |       8 |    6 |    4 |    1 |        0
  15 |        2 |         6 |       8 |    7 |   10 |    1 |        1
  16 |        3 |         6 |       8 |    8 |   -1 |    0 |        2
  17 |        1 |         6 |      10 |    6 |    2 |    1 |        0
  18 |        2 |         6 |      10 |   10 |   -1 |    0 |        1
(18 rows)

Combinations

pgr_trsp(Edges SQL, Restrictions SQL, Combinations SQL, [directed])

RETURNS SET OF (seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)
OR EMPTY SET
Example:

Using a combinations table on an undirected graph.

SELECT * FROM pgr_trsp(
  $$SELECT id, source, target, cost, reverse_cost FROM edges$$,
  $$SELECT path, cost FROM restrictions$$,
  $$SELECT * FROM (VALUES (6, 10), (6, 1), (6, 8), (1, 8)) AS combinations (source, target)$$);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         1 |       8 |    1 |    6 |    1 |        0
   2 |        2 |         1 |       8 |    3 |    7 |    1 |        1
   3 |        3 |         1 |       8 |    7 |   10 |  101 |        2
   4 |        4 |         1 |       8 |    8 |   -1 |    0 |      103
   5 |        1 |         6 |       1 |    6 |    4 |    1 |        0
   6 |        2 |         6 |       1 |    7 |   10 |    1 |        1
   7 |        3 |         6 |       1 |    8 |   12 |    1 |        2
   8 |        4 |         6 |       1 |   12 |   13 |    1 |        3
   9 |        5 |         6 |       1 |   17 |   15 |    1 |        4
  10 |        6 |         6 |       1 |   16 |    9 |    1 |        5
  11 |        7 |         6 |       1 |   11 |    8 |    1 |        6
  12 |        8 |         6 |       1 |    7 |    7 |    1 |        7
  13 |        9 |         6 |       1 |    3 |    6 |    1 |        8
  14 |       10 |         6 |       1 |    1 |   -1 |    0 |        9
  15 |        1 |         6 |       8 |    6 |    4 |    1 |        0
  16 |        2 |         6 |       8 |    7 |   10 |    1 |        1
  17 |        3 |         6 |       8 |    8 |   -1 |    0 |        2
  18 |        1 |         6 |      10 |    6 |    4 |    1 |        0
  19 |        2 |         6 |      10 |    7 |   10 |    1 |        1
  20 |        3 |         6 |      10 |    8 |   12 |    1 |        2
  21 |        4 |         6 |      10 |   12 |   13 |    1 |        3
  22 |        5 |         6 |      10 |   17 |   15 |    1 |        4
  23 |        6 |         6 |      10 |   16 |   16 |    1 |        5
  24 |        7 |         6 |      10 |   15 |    3 |    1 |        6
  25 |        8 |         6 |      10 |   10 |   -1 |    0 |        7
(25 rows)

Parameters

Column

Type

Description

Edges SQL

TEXT

SQL query as described.

Restrictions SQL

TEXT

SQL query as described.

Combinations SQL

TEXT

Combinations SQL as described below

start vid

ANY-INTEGER

Identifier of the departure vertex.

start vids

ARRAY [ANY-INTEGER]

Array of identifiers of destination vertices.

end vid

ANY-INTEGER

Identifier of the departure vertex.

end vids

ARRAY [ANY-INTEGER]

Array of identifiers of destination vertices.

Where:

ANY-INTEGER:

SMALLINT, INTEGER, BIGINT

Optional parameters

Column

Type

Default

Description

directed

BOOLEAN

true

  • When true the graph is considered Directed

  • When false the graph is considered as Undirected.

Inner Queries

Edges SQL

Column

Type

Default

Description

id

ANY-INTEGER

Identifier of the edge.

source

ANY-INTEGER

Identifier of the first end point vertex of the edge.

target

ANY-INTEGER

Identifier of the second end point vertex of the edge.

cost

ANY-NUMERICAL

Weight of the edge (source, target)

reverse_cost

ANY-NUMERICAL

-1

Weight of the edge (target, source)

  • When negative: edge (target, source) does not exist, therefore it’s not part of the graph.

Where:

ANY-INTEGER:

SMALLINT, INTEGER, BIGINT

ANY-NUMERICAL:

SMALLINT, INTEGER, BIGINT, REAL, FLOAT

Restrictions SQL

Column

Type

Description

path

ARRAY [ANY-INTEGER]

Sequence of edge identifiers that form a path that is not allowed to be taken. - Empty arrays or NULL arrays are ignored. - Arrays that have a NULL element will raise an exception.

Cost

ANY-NUMERICAL

Cost of taking the forbidden path.

Where:

ANY-INTEGER:

SMALLINT, INTEGER, BIGINT

ANY-NUMERICAL:

SMALLINT, INTEGER, BIGINT, REAL, FLOAT

Combinations SQL

Parameter

Type

Description

source

ANY-INTEGER

Identifier of the departure vertex.

target

ANY-INTEGER

Identifier of the arrival vertex.

Where:

ANY-INTEGER:

SMALLINT, INTEGER, BIGINT

Result Columns

Returns set of (seq, path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)

Column

Type

Description

seq

INTEGER

Sequential value starting from 1.

path_id

INTEGER

Path identifier.

  • Has value 1 for the first of a path from start_vid to end_vid.

path_seq

INTEGER

Relative position in the path. Has value 1 for the beginning of a path.

start_vid

BIGINT

Identifier of the starting vertex.

end_vid

BIGINT

Identifier of the ending vertex.

node

BIGINT

Identifier of the node in the path from start_vid to end_vid.

edge

BIGINT

Identifier of the edge used to go from node to the next node in the path sequence. -1 for the last node of the path.

cost

FLOAT

Cost to traverse from node using edge to the next node in the path sequence.

agg_cost

FLOAT

Aggregate cost from start_vid to node.

See Also

Indices and tables