Migration guide

Several functions are having changes on the signatures, and/or have been replaced by new functions.

Results can be different because of the changes.

Warning

All deprecated functions will be removed on next major version 4.0.0

Migration of pgr_alphaShape

Starting from v3.8.0

Before Deprecation: The following was calculated:

  • An alphaShape was calculated

After Deprecation:

PostGIS has two ways of generating alphaShape.

If you have SFCGAL, which you can install using

CREATE EXTENSION postgis_sfcgal
  • Since PostGIS 3.5+ use CG_AlphaShape

  • For PostGIS 3.5+ use the old name ST_AlphaShape

Other PostGIS options are * ST_ConvexHull * ST_ConcaveHull

Migration of pgr_nodeNetwork

Starting from v3.8.0

Before Deprecation: A table with <edges>_nodded was created. with split edges.

Migration

Use pgr_separateTouching and/or use pgr_separateCrossing

Migration of pgr_createTopology

Starting from v3.8.0

Before Deprecation: The following was calculated:

  • A table with <edges>_vertices_pgr was created.

After Deprecation: The user is responsible to create the complete topology.

Build a routing topology

The basic information to use the majority of the pgRouting functions id, source, target, cost, [reverse_cost] is what in pgRouting is called the routing topology.

reverse_cost is optional but strongly recommended to have in order to reduce the size of the database due to the size of the geometry columns. Having said that, in this documentation reverse_cost is used in this documentation.

When the data comes with geometries and there is no routing topology, then this step is needed.

All the start and end vertices of the geometries need an identifier that is to be stored in a source and target columns of the table of the data. Likewise, cost and reverse_cost need to have the value of traversing the edge in both directions.

If the columns do not exist they need to be added to the table in question. (see ALTER TABLE)

The function pgr_extractVertices is used to create a vertices table based on the edge identifier and the geometry of the edge of the graph.

SELECT * INTO vertices
FROM pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');
SELECT 18

Finally using the data stored on the vertices tables the source and target are filled up.

/* -- set the source information */
UPDATE edges AS e
SET source = v.id, x1 = x, y1 = y
FROM vertices AS v
WHERE ST_StartPoint(e.geom) = v.geom;
UPDATE 24
/* -- set the target information */
UPDATE edges AS e
SET target = v.id, x2 = x, y2 = y
FROM vertices AS v
WHERE ST_EndPoint(e.geom) = v.geom;
UPDATE 24

Migration of pgr_createVerticesTable

Starting from v3.8.0

Before Deprecation: The following was calculated:

  • A table with <edges>_vertices_pgr was created.

After Deprecation: The user is responsible to create the vertices table, indexes, etc. They may use pgr_extractVertices for that purpose.

SELECT * INTO vertices
FROM pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');
SELECT 17

Migration of pgr_analyzeOneWay

Starting from v3.8.0

Before Deprecation: The following was calculated:

  • Number of potential problems in directionality

WHERE

Directionality problems were calculated based on codes.

Dead ends.

A routing problem can arise when from a vertex there is only a way on or a way out but not both:

Either saving or using directly pgr_extractVertices get the dead ends information and determine if the adjacent edge is one way or not.

In this example pgr_extractVertices has already been applied.

WITH
deadends AS (
  SELECT (in_edges || out_edges)[1] as id
  FROM vertices where array_length(in_edges || out_edges, 1) = 1)
SELECT * FROM edges JOIN deadends  USING (id)
WHERE cost < 0 OR reverse_cost < 0;
 id | source | target | cost | reverse_cost | capacity | reverse_capacity | x1 | y1 | x2 | y2 | geom
----+--------+--------+------+--------------+----------+------------------+----+----+----+----+------
(0 rows)

Bridges.

Another routing problem can arise when there is an edge of an undirected graph whose deletion increases its number of connected components, and the bridge is only one way.

To determine if the bridges are or not one way.

SELECT id, cost < 0 OR reverse_cost<0 AS is_OneWway
FROM pgr_bridges('SELECT id, source, target, cost, reverse_cost FROM edges')
JOIN edges ON (edge = id);
 id | is_onewway
----+------------
  1 | f
  6 | f
  7 | f
 14 | f
 17 | f
 18 | f
(6 rows)

Migration of pgr_analyzeGraph

Starting from v3.8.0

Before Deprecation: The following was calculated:

  • Number of isolated segments.

  • Number of dead ends.

  • Number of potential gaps found near dead ends.

  • Number of intersections. (between 2 edges)

WHERE

Graph component:

A connected subgraph that is not part of any larger connected subgraph.

Isolated segment:

A graph component with only one segment.

Dead ends:

A vertex that participates in only one edge.

gaps:

Space between two geometries.

Intersection:

Is a topological relationship between two geometries.

Migration.

Components.

Instead of counting only isolated segments, determine all the components of the graph.

Depending of the final application requirements use:

For example:

SELECT *
FROM pgr_connectedComponents(
  'SELECT id, source, target, cost, reverse_cost FROM edges'
);
 seq | component | node
-----+-----------+------
   1 |         1 |    1
   2 |         1 |    3
   3 |         1 |    5
   4 |         1 |    6
   5 |         1 |    7
   6 |         1 |    8
   7 |         1 |    9
   8 |         1 |   10
   9 |         1 |   11
  10 |         1 |   12
  11 |         1 |   15
  12 |         1 |   16
  13 |         1 |   17
  14 |         2 |    2
  15 |         2 |    4
  16 |        13 |   13
  17 |        13 |   14
(17 rows)

Dead ends.

Instead of counting the dead ends, determine all the dead ends of the graph using pgr_degree.

For example:

SELECT *
FROM pgr_degree($$SELECT id, source, target FROM edges$$)
WHERE degree = 1;
 node | degree
------+--------
    9 |      1
    5 |      1
    4 |      1
   14 |      1
   13 |      1
    2 |      1
    1 |      1
(7 rows)

Potential gaps near dead ends.

Instead of counting potential gaps between geometries, determine the geometric gaps in the graph using pgr_findCloseEdges.

For example:

WITH
deadends AS (
  SELECT id,geom, (in_edges || out_edges)[1] as inhere
  FROM vertices where array_length(in_edges || out_edges, 1) = 1),
results AS (
  SELECT (pgr_findCloseEdges('SELECT id, geom FROM edges WHERE id != ' || inhere , geom, 0.001)).*
  FROM deadends)
SELECT d.id, edge_id, distance, st_AsText(geom) AS point, st_asText(edge) edge
FROM results JOIN deadends d USING (geom);
 id | edge_id |     distance      |           point           |                 edge
----+---------+-------------------+---------------------------+--------------------------------------
  4 |      14 | 1.00008890058e-12 | POINT(1.999999999999 3.5) | LINESTRING(1.999999999999 3.5,2 3.5)
(1 row)

Topological relationships.

Instead of counting intersections, determine topological relationships between geometries.

Several PostGIS functions can be used: ST_Intersects, ST_Crosses, ST_Overlaps, etc.

For example:

SELECT e1.id AS id1, e2.id AS id2
FROM edges e1, edges e2 WHERE e1 < e2 AND st_crosses(e1.geom, e2.geom);
 id1 | id2
-----+-----
  13 |  18
(1 row)

Migration of pgr_aStar

Starting from v3.6.0

Signatures to be migrated:

  • pgr_aStar (One to One)

  • pgr_aStar (One to Many)

  • pgr_aStar (Many to One)

Before Migration

  • Output columns were (seq, path_seq, [start_vid], [end_vid], node, edge, cost, agg_cost)

    • Depending on the overload used, the columns start_vid and end_vid might be missing:

      • pgr_aStar (One to One) does not have start_vid and end_vid.

      • pgr_aStar (One to Many) does not have start_vid.

      • pgr_aStar (Many to One) does not have end_vid.

Migration:

  • Be aware of the existence of the additional columns.

  • In pgr_aStar (One to One)

    • start_vid contains the start vid parameter value.

    • end_vid contains the end vid parameter value.

SELECT * FROM pgr_aStar(
  $$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
  6, 10);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         6 |      10 |    6 |    4 |    1 |        0
   2 |        2 |         6 |      10 |    7 |    8 |    1 |        1
   3 |        3 |         6 |      10 |   11 |    9 |    1 |        2
   4 |        4 |         6 |      10 |   16 |   16 |    1 |        3
   5 |        5 |         6 |      10 |   15 |    3 |    1 |        4
   6 |        6 |         6 |      10 |   10 |   -1 |    0 |        5
(6 rows)

  • In pgr_aStar (One to Many)

    • start_vid contains the start vid parameter value.

SELECT * FROM pgr_aStar(
  $$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
  6, ARRAY[3, 10]);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         6 |       3 |    6 |    4 |    1 |        0
   2 |        2 |         6 |       3 |    7 |    7 |    1 |        1
   3 |        3 |         6 |       3 |    3 |   -1 |    0 |        2
   4 |        1 |         6 |      10 |    6 |    4 |    1 |        0
   5 |        2 |         6 |      10 |    7 |    8 |    1 |        1
   6 |        3 |         6 |      10 |   11 |    9 |    1 |        2
   7 |        4 |         6 |      10 |   16 |   16 |    1 |        3
   8 |        5 |         6 |      10 |   15 |    3 |    1 |        4
   9 |        6 |         6 |      10 |   10 |   -1 |    0 |        5
(9 rows)

  • In pgr_aStar (Many to One)

    • end_vid contains the end vid parameter value.

SELECT * FROM pgr_aStar(
  $$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
  ARRAY[3, 6], 10);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         3 |      10 |    3 |    7 |    1 |        0
   2 |        2 |         3 |      10 |    7 |    8 |    1 |        1
   3 |        3 |         3 |      10 |   11 |    9 |    1 |        2
   4 |        4 |         3 |      10 |   16 |   16 |    1 |        3
   5 |        5 |         3 |      10 |   15 |    3 |    1 |        4
   6 |        6 |         3 |      10 |   10 |   -1 |    0 |        5
   7 |        1 |         6 |      10 |    6 |    4 |    1 |        0
   8 |        2 |         6 |      10 |    7 |    8 |    1 |        1
   9 |        3 |         6 |      10 |   11 |    9 |    1 |        2
  10 |        4 |         6 |      10 |   16 |   16 |    1 |        3
  11 |        5 |         6 |      10 |   15 |    3 |    1 |        4
  12 |        6 |         6 |      10 |   10 |   -1 |    0 |        5
(12 rows)

  • If needed filter out the added columns, for example:

SELECT seq, path_seq, node, edge, cost, agg_cost FROM pgr_aStar(
  $$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
  6, 10);
 seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
   1 |        1 |    6 |    4 |    1 |        0
   2 |        2 |    7 |    8 |    1 |        1
   3 |        3 |   11 |    9 |    1 |        2
   4 |        4 |   16 |   16 |    1 |        3
   5 |        5 |   15 |    3 |    1 |        4
   6 |        6 |   10 |   -1 |    0 |        5
(6 rows)

  • If needed add the new columns, similar to the following example where pgr_dijkstra is used, and the function had to be modified to be able to return the new columns:

    • In v3.0 the function my_dijkstra uses pgr_dijkstra.

    • Starting from v3.5 the function my_dijkstra returns the new additional columns of pgr_dijkstra.

Migration of pgr_bdAstar

Starting from v3.6.0

Signatures to be migrated:

  • pgr_bdAstar (One to One)

  • pgr_bdAstar (One to Many)

  • pgr_bdAstar (Many to One)

Before Migration:

  • Output columns were (seq, path_seq, [start_vid], [end_vid], node, edge, cost, agg_cost)

    • Depending on the overload used, the columns start_vid and end_vid might be missing:

      • pgr_bdAstar (One to One) does not have start_vid and end_vid.

      • pgr_bdAstar (One to Many) does not have start_vid.

      • pgr_bdAstar (Many to One) does not have end_vid.

Migration:

  • Be aware of the existence of the additional columns.

  • In pgr_bdAstar (One to One)

    • start_vid contains the start vid parameter value.

    • end_vid contains the end vid parameter value.

SELECT * FROM pgr_bdAstar(
  $$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
  6, 10);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         6 |      10 |    6 |    4 |    1 |        0
   2 |        2 |         6 |      10 |    7 |    8 |    1 |        1
   3 |        3 |         6 |      10 |   11 |    9 |    1 |        2
   4 |        4 |         6 |      10 |   16 |   16 |    1 |        3
   5 |        5 |         6 |      10 |   15 |    3 |    1 |        4
   6 |        6 |         6 |      10 |   10 |   -1 |    0 |        5
(6 rows)

  • In pgr_bdAstar (One to Many)

    • start_vid contains the start vid parameter value.

SELECT * FROM pgr_bdAstar(
  $$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
  6, ARRAY[3, 10]);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         6 |       3 |    6 |    4 |    1 |        0
   2 |        2 |         6 |       3 |    7 |    7 |    1 |        1
   3 |        3 |         6 |       3 |    3 |   -1 |    0 |        2
   4 |        1 |         6 |      10 |    6 |    4 |    1 |        0
   5 |        2 |         6 |      10 |    7 |    8 |    1 |        1
   6 |        3 |         6 |      10 |   11 |    9 |    1 |        2
   7 |        4 |         6 |      10 |   16 |   16 |    1 |        3
   8 |        5 |         6 |      10 |   15 |    3 |    1 |        4
   9 |        6 |         6 |      10 |   10 |   -1 |    0 |        5
(9 rows)

  • In pgr_bdAstar (Many to One)

    • end_vid contains the end vid parameter value.

SELECT * FROM pgr_bdAstar(
  $$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
  ARRAY[3, 6], 10);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         3 |      10 |    3 |    7 |    1 |        0
   2 |        2 |         3 |      10 |    7 |    8 |    1 |        1
   3 |        3 |         3 |      10 |   11 |    9 |    1 |        2
   4 |        4 |         3 |      10 |   16 |   16 |    1 |        3
   5 |        5 |         3 |      10 |   15 |    3 |    1 |        4
   6 |        6 |         3 |      10 |   10 |   -1 |    0 |        5
   7 |        1 |         6 |      10 |    6 |    4 |    1 |        0
   8 |        2 |         6 |      10 |    7 |    8 |    1 |        1
   9 |        3 |         6 |      10 |   11 |    9 |    1 |        2
  10 |        4 |         6 |      10 |   16 |   16 |    1 |        3
  11 |        5 |         6 |      10 |   15 |    3 |    1 |        4
  12 |        6 |         6 |      10 |   10 |   -1 |    0 |        5
(12 rows)

  • If needed filter out the added columns, for example:

SELECT seq, path_seq, node, edge, cost, agg_cost FROM pgr_bdAstar(
  $$SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges$$,
  6, 10);
 seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
   1 |        1 |    6 |    4 |    1 |        0
   2 |        2 |    7 |    8 |    1 |        1
   3 |        3 |   11 |    9 |    1 |        2
   4 |        4 |   16 |   16 |    1 |        3
   5 |        5 |   15 |    3 |    1 |        4
   6 |        6 |   10 |   -1 |    0 |        5
(6 rows)

  • If needed add the new columns, similar to the following example where pgr_dijkstra is used, and the function had to be modified to be able to return the new columns:

    • In v3.0 the function my_dijkstra uses pgr_dijkstra.

    • Starting from v3.5 the function my_dijkstra returns the new additional columns of pgr_dijkstra.

Migration of pgr_dijkstra

Starting from v3.5.0

Signatures to be migrated:

  • pgr_dijkstra (One to One)

  • pgr_dijkstra (One to Many)

  • pgr_dijkstra (Many to One)

Before Migration:

  • Output columns were (seq, path_seq, [start_vid], [end_vid], node, edge, cost, agg_cost)

    • Depending on the overload used, the columns start_vid and end_vid might be missing:

      • pgr_dijkstra (One to One) does not have start_vid and end_vid.

      • pgr_dijkstra (One to Many) does not have start_vid.

      • pgr_dijkstra (Many to One) does not have end_vid.

Migration:

  • Be aware of the existence of the additional columns.

  • In pgr_dijkstra (One to One)

    • start_vid contains the start vid parameter value.

    • end_vid contains the end vid parameter value.

SELECT * FROM pgr_dijkstra(
  $$SELECT id, source, target, cost, reverse_cost FROM edges$$,
  6, 10);
 seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
   1 |        1 |         6 |      10 |    6 |    4 |    1 |        0
   2 |        2 |         6 |      10 |    7 |    8 |    1 |        1
   3 |        3 |         6 |      10 |   11 |    9 |    1 |        2
   4 |        4 |         6 |      10 |   16 |   16 |    1 |        3
   5 |        5 |         6 |      10 |   15 |    3 |    1 |        4
   6 |        6 |         6 |      10 |   10 |   -1 |    0 |        5
(6 rows)

  • In pgr_dijkstra (One to Many)

    • start_vid contains the start vid parameter value.

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

  • In pgr_dijkstra (Many to One)

    • end_vid contains the end vid parameter value.

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

  • If needed filter out the added columns, for example:

SELECT seq, path_seq, node, edge, cost, agg_cost FROM pgr_dijkstra(
  $$SELECT id, source, target, cost, reverse_cost FROM edges$$,
  6, 10);
 seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
   1 |        1 |    6 |    4 |    1 |        0
   2 |        2 |    7 |    8 |    1 |        1
   3 |        3 |   11 |    9 |    1 |        2
   4 |        4 |   16 |   16 |    1 |        3
   5 |        5 |   15 |    3 |    1 |        4
   6 |        6 |   10 |   -1 |    0 |        5
(6 rows)

  • If needed add the new columns, for example:

    • In v3.0 the function my_dijkstra uses pgr_dijkstra.

    • Starting from v3.5 the function my_dijkstra returns the new additional columns of pgr_dijkstra.

Migration of pgr_drivingDistance

Starting from v3.6.0 pgr_drivingDistance result columns are being standardized.

from:

(seq, [from_v,] node, edge, cost, agg_cost)

to:

(seq, depth, start_vid, pred, node, edge, cost, agg_cost)

Signatures to be migrated:

  • pgr_drivingDistance(Single vertex)

  • pgr_drivingDistance(Multiple vertices)

Before Migration:

Output columns were (seq, [from_v,] node, edge, cost, agg_cost)

  • pgr_drivingDistance(Single vertex)

    • Does not have start_vid and depth result columns.

  • pgr_drivingDistance(Multiple vertices)

    • Has from_v instead of start_vid result column.

    • does not have depth result column.

Migration:

  • Be aware of the existence and name change of the result columns.

pgr_drivingDistance(Single vertex)

Using this example.

  • start_vid contains the start vid parameter value.

  • depth contains the depth of the node.

  • pred contains the predecessor of the node.

    SELECT * FROM pgr_drivingDistance(
      $$SELECT id, source, target, cost, reverse_cost FROM edges$$,
      11, 3.0);
     seq | depth | start_vid | pred | node | edge | cost | agg_cost
    -----+-------+-----------+------+------+------+------+----------
       1 |     0 |        11 |   11 |   11 |   -1 |    0 |        0
       2 |     1 |        11 |   11 |    7 |    8 |    1 |        1
       3 |     1 |        11 |   11 |   12 |   11 |    1 |        1
       4 |     1 |        11 |   11 |   16 |    9 |    1 |        1
       5 |     2 |        11 |    7 |    3 |    7 |    1 |        2
       6 |     2 |        11 |    7 |    6 |    4 |    1 |        2
       7 |     2 |        11 |    7 |    8 |   10 |    1 |        2
       8 |     2 |        11 |   16 |   15 |   16 |    1 |        2
       9 |     2 |        11 |   16 |   17 |   15 |    1 |        2
      10 |     3 |        11 |    3 |    1 |    6 |    1 |        3
      11 |     3 |        11 |    6 |    5 |    1 |    1 |        3
      12 |     3 |        11 |    8 |    9 |   14 |    1 |        3
      13 |     3 |        11 |   15 |   10 |    3 |    1 |        3
    (13 rows)
    
    

If needed filter out the added columns, for example, to return the original columns

SELECT seq, node, edge, cost, agg_cost
FROM pgr_drivingDistance(
  $$SELECT id, source, target, cost, reverse_cost FROM edges$$,
  11, 3.0);
 seq | node | edge | cost | agg_cost
-----+------+------+------+----------
   1 |   11 |   -1 |    0 |        0
   2 |    7 |    8 |    1 |        1
   3 |   12 |   11 |    1 |        1
   4 |   16 |    9 |    1 |        1
   5 |    3 |    7 |    1 |        2
   6 |    6 |    4 |    1 |        2
   7 |    8 |   10 |    1 |        2
   8 |   15 |   16 |    1 |        2
   9 |   17 |   15 |    1 |        2
  10 |    1 |    6 |    1 |        3
  11 |    5 |    1 |    1 |        3
  12 |    9 |   14 |    1 |        3
  13 |   10 |    3 |    1 |        3
(13 rows)

pgr_drivingDistance(Multiple vertices)

Using this example.

  • The from_v result column name changes to start_vid.

  • depth contains the depth of the node.

  • pred contains the predecessor of the node.

    SELECT *
    FROM pgr_drivingDistance(
      $$SELECT id, source, target, cost, reverse_cost FROM edges$$,
      ARRAY[11, 16], 3.0, equicost => true);
     seq | depth | start_vid | pred | node | edge | cost | agg_cost
    -----+-------+-----------+------+------+------+------+----------
       1 |     0 |        11 |   11 |   11 |   -1 |    0 |        0
       2 |     1 |        11 |   11 |    7 |    8 |    1 |        1
       3 |     1 |        11 |   11 |   12 |   11 |    1 |        1
       4 |     2 |        11 |    7 |    3 |    7 |    1 |        2
       5 |     2 |        11 |    7 |    6 |    4 |    1 |        2
       6 |     2 |        11 |    7 |    8 |   10 |    1 |        2
       7 |     3 |        11 |    3 |    1 |    6 |    1 |        3
       8 |     3 |        11 |    6 |    5 |    1 |    1 |        3
       9 |     3 |        11 |    8 |    9 |   14 |    1 |        3
      10 |     0 |        16 |   16 |   16 |   -1 |    0 |        0
      11 |     1 |        16 |   16 |   15 |   16 |    1 |        1
      12 |     1 |        16 |   16 |   17 |   15 |    1 |        1
      13 |     2 |        16 |   15 |   10 |    3 |    1 |        2
    (13 rows)
    
    

If needed filter out and rename columns, for example, to return the original columns:

SELECT seq, start_vid AS from_v, node, edge, cost, agg_cost
FROM pgr_drivingDistance(
  $$SELECT id, source, target, cost, reverse_cost FROM edges$$,
  ARRAY[11, 16], 3.0, equicost => true);
 seq | from_v | node | edge | cost | agg_cost
-----+--------+------+------+------+----------
   1 |     11 |   11 |   -1 |    0 |        0
   2 |     11 |    7 |    8 |    1 |        1
   3 |     11 |   12 |   11 |    1 |        1
   4 |     11 |    3 |    7 |    1 |        2
   5 |     11 |    6 |    4 |    1 |        2
   6 |     11 |    8 |   10 |    1 |        2
   7 |     11 |    1 |    6 |    1 |        3
   8 |     11 |    5 |    1 |    1 |        3
   9 |     11 |    9 |   14 |    1 |        3
  10 |     16 |   16 |   -1 |    0 |        0
  11 |     16 |   15 |   16 |    1 |        1
  12 |     16 |   17 |   15 |    1 |        1
  13 |     16 |   10 |    3 |    1 |        2
(13 rows)

Migration of pgr_kruskalDD / pgr_kruskalBFS / pgr_kruskalDFS

Starting from v3.7.0 pgr_kruskalDD, pgr_kruskalBFS and pgr_kruskalDFS result columns are being standardized.

from:

(seq, depth, start_vid, node, edge, cost, agg_cost)

to:

(seq, depth, start_vid, pred, node, edge, cost, agg_cost)

  • pgr_kruskalDD

    • Single vertex

    • Multiple vertices

  • pgr_kruskalDFS

    • Single vertex

    • Multiple vertices

  • pgr_kruskalBFS

    • Single vertex

    • Multiple vertices

Before Migration:

Output columns were (seq, depth, start_vid, node, edge, cost, agg_cost)

  • Single vertex and Multiple vertices

    • Do not have pred result column.

Migration:

  • Be aware of the existence of pred result columns.

  • If needed filter out the added columns

Kruskal single vertex

Using pgr_KruskalDD as example. Migration is similar to al the affected functions.

Comparing with this example.

Now column pred exists and contains the predecessor of the node.

SELECT * FROM pgr_kruskalDD(
  'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
  6, 3.5);
 seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
   1 |     0 |         6 |    6 |    6 |   -1 |    0 |        0
   2 |     1 |         6 |    6 |    5 |    1 |    1 |        1
   3 |     1 |         6 |    6 |   10 |    2 |    1 |        1
   4 |     2 |         6 |   10 |   15 |    3 |    1 |        2
   5 |     3 |         6 |   15 |   16 |   16 |    1 |        3
(5 rows)

If needed filter out the added columns, for example, to return the original columns

SELECT seq, depth, start_vid, node, edge, cost, agg_cost
FROM pgr_kruskalDD(
  'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
  6, 3.5);
 seq | depth | start_vid | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+----------
   1 |     0 |         6 |    6 |   -1 |    0 |        0
   2 |     1 |         6 |    5 |    1 |    1 |        1
   3 |     1 |         6 |   10 |    2 |    1 |        1
   4 |     2 |         6 |   15 |    3 |    1 |        2
   5 |     3 |         6 |   16 |   16 |    1 |        3
(5 rows)

Kruskal multiple vertices

Using pgr_KruskalDD as example. Migration is similar to al the affected functions.

Comparing with this example.

Now column pred exists and contains the predecessor of the node.

SELECT * FROM pgr_kruskalDD(
  'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
  ARRAY[9, 6], 3.5);
 seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
   1 |     0 |         6 |    6 |    6 |   -1 |    0 |        0
   2 |     1 |         6 |    6 |    5 |    1 |    1 |        1
   3 |     1 |         6 |    6 |   10 |    2 |    1 |        1
   4 |     2 |         6 |   10 |   15 |    3 |    1 |        2
   5 |     3 |         6 |   15 |   16 |   16 |    1 |        3
   6 |     0 |         9 |    9 |    9 |   -1 |    0 |        0
   7 |     1 |         9 |    9 |    8 |   14 |    1 |        1
   8 |     2 |         9 |    8 |    7 |   10 |    1 |        2
   9 |     3 |         9 |    7 |    3 |    7 |    1 |        3
  10 |     2 |         9 |    8 |   12 |   12 |    1 |        2
  11 |     3 |         9 |   12 |   11 |   11 |    1 |        3
  12 |     3 |         9 |   12 |   17 |   13 |    1 |        3
(12 rows)

If needed filter out the added columns, for example, to return the original columns

SELECT seq, depth, start_vid, node, edge, cost, agg_cost
FROM pgr_kruskalDD(
  'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
  ARRAY[9, 6], 3.5);
 seq | depth | start_vid | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+----------
   1 |     0 |         6 |    6 |   -1 |    0 |        0
   2 |     1 |         6 |    5 |    1 |    1 |        1
   3 |     1 |         6 |   10 |    2 |    1 |        1
   4 |     2 |         6 |   15 |    3 |    1 |        2
   5 |     3 |         6 |   16 |   16 |    1 |        3
   6 |     0 |         9 |    9 |   -1 |    0 |        0
   7 |     1 |         9 |    8 |   14 |    1 |        1
   8 |     2 |         9 |    7 |   10 |    1 |        2
   9 |     3 |         9 |    3 |    7 |    1 |        3
  10 |     2 |         9 |   12 |   12 |    1 |        2
  11 |     3 |         9 |   11 |   11 |    1 |        3
  12 |     3 |         9 |   17 |   13 |    1 |        3
(12 rows)

Migration of pgr_KSP

Starting from v3.6.0 pgr_KSP result columns are being standardized.

from:

(seq, path_id, path_seq, node, edge, cost, agg_cost)

from:

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

Signatures to be migrated:

  • pgr_KSP (One to One)

Before Migration:

  • Output columns were (seq, path_id, path_seq, node, edge, cost, agg_cost)

    • the columns start_vid and end_vid do not exist.

      • pgr_KSP (One to One) does not have start_vid and end_vid.

Migration:

  • Be aware of the existence of the additional columns.

pgr_KSP (One to One)

Using this example.

  • start_vid contains the start vid parameter value.

  • end_vid contains the end vid parameter value.

SELECT * FROM pgr_KSP(
  $$SELECT id, source, target, cost, reverse_cost FROM edges$$,
  6, 17, 2);
 seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------
   1 |       1 |        1 |         6 |      17 |    6 |    4 |    1 |        0
   2 |       1 |        2 |         6 |      17 |    7 |   10 |    1 |        1
   3 |       1 |        3 |         6 |      17 |    8 |   12 |    1 |        2
   4 |       1 |        4 |         6 |      17 |   12 |   13 |    1 |        3
   5 |       1 |        5 |         6 |      17 |   17 |   -1 |    0 |        4
   6 |       2 |        1 |         6 |      17 |    6 |    4 |    1 |        0
   7 |       2 |        2 |         6 |      17 |    7 |    8 |    1 |        1
   8 |       2 |        3 |         6 |      17 |   11 |    9 |    1 |        2
   9 |       2 |        4 |         6 |      17 |   16 |   15 |    1 |        3
  10 |       2 |        5 |         6 |      17 |   17 |   -1 |    0 |        4
(10 rows)

If needed filter out the added columns, for example, to return the original columns:

SELECT seq, path_id, path_seq, node, edge, cost, agg_cost FROM pgr_KSP(
  $$SELECT id, source, target, cost, reverse_cost FROM edges$$,
  6, 17, 2);
 seq | path_id | path_seq | node | edge | cost | agg_cost
-----+---------+----------+------+------+------+----------
   1 |       1 |        1 |    6 |    4 |    1 |        0
   2 |       1 |        2 |    7 |   10 |    1 |        1
   3 |       1 |        3 |    8 |   12 |    1 |        2
   4 |       1 |        4 |   12 |   13 |    1 |        3
   5 |       1 |        5 |   17 |   -1 |    0 |        4
   6 |       2 |        1 |    6 |    4 |    1 |        0
   7 |       2 |        2 |    7 |    8 |    1 |        1
   8 |       2 |        3 |   11 |    9 |    1 |        2
   9 |       2 |        4 |   16 |   15 |    1 |        3
  10 |       2 |        5 |   17 |   -1 |    0 |        4
(10 rows)

Migration of pgr_maxCardinalityMatch

pgr_maxCardinalityMatch works only for undirected graphs, therefore the directed flag has been removed.

Starting from v3.4.0

Signature to be migrated:

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

Migration is needed, because:

  • Use cost and reverse_cost on the inner query

  • Results are ordered

  • Works for undirected graphs.

  • New signature

    • pgr_maxCardinalityMatch(text) returns only edge column.

    • The optional flag directed is removed.

Before migration:

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

  • Columns used are going and coming to represent the existence of an edge.

  • Flag directed was used to indicate if it was for a directed or undirected graph.

    • The flag directed is ignored.

      • Regardless of it’s value it gives the result considering the graph as undirected.

Migration:

  • Use the columns cost and reverse_cost to represent the existence of an edge.

  • Do not use the flag directed.

  • In the query returns only edge column.

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

Migration of pgr_primDD / pgr_primBFS / pgr_primDFS

Starting from v3.7.0 pgr_primDD, pgr_primBFS and pgr_primDFS result columns are being standardized.

from:

(seq, depth, start_vid, node, edge, cost, agg_cost)

to:

(seq, depth, start_vid, pred, node, edge, cost, agg_cost)

  • pgr_primDD

    • Single vertex

    • Multiple vertices

  • pgr_primDFS

    • Single vertex

    • Multiple vertices

  • pgr_primBFS

    • Single vertex

    • Multiple vertices

Before Migration:

Output columns were (seq, depth, start_vid, node, edge, cost, agg_cost)

  • Single vertex and Multiple vertices

    • Do not have pred result column.

Migration:

  • Be aware of the existence of pred result columns.

  • If needed filter out the added columns

Prim single vertex

Using pgr_primDD as example. Migration is similar to al the affected functions.

Comparing with this example.

Now column pred exists and contains the predecessor of the node.

SELECT * FROM pgr_primDD(
  'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
  6, 3.5);
 seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
   1 |     0 |         6 |    6 |    6 |   -1 |    0 |        0
   2 |     1 |         6 |    6 |    5 |    1 |    1 |        1
   3 |     1 |         6 |    6 |   10 |    2 |    1 |        1
   4 |     2 |         6 |   10 |   15 |    3 |    1 |        2
   5 |     2 |         6 |   10 |   11 |    5 |    1 |        2
   6 |     3 |         6 |   11 |   16 |    9 |    1 |        3
   7 |     3 |         6 |   11 |   12 |   11 |    1 |        3
   8 |     1 |         6 |    6 |    7 |    4 |    1 |        1
   9 |     2 |         6 |    7 |    3 |    7 |    1 |        2
  10 |     3 |         6 |    3 |    1 |    6 |    1 |        3
  11 |     2 |         6 |    7 |    8 |   10 |    1 |        2
  12 |     3 |         6 |    8 |    9 |   14 |    1 |        3
(12 rows)

If needed filter out the added columns, for example, to return the original columns

SELECT seq, depth, start_vid, node, edge, cost, agg_cost
FROM pgr_primDD(
  'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
  6, 3.5);
 seq | depth | start_vid | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+----------
   1 |     0 |         6 |    6 |   -1 |    0 |        0
   2 |     1 |         6 |    5 |    1 |    1 |        1
   3 |     1 |         6 |   10 |    2 |    1 |        1
   4 |     2 |         6 |   15 |    3 |    1 |        2
   5 |     2 |         6 |   11 |    5 |    1 |        2
   6 |     3 |         6 |   16 |    9 |    1 |        3
   7 |     3 |         6 |   12 |   11 |    1 |        3
   8 |     1 |         6 |    7 |    4 |    1 |        1
   9 |     2 |         6 |    3 |    7 |    1 |        2
  10 |     3 |         6 |    1 |    6 |    1 |        3
  11 |     2 |         6 |    8 |   10 |    1 |        2
  12 |     3 |         6 |    9 |   14 |    1 |        3
(12 rows)

Prim multiple vertices

Using pgr_primDD as example. Migration is similar to al the affected functions.

Comparing with this example.

Now column pred exists and contains the predecessor of the node.

SELECT * FROM pgr_primDD(
  'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
  ARRAY[9, 6], 3.5);
 seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
   1 |     0 |         6 |    6 |    6 |   -1 |    0 |        0
   2 |     1 |         6 |    6 |    5 |    1 |    1 |        1
   3 |     1 |         6 |    6 |   10 |    2 |    1 |        1
   4 |     2 |         6 |   10 |   15 |    3 |    1 |        2
   5 |     2 |         6 |   10 |   11 |    5 |    1 |        2
   6 |     3 |         6 |   11 |   16 |    9 |    1 |        3
   7 |     3 |         6 |   11 |   12 |   11 |    1 |        3
   8 |     1 |         6 |    6 |    7 |    4 |    1 |        1
   9 |     2 |         6 |    7 |    3 |    7 |    1 |        2
  10 |     3 |         6 |    3 |    1 |    6 |    1 |        3
  11 |     2 |         6 |    7 |    8 |   10 |    1 |        2
  12 |     3 |         6 |    8 |    9 |   14 |    1 |        3
  13 |     0 |         9 |    9 |    9 |   -1 |    0 |        0
  14 |     1 |         9 |    9 |    8 |   14 |    1 |        1
  15 |     2 |         9 |    8 |    7 |   10 |    1 |        2
  16 |     3 |         9 |    7 |    6 |    4 |    1 |        3
  17 |     3 |         9 |    7 |    3 |    7 |    1 |        3
(17 rows)

If needed filter out the added columns, for example, to return the original columns

SELECT seq, depth, start_vid, node, edge, cost, agg_cost
FROM pgr_primDD(
  'SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id',
  ARRAY[9, 6], 3.5);
 seq | depth | start_vid | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+----------
   1 |     0 |         6 |    6 |   -1 |    0 |        0
   2 |     1 |         6 |    5 |    1 |    1 |        1
   3 |     1 |         6 |   10 |    2 |    1 |        1
   4 |     2 |         6 |   15 |    3 |    1 |        2
   5 |     2 |         6 |   11 |    5 |    1 |        2
   6 |     3 |         6 |   16 |    9 |    1 |        3
   7 |     3 |         6 |   12 |   11 |    1 |        3
   8 |     1 |         6 |    7 |    4 |    1 |        1
   9 |     2 |         6 |    3 |    7 |    1 |        2
  10 |     3 |         6 |    1 |    6 |    1 |        3
  11 |     2 |         6 |    8 |   10 |    1 |        2
  12 |     3 |         6 |    9 |   14 |    1 |        3
  13 |     0 |         9 |    9 |   -1 |    0 |        0
  14 |     1 |         9 |    8 |   14 |    1 |        1
  15 |     2 |         9 |    7 |   10 |    1 |        2
  16 |     3 |         9 |    6 |    4 |    1 |        3
  17 |     3 |         9 |    3 |    7 |    1 |        3
(17 rows)

Migration of pgr_withPointsDD

Starting from v3.6.0 pgr_withPointsDD - Proposed result columns are being standardized.

from:

(seq, [start_vid], node, edge, cost, agg_cost)

to:

(seq, depth, start_vid, pred, node, edge, cost, agg_cost)

And driving_side parameter changed from named optional to unnamed compulsory driving side and its validity differ for directed and undirected graphs.

Signatures to be migrated:

  • pgr_withPointsDD (Single vertex)

  • pgr_withPointsDD (Multiple vertices)

Before Migration:

  • pgr_withPointsDD (Single vertex)

    • Output columns were (seq, node, edge, cost, agg_cost)

    • Does not have start_vid, pred and depth result columns.

    • driving_side parameter was named optional now it is compulsory unnamed.

  • pgr_withPointsDD (Multiple vertices)

    • Output columns were (seq, start_vid, node, edge, cost, agg_cost)

    • Does not have depth and pred result columns.

    • driving_side parameter was named optional now it is compulsory unnamed.

Driving side was optional

The default values on this query are:

directed:

true

driving_side:

‘b’

details:

false

SELECT * FROM pgr_withPointsDD(
  $$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
  $$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
  -1, 3.3);
WARNING:  pgr_withpointsdd(text,text,bigint,double precision,boolean,character,boolean) deprecated signature on 3.6.0
 seq | node | edge | cost | agg_cost
-----+------+------+------+----------
   1 |   -1 |   -1 |    0 |        0
   2 |    5 |    1 |  0.4 |      0.4
   3 |    6 |    1 |  0.6 |      0.6
   4 |    7 |    4 |    1 |      1.6
   5 |    3 |    7 |    1 |      2.6
   6 |    8 |   10 |    1 |      2.6
   7 |   11 |    8 |    1 |      2.6
   8 |   -3 |   12 |  0.6 |      3.2
   9 |   -4 |    6 |  0.7 |      3.3
(9 rows)

Driving side was named optional

The default values on this query are:

directed:

true

details:

false

SELECT * FROM pgr_withPointsDD(
  $$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
  $$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
  -1, 3.3, driving_side => 'r');
WARNING:  pgr_withpointsdd(text,text,bigint,double precision,boolean,character,boolean) deprecated signature on 3.6.0
 seq | node | edge | cost | agg_cost
-----+------+------+------+----------
   1 |   -1 |   -1 |    0 |        0
   2 |    5 |    1 |  0.4 |      0.4
   3 |    6 |    1 |    1 |      1.4
   4 |    7 |    4 |    1 |      2.4
(4 rows)

On directed graph b could be used as driving side

The default values on this query are:

details:

false

SELECT * FROM pgr_withPointsDD(
  $$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
  $$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
  -1, 3.3, directed => true, driving_side => 'b');
WARNING:  pgr_withpointsdd(text,text,bigint,double precision,boolean,character,boolean) deprecated signature on 3.6.0
 seq | node | edge | cost | agg_cost
-----+------+------+------+----------
   1 |   -1 |   -1 |    0 |        0
   2 |    5 |    1 |  0.4 |      0.4
   3 |    6 |    1 |  0.6 |      0.6
   4 |    7 |    4 |    1 |      1.6
   5 |    3 |    7 |    1 |      2.6
   6 |    8 |   10 |    1 |      2.6
   7 |   11 |    8 |    1 |      2.6
   8 |   -3 |   12 |  0.6 |      3.2
   9 |   -4 |    6 |  0.7 |      3.3
(9 rows)

On undirected graph r could be used as driving side

Also l could be used as driving side

SELECT * FROM pgr_withPointsDD(
  $$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
  $$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
  -1, 3.3, 'r', directed => true);
 seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
   1 |     0 |        -1 |   -1 |   -1 |   -1 |    0 |        0
   2 |     1 |        -1 |   -1 |    5 |    1 |  0.4 |      0.4
   3 |     2 |        -1 |    5 |    6 |    1 |    1 |      1.4
   4 |     3 |        -1 |   -6 |    7 |    4 |    1 |      2.4
(4 rows)

After Migration:

  • Be aware of the existence of the additional result Columns.

  • New output columns are (seq, depth, start_vid, pred, node, edge, cost, agg_cost)

  • driving side parameter is unnamed compulsory, and valid values differ for directed and undirected graphs.

    • Does not have a default value.

    • In directed graph: valid values are [r, R, l, L]

    • In undirected graph: valid values are [b, B]

    • Using an invalid value throws an ERROR.

pgr_withPointsDD (Single vertex)

Using this example.

  • (seq, depth, start_vid, pred, node, edge, cost, agg_cost)

  • start_vid contains the start vid parameter value.

  • depth contains the depth from the start_vid vertex to the node.

  • pred contains the predecessor of the node.

To migrate, use an unnamed valid value for driving side after the distance parameter:

SELECT * FROM pgr_withPointsDD(
  $$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
  $$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
  -1, 3.3, 'r', directed => true);
 seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
   1 |     0 |        -1 |   -1 |   -1 |   -1 |    0 |        0
   2 |     1 |        -1 |   -1 |    5 |    1 |  0.4 |      0.4
   3 |     2 |        -1 |    5 |    6 |    1 |    1 |      1.4
   4 |     3 |        -1 |   -6 |    7 |    4 |    1 |      2.4
(4 rows)

To get results from previous versions:

  • filter out the additional columns, for example;

  • When details => false to remove the points use WHERE node >= 0 OR cost = 0

SELECT seq, node, edge, cost, agg_cost FROM pgr_withPointsDD(
  $$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
  $$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
  -1, 3.3, 'r', details => true);
 seq | node | edge | cost | agg_cost
-----+------+------+------+----------
   1 |   -1 |   -1 |    0 |        0
   2 |    5 |    1 |  0.4 |      0.4
   3 |    6 |    1 |    1 |      1.4
   4 |   -6 |    4 |  0.7 |      2.1
   5 |    7 |    4 |  0.3 |      2.4
(5 rows)

pgr_withPointsDD (Multiple vertices)

Using this example.

  • (seq, depth, start_vid, pred, node, edge, cost, agg_cost)

  • depth contains the depth from the start_vid vertex to the node.

  • pred contains the predecessor of the node.

SELECT * FROM pgr_withPointsDD(
  $$SELECT * FROM edges ORDER BY id$$,
  $$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
  ARRAY[-1, 16], 3.3, 'l', equicost => true);
 seq | depth | start_vid | pred | node | edge | cost | agg_cost
-----+-------+-----------+------+------+------+------+----------
   1 |     0 |        -1 |   -1 |   -1 |   -1 |    0 |        0
   2 |     1 |        -1 |   -1 |    6 |    1 |  0.6 |      0.6
   3 |     2 |        -1 |    6 |    7 |    4 |    1 |      1.6
   4 |     2 |        -1 |    6 |    5 |    1 |    1 |      1.6
   5 |     3 |        -1 |    7 |    3 |    7 |    1 |      2.6
   6 |     3 |        -1 |    7 |    8 |   10 |    1 |      2.6
   7 |     4 |        -1 |    8 |   -3 |   12 |  0.6 |      3.2
   8 |     4 |        -1 |    3 |   -4 |    6 |  0.7 |      3.3
   9 |     0 |        16 |   16 |   16 |   -1 |    0 |        0
  10 |     1 |        16 |   16 |   11 |    9 |    1 |        1
  11 |     1 |        16 |   16 |   15 |   16 |    1 |        1
  12 |     1 |        16 |   16 |   17 |   15 |    1 |        1
  13 |     2 |        16 |   15 |   10 |    3 |    1 |        2
  14 |     2 |        16 |   11 |   12 |   11 |    1 |        2
(14 rows)

To get results from previous versions:

  • Filter out the additional columns

  • When details => false to remove the points use WHERE node >= 0 OR cost = 0

SELECT seq, start_vid, node, edge, cost, agg_cost FROM pgr_withPointsDD(
  $$SELECT id, source, target, cost, reverse_cost FROM edges ORDER BY id$$,
  $$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
  ARRAY[-1, 16], 3.3, 'l', equicost => true) WHERE node >= 0 OR cost = 0;
 seq | start_vid | node | edge | cost | agg_cost
-----+-----------+------+------+------+----------
   1 |        -1 |   -1 |   -1 |    0 |        0
   2 |        -1 |    6 |    1 |  0.6 |      0.6
   3 |        -1 |    7 |    4 |    1 |      1.6
   4 |        -1 |    5 |    1 |    1 |      1.6
   5 |        -1 |    3 |    7 |    1 |      2.6
   6 |        -1 |    8 |   10 |    1 |      2.6
   9 |        16 |   16 |   -1 |    0 |        0
  10 |        16 |   11 |    9 |    1 |        1
  11 |        16 |   15 |   16 |    1 |        1
  12 |        16 |   17 |   15 |    1 |        1
  13 |        16 |   10 |    3 |    1 |        2
  14 |        16 |   12 |   11 |    1 |        2
(12 rows)

Migration of pgr_withPointsKSP

Starting from v3.6.0 pgr_withPointsKSP - Proposed result columns are being standardized.

from:

(seq, path_id, path_seq, node, edge, cost, agg_cost)

from:

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

And driving side parameter changed from named optional to unnamed compulsory driving side and its validity differ for directed and undirected graphs.

Signatures to be migrated:

  • pgr_withPointsKSP (One to One)

Before Migration:

  • Output columns were (seq, path_seq, [start_pid], [end_pid], node, edge, cost, agg_cost)

    • the columns start_vid and end_vid do not exist.

Migration:

  • Be aware of the existence of the additional result Columns.

  • New output columns are (seq, path_id, path_seq, start_vid, end_vid, node, edge, cost, agg_cost)

  • driving side parameter is unnamed compulsory, and valid values differ for directed and undirected graphs.

    • Does not have a default value.

    • In directed graph: valid values are [r, R, l, L]

    • In undirected graph: valid values are [b, B]

    • Using an invalid value throws an ERROR.

pgr_withPointsKSP (One to One)

Using this example.

  • start_vid contains the start vid parameter value.

  • end_vid contains the end vid parameter value.

SELECT * FROM pgr_withPointsKSP(
  $$SELECT id, source, target, cost, reverse_cost FROM edges$$,
  $$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
  -1, -2, 2, 'l');
 seq | path_id | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+---------+----------+-----------+---------+------+------+------+----------
   1 |       1 |        1 |        -1 |      -2 |   -1 |    1 |  0.6 |        0
   2 |       1 |        2 |        -1 |      -2 |    6 |    4 |    1 |      0.6
   3 |       1 |        3 |        -1 |      -2 |    7 |    8 |    1 |      1.6
   4 |       1 |        4 |        -1 |      -2 |   11 |   11 |    1 |      2.6
   5 |       1 |        5 |        -1 |      -2 |   12 |   13 |    1 |      3.6
   6 |       1 |        6 |        -1 |      -2 |   17 |   15 |  0.6 |      4.6
   7 |       1 |        7 |        -1 |      -2 |   -2 |   -1 |    0 |      5.2
   8 |       2 |        1 |        -1 |      -2 |   -1 |    1 |  0.6 |        0
   9 |       2 |        2 |        -1 |      -2 |    6 |    4 |    1 |      0.6
  10 |       2 |        3 |        -1 |      -2 |    7 |    8 |    1 |      1.6
  11 |       2 |        4 |        -1 |      -2 |   11 |    9 |    1 |      2.6
  12 |       2 |        5 |        -1 |      -2 |   16 |   15 |  1.6 |      3.6
  13 |       2 |        6 |        -1 |      -2 |   -2 |   -1 |    0 |      5.2
(13 rows)

If needed filter out the additional columns, for example, to return the original columns:

SELECT seq, path_id, path_seq, node, edge, cost, agg_cost FROM pgr_withPointsKSP(
  $$SELECT id, source, target, cost, reverse_cost FROM edges$$,
  $$SELECT pid, edge_id, fraction, side from pointsOfInterest$$,
  -1, -2, 2, 'l');
 seq | path_id | path_seq | node | edge | cost | agg_cost
-----+---------+----------+------+------+------+----------
   1 |       1 |        1 |   -1 |    1 |  0.6 |        0
   2 |       1 |        2 |    6 |    4 |    1 |      0.6
   3 |       1 |        3 |    7 |    8 |    1 |      1.6
   4 |       1 |        4 |   11 |   11 |    1 |      2.6
   5 |       1 |        5 |   12 |   13 |    1 |      3.6
   6 |       1 |        6 |   17 |   15 |  0.6 |      4.6
   7 |       1 |        7 |   -2 |   -1 |    0 |      5.2
   8 |       2 |        1 |   -1 |    1 |  0.6 |        0
   9 |       2 |        2 |    6 |    4 |    1 |      0.6
  10 |       2 |        3 |    7 |    8 |    1 |      1.6
  11 |       2 |        4 |   11 |    9 |    1 |      2.6
  12 |       2 |        5 |   16 |   15 |  1.6 |      3.6
  13 |       2 |        6 |   -2 |   -1 |    0 |      5.2
(13 rows)

Migration of pgr_trsp (Vertices)

Signature:

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

v3.4.0

See Also

Use pgr_dijkstra when there are no restrictions.

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 | 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 |    9 |    1 |        2
   4 |        4 |        15 |      16 |   16 |   -1 |    0 |        3
(4 rows)

To get the original column names:

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

Use pgr_trsp when there are 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)

To get the original column names:

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)

Signature:

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)
Deprecated:

v3.4.0

See Also

Use pgr_withPoints when there are no restrictions.

Use pgr_withPoints - Proposed (One to One) 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)

To get the original column names:

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

Use pgr_trsp_withPoints when there are 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)

To get the original column names:

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

Signature:

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

v3.4.0

See Also

Use pgr_dijkstraVia when there are no restrictions

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)

To get the original column names:

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

Use pgr_trspVia when there are 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)

To get the original column names:

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

Signature:

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

v3.4.0

See Also

Use pgr_withPointsVia when there are no restrictions

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)

To get the original column names:

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

Use pgr_trspVia_withPoints when there are 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)

To get the original column names:

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

Migration of restrictions

Starting from v3.4.0

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.

Creation of the old restrictions table

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

Old restrictions fill up

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

Old restrictions contents

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 359

  • 35

    • 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

The creation of the restrictions table

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

Adding the restrictions

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

Restrictions data

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)

The restriction with rid = 2 represents the path 359.

  • By inspection the path is clear.

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 pgRouting supplies an auxiliary function for reversal of an array _pgr_array_reverse needed for the migration.

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

See Also

Indices and tables