<div dir="ltr"><div>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. <br></div><div><br></div><div>This is a little awkward since it ends up returning a results table that's a single column that's a pseudo type record. <br></div><div><br></div><div>
<div style="color:rgb(212,212,212);background-color:rgb(30,30,30);font-family:Consolas,"Courier New",monospace;font-weight:normal;font-size:14px;line-height:19px;white-space:pre"><div><span style="color:rgb(86,156,214)">drop</span><span style="color:rgb(212,212,212)"> </span><span style="color:rgb(86,156,214)">table</span><span style="color:rgb(212,212,212)"> </span><span style="color:rgb(86,156,214)">if</span><span style="color:rgb(212,212,212)"> </span><span style="color:rgb(86,156,214)">exists</span><span style="color:rgb(212,212,212)"> scratch.routing_pairs;</span></div><br><div><span style="color:rgb(86,156,214)">create</span><span style="color:rgb(212,212,212)"> </span><span style="color:rgb(86,156,214)">table</span><span style="color:rgb(212,212,212)"> scratch.routing_pairs </span><span style="color:rgb(86,156,214)">as</span><span style="color:rgb(212,212,212)"> (</span></div><div><span style="color:rgb(212,212,212)"> </span><span style="color:rgb(86,156,214)">select</span></div><div><span style="color:rgb(212,212,212)"> dp1.tdg_id </span><span style="color:rgb(86,156,214)">as</span><span style="color:rgb(212,212,212)"> origin_id,</span></div><div><span style="color:rgb(212,212,212)"> dp1.nearest_net_node </span><span style="color:rgb(86,156,214)">as</span><span style="color:rgb(212,212,212)"> origin_node,</span></div><div><span style="color:rgb(212,212,212)"> dp2.tdg_id </span><span style="color:rgb(86,156,214)">as</span><span style="color:rgb(212,212,212)"> dest_id,</span></div><div><span style="color:rgb(212,212,212)"> dp2.nearest_net_node </span><span style="color:rgb(86,156,214)">as</span><span style="color:rgb(212,212,212)"> dest_node</span></div><div><span style="color:rgb(212,212,212)"> </span><span style="color:rgb(86,156,214)">from</span></div><div><span style="color:rgb(212,212,212)"> automated.doors_and_parking dp1</span></div><div><span style="color:rgb(212,212,212)"> </span><span style="color:rgb(86,156,214)">left join</span><span style="color:rgb(212,212,212)"> automated.doors_and_parking dp2 </span><span style="color:rgb(86,156,214)">on</span><span style="color:rgb(212,212,212)"> st_dwithin(dp1.geom, dp2.geom, </span><span style="color:rgb(181,206,168)">250</span><span style="color:rgb(212,212,212)">)</span></div><div><span style="color:rgb(212,212,212)"> </span><span style="color:rgb(86,156,214)">limit</span></div><div><span style="color:rgb(212,212,212)"> </span><span style="color:rgb(181,206,168)">100</span></div><div><span style="color:rgb(212,212,212)">);</span></div><br><div><span style="color:rgb(86,156,214)">select</span></div><div><span style="color:rgb(212,212,212)"> pgr_dijkstra(</span></div><div><span style="color:rgb(212,212,212)"> </span><span style="color:rgb(206,145,120)">'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'</span><span style="color:rgb(212,212,212)">,</span></div><div><span style="color:rgb(212,212,212)"> origin_node,</span></div><div><span style="color:rgb(212,212,212)"> dest_node,</span></div><div><span style="color:rgb(212,212,212)"> false</span></div><div><span style="color:rgb(212,212,212)"> )</span></div><div><span style="color:rgb(86,156,214)">from</span></div><div><span style="color:rgb(212,212,212)"> scratch.routing_pairs</span></div><div><span style="color:rgb(212,212,212)"> ;</span></div></div>
</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, May 9, 2022 at 3:22 PM Hugh Kelley <<a href="mailto:hghklly@gmail.com">hghklly@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>Hi,</div><div><br></div><div><br></div><div>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. <br></div><div><br></div><div>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. <br></div><div><br></div><div>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?</div><div><br></div><div>thanks for any advice or suggestions, hope everyone's week is off to a good start. <br></div><div><br></div><div>Hugh<br></div></div>
</blockquote></div><br clear="all"><br>-- <br><div dir="ltr" class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div dir="ltr">Hugh Kelley <br><div><br></div></div></div></div></div></div></div></div>