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 **********
Note:
See TracTickets
for help on using tickets.