[pgrouting-users] pass query results to routing function

[Hugh Kelley]

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)');


On Mon, May 9, 2022 at 4:41 PM Imre Samu <pella.samu at gmail.com> wrote:

> 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
>>
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/pgrouting-users
>


-- 
Hugh Kelley
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20220509/170ed37b/attachment.htm>