Skip to content

Migration Guide

Jump to bottom Edit New page
Vicky Vergara edited this page Mar 15, 2017 · 57 revisions

Table of Contents

Migrating from 2.3 to 2.4

Bidirectional Functions

pgr_bdDijstra

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_bdDijkstra(
    '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_bdDijkstra(
    'SELECT id, source, target, cost, x1, y1, x2, y2
     FROM edge_table',
    4, 10, false);

Convenience Functions 1

pgr_pointsToVids

  • Availability: 2.1.0
  • Deprecated: 2.3.0
  • No replacement

what to do

  • postGIS has ST_DWithin
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

How to detect that it needs migration

  • 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[][]

What to do

  • 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

What to do

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

Top

pgr_TSP with euclidean distances

  • Availability: 2.0
  • Deprecated signature: 2.2
  • Has replacement

How to detect that it needs migration

  • When the first parameter is of type text
  • When the result columns are (seq, id1, id2, cost)

What to do

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

Top

Convenience Functions

pgr_textToPoints

  • Availability: 2.1.0
  • Deprecated: 2.3.0
  • No replacement

what to do

PostGIS has ST_GeomFromText

Nothing

pgr_pointsToDMatrix

  • Availability: 2.1.0
  • Deprecated: 2.3.0
  • No replacement

what to do

  • When migrating pgr_TSP use pgr_eucledianTSP

pgr_vidsToDMatrix

  • Availability: 2.1.0
  • Deprecated: 2.3.0
  • No replacement

what to do

  • When migrating pgr_TSP use pgr_dijkstraCost

Top

pgr_flipGeometries

  • Availability: 2.1.0
  • Deprecated: 2.3.0
  • No replacement

what to do

Top

Migrating from 2.1 to 2.2

All Pairs Functions

Top

pgr_kDijkstraPath

  • Availability: 2.0.0
  • Deprecated: 2.2.0
  • Has a replacement

How to detect that it needs migration

  • When the name is pgr_kDijkstraPath
  • When the result columns are (seq, id1, id2, id3, cost)

What to do

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

Top

pgr_kDijkstraCost

  • Availability: 2.0.0
  • Deprecated: 2.2.0
  • Has a replacement

How to detect that it needs migration

  • When the name is pgr_kDijkstraCost
  • When the output columns are (seq, id1, id2, cost)

What to do

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

Top

pgr_apspJohnson

  • Availability: 2.0.0
  • Deprecated: 2.2.0
  • Has a replacement

How to detect that it needs migration

  • 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

What to do

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

Top

pgr_apspWarshall

  • Availability: 2.0.0
  • Deprecated: 2.2.0
  • Has a replacement

How to detect that it needs migration

  • 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 pgr_apspWarshall ends with two boolean values
  • When the sequence starts with 0
  • Only worked for directed graph

What to do

  • 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

Top

pgr_dijkstra

  • Availability: 2.0.0
  • Signature Changed: 2.1.0
  • A parameter was removed

How to detect that it needs migration

  • When the output columns are (seq, id1, id2, cost)
  • When the pgr_dijkstra ends with two boolean values
  • When the inner query columns need casting
  • When the sequence starts with 0

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

Top

pgr_KSP

  • Availability: 2.0.0
  • Signature Changed: 2.1.0
  • A parameter was renamed
  • Functionality of the renamed parameter changed

How to detect that it needs migration

  • 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

What to do

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

Top

pgr_drivingDistance

  • Availability: 2.0.0
  • Signature Changed: 2.1.0
  • A parameter was removed

How to detect that it needs migration

  • When the output columns are (seq, id1, id2, cost)
  • When the pgr_drivingDistance ends with two boolean values
  • When the inner query columns need casting
  • When the sequence starts with 0

What to do

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

Top

pgr_createTopology

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

What to do

Nothing

Top

pgr_alphaShape

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

What to do

Nothing

Top

Developers functions

The following functions are deprecated and no longer maintained.

pgr_getColumnName

  • Availability: 2.0.0
  • Deprecated: 2.1.0

what to do

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

Top

pgr_getTableName

  • Availability: 2.0.0
  • Deprecated: 2.1.0

what to do

The parameters is the table name:

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

Top

pgr_isColumnIndexed

  • Availability: 2.0.0
  • Deprecated: 2.1.0

what to do

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

SELECT * FROM pg_indexes WHERE tablename = 'edge_table';

Top

pgr_isColumnInTable

  • Availability: 2.0.0
  • Deprecated: 2.1.0

what to do

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';

Top

pgr_quote_ident

  • Availability: 2.0.0
  • Deprecated: 2.1.0

what to do

Use postgreSQL function quote_ident instead.

Top

pgr_versionless

  • Availability: 2.0.0
  • Deprecated: 2.1.0

what to do

Here is a link on how to compare versions using postgreSQL

  • Use the method of your choice

Top

pgr_startPoint

  • Availability: 2.0.0
  • Deprecated: 2.1.0

what to do

  • Use postGIS function ST_StartPoint instead

Top

pgr_endPoint

  • Availability: 2.0.0
  • Deprecated: 2.1.0

what to do

Use postGIS function ST_EndPoint instead

Top

Contact: https://discourse.osgeo.org/c/pgrouting/pgrouting-dev

pgRouting Project

pgRouting Project

Quick Links

Clone this wiki locally