Opened 7 years ago

Last modified 6 years ago

#3418 closed defect

KNN recheck in 9.5 fails with index returned tuples in wrong order when used in function — at Version 1

Reported by: robe Owned by: pramsey
Priority: medium Milestone: PostGIS 2.2.5
Component: postgis Version: 2.2.x
Keywords: Cc: pramsey

Description (last modified by robe)

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 **********

Change History (1)

comment:1 by robe, 7 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.