<div dir="ltr"><div dir="ltr"><div dir="ltr"><div>The data is here.</div><div><br></div><div><pre style="box-sizing:border-box;font-family:Menlo,Monaco,"Courier New",monospace;margin-top:0px;margin-bottom:0px;padding:1rem;font-size:0.8rem;line-height:1.4;word-break:break-all;background-color:rgb(249,249,249);color:rgb(81,81,81)"><code class="gmail-language-sql" style="box-sizing:border-box;font-family:Menlo,Monaco,"Courier New",monospace;padding:0px;font-size:16px;color:inherit;background-color:transparent;border-radius:3px"><span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">CREATE</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">TABLE</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-p" style="box-sizing:border-box">(</span>
<span class="gmail-n" style="box-sizing:border-box">segment</span> <span class="gmail-n" style="box-sizing:border-box">geometry</span><span class="gmail-p" style="box-sizing:border-box">,</span>
<span class="gmail-n" style="box-sizing:border-box">id</span> <span class="gmail-nb" style="box-sizing:border-box;color:rgb(51,102,102)">integer</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">primary</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">key</span>
<span class="gmail-p" style="box-sizing:border-box">);</span>
<span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INSERT</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INTO</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">VALUES</span> <span class="gmail-p" style="box-sizing:border-box">(</span><span class="gmail-s1" style="box-sizing:border-box;color:rgb(204,51,0)">'LINESTRING(1 1, 0 0)'</span><span class="gmail-p" style="box-sizing:border-box">,</span> <span class="gmail-mi" style="box-sizing:border-box;color:rgb(255,102,0)">1</span><span class="gmail-p" style="box-sizing:border-box">);</span>
<span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INSERT</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INTO</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">VALUES</span> <span class="gmail-p" style="box-sizing:border-box">(</span><span class="gmail-s1" style="box-sizing:border-box;color:rgb(204,51,0)">'LINESTRING(2 1, 1 1)'</span><span class="gmail-p" style="box-sizing:border-box">,</span> <span class="gmail-mi" style="box-sizing:border-box;color:rgb(255,102,0)">2</span><span class="gmail-p" style="box-sizing:border-box">);</span>
<span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INSERT</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INTO</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">VALUES</span> <span class="gmail-p" style="box-sizing:border-box">(</span><span class="gmail-s1" style="box-sizing:border-box;color:rgb(204,51,0)">'LINESTRING(1 2, 1 1)'</span><span class="gmail-p" style="box-sizing:border-box">,</span> <span class="gmail-mi" style="box-sizing:border-box;color:rgb(255,102,0)">3</span><span class="gmail-p" style="box-sizing:border-box">);</span>
<span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INSERT</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INTO</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">VALUES</span> <span class="gmail-p" style="box-sizing:border-box">(</span><span class="gmail-s1" style="box-sizing:border-box;color:rgb(204,51,0)">'LINESTRING(3 1, 2 1)'</span><span class="gmail-p" style="box-sizing:border-box">,</span> <span class="gmail-mi" style="box-sizing:border-box;color:rgb(255,102,0)">4</span><span class="gmail-p" style="box-sizing:border-box">);</span>
<span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INSERT</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INTO</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">VALUES</span> <span class="gmail-p" style="box-sizing:border-box">(</span><span class="gmail-s1" style="box-sizing:border-box;color:rgb(204,51,0)">'LINESTRING(3 2, 2 1)'</span><span class="gmail-p" style="box-sizing:border-box">,</span> <span class="gmail-mi" style="box-sizing:border-box;color:rgb(255,102,0)">5</span><span class="gmail-p" style="box-sizing:border-box">);</span>
<span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INSERT</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INTO</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">VALUES</span> <span class="gmail-p" style="box-sizing:border-box">(</span><span class="gmail-s1" style="box-sizing:border-box;color:rgb(204,51,0)">'LINESTRING(2 3, 1 2)'</span><span class="gmail-p" style="box-sizing:border-box">,</span> <span class="gmail-mi" style="box-sizing:border-box;color:rgb(255,102,0)">6</span><span class="gmail-p" style="box-sizing:border-box">);</span>
<span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INSERT</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INTO</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">VALUES</span> <span class="gmail-p" style="box-sizing:border-box">(</span><span class="gmail-s1" style="box-sizing:border-box;color:rgb(204,51,0)">'LINESTRING(1 3, 1 2)'</span><span class="gmail-p" style="box-sizing:border-box">,</span> <span class="gmail-mi" style="box-sizing:border-box;color:rgb(255,102,0)">7</span><span class="gmail-p" style="box-sizing:border-box">);</span>
<span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INSERT</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INTO</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">VALUES</span> <span class="gmail-p" style="box-sizing:border-box">(</span><span class="gmail-s1" style="box-sizing:border-box;color:rgb(204,51,0)">'LINESTRING(4 2, 3 2)'</span><span class="gmail-p" style="box-sizing:border-box">,</span> <span class="gmail-mi" style="box-sizing:border-box;color:rgb(255,102,0)">8</span><span class="gmail-p" style="box-sizing:border-box">);</span>
<span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INSERT</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INTO</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">VALUES</span> <span class="gmail-p" style="box-sizing:border-box">(</span><span class="gmail-s1" style="box-sizing:border-box;color:rgb(204,51,0)">'LINESTRING(3 4, 2 3)'</span><span class="gmail-p" style="box-sizing:border-box">,</span> <span class="gmail-mi" style="box-sizing:border-box;color:rgb(255,102,0)">9</span><span class="gmail-p" style="box-sizing:border-box">);</span>
<span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INSERT</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INTO</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">VALUES</span> <span class="gmail-p" style="box-sizing:border-box">(</span><span class="gmail-s1" style="box-sizing:border-box;color:rgb(204,51,0)">'LINESTRING(2 4, 2 3)'</span><span class="gmail-p" style="box-sizing:border-box">,</span> <span class="gmail-mi" style="box-sizing:border-box;color:rgb(255,102,0)">10</span><span class="gmail-p" style="box-sizing:border-box">);</span>
<span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INSERT</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INTO</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">VALUES</span> <span class="gmail-p" style="box-sizing:border-box">(</span><span class="gmail-s1" style="box-sizing:border-box;color:rgb(204,51,0)">'LINESTRING(1 4, 1 3)'</span><span class="gmail-p" style="box-sizing:border-box">,</span> <span class="gmail-mi" style="box-sizing:border-box;color:rgb(255,102,0)">11</span><span class="gmail-p" style="box-sizing:border-box">);</span>
<span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INSERT</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INTO</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">VALUES</span> <span class="gmail-p" style="box-sizing:border-box">(</span><span class="gmail-s1" style="box-sizing:border-box;color:rgb(204,51,0)">'LINESTRING(4 3, 4 2)'</span><span class="gmail-p" style="box-sizing:border-box">,</span> <span class="gmail-mi" style="box-sizing:border-box;color:rgb(255,102,0)">12</span><span class="gmail-p" style="box-sizing:border-box">);</span>
<span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INSERT</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INTO</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">VALUES</span> <span class="gmail-p" style="box-sizing:border-box">(</span><span class="gmail-s1" style="box-sizing:border-box;color:rgb(204,51,0)">'LINESTRING(4 4, 3 4)'</span><span class="gmail-p" style="box-sizing:border-box">,</span> <span class="gmail-mi" style="box-sizing:border-box;color:rgb(255,102,0)">13</span><span class="gmail-p" style="box-sizing:border-box">);</span>
<span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">CREATE</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">INDEX</span> <span class="gmail-n" style="box-sizing:border-box">network_gix</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">ON</span> <span class="gmail-n" style="box-sizing:border-box">network</span> <span class="gmail-k" style="box-sizing:border-box;color:rgb(0,102,153)">USING</span> <span class="gmail-n" style="box-sizing:border-box">GIST</span> <span class="gmail-p" style="box-sizing:border-box">(</span><span class="gmail-n" style="box-sizing:border-box">segment</span><span class="gmail-p" style="box-sizing:border-box">);</span></code></pre></div><br><div class="gmail_quote"><div class="gmail_attr">Add insert into network values ('linestring(1 1, 1 0)', 14);</div><div class="gmail_attr"><br></div><div class="gmail_attr">or insert into network values ('linestring(1 1, 0 1)', 15);</div><div class="gmail_attr">Source:</div><div class="gmail_attr"><a href="http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html">Network Walking in PostGIS · Paul Ramsey (cleverelephant.ca)</a><br></div><div class="gmail_attr"><br></div><div class="gmail_attr"><br></div><div class="gmail_attr">Regards,</div><div class="gmail_attr"><br></div><div class="gmail_attr">David</div><div dir="ltr" class="gmail_attr"><br></div><div dir="ltr" class="gmail_attr"><br></div><div dir="ltr" class="gmail_attr"><br></div><div dir="ltr" class="gmail_attr"><br></div><div dir="ltr" class="gmail_attr">On Mon, 17 Jul 2023 at 23:12, Imre Samu <<a href="mailto:pella.samu@gmail.com">pella.samu@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">> How about finding out all possible route paths.?<div><br><div>I'm sorry, I don't fully understand your question as it could be interpreted in several ways.<br>Could you provide a small set of test data and specify the kind of output you're looking for?<br></div></div><div><br></div><div>Thanks,</div><div> Imre</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Shaozhong SHI <<a href="mailto:shishaozhong@gmail.com" target="_blank">shishaozhong@gmail.com</a>> ezt írta (időpont: 2023. júl. 17., H, 21:24):<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">How about finding out all possible route paths.?<div>Regards, david<br><br>On Monday, 17 July 2023, Imre Samu <<a href="mailto:pella.samu@gmail.com" target="_blank">pella.samu@gmail.com</a>> wrote:<br><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>> Which one is the best example for finding all paths with recursive query?</div><div><br></div><div>What type of graph are you working with?<br></div><div><br></div><div>1.) <br></div>You can check Yugabyte (PostgreSQL compatible) documentation for pure SQL recursive-graph solutions for :<div>- Undirected cyclic graph<br>- Directed cyclic graph<br>- Directed acyclic graph<br>- Rooted tree<br>- Unique containing paths<br><div><a href="https://docs.yugabyte.com/preview/api/ysql/the-sql-language/with-clause/traversing-general-graphs/common-code/" target="_blank">https://docs.yugabyte.com/preview/api/ysql/the-sql-language/with-clause/traversing-general-graphs/common-code/</a><br></div></div><div>I think there's a pretty good summary for solving basic graph problems with SQL, which could potentially help you understand your problem as well and find a solution for it...<br></div><div><br></div><div>2.) </div><div>Or use pgr_KSP - if you're only interested in the final result and you're not attached to the purely recursive SQL solution. <br></div><div>And it's much more optimal for large graphs as well. </div><div>Here, you just need to provide a large enough K value (e.g., ~ maximum (integer/bigint) value) and then you get all possible paths. </div><div>Or if you're only interested in the top 2, then set K=2.<br></div><div><br></div><div><a href="https://docs.pgrouting.org/latest/en/pgr_KSP.html" target="_blank">https://docs.pgrouting.org/latest/en/pgr_KSP.html</a><br></div><div><span style="font-family:"Helvetica Neue",Helvetica,Arial,sans-serif;font-size:14px"><font color="#0000ff">"The K shortest path routing algorithm based on Yen’s algorithm<b>. “K” is the number of shortest paths desired.</b>"</font></span><br></div><div><a href="https://en.wikipedia.org/wiki/K_shortest_path_routing" target="_blank">https://en.wikipedia.org/wiki/K_shortest_path_routing</a><br></div><div><br></div><div><span style="color:rgb(51,51,51);font-family:"Helvetica Neue",Helvetica,Arial,sans-serif;font-size:14px">regards,</span></div><div><span style="color:rgb(51,51,51);font-family:"Helvetica Neue",Helvetica,Arial,sans-serif;font-size:14px"> Imre</span></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Shaozhong SHI <<a href="mailto:shishaozhong@gmail.com" target="_blank">shishaozhong@gmail.com</a>> ezt írta (időpont: 2023. júl. 17., H, 9:01):<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">Which one is the best example for finding all paths with recursive query?<div><br></div><div>Regards,</div><div><br></div><div>David</div></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div>
</blockquote></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div></div></div></div>