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,

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

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;


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.