Add support for passing bigint as ids and returning them

[woodbri]

pgrouting does NOT support passing bigint or numeric types as ids. And if supported we would want to return the input type of columns in the results.

pgr_test=# SELECT * FROM pgr_tsp('SELECT id AS source_id, x, y FROM vertex_table where id in (2,7,11,8) ', '2,7,11,8', 7);
 seq | id1 | id2 |       cost
-----+-----+-----+------------------
   0 |   7 |   1 |                1
   1 |   2 |   2 | 1.41421353816986
   2 |   8 |   3 |                1
   3 |  11 |   4 | 1.41421353816986
(4 rows)

pgr_test=# SELECT * FROM pgr_tsp('SELECT id::bigint AS source_id, x, y FROM vertex_table where id in (2,7,11,8) ', '2,7,11,8', 7);
 seq |     id1     | id2 |       cost
-----+-------------+-----+------------------
   0 | -1185000292 |   1 | 1.41421353816986
   1 | -1185000020 |   2 |                1
   2 | -1184999884 |   3 | 1.41421353816986
   3 | -1185000156 |   4 |                1
(4 rows)

pgr_test=# SELECT * FROM pgr_tsp('SELECT id::numeric AS source_id, x, y FROM vertex_table where id in (2,7,11,8) ', '2,7,11,8', 7);
 seq |     id1     | id2 |       cost
-----+-------------+-----+------------------
   0 | -1185000292 |   1 | 1.41421353816986
   1 | -1185000020 |   2 |                1
   2 | -1184999884 |   3 | 1.41421353816986
   3 | -1185000156 |   4 |                1
(4 rows)

[cvvergara]

pgr_ksp is ready c and c++ code can accept bigint, int, smallint for the id's on Fixed with commit 5cdd106ebe but: need to be hand-enabled until all other functions support it not tested on 32bit.

[cvvergara]

That would mean go thru all the C and C++ code and change everything that applies to long, int64_t (better I think I haven't tested long and int64_t on 32 bit), then change the pgr_costresult structure to spit out bigint where appropiate. Might break users applications. create function foo( bar int) ....

select foo(id) , cost from pgr_function( .....)

ERROR, foo(bigint) not defined

[cvvergara]

Forgot to mention that also the sql based code like nodeNetwork, createTopology would need to be changed and what about the osm2pgrouting?

[manikanta-kondeti]

I think that needs to be changed as well. OSM data has node and edge ids that can not be held in an integer field.

[woodbri]

I think this is too risky to do as part of 2.1.0 and should get pushed out to 2.2.0 or beyond. This is a major effort and highly disruptive to the existing code base. That said it can be done in a parallel branch and when we think it is ready and tested we can then look at merging it into the main stream. @cvvergara has outlined nicely the risks above.

Changing this to 2.2.0 Milestone

[cvvergara]

New functionality (in general) do not have this issue.

I am making a table of functions that have this issue and when it was solved

version	function	fixed on	with function
2.0	pgr_dijkstra	2.1	pgr_dijkstra
2.0	pgr_ksp	2.1	pgr_ksp
2.0	pgr_drivingDistance	2.1	pgr_drivingDistance
2.0	pgr_apspJohnson	2.2	pgr_Johnson
2.0	pgr_apspWarshall	2.2	pgr_floydWarshall
2.0	pgr_astar	2.3	pgr_astar
2.0	pgr_bdAstar	WIP 2.5	pgr_bdAstar
2.0	pgr_bdDijkstra	2.4	pgr_bdDijkstra
2.0	pgr_kDijkstraPath	2.2	pgr_dijkstra
2.0	pgr_kDijkstraCost	2.2	pgr_dijkstraCost
2.0	pgr_tsp	2.3	pgr_tsp & pgr_eucledianTSP
2.0	pgr_trsp	3.4	pgr_trsp & pgr_trsp_withPoints
2.0	pgr_alphaShape	--	NA
2.0	pgr_pointsAsPolygon	--	NA
2.0	pgr_createVerticesTable	--	NA
2.0	pgr_createTopology	--	NA
2.0	pgr_createVerticesTable	--	NA
2.0	pgr_analyzeGraph	--	NA
2.0	pgr_analyzeOneway	--	NA
2.0	pgr_nodeNetwork	--	NA
2.1	pgr_trspViaVertices	3.4	pgr_trspVia
2.1	pgr_trspViaEdges	3.4	pgr_trspVia_withPoints
2.1	pgr_vrpOneDepot	--	NA
2.1	pgr_vrppdtw	WIP 2.5	NA

WIP = work in progres TBD = To be defined NA = Not applicable

[cvvergara]

only TRSP remains with this issue

[ziXet]

Is there any update for TRSP?

[cvvergara]

Work has been done for TRSP on develop branch for version 3.4 (due to be released on September/October 2022:

2238

Please check the documentation https://docs.pgrouting.org/dev/en/TRSP-family.html and the migration guide: https://docs.pgrouting.org/dev/en/trsp_migration.html

FYI @ziXet