[pgrouting-users] pass query results to routing function

pgRouting pgrouting-users
, ,
1

Hi,

I’m wondering if anyone has an example of a query that calculates routes between pairs of nodes that is a subset of all the possible pairs in the network.

I have a network with ~5297 nodes, for ~28 million possible pairs. I could calculate routes for all of these pairs and then use the subset of routes that i need, but it seems that there could be a way to calculate only routes for the ~17 thousand pairs I actually care about. If possible I would filter to only calculate pairs where the origin node is ST_DWITHIN(a.geom, b.geom, 250), for example.

is there a way to pass the results of a query to the pgr_dijkstra() function? I haven’t seen an example of that as I’ve read through the docs. is there a way to apply a condition like st_diwthin() to the arrays passed to the function?

thanks for any advice or suggestions, hope everyone’s week is off to a good start.

Hugh

2

To follow up, the structure below seems to work fairly well for this. would still be curious to see better options if anyone knows of any.

This is a little awkward since it ends up returning a results table that’s a single column that’s a pseudo type record.

drop table if exists scratch.routing_pairs;

create table scratch.routing_pairs as (
select
dp1.tdg_id as origin_id,
dp1.nearest_net_node as origin_node,
dp2.tdg_id as dest_id,
dp2.nearest_net_node as dest_node
from
automated.doors_and_parking dp1
left join automated.doors_and_parking dp2 on st_dwithin(dp1.geom, dp2.geom, 250)
limit
100
);

select
pgr_dijkstra(
‘SELECT tdg_id as id, source, target, length_cost as cost FROM automated.ped_network as edge_table where source is not null and target is not null’,
origin_node,
dest_node,
false
)
from
scratch.routing_pairs
;

···

Hugh Kelley

3

Hi Hugh,

… query that calculates routes between pairs of nodes that is a subset of all the possible pairs in the network.

Have you tried the “combinations SQL” method?
https://docs.pgrouting.org/latest/en/pgr_dijkstra.html#combinations

SELECT * FROM pgr_dijkstra(
‘SELECT id, source, target, cost, reverse_cost FROM edge_table’,
‘SELECT * FROM combinations_table’,
FALSE
);

Regards,
Imre

Hugh Kelley <hghklly@gmail.com> ezt írta (időpont: 2022. máj. 9., H, 21:43):

To follow up, the structure below seems to work fairly well for this. would still be curious to see better options if anyone knows of any.

This is a little awkward since it ends up returning a results table that’s a single column that’s a pseudo type record.

drop table if exists scratch.routing_pairs;

create table scratch.routing_pairs as (
select
dp1.tdg_id as origin_id,
dp1.nearest_net_node as origin_node,
dp2.tdg_id as dest_id,
dp2.nearest_net_node as dest_node
from
automated.doors_and_parking dp1
left join automated.doors_and_parking dp2 on st_dwithin(dp1.geom, dp2.geom, 250)
limit
100
);

select
pgr_dijkstra(
‘SELECT tdg_id as id, source, target, length_cost as cost FROM automated.ped_network as edge_table where source is not null and target is not null’,
origin_node,
dest_node,
false
)
from
scratch.routing_pairs
;

On Mon, May 9, 2022 at 3:22 PM Hugh Kelley <hghklly@gmail.com> wrote:

Hi,

I’m wondering if anyone has an example of a query that calculates routes between pairs of nodes that is a subset of all the possible pairs in the network.

I have a network with ~5297 nodes, for ~28 million possible pairs. I could calculate routes for all of these pairs and then use the subset of routes that i need, but it seems that there could be a way to calculate only routes for the ~17 thousand pairs I actually care about. If possible I would filter to only calculate pairs where the origin node is ST_DWITHIN(a.geom, b.geom, 250), for example.

is there a way to pass the results of a query to the pgr_dijkstra() function? I haven’t seen an example of that as I’ve read through the docs. is there a way to apply a condition like st_diwthin() to the arrays passed to the function?

thanks for any advice or suggestions, hope everyone’s week is off to a good start.

Hugh

–

Hugh Kelley

Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/pgrouting-users

4

thanks Imre, I had looked at that but hadn’t seen this query in the examples showing how it’s meant to be structured. That’s exactly what I needed.

SELECT * FROM pgr_dijkstra(
    'SELECT id, source, target, cost, reverse_cost FROM edge_table',
    'SELECT * FROM (VALUES (2, 3), (2, 5), (11, 3), (11, 5)) AS combinations (source, target)'
);
···

Hugh Kelley