I need some help writing correctly queries to generate shortest path between multiple od pairs using the pg_trsp algorithm. It works well with a single od writted like this :
SELECT * FROM pgr_trsp (
'SELECT id::integer, source::integer, target::integer, cost, reverse_cost FROM edges_df',
97270,
97269,
True,
True,
'SELECT to_cost, target_id::int4, via_path from restrictions'
) a
However, when I tried to generalized the query using another table as a source and target, I have the following error : pgr_trsp(unknown, bigint[], bigint[], boolean, boolean, unknown).
SELECT * FROM pgr_trsp (
'SELECT id::integer, source::integer, target::integer, cost, reverse_cost FROM edges_df',
array(SELECT vertex_id FROM od_vertex_id),
array(SELECT vertex_id FROM od_vertex_id),
True,
True,
'SELECT to_cost, target_id::int4, via_path from restrictions'
)
I tried the following command to make sure my input data was well formatted and it seems correct.
select array(SELECT vertex_id FROM od_vertex_id)
I also tried an alternative where I specify myself the array with id of vertices but still got the same error. It confirms my issue is not related to my od_vertex_id table.
SELECT * FROM pgr_trsp (
'SELECT id::integer, source::integer, target::integer, cost, reverse_cost FROM edges_df',
97270,
ARRAY[97269, 27268],
True,
True,
'SELECT to_cost, target_id::int4, via_path from restrictions'
) a
According to the documentation https://docs.pgrouting.org/3.0/en/pgr_dijkstra.html or another question here https://gis.stackexchange.com/questions/207813/how-to-use-pgr-dijkstra-many-to-many, I feel like I am in the good direction but ain't working yet.
Any help appreciated !
Thanks
Edited on 2024-11-06 as I finally managed to make it work !
SELECT a.* FROM pgr_trsp(
$$SELECT id::integer, source::integer, target::integer, cost, reverse_cost FROM edges_df$$,
$$select to_cost as cost, via_path::int4 as target_id, ARRAY[target_id::integer] as path
from restrictions$$,
$$ select * from od_matrix where pair_rank>={rank_min} and pair_rank<={rank_max}$$
) a
First part is the edge network, second part is the turning restrictions and last is a table with od pairs for each row. rank_min and max are additional parameters to go through the od table and keep the number of request to an operable size.
Finally found the answer after many tryouts and errors. It looks like the boolean parameters for direction / cost are not needed when doing the request with many to many. Additionally the parameters are required in this specific order :
As I have hundred thousands of od pairs, I added an extra parameter to limit the number of od pairs within the sql query so that the output size stays reasonable. The query is embedded in a bigger procedure so that the output (a table of edges) is inserted in a temp table. Then, this temp table is merged to edges properties and converted into paths.
SELECT a.* FROM pgr_trsp(
$$SELECT id::integer, source::integer, target::integer, cost, reverse_cost FROM edges_df$$,
$$select to_cost as cost, target_id::int4, ARRAY[path::integer] as path from restrictions$$,
$$ select * from od_matrix where pair_rank>={rank_min} and pair_rank<={rank_max}$$
) a