<div dir="ltr">Hi Hugh,<div><br></div><div>> ... 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><b>Have you tried the "combinations SQL" method?</b></div><div><a href="https://docs.pgrouting.org/latest/en/pgr_dijkstra.html#combinations">https://docs.pgrouting.org/latest/en/pgr_dijkstra.html#combinations</a><br></div><div>SELECT * FROM pgr_dijkstra(<br> 'SELECT id, source, target, cost, reverse_cost FROM edge_table',<br> <b><font color="#0000ff"> 'SELECT * FROM combinations_table',</font></b><br> FALSE<br>);<br></div><div><br></div><div>Regards,</div><div> Imre</div><div><br></div><div><br></div><div><br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Hugh Kelley <<a href="mailto:hghklly@gmail.com">hghklly@gmail.com</a>> ezt írta (időpont: 2022. máj. 9., H, 21:43):<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>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-wrap"><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" target="_blank">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"><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>
_______________________________________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/pgrouting-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/pgrouting-users</a><br>
</blockquote></div>