[pgrouting-users] pass query results to routing function

[Imre Samu]

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 at 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 at 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 at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/pgrouting-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20220509/a7e613fd/attachment-0001.htm>