Migration Guide

Vicky Vergara edited this page Sep 7, 2017 · 57 revisions

Overview

Upgrade from 2.4 to 2.5

Upgrade from 2.3 to 2.4

Upgrade from 2.2 to 2.3

Upgrade from 2.1 to 2.2

Upgrade from 2.0 to 2.1


Top

Migrating from 2.4 to 2.5

pgr_bdAstar

How to detect that it needs migration

  • when the output columns are (seq, id1, id2, cost)
  • when the pgr_bdAstar ends with two boolean values
  • when the inner query columns need casting

What to do

  • Adjust the returning to the new column names (seq, path_seq, node, edge, cost, agg_cost)
    • id1 is now node
    • id2 is now edge
  • Remove the inner query casting
  • Remove any contradiction
    • when the last boolean value has a value false: physically the column reverse_cost must not exist in the inner query
    • when the last boolean value has a value true: physically the column reverse_cost must exist in the inner query
  • Remove the last boolean value

Example

SELECT seq, id1, id2, cost FROM pgr_bdAStar(
    'SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost, x1, y1, x2, y2
     FROM edge_table',
    4, 10, false, false);

migrates to

SELECT seq, path_seq, node, edge, cost, agg_cost FROM pgr_bdAStar(
    'SELECT id, source, target, cost, x1, y1, x2, y2
     FROM edge_table',
    4, 10, false);

Max Flow

  • pgr_maxFlowBoykovKolmogorov,
  • pgr_maxFlowEdmondsKarp,
  • pgr_maxFlowPushRelabel

How to detect that it needs migration

  • The function has maxFlow on the name
  • when the output columns are (seq, edge_id, source, target, flow, residual_capacity)

What to do

  • Adjust the returning columns to the new column names (seq, edge, start_vid, end_vid, flow, residual_capacity)
    • source is now start_vid
    • target is now end_vid
  • Change the name acordingly:
    • pgr_maxFlowBoykovKolmogorov to pgr_boykovKolmogorov
    • pgr_maxFlowEdmondsKarp to pgr_edmondsKarp
    • pgr_maxFlowPushRelabel to pgr_pushRelabel

Example

SELECT seq, edge_id, source, target, flow, residual_capacity
FROM pgr_maxFlowBoykovKolmogorov(
    'SELECT id, source, target, capacity, reverse_capacity
    FROM edge_table',
    6, 11
);

migrates to

SELECT seq, edge, start_vid, end_vid, flow, residual_capacity
FROM pgr_boykovKolmogorov(
    'SELECT id, source, target, capacity, reverse_capacity
    FROM edge_table',
    6, 11
);

pgr_pointToId

Availability: 2.0.x Deprecated: 2.5.0

what to do

given 'my_table' with the columns (gid, my_point_geom)

SELECT gid AS id
FROM  my_table
WHERE ST_Equals( my_point_geom, ST_SetSRID(ST_Point(-71.1043443253471, 42.3150676015829),4326))

Migrating from 2.3 to 2.4

Bidirectional Functions

pgr_bdDijkstra

-> Who is affected?
  • when the output columns are (seq, id1, id2, cost)
  • when the pgr_bdAstar ends with two boolean values
  • when the inner query columns need casting
-> Resolution
  • Adjust the returning to the new column names (seq, path_seq, node, edge, cost, agg_cost)
    • id1 is now node
    • id2 is now edge
  • Remove the inner query casting
  • Remove any contradiction
    • when the last boolean value has a value false: physically the column reverse_cost must not exist in the inner query
    • when the last boolean value has a value true: physically the column reverse_cost must exist in the inner query
  • Remove the last boolean value
-> Example
SELECT seq, id1, id2, cost FROM pgr_bdDijkstra(
    'SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost
     FROM edge_table',
    4, 10, false, false);

migrates to

SELECT seq, path_seq, node, edge, cost, agg_cost FROM pgr_bdDijkstra(
    'SELECT id, source, target, cost
     FROM edge_table',
    4, 10, false);

Convenience Functions

pgr_pointsToVids

  • Availability: 2.1.0
  • Deprecated: 2.3.0
  • No replacement
-> Resolution

PostGIS has ST_DWithin.

-> Example
SELECT id AS Vids FROM edge_table_vertices_pgr
WHERE ST_DWithin(the_geom, ST_Point(-97, 30), 0.001);

Top

Migrating from 2.2 to 2.3

TSP Functions

pgr_TSP with distance matrix

  • Availability: 2.0
  • Deprecated signature: 2.2
  • Has replacement
-> Who is affected?
  • When the first parameter is of type float8[][]
  • When the result columns are (seq, id)
  • When a function that return float8 was written to create the first parameter
  • When you have to convert a table to a matrix to get a float8[][]
  • start_vid or end_vid are INTEGER
-> Example 1

Suppose that the function make_matrix(, ids::BIGINT[]) was written to create a distance matrix of the ids in the array using internally pgr_dijkstra and returning float8[][]

-> Resolution
  • Use pgr_dijkstraCostMatrix
  • Adjust the returning columns to the new column names (seq, node, cost, agg_cost)
    • before id was an index to the array, now its not returned
  • Remove the inner query casting
  • start_vid or end_vid are BIGINT
    • a constant has to be casted to BIGINT otherwise it will get the old signature
    • a variable has to be of type BIGINT
SELECT seq, id
FROM pgr_tsp(
    make_matrix(<parameters>),  ARRAY[2,5,7,9]),
    1); -- the `1` represents the first element of the array, that is the `2`

migrates to

SELECT seq, node, cost, agg_cost FROM pgr_TSP(
    $$
    SELECT * FROM pgr_dijkstraCostMatrix(
        'SELECT id, source, target, cost, reverse_cost FROM edge_table',
        ARRAY[2,5,7,9],
        false
    )
    $$,
    2::BIGINT);
-> Example 2

Suppose that the table dmatrix_table has columns (start_vid, end_vid, agg_cost) and stores the values for a distance matrix of ids (2,5,7,9) and convert_to_matrix(table::TEXT) was written to create the distance matrix of the ids based on the dmatrix_table information

-> Resolution
  • use an SQL query to select all the values of dmatrix_table
  • Adjust the returning columns to the new column names (seq, node, cost, agg_cost)
    • before id was an index to the array, now its not returned
  • Remove the inner query casting
  • start_vid or end_vid are BIGINT
    • a constant has to be casted to BIGINT otherwise it will get the old signature
    • a variable has to be of type BIGINT
SELECT seq, id
FROM pgr_tsp(
    convert_to_matrix('dmatrix_table'),
    1); -- the `1` represents the first element of the array, that is the id `2`

migrates to

SELECT * FROM pgr_TSP(
    $$
    SELECT * FROM dmatrix_table
    $$,
2::BIGINT);

pgr_TSP with euclidean distances

  • Availability: 2.0
  • Deprecated signature: 2.2
  • Has replacement
-> Who is affected?
  • When the first parameter is of type text
  • When the result columns are (seq, id1, id2, cost)
-> Resolution
  • Change the name to pgr_eucledianTSP
  • Adjust the returning columns to the new column names (seq, node, cost, agg_cost)
    • id1 is unused
    • id2 is now node
  • Remove the inner query casting
-> Example
SELECT seq, id1, id2, cost 
FROM pgr_tsp(
   'SELECT id::INTEGER, x, y
   FROM vertex_table'
, 6, 5);

migrates to

SELECT seq, node, cost, agg_cost
FROM pgr_eucledianTSP(
   'SELECT id, x, y
   FROM vertex_table'
, 6, 5);

Convenience Functions

pgr_textToPoints

  • Availability: 2.1.0
  • Deprecated: 2.3.0
  • No replacement
-> Resolution

Nothing, PostGIS has ST_GeomFromText.

pgr_pointsToDMatrix

  • Availability: 2.1.0
  • Deprecated: 2.3.0
  • No replacement
-> Resolution

When migrating pgr_TSP use pgr_eucledianTSP.

pgr_vidsToDMatrix

  • Availability: 2.1.0
  • Deprecated: 2.3.0
  • No replacement
-> Resolution

When migrating pgr_TSP use pgr_dijkstraCost.

pgr_flipGeometries

  • Availability: 2.1.0
  • Deprecated: 2.3.0
  • No replacement
-> Resolution

Top

Migrating from 2.1 to 2.2

All Pairs Functions

pgr_kDijkstraPath

  • Availability: 2.0.0
  • Deprecated: 2.2.0
  • Has a replacement
-> Who is affected?
  • When the name is pgr_kDijkstraPath
  • When the result columns are (seq, id1, id2, id3, cost)
-> Resolution
  • Change the name to pgr_dijkstra
  • Adjust the returning to the new column names (seq, path_seq, end_vid, node, edge, cost, agg_cost)
    • id1 is now end_vid
    • id1 is now node
    • id2 is now edge
  • Remove the inner query casting
  • Remove any contradiction
    • When the last boolean value has a value false: physically the column reverse_cost must not exist in the inner query
    • When the last boolean value has a value true: physically the column reverse_cost must exist in the inner query
  • Remove the last boolean value
  • Remove the inner query casting
-> Example
SELECT seq, id1, id2, id3, cost FROM pgr_kDijkstraPath(
    'SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost
     FROM edge_table',
    10, array[4,12], false, false);

migrates to

SELECT  seq, path_seq, end_vid, node, edge, cost, agg_cost FROM pgr_dijkstra(
    'SELECT id, source, target, cost
    FROM edge_table',
    10, array[4,12], false);

pgr_kDijkstraCost

  • Availability: 2.0.0
  • Deprecated: 2.2.0
  • Has a replacement
-> Who is affected?
  • When the name is pgr_kDijkstraCost
  • When the output columns are (seq, id1, id2, cost)
-> Resolution
  • Change the name to pgr_dijkstraCost
  • Adjust the returning to the new column names (start_vid, end_vid, agg_cost)
    • id1 is now start_vid
    • id2 is now end_vid
    • cost is now agg_cost
  • Remove the inner query casting
  • Remove any contradiction
    • When the last boolean value has a value false: physically the column reverse_cost must not exist in the inner query
    • When the last boolean value has a value true: physically the column reverse_cost must exist in the inner query
  • Remove the last boolean value
-> Example
SELECT seq, id1, id2, cost FROM pgr_kDijkstraCost(
    'SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost
     FROM edge_table',
    10, array[4,12], false, false);

migrates to

SELECT  start_vid, end_vid, agg_cost FROM pgr_dijkstraCost(
    'SELECT id, source, target, cost
    FROM edge_table',
    10, array[4,12], false);

pgr_apspJohnson

  • Availability: 2.0.0
  • Deprecated: 2.2.0
  • Has a replacement
-> Who is affected?
  • When the output columns are (seq, id1, id2, cost)
  • When it has apsp as part of the name.
  • When the inner query columns need casting
  • When the sequence starts with 0
  • Only worked for directed graph
-> Resolution
  • Change the name to pgr_johnson
  • Adjust the returning to the new column names (start_vid, end_vid, agg_cost)
    • id1 is now start_vid
    • id2 is now end_vid
    • cost is now agg_cost
  • Remove the inner query casting
-> Example
SELECT seq, id1, id2, cost FROM pgr_apspJohnson(
    'SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost
     FROM edge_table');

migrates to

SELECT start_vid, end_vid, agg_cost FROM pgr_johnson(
    'SELECT id, source, target, cost
     FROM edge_table');

pgr_apspWarshall

  • Availability: 2.0.0
  • Deprecated: 2.2.0
  • Has a replacement
-> Who is affected?
  • When the output columns are (seq, id1, id2, cost)
  • When it has apsp as part of the name.
  • When the inner query columns need casting
  • When thepgr_apspWarshall ends with two boolean values
  • When the sequence starts with 0
  • Only worked for directed graph
-> Resolution
  • Change the name to pgr_floydWarshall
  • Adjust the returning to the new column names (start_vid, end_vid, agg_cost)
    • id1 is now start_vid
    • id2 is now end_vid
    • cost is now agg_cost
  • Remove the inner query casting
  • Remove any contradiction
    • When the last boolean value has a value false: physically the column reverse_cost must not exist in the inner query
    • When the last boolean value has a value true: physically the column reverse_cost must exist in the inner query
  • Remove the last boolean value
-> Example
SELECT seq, id1, id2, cost FROM pgr_apspWarshall(
    'SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost
     FROM edge_table',
    true, false);

migrates to

SELECT start_vid, end_vid, agg_cost FROM pgr_floydWarshall(
    'SELECT id, source, target, cost
     FROM edge_table',
    true);

Top

Migrating from 2.0 to 2.1

pgRouting functions

pgr_dijkstra

  • Availability: 2.0.0
  • Signature Changed: 2.1.0
  • A parameter was removed
-> Who is affected?
  • When the output columns are (seq, id1, id2, cost)
  • When thepgr_dijkstra ends with two boolean values
  • When the inner query columns need casting
  • When the sequence starts with 0
-> Resolution
  • Adjust the returning to the new column names (seq, path_seq, node, edge, cost, agg_cost)
    • id1 is now node
    • id2 is now edge
  • Remove the inner query casting
  • Remove any contradiction
    • When the last boolean value has a value false: physically the column reverse_cost must not exist in the inner query
    • When the last boolean value has a value true: physically the column reverse_cost must exist in the inner query
  • Remove the last boolean value
  • The sequence starts with 1, adjust any cycle to this value
-> Example
SELECT seq, id1, id2, cost FROM pgr_dijkstra(
    'SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost
     FROM edge_table',
    4, 10, true, false);

migrates to

SELECT seq, path_seq, node, edge, cost, agg_cost FROM pgr_dijkstra(
    'SELECT id, source, target, cost
     FROM edge_table',
    4, 10, true);

pgr_KSP

  • Availability: 2.0.0
  • Signature Changed: 2.1.0
  • A parameter was renamed
  • Functionality of the renamed parameter changed
-> Who is affected?
  • When the output columns are (seq, id1, id2, id3, cost)
  • When the pgr_KSP ends with one boolean value (has_rcost)
  • When the inner query columns need casting
  • When the sequence starts with 0
  • Only worked for directed graphs
-> Resolution
  • Adjust the returning to the new column names seq, path_id, path_seq, node, edge, cost, agg_cost)
    • id1 is now path_id
    • id2 is now node
    • id3 is now edge
  • Remove the inner query casting
  • Remove any contradiction
    • When the boolean value has a value false: physically the column reverse_cost must not exist in the inner query
    • When the boolean value has a value true: physically the column reverse_cost must exist in the inner query
  • Substitute the boolean value to be directed := true
  • The sequence starts with 1, adjust any cycle to this value
-> Example
SELECT seq, id1, id2, id3, cost FROM pgr_KSP(
    'SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost
     FROM edge_table',
    2, 11, 2, false);

migrates to

SELECT seq, path_seq, node, edge, cost, agg_cost FROM pgr_KSP(
    'SELECT id, source, target, cost
     FROM edge_table',
    2, 11, 2, directed := true);

pgr_drivingDistance

  • Availability: 2.0.0
  • Signature Changed: 2.1.0
  • A parameter was removed
-> Who is affected?
  • When the output columns are (seq, id1, id2, cost)
  • When thepgr_drivingDistance ends with two boolean values
  • When the inner query columns need casting
  • When the sequence starts with 0
-> Resolution
  • Adjust the returning to the new column names (seq, node, edge, cost, agg_cost)
    • id1 is now node
    • id2 is now edge and was not considered useful, now its part of a spanning tree
  • Remove the inner query casting
  • Remove any contradiction
    • When the last boolean value has a value false: physically the column reverse_cost must not exist in the inner query
    • When the last boolean value has a value true: physically the column reverse_cost must exist in the inner query
  • Remove the last boolean value
  • The sequence starts with 1, adjust any cycle to this value
-> Example
SELECT seq, id1, id2, cost FROM pgr_drivingDistance(
    'SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost, reverse_cost
     FROM edge_table',
    4, 3, true, false);

migrates to

SELECT seq, path_seq, node, edge, cost, agg_cost FROM pgr_drivingDistance(
    'SELECT id, source, target, cost
     FROM edge_table',
    4, 3, true);

pgr_createTopology

  • Availability: 2.0.0
  • Signature Changed: 2.1.0
  • An extra parameter was added
-> Resolution

Nothing

pgr_alphaShape

  • Availability: 2.0.0
  • Signature Changed: 2.1.0
  • An extra parameter was added
-> Resolution

Nothing

Developers functions

The following functions are deprecated and no longer maintained.

pgr_getColumnName

  • Availability: 2.0.0
  • Deprecated: 2.1.0
-> Resolution

One of the parameters is the column name

  • Its like having f(x,y) returns y
  • Instead of calling the function, use the second parameter

pgr_getTableName

  • Availability: 2.0.0
  • Deprecated: 2.1.0
-> Resolution

The parameters is the table name:

  • Its like having f(x) returns x
  • Instead of calling the function, use the parameter

pgr_isColumnIndexed

  • Availability: 2.0.0
  • Deprecated: 2.1.0
-> Resolution

This SQL lists the indexes on the table, Modify to suit your needs

SELECT * FROM pg_indexes WHERE tablename = 'edge_table';

pgr_isColumnInTable

  • Availability: 2.0.0
  • Deprecated: 2.1.0
-> Resolution

To see if the_column is in the table my_table

SELECT count(*) = 1
    FROM information_schema.columns
    WHERE table_name='my_table' AND column_name = 'the_column';

pgr_quote_ident

  • Availability: 2.0.0
  • Deprecated: 2.1.0
-> Resolution

Use PostgreSQL function quote_ident instead.

pgr_versionless

  • Availability: 2.0.0
  • Deprecated: 2.1.0
-> Resolution

Use the method of your choice. Here is a link on how to compare versions using PostgreSQL.

pgr_startPoint

  • Availability: 2.0.0
  • Deprecated: 2.1.0
-> Resolution

Use PostGIS function ST_StartPoint instead.

pgr_endPoint

  • Availability: 2.0.0
  • Deprecated: 2.1.0
-> Resolution

Use PostGIS function ST_EndPoint instead.

Top

Clone this wiki locally
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.