Guía de migración

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

Resultados pueden ser diferentes dado los cambios.

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 .

Advertencia

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

Migración de pgr_maxCardinalityMatch

Firmas que serán migradas:

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

  • Los resultados son ordenados

  • 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 tipo``ANY-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 con``rid = 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)

Firmas que serán migradas:

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

Use pgr_dijkstra instead.

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.

  • pgr_dijkstra:

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

  • pgr_trsp - Proposed:

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

Firmas que serán migradas:

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

Use pgr_withPoints - Propuesto instead.

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.

  • pgr_withPoints - Propuesto:

    • 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

Use pgr_trsp_withPoints - Proposed instead.

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.

  • pgr_trsp_withPoints - Proposed:

    • 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

Firmas que serán migradas:

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

Use pgr_dijkstraVia - Proposed instead.

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.

  • pgr_dijkstraVia - Proposed:

    • 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

Use pgr_trspVia - Proposed instead.

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.

  • pgr_trspVia - Proposed:

    • 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

Firmas que serán migradas:

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

Use pgr_withPointsVia - Proposed instead.

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.

  • pgr_withPointsVia - Proposed:

    • 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

Use pgr_trspVia_withPoints - Proposed instead.

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.

  • pgr_trspVia_withPoints - Proposed:

    • 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

Ver también

Índices y tablas