<div dir="ltr"><div>Hi Regina,</div><div><br></div>On a lark, I also tested with 2.3.0dev r14538 (9.5rc1)<br><div><br></div><div>"POSTGIS="2.3.0dev r14538" GEOS="3.5.0-CAPI-1.9.0 r4084" SFCGAL="1.2.2" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="0.11.99" RASTER"<br></div><div><br></div><div>Same result.</div><div><br></div><div>Cheers,</div><div>Best,</div><div>Steve</div><div><br></div><div><br></div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Sun, Jan 3, 2016 at 9:34 AM, Stephen Mather <span dir="ltr"><<a href="mailto:stephen@smathermather.com" target="_blank">stephen@smathermather.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div>2.2.0 and RC1. Should I be trying 2.2 branch?</div><div><br></div><div><br></div><div>SELECT version();<br></div><div><br></div><div>"PostgreSQL 9.5rc1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit"<br></div><div><br></div><div><br></div><div>SELECT postgis_full_version();<br></div><div><br></div><div>"POSTGIS="2.2.0 r14208" GEOS="3.5.0-CAPI-1.9.0 r4084" SFCGAL="1.2.2" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="0.11.99" RASTER"<br></div><div><br></div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote"><div><div class="h5">On Sun, Jan 3, 2016 at 2:18 AM, Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span> wrote:<br></div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div class="h5"><div lang="EN-US" link="blue" vlink="purple"><div><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Steve,<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><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Haven't had a chance to look thru what you have here and test it out myself. <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><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">The issue I flagged in the ticket was fixed in PostgreSQL 9.5 code base upstream:<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><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><a href="http://www.postgresql.org/message-id/55630D9D.3090508@iki.fi" target="_blank">http://www.postgresql.org/message-id/55630D9D.3090508@iki.fi</a><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><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">It's my understanding that you should never get that notice again. So I'm a bit concerned you are. It's possible you found another area where this issue arises and is not handled.<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><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Are you running PostgreSQL 9.5RC1 and latest PostGIS 2.2 branch (or 2.2.0?).<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><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Thanks,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Regina<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><p class="MsoNormal" style="margin-left:.5in"><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-devel [mailto:<a href="mailto:postgis-devel-bounces@lists.osgeo.org" target="_blank">postgis-devel-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Stephen Mather<br><b>Sent:</b> Sunday, January 03, 2016 1:56 AM<br><b>To:</b> PostGIS Development Discussion <<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a>><br><b>Subject:</b> [postgis-devel] KNN and 9.5<u></u><u></u></span></p><div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p><div><div><p class="MsoNormal" style="margin-left:.5in">Hi All,<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">Short version:<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">Does Regina's comment on <a href="http://trac.osgeo.org/postgis/ticket/2703" target="_blank">http://trac.osgeo.org/postgis/ticket/2703</a> regarding <span style="font-size:10.0pt;color:black;background:#f7f7f7">ERROR: index returned tuples in wrong order</span> apply to any uses of indices in conjunction with KNN?<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">Long version:<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">Digging many of the 2.2 / 9.5 updates. Having fun on break playing with skeleton simplification and using KNN in the process. Yes. For those of you who know me, this is an obsession that won't die quietly.<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><a href="https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-19-26-am.png" target="_blank">https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-19-26-am.png</a><u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">I've got a function that does my KNN in the usual style, finds the nearest 2 points, and makes a line from them:<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><div><p class="MsoNormal" style="margin-left:.5in">CREATE OR REPLACE FUNCTION zz_knn_wonderful_points (geometry) RETURNS geometry AS $$<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">-- Here are my wonderful points to KNN search:<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">WITH index_query AS (<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> SELECT edge.geom AS geom<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> FROM (SELECT * FROM regions_dpoints WHERE gid = 882) AS edge<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">-- This is my query point<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> ORDER BY $1<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> <-><u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> edge.geom LIMIT 2<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> )<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">SELECT ST_MakeLine(geom) FROM index_query<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">$$ LANGUAGE SQL;<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">The intent is to implement angle values as an importance criterion for simplifying the skeleton as described at minute 2:20 in Balint Miklos video on the scale axis transform: <a href="http://balintmiklos.com/scale-axis/The_Scale_Axis_Picture_Show.mp4" target="_blank">http://balintmiklos.com/scale-axis/The_Scale_Axis_Picture_Show.mp4</a><u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">Actually, in the final function, I will likely directly calculate the angle and return that instead of the geometry, but I digress.<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">Regardless, when I use this function as is, it works great in drawing lines connecting the two nearest points to the vertex in the medial axis:<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">DROP TABLE IF EXISTS test CASCADE;<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">CREATE TABLE test AS<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">WITH returnline AS (<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> SELECT oid, gid,<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> ST_Union(<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> ST_MakeLine( ST_StartPoint(zz_knn_wonderful_points(subset.geom)), subset.geom),<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> ST_MakeLine( ST_EndPoint(zz_knn_wonderful_points(subset.geom)), subset.geom)<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> )<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> AS geom FROM<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> (SELECT * FROM regions_points WHERE oid = 882) subset<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">)<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">SELECT * FROM returnline<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">;<u></u><u></u></p></div></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">However, as implemented, this will not work in the case of overlapping geometries, such as Natural Earth's label polygon dataset "ne_10m_geography_regions_polys":<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><a href="https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-33-01-am.png" target="_blank">https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-33-01-am.png</a><u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">And so I add a constraint using an integer ID:<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><div><p class="MsoNormal" style="margin-left:.5in">CREATE OR REPLACE FUNCTION zz_knn(integer,geometry) RETURNS geometry AS $$<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">-- Here are my wonderful points to KNN search:<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">WITH index_query AS (<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> SELECT edge.geom AS geom<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> FROM (SELECT * FROM regions_dpoints WHERE gid = $1) AS edge<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">-- This is my query point<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> ORDER BY $2<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> <-><u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> edge.geom LIMIT 2<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> )<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">SELECT ST_MakeLine(geom) FROM index_query<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">$$ LANGUAGE SQL;<u></u><u></u></p></div></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">And now it takes much longer and fails with <u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">"<u></u><u></u></p></div><div><p style="margin-right:0in;margin-bottom:0in;margin-left:.5in;margin-bottom:.0001pt"><span style="font-size:9.0pt;font-family:"Monaco",serif">ERROR: index returned tuples in wrong order<u></u><u></u></span></p><p style="margin-right:0in;margin-bottom:0in;margin-left:.5in;margin-bottom:.0001pt"><span style="font-size:9.0pt;font-family:"Monaco",serif">CONTEXT: SQL function "zz_knn" statement 1<u></u><u></u></span></p><p style="margin-right:0in;margin-bottom:0in;margin-left:.5in;margin-bottom:.0001pt"><span style="font-size:9.0pt;font-family:"Monaco",serif">********** Error **********<u></u><u></u></span></p><p style="margin-right:0in;margin-bottom:0in;margin-left:.5in;margin-bottom:.0001pt;min-height:16px"><span style="font-size:9.0pt;font-family:"Monaco",serif"><u></u> <u></u></span></p><p style="margin-right:0in;margin-bottom:0in;margin-left:.5in;margin-bottom:.0001pt"><span style="font-size:9.0pt;font-family:"Monaco",serif">ERROR: index returned tuples in wrong order<u></u><u></u></span></p><p style="margin-right:0in;margin-bottom:0in;margin-left:.5in;margin-bottom:.0001pt"><span style="font-size:9.0pt;font-family:"Monaco",serif">SQL state: XX000<u></u><u></u></span></p><p style="margin-right:0in;margin-bottom:0in;margin-left:.5in;margin-bottom:.0001pt"><span style="font-size:9.0pt;font-family:"Monaco",serif">Context: SQL function "zz_knn" statement 1<u></u><u></u></span></p><p style="margin-right:0in;margin-bottom:0in;margin-left:.5in;margin-bottom:.0001pt;min-height:16px"><span style="font-size:9.0pt;font-family:"Monaco",serif">"<u></u><u></u></span></p></div><div><p class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">Thanks!<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">Cheers,<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">Best,<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">Steve Mather<u></u><u></u></p></div></div></div></div></div></div><br></div></div>_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br></blockquote></div><br></div>
</blockquote></div><br></div>