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.
Migrating functions:
pgr_maxCardinalityMatch works only for undirected graphs, therefore the
directed flag has been removed.
pgr_trsp - Proposed signatures have changed and many issues have been fixed in the new signatures. This section will show how to migrate from the old signatures to the new replacement functions. This also affects the restrictions.
Warning
All deprecated functions will be removed on next mayor version 4.0.0
Migration of pgr_maxCardinalityMatch¶
Signature to be migrated:
pgr_maxCardinalityMatch(Edges SQL, [directed])
RETURNS SETOF (seq, edge, source, target)
Migration is needed, because:
Use
costandreverse_coston the inner queryResults are ordered
Works for undirected graphs.
New signature
pgr_maxCardinalityMatch(text)returns onlyedgecolumn.The optional flag
directedis 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 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
goingandcomingto represent the existence of an edge.Flag
directedwas used to indicate if it was for a directed or undirected graph.The flag
directedis ignored.Regardless of it’s value it gives the result considering the graph as undirected.
- Migration:
Use the columns
costandreverse_costto represent the existence of an edge.Do not use the flag
directed.In the query returns only
edgecolumn.
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 restrictions¶
The structure of the restrictions have changed:
Old restrictions structure¶
On the deprecated signatures:
Column
ridis ignoredvia_pathMust be in reverse order.
Is of type
TEXT.When more than one via edge must be separated with
,.
target_idIs the last edge of the forbidden path.
Is of type
INTEGER.
to_costIs 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 \(3 \rightarrow 5
\rightarrow9\)
\(3\rightarrow5\)
is on column
via_pathin reverse orderis of type
TEXT
\(9\)
is on column
target_idis of type
INTEGER
New restrictions structure¶
Column
idis ignoredColumn
pathIs of type
ARRAY[ANY-INTEGER].Contains all the edges involved on the restriction.
The array has the ordered edges of the restriction.
Column
costIs 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 \(3 \rightarrow5
\rightarrow9\).
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_restrictionsis been used.
For this migration pgRouting supplies an auxiliary function for reversal of an array
_pgr_array_reverseneeded 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)
Migration of pgr_trsp (Vertices)¶
Signature to be migrated:
pgr_trsp(Edges SQL, source, target,
directed boolean, has_rcost boolean
[,restrict_sql text]);
RETURNS SETOF (seq, id1, id2, cost)
The integral type of the
Edges SQLcan only beINTEGER.The floating point type of the
Edges SQLcan only beFLOAT.directedflag is compulsory.Does not have a default value.
Does not autodetect if
reverse_costcolumn exist.User must be careful to match the existence of the column with the value of
has_rcostparameter.
The restrictions inner query is optional.
The output column names are meaningless
Migrate by using:
pgr_dijkstra when there are no restrictions,
pgr_trsp - Proposed (One to One) when there are restrictions.
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.
-
Autodetects if
reverse_costcolumn is in the edges SQL.Accepts
ANY-INTEGERon integral typesAccepts
ANY-NUMERICALon floating point typesdirectedflag has a default value oftrue.Use the same value that on the original query.
In this example it is
truewhich 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)
id1is the nodeid2is the edge
Migrating pgr_trsp (Vertices) using pgr_trsp¶
The following query has restrictions.
SELECT * FROM pgr_trsp(
$$SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost
FROM edges WHERE id != 16$$,
15, 16,
true, true,
$$SELECT to_cost, target_id::INTEGER, via_path
FROM old_restrictions$$);
WARNING: pgr_trsp(text,integer,integer,boolean,boolean) deprecated on v3.4.0
seq | id1 | id2 | cost
-----+-----+-----+------
0 | 15 | 3 | 1
1 | 10 | 5 | 1
2 | 11 | 11 | 1
3 | 12 | 13 | 1
4 | 17 | 15 | 1
5 | 16 | -1 | 0
(6 rows)
A message about deprecation is shown
Deprecated functions will be removed on the next mayor version 4.0.0
The restrictions are the last parameter of the function
Using the old structure of restrictions
Use pgr_trsp - Proposed (One to One) instead.
SELECT * FROM pgr_trsp(
$$SELECT id, source, target, cost, reverse_cost
FROM edges WHERE id != 16$$,
$$SELECT * FROM new_restrictions$$,
15, 16);
seq | path_seq | start_vid | end_vid | node | edge | cost | agg_cost
-----+----------+-----------+---------+------+------+------+----------
1 | 1 | 15 | 16 | 15 | 3 | 1 | 0
2 | 2 | 15 | 16 | 10 | 5 | 1 | 1
3 | 3 | 15 | 16 | 11 | 11 | 1 | 2
4 | 4 | 15 | 16 | 12 | 13 | 1 | 3
5 | 5 | 15 | 16 | 17 | 15 | 1 | 4
6 | 6 | 15 | 16 | 16 | -1 | 0 | 5
(6 rows)
The new structure of restrictions is been used.
It is the second parameter.
The types casting has been removed.
-
Autodetects if
reverse_costcolumn is in the edges SQL.Accepts
ANY-INTEGERon integral typesAccepts
ANY-NUMERICALon floating point typesdirectedflag has a default value oftrue.Use the same value that on the original query.
In this example it is
truewhich 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)
id1is the nodeid2is the edge
Migration of pgr_trsp (Edges)¶
Signature to be migrated:
pgr_trsp(sql text, source_edge integer, source_pos float8,
target_edge integer, target_pos float8,
directed boolean, has_rcost boolean
[,restrict_sql text]);
RETURNS SETOF (seq, id1, id2, cost)
The integral types of the
sqlcan only beINTEGER.The floating point type of the
sqlcan only beFLOAT.directedflag is compulsory.Does not have a default value.
Does not autodetect if
reverse_costcolumn exist.User must be careful to match the existence of the column with the value of
has_rcostparameter.
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:
pgr_withPoints - Proposed when there are no restrictions,
pgr_trsp_withPoints - Proposed (One to One) when there are restrictions.
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 - Proposed 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.
-
Autodetects if
reverse_costcolumn is in the edges SQL.Accepts
ANY-INTEGERon integral typesAccepts
ANY-NUMERICALon floating point typesdirectedflag has a default value oftrue.Use the same value that on the original query.
In this example it is
truewhich is the default value.The flag has been omitted and the default is been used.
On the points query do not include the
sidecolumn.
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)
id1is the nodeid2is 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_costcolumn is in the edges SQL.Accepts
ANY-INTEGERon integral typesAccepts
ANY-NUMERICALon floating point typesdirectedflag has a default value oftrue.Use the same value that on the original query.
In this example it is
truewhich is the default value.The flag has been omitted and the default is been used.
On the points query do not include the
sidecolumn.
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)
id1is the nodeid2is the edge
Migration of pgr_trspViaVertices¶
Signature to be migrated:
pgr_trspViaVertices(sql text, vids integer[],
directed boolean, has_rcost boolean
[, turn_restrict_sql text]);
RETURNS SETOF (seq, id1, id2, id3, cost)
The integral types of the
Edges SQLcan only beINTEGER.The floating point type of the
Edges SQLcan only beFLOAT.directedflag is compulsory.Does not have a default value.
Does not autodetect if
reverse_costcolumn exist.User must be careful to match the existence of the column with the value of
has_rcostparameter.
The restrictions inner query is optional.
Migrate by using:
pgr_dijkstraVia - Proposed when there are no restrictions,
pgr_trspVia - Proposed when there are restrictions.
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.
-
Autodetects if
reverse_costcolumn is in the edges SQL.Accepts
ANY-INTEGERon integral typesAccepts
ANY-NUMERICALon floating point typesdirectedflag has a default value oftrue.Use the same value that on the original query.
In this example it is
truewhich is the default value.The flag has been omitted and the default is been used.
On the points query do not include the
sidecolumn.
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)
id1is the path identifierid2is the nodeid3is 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.
-
Autodetects if
reverse_costcolumn is in the edges SQL.Accepts
ANY-INTEGERon integral typesAccepts
ANY-NUMERICALon floating point typesdirectedflag has a default value oftrue.Use the same value that on the original query.
In this example it is
truewhich is the default value.The flag has been omitted and the default is been used.
On the points query do not include the
sidecolumn.
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)
id1is the path identifierid2is the nodeid3is the edge
Migration of pgr_trspViaEdges¶
Signature to be migrated:
pgr_trspViaEdges(sql text, eids integer[], pcts float8[],
directed boolean, has_rcost boolean
[, turn_restrict_sql text]);
RETURNS SETOF (seq, id1, id2, id3, cost)
The integral types of the
Edges SQLcan only beINTEGER.The floating point type of the
Edges SQLcan only beFLOAT.directedflag is compulsory.Does not have a default value.
Does not autodetect if
reverse_costcolumn exist.User must be careful to match the existence of the column with the value of
has_rcostparameter.
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:
pgr_withPointsVia - Proposed when there are no restrictions,
pgr_trspVia_withPoints - Proposed when there are restrictions.
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.
-
Autodetects if
reverse_costcolumn is in the edges SQL.Accepts
ANY-INTEGERon integral typesAccepts
ANY-NUMERICALon floating point typesdirectedflag has a default value oftrue.Use the same value that on the original query.
In this example it is
truewhich is the default value.The flag has been omitted and the default is been used.
On the points query do not include the
sidecolumn.
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)
id1is the path identifierid2is the nodeid3is 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_costcolumn is in the edges SQL.Accepts
ANY-INTEGERon integral typesAccepts
ANY-NUMERICALon floating point typesdirectedflag has a default value oftrue.Use the same value that on the original query.
In this example it is
truewhich is the default value.The flag has been omitted and the default is been used.
On the points query do not include the
sidecolumn.
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)
id1is the path identifierid2is the nodeid3is the edge
See Also¶
Indices and tables