[SQL] pgRouting - many to many with turn restrictions

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.

Continue reading...