<div dir="ltr"><div>Hi All,</div><div><br></div><div>Short version:</div><div>Does Regina's comment on <a href="http://trac.osgeo.org/postgis/ticket/2703">http://trac.osgeo.org/postgis/ticket/2703</a> regarding <span style="color:rgb(0,0,0);font-size:13px;background-color:rgb(247,247,247)">ERROR: index returned tuples in wrong order</span> apply to any uses of indices in conjunction with KNN?</div><div><br></div><div>Long version:</div><div>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.</div><div><br></div><div><a href="https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-19-26-am.png">https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-19-26-am.png</a><br></div><div><br></div><div>I've got a function that does my KNN in the usual style, finds the nearest 2 points, and makes a line from them:</div><div><br></div><div><div>CREATE OR REPLACE FUNCTION zz_knn_wonderful_points (geometry) RETURNS geometry AS $$</div><div><br></div><div>-- Here are my wonderful points to KNN search:</div><div>WITH index_query AS (</div><div><br></div><div><span class="" style="white-space:pre"> </span>SELECT edge.geom AS geom</div><div><span class="" style="white-space:pre"> </span>FROM (SELECT * FROM regions_dpoints WHERE gid = 882) AS edge</div><div>-- This is my query point</div><div><span class="" style="white-space:pre"> </span>ORDER BY $1</div><div><span class="" style="white-space:pre"> </span><-></div><div><span class="" style="white-space:pre"> </span>edge.geom LIMIT 2</div><div><span class="" style="white-space:pre"> </span>)</div><div>SELECT ST_MakeLine(geom) FROM index_query</div><div><br></div><div>$$ LANGUAGE SQL;</div><div><br></div><div>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">http://balintmiklos.com/scale-axis/The_Scale_Axis_Picture_Show.mp4</a></div><div><br></div><div>Actually, in the final function, I will likely directly calculate the angle and return that instead of the geometry, but I digress.</div><div><br></div><div>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:</div><div><br></div><div>DROP TABLE IF EXISTS test CASCADE;</div><div>CREATE TABLE test AS</div><div>WITH returnline AS (</div><div><span class="" style="white-space:pre"> </span>SELECT oid, gid,</div><div><span class="" style="white-space:pre"> </span>ST_Union(</div><div><span class="" style="white-space:pre"> </span>ST_MakeLine( ST_StartPoint(zz_knn_wonderful_points(subset.geom)), subset.geom),</div><div><span class="" style="white-space:pre"> </span>ST_MakeLine( ST_EndPoint(zz_knn_wonderful_points(subset.geom)), subset.geom)</div><div><span class="" style="white-space:pre"> </span>)</div><div><span class="" style="white-space:pre"> </span>AS geom FROM</div><div><span class="" style="white-space:pre"> </span>(SELECT * FROM regions_points WHERE oid = 882) subset</div><div>)</div><div>SELECT * FROM returnline</div><div>;</div></div><div><br></div><div>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":</div><div><br></div><div><a href="https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-33-01-am.png">https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-33-01-am.png</a><br></div><div><br></div><div>And so I add a constraint using an integer ID:</div><div><br></div><div><div>CREATE OR REPLACE FUNCTION zz_knn(integer,geometry) RETURNS geometry AS $$</div><div><br></div><div>-- Here are my wonderful points to KNN search:</div><div>WITH index_query AS (</div><div><br></div><div><span class="" style="white-space:pre"> </span>SELECT edge.geom AS geom</div><div><span class="" style="white-space:pre"> </span>FROM (SELECT * FROM regions_dpoints WHERE gid = $1) AS edge</div><div>-- This is my query point</div><div><span class="" style="white-space:pre"> </span>ORDER BY $2</div><div><span class="" style="white-space:pre"> </span><-></div><div><span class="" style="white-space:pre"> </span>edge.geom LIMIT 2</div><div><span class="" style="white-space:pre"> </span>)</div><div>SELECT ST_MakeLine(geom) FROM index_query</div><div><br></div><div>$$ LANGUAGE SQL;</div></div><div><br></div><div>And now it takes much longer and fails with </div><div><br></div><div>"</div><div><p style="margin:0px;font-size:12px;line-height:normal;font-family:Monaco">ERROR: index returned tuples in wrong order</p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:Monaco">CONTEXT: SQL function "zz_knn" statement 1</p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:Monaco">********** Error **********</p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:Monaco;min-height:16px"><br></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:Monaco">ERROR: index returned tuples in wrong order</p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:Monaco">SQL state: XX000</p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:Monaco">Context: SQL function "zz_knn" statement 1</p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:Monaco;min-height:16px">"</p></div><div><br></div><div>Thanks!</div><div>Cheers,</div><div>Best,</div><div>Steve Mather</div></div>