Opened 9 years ago

Last modified 8 years ago

#3418 closed defect

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

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

Description

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

Change History (0)

Note: See TracTickets for help on using tickets.