# pgr_trsp - Migration guide¶

pgr_trsp signatures have changed and many issues have been fixed in the new signatures. This section will show how to migrate from the old signatures to the new replacement functions.

Nota

Results might be different as the deprecated function’s code is different from the replacement function.

All deprecated functions will be removed on next mayor version 4.0.0

## Migration of restrictions¶

The structure of the restrictions have changed:

### Old restrictions structure¶

On the deprecated signatures:

• Column rid is ignored

• via_path

• Must be in reverse order.

• Is of type TEXT.

• When more than one via edge must be separated with ,.

• target_id

• Is the last edge of the forbidden path.

• Is of type INTEGER.

• to_cost

• Is of type FLOAT.

For example:

SELECT * FROM old_restrictions;
rid | to_cost | target_id | via_path
-----+---------+-----------+----------
1 |     100 |         7 | 4
1 |     100 |        11 | 8
1 |     100 |        10 | 7
2 |       4 |         9 | 5, 3
3 |     100 |         9 | 16
(5 rows)



The restriction with rid = 2 is representing $$3\rightarrow5\rightarrow9$$

• $$3\rightarrow5$$

• is on column via_path in reverse order

• is of type TEXT

• $$9$$

• is on column target_id

• is of type INTEGER

### New restrictions structure¶

• Column id is ignored

• Column path

• Is of type ARRAY[ANY-INTEGER].

• Contains all the edges involved on the restriction.

• The array has the ordered edges of the restriction.

• Column cost

• Is of type ANY-NUMERICAL

SELECT * FROM restrictions;
id |  path   | cost
----+---------+------
1 | {4,7}   |  100
2 | {8,11}  |  100
3 | {7,10}  |  100
4 | {3,5,9} |    4
5 | {9,16}  |  100
(5 rows)



### Migration¶

To transform the old restrictions table to the new restrictions structure,

• Create a new table with the new restrictions structure.

• In this migration guide new_restrictions is been used.

• For this migration pgRuoting supllies an auxilary function for reversal of an array _pgr_array_reverse needed for the migration.

• _pgr_array_reverse:

• Was created temporarly for this migration

• Is not documented.

• Will be removed on the next mayor version 4.0.0

SELECT rid AS id,
_pgr_array_reverse(
array_prepend(target_id, string_to_array(via_path::text, ',')::BIGINT[])) AS path,
to_cost AS cost
INTO new_restrictions
FROM old_restrictions;
SELECT 5


The migrated table contents:

SELECT * FROM new_restrictions;
id |  path   | cost
----+---------+------
1 | {4,7}   |  100
1 | {8,11}  |  100
1 | {7,10}  |  100
2 | {3,5,9} |    4
3 | {16,9}  |  100
(5 rows)



## Migration of pgr_trsp (Vertices)¶

Signature to be migrated:

pgr_trsp(Edges SQL, source, target,
directed boolean, has_rcost boolean
[,restrict_sql text]);
RETURNS SETOF (seq, id1, id2, cost)

• The integral type of the Edges SQL can only be INTEGER.

• The floating point type of the Edges SQL can only be FLOAT.

• directed flag is compulsory.

• Does not have a default value.

• Does not autodetect if reverse_cost column exist.

• User must be carefull to match the existance of the column with the value of has_rcost parameter.

• The restrictions inner query is optional.

• The output column names are meaningless

Migrate by using:

### Migrating pgr_trsp (Vertices) using pgr_dijkstra¶

The following query does not have restrictions.

SELECT * FROM pgr_trsp(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost FROM edge_table WHERE id != 16$$,
4, 9,
true, true);
WARNING:  pgr_trsp(text,integer,integer,boolean,boolean) is been deprecated
seq | id1 | id2 | cost
-----+-----+-----+------
0 |   4 |   3 |    1
1 |   3 |   5 |    1
2 |   6 |   9 |    1
3 |   9 |  -1 |    0
(4 rows)


• A message about deprecation is shown

• Deprecated functions will be removed on the next mayor version 4.0.0

SELECT * FROM pgr_dijkstra(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table WHERE id != 16$$,
4, 9);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
1 |        1 |    4 |    3 |    1 |        0
2 |        2 |    3 |    5 |    1 |        1
3 |        3 |    6 |    9 |    1 |        2
4 |        4 |    9 |   -1 |    0 |        3
(4 rows)


• The types casting has been removed.

• Autodetects if reverse_cost column is in the edges SQL.

• Accepts ANY-INTEGER on integral types

• Accepts ANY-NUMERICAL on floating point types

• directed flag has a default value of true.

• Use the same value that on the original query.

• In this example it is true which is the default value.

• The flag has been omitted and the default is been used.

When the need of using strictly the same (meaningless) names and types of the function been migrated then:

SELECT seq, node::INTEGER AS id1, edge::INTEGER AS id2, cost
FROM pgr_dijkstra(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table WHERE id != 16$$,
4, 9);
seq | id1 | id2 | cost
-----+-----+-----+------
1 |   4 |   3 |    1
2 |   3 |   5 |    1
3 |   6 |   9 |    1
4 |   9 |  -1 |    0
(4 rows)


• id1 is the node

• id2 is the edge

### Migrating pgr_trsp (Vertices) using pgr_trsp¶

The following query has restrictions.

SELECT * FROM pgr_trsp(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost FROM edge_table WHERE id != 16$$,
4, 9,
true, true,
$$SELECT to_cost, target_id::INTEGER, via_path FROM old_restrictions$$);
WARNING:  pgr_trsp(text,integer,integer,boolean,boolean) is been deprecated
seq | id1 | id2 | cost
-----+-----+-----+------
0 |   4 |   3 |    1
1 |   3 |   2 |    1
2 |   2 |   4 |    1
3 |   5 |   8 |    1
4 |   6 |   9 |    1
5 |   9 |  -1 |    0
(6 rows)


• A message about deprecation is shown

• Deprecated functions will be removed on the next mayor version 4.0.0

• The restrictions are the last parameter of the function

• Using the old structure of restrictions

Use pgr_trsp - Proposed (One to One) instead.

SELECT * FROM pgr_trsp(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table WHERE id != 16$$,
$$SELECT * FROM new_restrictions$$,
4, 9);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 |        1 |         4 |       9 |    4 |    3 |    1 |        0
2 |        2 |         4 |       9 |    3 |    2 |    1 |        1
3 |        3 |         4 |       9 |    2 |    4 |    1 |        2
4 |        4 |         4 |       9 |    5 |    8 |    1 |        3
5 |        5 |         4 |       9 |    6 |    9 |    1 |        4
6 |        6 |         4 |       9 |    9 |   -1 |    0 |        5
(6 rows)


• The new structure of restrictions is been used.

• It is the second parameter.

• The types casting has been removed.

• Autodetects if reverse_cost column is in the edges SQL.

• Accepts ANY-INTEGER on integral types

• Accepts ANY-NUMERICAL on floating point types

• directed flag has a default value of true.

• Use the same value that on the original query.

• In this example it is true which is the default value.

• The flag has been omitted and the default is been used.

When the need of using strictly the same (meaningless) names and types of the function been migrated then:

SELECT seq, node::INTEGER AS id1, edge::INTEGER AS id2, cost
FROM pgr_trsp(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table WHERE id != 16$$,
$$SELECT * FROM new_restrictions$$,
4, 9);
seq | id1 | id2 | cost
-----+-----+-----+------
1 |   4 |   3 |    1
2 |   3 |   2 |    1
3 |   2 |   4 |    1
4 |   5 |   8 |    1
5 |   6 |   9 |    1
6 |   9 |  -1 |    0
(6 rows)


• id1 is the node

• id2 is the edge

## Migration of pgr_trsp (Edges)¶

Signature to be migrated:

pgr_trsp(sql text, source_edge integer, source_pos float8,
target_edge integer, target_pos float8,
directed boolean, has_rcost boolean
[,restrict_sql text]);
RETURNS SETOF (seq, id1, id2, cost)

• The integral types of the sql can only be INTEGER.

• The floating point type of the sql can only be FLOAT.

• directed flag is compulsory.

• Does not have a default value.

• Does not autodetect if reverse_cost column exist.

• User must be carefull to match the existance of the column with the value of has_rcost parameter.

• The restrictions inner query is optional.

For these migration guide the following points will be used:

SELECT pid, edge_id, fraction, side FROM pointsOfInterest
WHERE pid IN (3, 4);
pid | edge_id | fraction | side
-----+---------+----------+------
3 |      12 |      0.6 | l
4 |       6 |      0.3 | r
(2 rows)



Migrate by using:

### Migrating pgr_trsp (Edges) using pgr_withPoints¶

The following query does not have restrictions.

SELECT * FROM pgr_trsp(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost FROM edge_table$$,
6, 0.3, 12, 0.6,
true, true);
WARNING:  pgr_trsp(text,integer,float,integer,float,boolean,boolean) is been deprecated
seq | id1 | id2 | cost
-----+-----+-----+------
0 |  -1 |   6 |  0.7
1 |   8 |   7 |    1
2 |   5 |  10 |    1
3 |  10 |  12 |  0.6
4 |  -2 |  -1 |    0
(5 rows)


• A message about deprecation is shown

• Deprecated functions will be removed on the next mayor version 4.0.0

Use pgr_withPoints - Proposed instead.

SELECT * FROM pgr_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (4,3)$$,
-4, -3,
details => false);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
1 |        1 |   -4 |    6 |  0.7 |        0
2 |        2 |    8 |    7 |    1 |      0.7
3 |        3 |    5 |   10 |    1 |      1.7
4 |        4 |   10 |   12 |  0.6 |      2.7
5 |        5 |   -3 |   -1 |    0 |      3.3
(5 rows)


• The types casting has been removed.

• Do not show details, as the deprecated function does not show details.

• Autodetects if reverse_cost column is in the edges SQL.

• Accepts ANY-INTEGER on integral types

• Accepts ANY-NUMERICAL on floating point types

• directed flag has a default value of true.

• Use the same value that on the original query.

• In this example it is true which is the default value.

• The flag has been omitted and the default is been used.

• On the points query do not include the side column.

When the need of using strictly the same (meaningless) names and types, and node values of the function been migrated then:

SELECT seq, node::INTEGER AS id1, edge::INTEGER AS id2, cost
FROM pgr_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table$$,
$$SELECT * FROM (VALUES (1, 6, 0.3),(2, 12, 0.6)) AS t(pid, edge_id, fraction)$$,
-1, -2,
details => false);
seq | id1 | id2 | cost
-----+-----+-----+------
1 |  -1 |   6 |  0.7
2 |   8 |   7 |    1
3 |   5 |  10 |    1
4 |  10 |  12 |  0.6
5 |  -2 |  -1 |    0
(5 rows)


• id1 is the node

• id2 is the edge

### Migrating pgr_trsp (Edges) using pgr_trsp_withPoints¶

The following query has restrictions.

SELECT * FROM pgr_trsp(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost FROM edge_table$$,
6, 0.3, 12, 0.6, true, true,
$$SELECT to_cost, target_id::INTEGER, via_path FROM old_restrictions$$);
WARNING:  pgr_trsp(text,integer,float,integer,float,boolean,boolean) is been deprecated
seq | id1 | id2 | cost
-----+-----+-----+------
0 |  -1 |   6 |  0.7
1 |   8 |   7 |    1
2 |   5 |   8 |    1
3 |   6 |   9 |    1
4 |   9 |  16 |    1
5 |   4 |   3 |    1
6 |   3 |   2 |    1
7 |   2 |   4 |    1
8 |   5 |  10 |    1
9 |  10 |  12 |  0.6
(10 rows)


• A message about deprecation is shown

• Deprecated functions will be removed on the next mayor version 4.0.0

• The restrictions are the last parameter of the function

• Using the old structure of restrictions

Use pgr_trsp_withPoints - Proposed instead.

SELECT * FROM pgr_trsp_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (4,3)$$,
-4, -3,
details => false);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 |        1 |        -4 |      -3 |   -4 |    6 |  0.7 |        0
2 |        2 |        -4 |      -3 |    8 |    7 |    1 |      0.7
3 |        3 |        -4 |      -3 |    5 |    8 |    1 |      1.7
4 |        4 |        -4 |      -3 |    6 |    9 |    1 |      2.7
5 |        5 |        -4 |      -3 |    9 |   16 |    1 |      3.7
6 |        6 |        -4 |      -3 |    4 |    3 |    1 |      4.7
7 |        7 |        -4 |      -3 |    3 |    2 |    1 |      5.7
8 |        8 |        -4 |      -3 |    2 |    4 |    1 |      6.7
9 |        9 |        -4 |      -3 |    5 |   10 |    1 |      7.7
10 |       10 |        -4 |      -3 |   10 |   12 |  0.6 |      8.7
11 |       11 |        -4 |      -3 |   -3 |   -1 |    0 |      9.3
(11 rows)


• The new structure of restrictions is been used.

• It is the second parameter.

• The types casting has been removed.

• Do not show details, as the deprecated function does not show details.

• Autodetects if reverse_cost column is in the edges SQL.

• Accepts ANY-INTEGER on integral types

• Accepts ANY-NUMERICAL on floating point types

• directed flag has a default value of true.

• Use the same value that on the original query.

• In this example it is true which is the default value.

• The flag has been omitted and the default is been used.

• On the points query do not include the side column.

When the need of using strictly the same (meaningless) names and types, and node values of the function been migrated then:

SELECT seq, node::INTEGER AS id1, edge::INTEGER AS id2, cost
FROM pgr_trsp_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT * FROM (VALUES (1, 6, 0.3),(2, 12, 0.6)) AS t(pid, edge_id, fraction)$$,
-1, -2,
details => false)
WHERE edge != -1;
seq | id1 | id2 | cost
-----+-----+-----+------
1 |  -1 |   6 |  0.7
2 |   8 |   7 |    1
3 |   5 |   8 |    1
4 |   6 |   9 |    1
5 |   9 |  16 |    1
6 |   4 |   3 |    1
7 |   3 |   2 |    1
8 |   2 |   4 |    1
9 |   5 |  10 |    1
10 |  10 |  12 |  0.6
(10 rows)


• id1 is the node

• id2 is the edge

## Migration of pgr_trspViaVertices¶

Signature to be migrated:

pgr_trspViaVertices(sql text, vids integer[],
directed boolean, has_rcost boolean
[, turn_restrict_sql text]);
RETURNS SETOF (seq, id1, id2, id3, cost)

• The integral types of the Edges SQL can only be INTEGER.

• The floating point type of the Edges SQL can only be FLOAT.

• directed flag is compulsory.

• Does not have a default value.

• Does not autodetect if reverse_cost column exist.

• User must be carefull to match the existance of the column with the value of has_rcost parameter.

• The restrictions inner query is optional.

Migrate by using:

### Migrating pgr_trspViaVertices using pgr_dijkstraVia¶

The following query does not have restrictions.

SELECT * FROM pgr_trspViaVertices(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost FROM edge_table$$,
ARRAY[2, 8, 2],
true, true);
WARNING:  pgr_trspViaVertices(text,anyarray,boolean,boolean,text) is been deprecated
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 |   1 |   2 |   4 |    1
2 |   1 |   5 |   7 |    1
3 |   2 |   8 |   7 |    1
4 |   2 |   5 |   4 |    1
5 |   2 |   2 |  -1 |    0
(5 rows)


• A message about deprecation is shown

• Deprecated functions will be removed on the next mayor version 4.0.0

Use pgr_dijkstraVia - Proposed instead.

SELECT * FROM pgr_dijkstraVia(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table$$,
ARRAY[2, 8, 2]);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 |       1 |        1 |         2 |       8 |    2 |    4 |    1 |        0 |              0
2 |       1 |        2 |         2 |       8 |    5 |    7 |    1 |        1 |              1
3 |       1 |        3 |         2 |       8 |    8 |   -1 |    0 |        2 |              2
4 |       2 |        1 |         8 |       2 |    8 |    7 |    1 |        0 |              2
5 |       2 |        2 |         8 |       2 |    5 |    4 |    1 |        1 |              3
6 |       2 |        3 |         8 |       2 |    2 |   -2 |    0 |        2 |              4
(6 rows)


• The types casting has been removed.

• Autodetects if reverse_cost column is in the edges SQL.

• Accepts ANY-INTEGER on integral types

• Accepts ANY-NUMERICAL on floating point types

• directed flag has a default value of true.

• Use the same value that on the original query.

• In this example it is true which is the default value.

• The flag has been omitted and the default is been used.

• On the points query do not include the side column.

When the need of using strictly the same (meaningless) names and types of the function been migrated then:

SELECT row_number() over(ORDER BY seq) AS seq,
path_id::INTEGER AS id1, node::INTEGER AS id2,
CASE WHEN edge >= 0 THEN edge::INTEGER ELSE -1 END AS id3, cost::FLOAT
FROM pgr_dijkstraVia(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table$$,
ARRAY[2, 8, 2])
WHERE edge != -1;
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 |   1 |   2 |   4 |    1
2 |   1 |   5 |   7 |    1
3 |   2 |   8 |   7 |    1
4 |   2 |   5 |   4 |    1
5 |   2 |   2 |  -1 |    0
(5 rows)


• id1 is the path identifier

• id2 is the node

• id3 is the edge

### Migrating pgr_trspViaVertices using pgr_trspVia¶

The following query has restrictions.

SELECT * FROM pgr_trspViaVertices(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost FROM edge_table$$,
ARRAY[2, 8, 2],
true, true,
$$SELECT to_cost, target_id::INTEGER, via_path FROM old_restrictions$$);
WARNING:  pgr_trspViaVertices(text,anyarray,boolean,boolean,text) is been deprecated
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 |   1 |   2 |   4 |    1
2 |   1 |   5 |   8 |    1
3 |   1 |   6 |   9 |    1
4 |   1 |   9 |  16 |    1
5 |   1 |   4 |   3 |    1
6 |   1 |   3 |   5 |    1
7 |   1 |   6 |   8 |    1
8 |   1 |   5 |   7 |    1
9 |   2 |   8 |   7 |    1
10 |   2 |   5 |   4 |    1
11 |   2 |   2 |  -1 |    0
(11 rows)


• A message about deprecation is shown

• Deprecated functions will be removed on the next mayor version 4.0.0

• The restrictions are the last parameter of the function

• Using the old structure of restrictions

Use pgr_trspVia - Proposed instead.

SELECT * FROM pgr_trspVia(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table$$,
$$SELECT * FROM new_restrictions$$,
ARRAY[2, 8, 2]);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 |       1 |        1 |         2 |       8 |    2 |    4 |    1 |        0 |              0
2 |       1 |        2 |         2 |       8 |    5 |    8 |    1 |        1 |              1
3 |       1 |        3 |         2 |       8 |    6 |    9 |    1 |        2 |              2
4 |       1 |        4 |         2 |       8 |    9 |   16 |    1 |        3 |              3
5 |       1 |        5 |         2 |       8 |    4 |    3 |    1 |        4 |              4
6 |       1 |        6 |         2 |       8 |    3 |    5 |    1 |        5 |              5
7 |       1 |        7 |         2 |       8 |    6 |    8 |    1 |        6 |              6
8 |       1 |        8 |         2 |       8 |    5 |    7 |    1 |        7 |              7
9 |       1 |        9 |         2 |       8 |    8 |   -1 |    0 |        8 |              8
10 |       2 |        1 |         8 |       2 |    8 |    7 |    1 |        0 |              8
11 |       2 |        2 |         8 |       2 |    5 |    4 |    1 |        1 |              9
12 |       2 |        3 |         8 |       2 |    2 |   -2 |    0 |        2 |             10
(12 rows)


• The new structure of restrictions is been used.

• It is the second parameter.

• The types casting has been removed.

• Autodetects if reverse_cost column is in the edges SQL.

• Accepts ANY-INTEGER on integral types

• Accepts ANY-NUMERICAL on floating point types

• directed flag has a default value of true.

• Use the same value that on the original query.

• In this example it is true which is the default value.

• The flag has been omitted and the default is been used.

• On the points query do not include the side column.

When the need of using strictly the same (meaningless) names and types of the function been migrated then:

SELECT row_number() over(ORDER BY seq) AS seq,
path_id::INTEGER AS id1, node::INTEGER AS id2,
CASE WHEN edge >= 0 THEN edge::INTEGER ELSE -1 END AS id3, cost::FLOAT
FROM pgr_trspVia(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table$$,
$$SELECT * FROM new_restrictions$$,
ARRAY[2, 8, 2])
WHERE edge != -1;
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 |   1 |   2 |   4 |    1
2 |   1 |   5 |   8 |    1
3 |   1 |   6 |   9 |    1
4 |   1 |   9 |  16 |    1
5 |   1 |   4 |   3 |    1
6 |   1 |   3 |   5 |    1
7 |   1 |   6 |   8 |    1
8 |   1 |   5 |   7 |    1
9 |   2 |   8 |   7 |    1
10 |   2 |   5 |   4 |    1
11 |   2 |   2 |  -1 |    0
(11 rows)


• id1 is the path identifier

• id2 is the node

• id3 is the edge

## Migration of pgr_trspViaEdges¶

Signature to be migrated:

pgr_trspViaEdges(sql text, eids integer[], pcts float8[],
directed boolean, has_rcost boolean
[, turn_restrict_sql text]);
RETURNS SETOF (seq, id1, id2, id3, cost)

• The integral types of the Edges SQL can only be INTEGER.

• The floating point type of the Edges SQL can only be FLOAT.

• directed flag is compulsory.

• Does not have a default value.

• Does not autodetect if reverse_cost column exist.

• User must be carefull to match the existance of the column with the value of has_rcost parameter.

• The restrictions inner query is optional.

For these migration guide the following points will be used:

SELECT pid, edge_id, fraction, side FROM pointsOfInterest
WHERE pid IN (3, 4, 6);
pid | edge_id | fraction | side
-----+---------+----------+------
3 |      12 |      0.6 | l
4 |       6 |      0.3 | r
6 |       4 |      0.7 | b
(3 rows)



And will travel thru the follwoing Via points $$4\rightarrow3\rightarrow6$$

Migrate by using:

### Migrating pgr_trspViaEdges using pgr_withPointsVia¶

The following query does not have restrictions.

SELECT * FROM pgr_trspViaEdges(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost FROM edge_table$$,
ARRAY[6, 12, 4], ARRAY[0.3, 0.6, 0.7],
true, true);
WARNING:  pgr_trspViaEdges(text, integer[], float[], boolean, boolean, text) is been deprecated
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 |   1 |  -1 |   6 |  0.7
2 |   1 |   8 |   7 |    1
3 |   1 |   5 |  10 |    1
4 |   1 |  10 |  12 |  0.6
5 |   1 |  -2 |  -1 |    0
6 |   2 |  -2 |  12 |  0.4
7 |   2 |  11 |  13 |    1
8 |   2 |  12 |  15 |    1
9 |   2 |   9 |   9 |    1
10 |   2 |   6 |   8 |    1
11 |   2 |   5 |   4 |  0.7
12 |   2 |  -3 |  -2 |    0
(12 rows)


• A message about deprecation is shown

• Deprecated functions will be removed on the next mayor version 4.0.0

Use pgr_withPointsVia - Proposed instead.

SELECT * FROM pgr_withPointsVia(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (3, 4, 6)$$,
ARRAY[-4, -3, -6],
details => false);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 |       1 |        1 |        -4 |      -3 |   -4 |    6 |  0.7 |        0 |              0
2 |       1 |        2 |        -4 |      -3 |    8 |    7 |    1 |      0.7 |            0.7
3 |       1 |        3 |        -4 |      -3 |    5 |   10 |    1 |      1.7 |            1.7
4 |       1 |        4 |        -4 |      -3 |   10 |   12 |  0.6 |      2.7 |            2.7
5 |       1 |        5 |        -4 |      -3 |   -3 |   -1 |    0 |      3.3 |            3.3
6 |       2 |        1 |        -3 |      -6 |   -3 |   12 |  0.4 |        0 |            3.3
7 |       2 |        2 |        -3 |      -6 |   11 |   13 |    1 |      0.4 |            3.7
8 |       2 |        3 |        -3 |      -6 |   12 |   15 |    1 |      1.4 |            4.7
9 |       2 |        4 |        -3 |      -6 |    9 |    9 |    1 |      2.4 |            5.7
10 |       2 |        5 |        -3 |      -6 |    6 |    8 |    1 |      3.4 |            6.7
11 |       2 |        6 |        -3 |      -6 |    5 |    4 |  0.3 |      4.4 |            7.7
12 |       2 |        7 |        -3 |      -6 |   -6 |   -2 |    0 |      4.7 |              8
(12 rows)


• The types casting has been removed.

• Do not show details, as the deprecated function does not show details.

• Autodetects if reverse_cost column is in the edges SQL.

• Accepts ANY-INTEGER on integral types

• Accepts ANY-NUMERICAL on floating point types

• directed flag has a default value of true.

• Use the same value that on the original query.

• In this example it is true which is the default value.

• The flag has been omitted and the default is been used.

• On the points query do not include the side column.

When the need of using strictly the same (meaningless) names and types, and node values of the function been migrated then:

SELECT row_number() over(ORDER BY seq) AS seq,
path_id::INTEGER AS id1, node::INTEGER AS id2,
CASE WHEN edge >= 0 THEN edge::INTEGER ELSE -1 END AS id3, cost::FLOAT
FROM pgr_withPointsVia(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table$$,
$$SELECT * FROM (VALUES (1, 6, 0.3),(2, 12, 0.6),(3, 4, 0.7)) AS t(pid, edge_id, fraction)$$,
ARRAY[-1, -2, -3],
details=> false);
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 |   1 |  -1 |   6 |  0.7
2 |   1 |   8 |   7 |    1
3 |   1 |   5 |  10 |    1
4 |   1 |  10 |  12 |  0.6
5 |   1 |  -2 |  -1 |    0
6 |   2 |  -2 |  12 |  0.4
7 |   2 |  11 |  13 |    1
8 |   2 |  12 |  15 |    1
9 |   2 |   9 |   9 |    1
10 |   2 |   6 |   8 |    1
11 |   2 |   5 |   4 |  0.3
12 |   2 |  -3 |  -1 |    0
(12 rows)


• id1 is the path identifier

• id2 is the node

• id3 is the edge

### Migrating pgr_trspViaEdges using pgr_trspVia_withPoints¶

The following query has restrictions.

SELECT * FROM pgr_trspViaEdges(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost FROM edge_table$$,
ARRAY[6, 12, 4], ARRAY[0.3, 0.6, 0.7],
true, true,
$$SELECT to_cost, target_id::INTEGER, via_path FROM old_restrictions$$);
WARNING:  pgr_trspViaEdges(text, integer[], float[], boolean, boolean, text) is been deprecated
WARNING:  pgr_trsp(text,integer,float,integer,float,boolean,boolean) is been deprecated
WARNING:  pgr_trsp(text,integer,float,integer,float,boolean,boolean) is been deprecated
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 |   1 |  -1 |   6 |  0.7
2 |   1 |   8 |   7 |    1
3 |   1 |   5 |   8 |    1
4 |   1 |   6 |   9 |    1
5 |   1 |   9 |  16 |    1
6 |   1 |   4 |   3 |    1
7 |   1 |   3 |   2 |    1
8 |   1 |   2 |   4 |    1
9 |   1 |   5 |  10 |    1
10 |   1 |  10 |  12 |    1
11 |   2 |  11 |  13 |    1
12 |   2 |  12 |  15 |    1
13 |   2 |   9 |   9 |    1
14 |   2 |   6 |   8 |    1
15 |   2 |   5 |   4 |  0.3
(15 rows)


• A message about deprecation is shown

• Deprecated functions will be removed on the next mayor version 4.0.0

• The restrictions are the last parameter of the function

• Using the old structure of restrictions

Use pgr_trspVia_withPoints - Proposed instead.

SELECT * FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT pid, edge_id, fraction FROM pointsOfInterest WHERE pid IN (3, 4, 6)$$,
ARRAY[-4, -3, -6],
details => false);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 |       1 |        1 |        -4 |      -3 |   -4 |    6 |  0.7 |        0 |              0
2 |       1 |        2 |        -4 |      -3 |    8 |    7 |    1 |      0.7 |            0.7
3 |       1 |        3 |        -4 |      -3 |    5 |    4 |  0.6 |      1.7 |            1.7
4 |       1 |        4 |        -4 |      -3 |    5 |   10 |    1 |      2.3 |            2.3
5 |       1 |        5 |        -4 |      -3 |   10 |   12 |  0.6 |      3.3 |            3.3
6 |       1 |        6 |        -4 |      -3 |   -3 |   -1 |    0 |      3.9 |            3.9
7 |       2 |        1 |        -3 |      -6 |   -3 |   12 |  0.4 |        0 |            3.9
8 |       2 |        2 |        -3 |      -6 |   11 |   13 |    1 |      0.4 |            4.3
9 |       2 |        3 |        -3 |      -6 |   12 |   15 |    1 |      1.4 |            5.3
10 |       2 |        4 |        -3 |      -6 |    9 |    9 |    1 |      2.4 |            6.3
11 |       2 |        5 |        -3 |      -6 |    6 |    8 |    1 |      3.4 |            7.3
12 |       2 |        6 |        -3 |      -6 |    5 |    4 |  0.3 |      4.4 |            8.3
13 |       2 |        7 |        -3 |      -6 |   -6 |   -2 |    0 |      4.7 |            8.6
(13 rows)


• The new structure of restrictions is been used.

• It is the second parameter.

• The types casting has been removed.

• Do not show details, as the deprecated function does not show details.

• Autodetects if reverse_cost column is in the edges SQL.

• Accepts ANY-INTEGER on integral types

• Accepts ANY-NUMERICAL on floating point types

• directed flag has a default value of true.

• Use the same value that on the original query.

• In this example it is true which is the default value.

• The flag has been omitted and the default is been used.

• On the points query do not include the side column.

When the need of using strictly the same (meaningless) names and types, and node values of the function been migrated then:

SELECT row_number() over(ORDER BY seq) AS seq,
path_id::INTEGER AS id1, node::INTEGER AS id2,
CASE WHEN edge >= 0 THEN edge::INTEGER ELSE -1 END AS id3, cost::FLOAT
FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edge_table$$,
$$SELECT * FROM new_restrictions$$,
$$SELECT * FROM (VALUES (1, 6, 0.3),(2, 12, 0.6),(3, 4, 0.7)) AS t(pid, edge_id, fraction)$$,
ARRAY[-1, -2, -3],
details => false);
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 |   1 |  -1 |   6 |  0.7
2 |   1 |   8 |   7 |    1
3 |   1 |   5 |   4 |  0.6
4 |   1 |   5 |  10 |    1
5 |   1 |  10 |  12 |  0.6
6 |   1 |  -2 |  -1 |    0
7 |   2 |  -2 |  12 |  0.4
8 |   2 |  11 |  13 |    1
9 |   2 |  12 |  15 |    1
10 |   2 |   9 |   9 |    1
11 |   2 |   6 |   8 |    1
12 |   2 |   5 |   4 |  0.3
13 |   2 |  -3 |  -1 |    0
(13 rows)


• id1 is the path identifier

• id2 is the node

• id3 is the edge

Índices y tablas