[postgis-tickets] [PostGIS] #3418: KNN recheck in 9.5 fails with index returned tuples in wrong order when used in function

[PostGIS]

#3418: KNN recheck in 9.5 fails with index returned tuples in wrong order when
used in function
---------------------+---------------------------
 Reporter:  robe     |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  medium   |  Milestone:  PostGIS 2.2.2
Component:  postgis  |    Version:  2.2.x
 Keywords:           |
---------------------+---------------------------
 As Stephen Mathers noted on the list, he was able to break our precious
 KNN recheck code.  I'm hoping this is something we can push upstream. Will
 try to replicate with build in PostgreSQL geometry types.

 -- to replicate
 {{{

 DROP TABLE knn_recheck_geom IF EXISTS;
 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, 9) AS x CROSS JOIN
 generate_series(-300,1000,9) As y;

 CREATE OR REPLACE FUNCTION zz_2nn_angle(geometry) RETURNS float AS $$
 -- Here are my wonderful points to KNN search:
 WITH index_query AS (

         SELECT edge.geom AS geom
         FROM (SELECT * FROM knn_recheck_geom) AS edge
 -- This is my query point
         ORDER BY $1
                 <->
         edge.geom LIMIT 2
         ),
 templine AS (
         SELECT ST_MakeLine(geom) AS geom FROM index_query
 ),
 angle1 AS (
         SELECT ST_Azimuth(ST_StartPoint(geom), $1) angle FROM templine
 ),
 angle2 AS (
         SELECT ST_Azimuth(ST_EndPoint(geom), $1) angle FROM templine
 )
 SELECT a1.angle - a2.angle FROM angle1 a1, angle2 a2
 $$ LANGUAGE SQL;

 CREATE INDEX idx_knn_recheck_geom on knn_recheck_geom using gist(geom);


 WITH returnline AS (
         SELECT gid, geom,
                 zz_1nn_d(geom) AS distance,
                 abs(degrees(zz_2nn_angle(geom))) AS angle FROM
                 (SELECT * FROM knn_recheck_geom) subset
 )
 SELECT * FROM returnline;

 }}}

 Returns:


 {{{
 ERROR:  index returned tuples in wrong order
 CONTEXT:  SQL function "zz_2nn_angle" statement 1
 ********** Error **********
 }}}

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3418>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.