[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
Resolution:           |   Keywords:
----------------------+---------------------------
Description changed by robe:

Old description:

> As Stephen Mathers noted on the list, https://lists.osgeo.org/pipermail
> /postgis-devel/2016-January/025559.html
>
> 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 **********
> }}}

New description:

 As Stephen Mathers noted on the list, https://lists.osgeo.org/pipermail
 /postgis-devel/2016-January/025559.html

 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);


 SELECT zz_2nn_angle(geom)
 FROM knn_recheck_geom;

 }}}

 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#comment:2>
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.