[postgis-tickets] [PostGIS] #3418: KNN recheck in 9.5 fails with index returned tuples in wrong order when used in function
PostGIS
trac at osgeo.org
Tue Jan 5 21:16:52 PST 2016
#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
Keywords: |
---------------------+---------------------------
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 **********
}}}
--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3418>
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.
More information about the postgis-tickets
mailing list