<div dir="ltr"><div>Hi Regina,</div><div><br></div>Thanks for the note !<div><br></div><div>Nicolas</div></div><div class="gmail_extra"><br><div class="gmail_quote">On 3 June 2018 at 03:59, Regina Obe <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div lang="EN-US" link="blue" vlink="purple"><div class="m_3574011201267776053WordSection1"><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Side note if you are using PostGIS >= 2.2 + PostgreSQL 9.5+, then no need to compute distance twice because <-> would give you true distance. So you can shorten a bit (and should improve speed slightly) with<u></u><u></u></span></p><span class=""><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal">SELECT<u></u><u></u></p><p class="MsoNormal"> <a href="http://p1.id" target="_blank">p1.id</a>,<u></u><u></u></p><p class="MsoNormal"> <a href="http://t.id" target="_blank">t.id</a> AS id2,<u></u><u></u></p><p class="MsoNormal"> t.dist<u></u><u></u></p><p class="MsoNormal">FROM parc p1<u></u><u></u></p><p class="MsoNormal"> CROSS JOIN LATERAL<u></u><u></u></p><p class="MsoNormal"> (SELECT<u></u><u></u></p><p class="MsoNormal"> <a href="http://p2.id" target="_blank">p2.id</a>,<u></u><u></u></p></span><p class="MsoNormal"> p1.geom <-> p2.geom AS dist<u></u><u></u></p><span class=""><p class="MsoNormal"> FROM parc p2<u></u><u></u></p><p class="MsoNormal"> WHERE <a href="http://p1.id" target="_blank">p1.id</a> <> <a href="http://p2.id" target="_blank">p2.id</a><u></u><u></u></p></span><p class="MsoNormal"> ORDER BY dist<u></u><u></u></p><span class=""><p class="MsoNormal"> LIMIT 1) AS t<u></u><u></u></p><p class="MsoNormal">ORDER BY <a href="http://p1.id" target="_blank">p1.id</a>, t.dist;<u></u><u></u></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p></span><div style="border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt"><div><div style="border:none;border-top:solid #e1e1e1 1.0pt;padding:3.0pt 0in 0in 0in"><p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"> postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@<wbr>lists.osgeo.org</a>] <b>On Behalf Of </b>Vispo Leblanc<br><b>Sent:</b> Thursday, May 31, 2018 12:05 PM<br><b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><b>Subject:</b> Re: [postgis-users] Increase KNN efficiency<u></u><u></u></span></p></div></div><div><div class="h5"><p class="MsoNormal"><u></u> <u></u></p><div><div><p class="MsoNormal">Thanks Nicolas, this is <b><u>way better</u></b>, I guess my previous query was calculating all distance between all elements. <u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Thanks again! <u></u><u></u></p></div><p class="MsoNormal"><u></u> <u></u></p><div><div><p class="MsoNormal">On Thu, May 31, 2018 at 2:47 AM Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>> wrote:<u></u><u></u></p></div><blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in"><div><p class="MsoNormal">Hi,<u></u><u></u></p><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Put the KNN operator in a LATERAL subquery, using LIMIT x to limit NN to x elements:<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><div><p class="MsoNormal">SELECT<u></u><u></u></p></div><div><p class="MsoNormal"> <a href="http://p1.id" target="_blank">p1.id</a>,<u></u><u></u></p></div><div><p class="MsoNormal"> <a href="http://t.id" target="_blank">t.id</a> AS id2,<u></u><u></u></p></div><div><p class="MsoNormal"> t.dist<u></u><u></u></p></div><div><p class="MsoNormal">FROM parc p1<u></u><u></u></p></div><div><p class="MsoNormal"> CROSS JOIN LATERAL<u></u><u></u></p></div><div><p class="MsoNormal"> (SELECT<u></u><u></u></p></div><div><p class="MsoNormal"> <a href="http://p2.id" target="_blank">p2.id</a>,<u></u><u></u></p></div><div><p class="MsoNormal"> st_distance(p1.geom, p2.geom) AS dist<u></u><u></u></p></div><div><p class="MsoNormal"> FROM parc p2<u></u><u></u></p></div><div><p class="MsoNormal"> WHERE <a href="http://p1.id" target="_blank">p1.id</a> <> <a href="http://p2.id" target="_blank">p2.id</a><u></u><u></u></p></div><div><p class="MsoNormal"> ORDER BY p1.geom <-> p2.geom<u></u><u></u></p></div><div><p class="MsoNormal"> LIMIT 1) AS t<u></u><u></u></p></div><div><p class="MsoNormal">ORDER BY <a href="http://p1.id" target="_blank">p1.id</a>, t.dist;<u></u><u></u></p></div></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Nicolas<u></u><u></u></p></div></div><div><p class="MsoNormal"><u></u> <u></u></p><div><p class="MsoNormal">On 30 May 2018 at 23:07, Vispo Leblanc <<a href="mailto:vispoleblanc@gmail.com" target="_blank">vispoleblanc@gmail.com</a>> wrote:<u></u><u></u></p><blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in"><div><div><p class="MsoNormal">Hello PostGIS users, <u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">I am looking for a way to increase the speed of my query with KNN. <u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">I have two tables with millions of points in each table. I would like to find the nearest neighbor (only one) of my points in table A with my points in table B.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">I wrote the following query: <u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">WITH index_query as (<br> SELECT<br> <a href="http://t1.id" target="_blank">t1.id</a>,<br> t1.geom AS geom,<br> t2.geom AS geom2, <br> t1.geom <-> t2.geom AS distance<br> FROM research_scrapper.table1 t1, <br> research_scrapper.table2 t2<br> ORDER BY t1.geom <-> t2.geom<br>)<br>SELECT tbl.cas_id, tbl.geom <br>FROM index_query tbl<br>INNER JOIN<br> (<br> SELECT geom2, <br> MIN(distance) as distance<br> FROM index_query <br> GROUP BY geom2<br> )tbl1<br> ON tbl1.geom_generated=tbl.geom_<wbr>generated<br> AND tbl1.distance = tbl.distance<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">This is taking forever, would you have any suggestions on how to increase the efficiency?<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Thanks for your help,<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div></div><p class="MsoNormal"><br>______________________________<wbr>_________________<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" target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><u></u><u></u></p></blockquote></div><p class="MsoNormal"><u></u> <u></u></p></div><p class="MsoNormal">______________________________<wbr>_________________<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" target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><u></u><u></u></p></blockquote></div></div></div></div></div></div></div><br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">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/<wbr>mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>