sqlpostgresqlgispgroutingosm2pgsql

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)

Query results

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.

Solution

  • 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 :

    1. the edge table,
    2. the turning restrictions table where target_id is an integer and path an array of integer a,
    3. the od pair table.

    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