Guía de migración¶

Varias funciones están teniendo cambios en las firmas, y/o han sido remplazadas por nuevas funciones.

Migrando funciones:

pgr_maxCardinalityMatch funciona solamente para grafos no dirigidos, entonces la bandera directed ha sido removida.

pgr_trsp - Proposed firmas han cambiando y varios problemas han sido arreglados en las nuevas firmas. Esta sección enseñara como migrar las viejas firmas a la nueva función de reemplazo. Esto también afecta las restricciones .

Todas las funciones obsoletas serán removidas en las siguiente versión mayor 4.0.0

Migración de pgr_maxCardinalityMatch¶

pgr_maxCardinalityMatch(Edges SQL, [directed])
RETURNS SETOF (seq, edge, source, target)


Migración es necesaria, porque:

• Usa cost y reverse_cost en la consulta interna

• Funciona para grafos no dirigidos.

• Nueva firma

• pgr_maxCardinalityMatch(text) regresa solamente la columna edge.

• La bandera opcional directed es removida.

Antes de migración:

SELECT * FROM pgr_maxCardinalityMatch(
$$SELECT id, source, target, cost AS going, reverse_cost AS coming FROM edges$$,
directed => true
);
WARNING:  pgr_maxCardinalityMatch(text,boolean) deprecated on v3.4.0
seq | edge | source | target
-----+------+--------+--------
1 |    1 |      5 |      6
2 |    5 |     10 |     11
3 |    6 |      1 |      3
4 |   13 |     12 |     17
5 |   14 |      8 |      9
6 |   16 |     15 |     16
7 |   17 |      2 |      4
8 |   18 |     13 |     14
(8 rows)


• Las columnas usadas son going y coming para representar la existencia de la arista.

• La flag directed es usada para indicar si fue para un grafo dirigido or no dirigido.

• La bandera directed es ignorada.

• Independiente de su valor, da el resultado considerando el grafo como no dirigido.

Migración:

• Usa las columnas cost y reverse_cost para representar la existenica de una arista.

• No use la bandera directed.

• En la consulta solo devuelve la columna edge.

SELECT * FROM pgr_maxCardinalityMatch(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$
);
edge
------
1
5
6
13
14
16
17
18
(8 rows)



Migración de restricciones¶

La estructura de las restricciones ha cambiado:

Estructura de restricciones vieja¶

Sobre las firmas obsoletas:

• La columna rid es ignorada

• via_path

• Debe de estar en orden reverso.

• Es de tipo TEXT.

• Cuando hay más de una arista debe de ser separada con ,.

• target_id

• Es la última arista del camino prohibido.

• Es de tipo de INTEGER.

• to_cost

• Es de tipo FLOAT.

Creación de la vieja table de restricciones

CREATE TABLE old_restrictions (
rid BIGINT NOT NULL,
to_cost FLOAT,
target_id BIGINT,
via_path TEXT
);
CREATE TABLE


Las viejas restricciones se llenan

INSERT INTO old_restrictions (rid, to_cost, target_id, via_path) VALUES
(1, 100,  7,  '4'),
(1, 100, 11,  '8'),
(1, 100, 10,  '7'),
(2,   4,  9,  '5, 3'),
(3, 100,  9, '16');
INSERT 0 5


Contenido de viejas restricciones¶

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)



La restricción con rid = 2 representa $$3 \rightarrow 5 \rightarrow9$$

• $$3\rightarrow5$$

• esta en la columna via_path en orden reverso

• es de tipo TEXT

• $$9$$

• está en la columna target_id

• es de tipo INTEGER

Estructura de nuevas restricciones¶

• La columna id es ignorada

• Columna path

• Es de tipo ARRAY[ANY-INTEGER].

• Contiene todas las aristas involucradas con la restricción

• El conjunto tiene las aristas ordenadas de la restricción.

• Columna cost

• Es de tipoANY-NUMERICAL

La creación de la tabla de restricciones

CREATE TABLE restrictions (
id SERIAL PRIMARY KEY,
path BIGINT[],
cost FLOAT
);
CREATE TABLE


Agregando las restricciones

INSERT INTO restrictions (path, cost) VALUES
(ARRAY[4, 7], 100),
(ARRAY[8, 11], 100),
(ARRAY[7, 10], 100),
(ARRAY[3, 5, 9], 4),
(ARRAY[9, 16], 100);
INSERT 0 5


Datos de restricciones¶

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)



La restricción conrid = 2 representa el camino $$3 \rightarrow5 \rightarrow9$$.

• El camino es claro por inspección.

Migración¶

Para transformar la antigua tabla de restricciones a la nueva estructura de restricciones,

• Crea una nueva tabla con la nueva estructura de restricciones.

• En esta guía de migración new_restrictions está siendo usada.

• Para esta migración pgRouting proporciona una función auxiliar para la inversión de un conjunto _pgr_array_reverse necesaria para la migración.

• _pgr_array_reverse:

• Was created temporally 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)¶

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 careful to match the existence 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 edges WHERE id != 16$$,
15, 16,
true, true);
WARNING:  pgr_trsp(text,integer,integer,boolean,boolean) deprecated on v3.4.0
seq | id1 | id2 | cost
-----+-----+-----+------
0 |  15 |   3 |    1
1 |  10 |   5 |    1
2 |  11 |   9 |    1
3 |  16 |  -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 edges WHERE id != 16$$,
15, 16);
seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
1 |        1 |   15 |    3 |    1 |        0
2 |        2 |   10 |    5 |    1 |        1
3 |        3 |   11 |    9 |    1 |        2
4 |        4 |   16 |   -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 edges WHERE id != 16$$,
15, 16);
seq | id1 | id2 | cost
-----+-----+-----+------
1 |  15 |   3 |    1
2 |  10 |   5 |    1
3 |  11 |   9 |    1
4 |  16 |  -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 edges WHERE id != 16$$,
15, 16,
true, true,
$$SELECT to_cost, target_id::INTEGER, via_path FROM old_restrictions$$);
WARNING:  pgr_trsp(text,integer,integer,boolean,boolean) deprecated on v3.4.0
seq | id1 | id2 | cost
-----+-----+-----+------
0 |  15 |   3 |    1
1 |  10 |   5 |    1
2 |  11 |  11 |    1
3 |  12 |  13 |    1
4 |  17 |  15 |    1
5 |  16 |  -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 edges WHERE id != 16$$,
$$SELECT * FROM new_restrictions$$,
15, 16);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 |        1 |        15 |      16 |   15 |    3 |    1 |        0
2 |        2 |        15 |      16 |   10 |    5 |    1 |        1
3 |        3 |        15 |      16 |   11 |   11 |    1 |        2
4 |        4 |        15 |      16 |   12 |   13 |    1 |        3
5 |        5 |        15 |      16 |   17 |   15 |    1 |        4
6 |        6 |        15 |      16 |   16 |   -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 edges WHERE id != 16$$,
$$SELECT * FROM new_restrictions$$,
15, 16);
seq | id1 | id2 | cost
-----+-----+-----+------
1 |  15 |   3 |    1
2 |  10 |   5 |    1
3 |  11 |  11 |    1
4 |  12 |  13 |    1
5 |  17 |  15 |    1
6 |  16 |  -1 |    0
(6 rows)


• id1 is the node

• id2 is the edge

Migration of pgr_trsp (Edges)¶

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 careful to match the existence 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 edges$$,
6, 0.3, 12, 0.6,
true, true);
WARNING:  pgr_trsp(text,integer,float,integer,float,boolean,boolean) deprecated on v3.4.0
seq | id1 | id2 | cost
-----+-----+-----+------
0 |  -1 |   6 |  0.7
1 |   3 |   7 |    1
2 |   7 |  10 |    1
3 |   8 |  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

SELECT * FROM pgr_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$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 |    3 |    7 |    1 |      0.7
3 |        3 |    7 |   10 |    1 |      1.7
4 |        4 |    8 |   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 edges$$,
$$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 |   3 |   7 |    1
3 |   7 |  10 |    1
4 |   8 |  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 edges$$,
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) deprecated on v3.4.0
seq | id1 | id2 | cost
-----+-----+-----+------
0 |  -1 |   6 |  0.7
1 |   3 |   7 |    1
2 |   7 |   8 |    1
3 |  11 |   9 |    1
4 |  16 |  16 |    1
5 |  15 |   3 |    1
6 |  10 |   2 |    1
7 |   6 |   4 |    1
8 |   7 |  10 |    1
9 |   8 |  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

SELECT * FROM pgr_trsp_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$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 |    3 |    7 |    1 |      0.7
3 |        3 |        -4 |      -3 |    7 |    8 |    1 |      1.7
4 |        4 |        -4 |      -3 |   11 |    9 |    1 |      2.7
5 |        5 |        -4 |      -3 |   16 |   16 |    1 |      3.7
6 |        6 |        -4 |      -3 |   15 |    3 |    1 |      4.7
7 |        7 |        -4 |      -3 |   10 |    2 |    1 |      5.7
8 |        8 |        -4 |      -3 |    6 |    4 |    1 |      6.7
9 |        9 |        -4 |      -3 |    7 |   10 |    1 |      7.7
10 |       10 |        -4 |      -3 |    8 |   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 edges$$,
$$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 |   3 |   7 |    1
3 |   7 |   8 |    1
4 |  11 |   9 |    1
5 |  16 |  16 |    1
6 |  15 |   3 |    1
7 |  10 |   2 |    1
8 |   6 |   4 |    1
9 |   7 |  10 |    1
10 |   8 |  12 |  0.6
(10 rows)


• id1 is the node

• id2 is the edge

Migration of pgr_trspViaVertices¶

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 careful to match the existence 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 edges$$,
ARRAY[6, 3, 6],
true, true);
WARNING:  pgr_trspViaVertices(text,anyarray,boolean,boolean,text) is been deprecated
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 |   1 |   6 |   4 |    1
2 |   1 |   7 |   7 |    1
3 |   2 |   3 |   7 |    1
4 |   2 |   7 |   4 |    1
5 |   2 |   6 |  -1 |    0
(5 rows)


• A message about deprecation is shown

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

SELECT * FROM pgr_dijkstraVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
ARRAY[6, 3, 6]);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 |       1 |        1 |         6 |       3 |    6 |    4 |    1 |        0 |              0
2 |       1 |        2 |         6 |       3 |    7 |    7 |    1 |        1 |              1
3 |       1 |        3 |         6 |       3 |    3 |   -1 |    0 |        2 |              2
4 |       2 |        1 |         3 |       6 |    3 |    7 |    1 |        0 |              2
5 |       2 |        2 |         3 |       6 |    7 |    4 |    1 |        1 |              3
6 |       2 |        3 |         3 |       6 |    6 |   -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 edges$$,
ARRAY[6, 3, 6])
WHERE edge != -1;
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 |   1 |   6 |   4 |    1
2 |   1 |   7 |   7 |    1
3 |   2 |   3 |   7 |    1
4 |   2 |   7 |   4 |    1
5 |   2 |   6 |  -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 edges$$,
ARRAY[6, 3, 6],
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 |   6 |   4 |    1
2 |   1 |   7 |   8 |    1
3 |   1 |  11 |   9 |    1
4 |   1 |  16 |  16 |    1
5 |   1 |  15 |   3 |    1
6 |   1 |  10 |   5 |    1
7 |   1 |  11 |   8 |    1
8 |   1 |   7 |   7 |    1
9 |   2 |   3 |   7 |    1
10 |   2 |   7 |   4 |    1
11 |   2 |   6 |  -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

SELECT * FROM pgr_trspVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$SELECT * FROM new_restrictions$$,
ARRAY[6, 3, 6]);
seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost | route_agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------+----------------
1 |       1 |        1 |         6 |       3 |    6 |    4 |    1 |        0 |              0
2 |       1 |        2 |         6 |       3 |    7 |    8 |    1 |        1 |              1
3 |       1 |        3 |         6 |       3 |   11 |    9 |    1 |        2 |              2
4 |       1 |        4 |         6 |       3 |   16 |   16 |    1 |        3 |              3
5 |       1 |        5 |         6 |       3 |   15 |    3 |    1 |        4 |              4
6 |       1 |        6 |         6 |       3 |   10 |    5 |    1 |        5 |              5
7 |       1 |        7 |         6 |       3 |   11 |    8 |    1 |        6 |              6
8 |       1 |        8 |         6 |       3 |    7 |    7 |    1 |        7 |              7
9 |       1 |        9 |         6 |       3 |    3 |   -1 |    0 |        8 |              8
10 |       2 |        1 |         3 |       6 |    3 |    7 |    1 |        0 |              8
11 |       2 |        2 |         3 |       6 |    7 |    4 |    1 |        1 |              9
12 |       2 |        3 |         3 |       6 |    6 |   -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 edges$$,
$$SELECT * FROM new_restrictions$$,
ARRAY[6, 3, 6])
WHERE edge != -1;
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 |   1 |   6 |   4 |    1
2 |   1 |   7 |   8 |    1
3 |   1 |  11 |   9 |    1
4 |   1 |  16 |  16 |    1
5 |   1 |  15 |   3 |    1
6 |   1 |  10 |   5 |    1
7 |   1 |  11 |   8 |    1
8 |   1 |   7 |   7 |    1
9 |   2 |   3 |   7 |    1
10 |   2 |   7 |   4 |    1
11 |   2 |   6 |  -1 |    0
(11 rows)


• id1 is the path identifier

• id2 is the node

• id3 is the edge

Migration of pgr_trspViaEdges¶

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 careful to match the existence 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 following 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 edges$$,
ARRAY[6, 12, 4], ARRAY[0.3, 0.6, 0.7],
true, true);
WARNING:  pgr_trspViaEdges(text,integer[],float[],boolean,boolean,text) deprecated on v3.4.0
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 |   1 |  -1 |   6 |  0.7
2 |   1 |   3 |   7 |    1
3 |   1 |   7 |  10 |    1
4 |   1 |   8 |  12 |  0.6
5 |   1 |  -2 |  -1 |    0
6 |   2 |  -2 |  12 |  0.4
7 |   2 |  12 |  13 |    1
8 |   2 |  17 |  15 |    1
9 |   2 |  16 |   9 |    1
10 |   2 |  11 |   8 |    1
11 |   2 |   7 |   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

SELECT * FROM pgr_withPointsVia(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$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 |    3 |    7 |    1 |      0.7 |            0.7
3 |       1 |        3 |        -4 |      -3 |    7 |   10 |    1 |      1.7 |            1.7
4 |       1 |        4 |        -4 |      -3 |    8 |   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 |   12 |   13 |    1 |      0.4 |            3.7
8 |       2 |        3 |        -3 |      -6 |   17 |   15 |    1 |      1.4 |            4.7
9 |       2 |        4 |        -3 |      -6 |   16 |    9 |    1 |      2.4 |            5.7
10 |       2 |        5 |        -3 |      -6 |   11 |    8 |    1 |      3.4 |            6.7
11 |       2 |        6 |        -3 |      -6 |    7 |    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 edges$$,
$$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 |   3 |   7 |    1
3 |   1 |   7 |  10 |    1
4 |   1 |   8 |  12 |  0.6
5 |   1 |  -2 |  -1 |    0
6 |   2 |  -2 |  12 |  0.4
7 |   2 |  12 |  13 |    1
8 |   2 |  17 |  15 |    1
9 |   2 |  16 |   9 |    1
10 |   2 |  11 |   8 |    1
11 |   2 |   7 |   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 edges$$,
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) deprecated on v3.4.0
WARNING:  pgr_trsp(text,integer,float,integer,float,boolean,boolean) deprecated on v3.4.0
WARNING:  pgr_trsp(text,integer,float,integer,float,boolean,boolean) deprecated on v3.4.0
seq | id1 | id2 | id3 | cost
-----+-----+-----+-----+------
1 |   1 |  -1 |   6 |  0.7
2 |   1 |   3 |   7 |    1
3 |   1 |   7 |   8 |    1
4 |   1 |  11 |   9 |    1
5 |   1 |  16 |  16 |    1
6 |   1 |  15 |   3 |    1
7 |   1 |  10 |   2 |    1
8 |   1 |   6 |   4 |    1
9 |   1 |   7 |  10 |    1
10 |   1 |   8 |  12 |    1
11 |   2 |  12 |  13 |    1
12 |   2 |  17 |  15 |    1
13 |   2 |  16 |   9 |    1
14 |   2 |  11 |   8 |    1
15 |   2 |   7 |   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

SELECT * FROM pgr_trspVia_withPoints(
$$SELECT id, source, target, cost, reverse_cost FROM edges$$,
$$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 |    3 |    7 |    1 |      0.7 |            0.7
3 |       1 |        3 |        -4 |      -3 |    7 |    4 |  0.6 |      1.7 |            1.7
4 |       1 |        4 |        -4 |      -3 |    7 |   10 |    1 |      2.3 |            2.3
5 |       1 |        5 |        -4 |      -3 |    8 |   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 |   12 |   13 |    1 |      0.4 |            4.3
9 |       2 |        3 |        -3 |      -6 |   17 |   15 |    1 |      1.4 |            5.3
10 |       2 |        4 |        -3 |      -6 |   16 |    9 |    1 |      2.4 |            6.3
11 |       2 |        5 |        -3 |      -6 |   11 |    8 |    1 |      3.4 |            7.3
12 |       2 |        6 |        -3 |      -6 |    7 |    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 edges$$,
$$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 |   3 |   7 |    1
3 |   1 |   7 |   4 |  0.6
4 |   1 |   7 |  10 |    1
5 |   1 |   8 |  12 |  0.6
6 |   1 |  -2 |  -1 |    0
7 |   2 |  -2 |  12 |  0.4
8 |   2 |  12 |  13 |    1
9 |   2 |  17 |  15 |    1
10 |   2 |  16 |   9 |    1
11 |   2 |  11 |   8 |    1
12 |   2 |   7 |   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