<div dir="ltr">:) Thanks Regina. You made my morning.</div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Jan 6, 2016 at 12:11 AM, Paragon Corporation <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">Steve,<br>
<br>
Nevermind. I was able to recreate your error with my test data. I had to reduce the table set so my patience wasn't exhausted.<br>
<br>
I'll ticket this one and see if I can trigger the same error with the built-in geometry types so I can complain upstream. So final test data and test to trigger:<br>
<span class=""><br>
CREATE OR REPLACE FUNCTION zz_2nn_angle(geometry) RETURNS float AS $$<br>
<br>
-- Here are my wonderful points to KNN search:<br>
WITH index_query AS (<br>
<br>
SELECT edge.geom AS geom<br>
FROM (SELECT * FROM knn_recheck_geom) AS edge<br>
-- This is my query point<br>
ORDER BY $1<br>
<-><br>
edge.geom LIMIT 2<br>
),<br>
templine AS (<br>
SELECT ST_MakeLine(geom) AS geom FROM index_query<br>
),<br>
angle1 AS (<br>
SELECT ST_Azimuth(ST_StartPoint(geom), $1) angle FROM templine<br>
),<br>
angle2 AS (<br>
SELECT ST_Azimuth(ST_EndPoint(geom), $1) angle FROM templine<br>
)<br>
SELECT a1.angle - a2.angle FROM angle1 a1, angle2 a2<br>
<br>
$$ LANGUAGE SQL;<br>
<br>
</span>CREATE OR REPLACE FUNCTION zz_1nn_d(geometry) RETURNS float AS $$<br>
<span class=""><br>
-- Here are my wonderful points to KNN search:<br>
WITH index_query AS (<br>
<br>
SELECT edge.geom AS geom<br>
</span> FROM (SELECT * FROM knn_recheck_geom WHERE gid = 881) AS edge<br>
<span class="">-- This is my query point<br>
ORDER BY $1<br>
<-><br>
</span> edge.geom LIMIT 1<br>
)<br>
SELECT ST_Distance($1,geom) FROM index_query<br>
<br>
$$ LANGUAGE SQL;<br>
<span class=""><br>
CREATE TABLE knn_recheck_geom(gid serial primary key, geom geometry);<br>
INSERT INTO knn_recheck_geom(gid,geom)<br>
SELECT ROW_NUMBER() OVER(ORDER BY x,y) AS gid, ST_Point(x*0.777,y*0.887) As geom<br>
</span>FROM generate_series(-100,1000, 9) AS x CROSS JOIN generate_series(-300,1000,9) As y;<br>
<br>
---<br>
DROP TABLE IF EXISTS test_angle_subset CASCADE;<br>
CREATE TABLE test_angle_subset AS<br>
WITH returnline AS (<br>
SELECT gid, geom,<br>
zz_1nn_d(geom) AS distance,<br>
abs(degrees(zz_2nn_angle(geom))) AS angle FROM<br>
(SELECT * FROM knn_recheck_geom) subset<br>
<span class="">)<br>
SELECT * FROM returnline;<br>
<br>
</span>Yields error:<br>
<span class=""><br>
ERROR: index returned tuples in wrong order<br>
</span>CONTEXT: SQL function "zz_2nn_angle" statement 1<br>
********** Error **********<br>
<br>
<br>
Though I can't trigger the error with the 1nn function<br>
<br>
e.g this works fine:<br>
<br>
SELECT zz_1nn_d(geom)<br>
FROM knn_recheck_geom;<br>
<br>
Retuns 17835 rows in 300 ms.<br>
<br>
But this:<br>
<br>
<br>
SELECT zz_2nn_angle(geom)<br>
FROM knn_recheck_geom;<br>
<br>
<br>
--- triggers same error<br>
<span class="">ERROR: index returned tuples in wrong order<br>
</span>CONTEXT: SQL function "zz_2nn_angle" statement 1<br>
<br>
You are a cruel bastard Steve. You make me proud,<br>
<br>
Regina<br>
<div class="HOEnZb"><div class="h5"><br>
<br>
<br>
<br>
<br>
<br>
-----Original Message-----<br>
From: postgis-devel [mailto:<a href="mailto:postgis-devel-bounces@lists.osgeo.org">postgis-devel-bounces@lists.osgeo.org</a>] On Behalf Of Paragon Corporation<br>
Sent: Tuesday, January 05, 2016 11:56 PM<br>
To: 'PostGIS Development Discussion' <<a href="mailto:postgis-devel@lists.osgeo.org">postgis-devel@lists.osgeo.org</a>><br>
Subject: Re: [postgis-devel] KNN and 9.5<br>
<br>
Steve,<br>
<br>
I tested with my junk dataset and I can't trigger the issue.<br>
<br>
Are you able to with this?<br>
<br>
CREATE TABLE knn_recheck_geom(gid serial primary key, geom geometry); INSERT INTO knn_recheck_geom(gid,geom) SELECT ROW_NUMBER() OVER(ORDER BY x,y) AS gid, ST_Point(x*0.777,y*0.887) As geom FROM generate_series(-100,1000, 1) AS x CROSS JOIN generate_series(-300,1000,1) As y;<br>
<br>
CREATE INDEX idx_knn_recheck_geom on knn_recheck_geom using gist(geom);<br>
<br>
<br>
CREATE OR REPLACE FUNCTION zz_2nn_angle(geometry) RETURNS float AS $$<br>
<br>
-- Here are my wonderful points to KNN search:<br>
WITH index_query AS (<br>
<br>
SELECT edge.geom AS geom<br>
FROM (SELECT * FROM knn_recheck_geom) AS edge<br>
-- This is my query point<br>
ORDER BY $1<br>
<-><br>
edge.geom LIMIT 2<br>
),<br>
templine AS (<br>
SELECT ST_MakeLine(geom) AS geom FROM index_query ),<br>
angle1 AS (<br>
SELECT ST_Azimuth(ST_StartPoint(geom), $1) angle FROM templine ),<br>
angle2 AS (<br>
SELECT ST_Azimuth(ST_EndPoint(geom), $1) angle FROM templine<br>
)<br>
SELECT a1.angle - a2.angle FROM angle1 a1, angle2 a2<br>
<br>
$$ LANGUAGE SQL;<br>
<br>
vacuum analyze knn_recheck_geom;<br>
<br>
Then I ran with this query:<br>
<br>
SELECT zz_2nn_angle(ST_Point(10,2)); --returns 2.56969144546978 in 11ms<br>
<br>
This is testing on PostGIS 2.2.1, PostgreSQL 9.5.0rc1 under windows 7 64-bit<br>
<br>
I'm gonna try next on a real dataset. Maybe I don't have enough test data or distribution is too predictable.<br>
<br>
Does the above test crash for you?<br>
<br>
<br>
Thanks,<br>
Regina<br>
<br>
<br>
<br>
<br>
<br>
<br>
From: postgis-devel [mailto:<a href="mailto:postgis-devel-bounces@lists.osgeo.org">postgis-devel-bounces@lists.osgeo.org</a>] On Behalf Of Stephen Mather<br>
Sent: Tuesday, January 05, 2016 10:31 PM<br>
To: PostGIS Development Discussion <<a href="mailto:postgis-devel@lists.osgeo.org">postgis-devel@lists.osgeo.org</a>><br>
Subject: Re: [postgis-devel] KNN and 9.5<br>
<br>
Hi Regina,<br>
<br>
The dataset is from Natural Earth: <a href="http://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/physical/ne_10m_geography_regions_polys.zip" rel="noreferrer" target="_blank">http://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/physical/ne_10m_geography_regions_polys.zip</a><br>
<br>
I imported that dataset and transformed to 3857 just to get it in planar coordinates. I affectionately called it regions_polys_subset.<br>
<br>
<a href="https://gist.github.com/smathermather/320273309124bf8b80a7" rel="noreferrer" target="_blank">https://gist.github.com/smathermather/320273309124bf8b80a7</a><br>
<br>
But honestly, as long as you throw the knn in a function, any point dataset will do it, I think.<br>
<br>
Hope this helps (and helps in time!)!<br>
Cheers,<br>
Best,<br>
Steve<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
On Sun, Jan 3, 2016 at 9:24 PM, Paragon Corporation <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>> wrote:<br>
Steve,<br>
<br>
Well that sucks. Can I borrow your dataset to test with?<br>
<br>
Thanks,<br>
Regina<br>
<br>
From: postgis-devel [mailto:<a href="mailto:postgis-devel-bounces@lists.osgeo.org">postgis-devel-bounces@lists.osgeo.org</a>] On Behalf Of Stephen Mather<br>
Sent: Sunday, January 03, 2016 10:41 AM<br>
To: PostGIS Development Discussion <<a href="mailto:postgis-devel@lists.osgeo.org">postgis-devel@lists.osgeo.org</a>><br>
Subject: Re: [postgis-devel] KNN and 9.5<br>
<br>
Hi Regina,<br>
<br>
On a lark, I also tested with 2.3.0dev r14538 (9.5rc1)<br>
<br>
"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>
<br>
Same result.<br>
<br>
Cheers,<br>
Best,<br>
Steve<br>
<br>
<br>
<br>
<br>
On Sun, Jan 3, 2016 at 9:34 AM, Stephen Mather <<a href="mailto:stephen@smathermather.com">stephen@smathermather.com</a>> wrote:<br>
2.2.0 and RC1. Should I be trying 2.2 branch?<br>
<br>
<br>
SELECT version();<br>
<br>
"PostgreSQL 9.5rc1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit"<br>
<br>
<br>
SELECT postgis_full_version();<br>
<br>
"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>
<br>
<br>
<br>
On Sun, Jan 3, 2016 at 2:18 AM, Paragon Corporation <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>> wrote:<br>
Steve,<br>
<br>
Haven't had a chance to look thru what you have here and test it out myself.<br>
<br>
The issue I flagged in the ticket was fixed in PostgreSQL 9.5 code base upstream:<br>
<br>
<a href="http://www.postgresql.org/message-id/55630D9D.3090508@iki.fi" rel="noreferrer" target="_blank">http://www.postgresql.org/message-id/55630D9D.3090508@iki.fi</a><br>
<br>
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.<br>
<br>
Are you running PostgreSQL 9.5RC1 and latest PostGIS 2.2 branch (or 2.2.0?).<br>
<br>
Thanks,<br>
Regina<br>
<br>
From: postgis-devel [mailto:<a href="mailto:postgis-devel-bounces@lists.osgeo.org">postgis-devel-bounces@lists.osgeo.org</a>] On Behalf Of Stephen Mather<br>
Sent: Sunday, January 03, 2016 1:56 AM<br>
To: PostGIS Development Discussion <<a href="mailto:postgis-devel@lists.osgeo.org">postgis-devel@lists.osgeo.org</a>><br>
Subject: [postgis-devel] KNN and 9.5<br>
<br>
Hi All,<br>
<br>
Short version:<br>
Does Regina's comment on <a href="http://trac.osgeo.org/postgis/ticket/2703" rel="noreferrer" target="_blank">http://trac.osgeo.org/postgis/ticket/2703</a> regarding ERROR: index returned tuples in wrong order apply to any uses of indices in conjunction with KNN?<br>
<br>
Long version:<br>
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.<br>
<br>
<a href="https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-19-26-am.png" rel="noreferrer" target="_blank">https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-19-26-am.png</a><br>
<br>
I've got a function that does my KNN in the usual style, finds the nearest 2 points, and makes a line from them:<br>
<br>
CREATE OR REPLACE FUNCTION zz_knn_wonderful_points (geometry) RETURNS geometry AS $$<br>
<br>
-- Here are my wonderful points to KNN search:<br>
WITH index_query AS (<br>
<br>
SELECT edge.geom AS geom<br>
FROM (SELECT * FROM regions_dpoints WHERE gid = 882) AS edge<br>
-- This is my query point<br>
ORDER BY $1<br>
<-><br>
edge.geom LIMIT 2<br>
)<br>
SELECT ST_MakeLine(geom) FROM index_query<br>
<br>
$$ LANGUAGE SQL;<br>
<br>
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" rel="noreferrer" target="_blank">http://balintmiklos.com/scale-axis/The_Scale_Axis_Picture_Show.mp4</a><br>
<br>
Actually, in the final function, I will likely directly calculate the angle and return that instead of the geometry, but I digress.<br>
<br>
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:<br>
<br>
DROP TABLE IF EXISTS test CASCADE;<br>
CREATE TABLE test AS<br>
WITH returnline AS (<br>
SELECT oid, gid,<br>
ST_Union(<br>
ST_MakeLine( ST_StartPoint(zz_knn_wonderful_points(subset.geom)), subset.geom),<br>
ST_MakeLine( ST_EndPoint(zz_knn_wonderful_points(subset.geom)), subset.geom)<br>
)<br>
AS geom FROM<br>
(SELECT * FROM regions_points WHERE oid = 882) subset<br>
)<br>
SELECT * FROM returnline<br>
;<br>
<br>
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":<br>
<br>
<a href="https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-33-01-am.png" rel="noreferrer" target="_blank">https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-33-01-am.png</a><br>
<br>
And so I add a constraint using an integer ID:<br>
<br>
CREATE OR REPLACE FUNCTION zz_knn(integer,geometry) RETURNS geometry AS $$<br>
<br>
-- Here are my wonderful points to KNN search:<br>
WITH index_query AS (<br>
<br>
SELECT edge.geom AS geom<br>
FROM (SELECT * FROM regions_dpoints WHERE gid = $1) AS edge<br>
-- This is my query point<br>
ORDER BY $2<br>
<-><br>
edge.geom LIMIT 2<br>
)<br>
SELECT ST_MakeLine(geom) FROM index_query<br>
<br>
$$ LANGUAGE SQL;<br>
<br>
And now it takes much longer and fails with<br>
<br>
"<br>
ERROR: index returned tuples in wrong order<br>
CONTEXT: SQL function "zz_knn" statement 1<br>
********** Error **********<br>
<br>
ERROR: index returned tuples in wrong order SQL state: XX000<br>
Context: SQL function "zz_knn" statement 1 "<br>
<br>
Thanks!<br>
Cheers,<br>
Best,<br>
Steve Mather<br>
<br>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org">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>
<br>
<br>
<br>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org">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>
<br>
<br>
<br>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org">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>
<br>
<br>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org">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></div></div></blockquote></div><br></div>