[postgis-devel] KNN and 9.5

[Stephen Mather]

Hi All,

Short version:
Does Regina's comment on http://trac.osgeo.org/postgis/ticket/2703
regarding ERROR: index returned tuples in wrong order apply to any uses of
indices in conjunction with KNN?

Long version:
Digging many of the 2.2 / 9.5 updates. Having fun on break playing with
skeleton simplification and using KNN in the process. Yes. For those of you
who know me, this is an obsession that won't die quietly.

https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-19-26-am.png

I've got a function that does my KNN in the usual style, finds the nearest
2 points, and makes a line from them:

CREATE OR REPLACE FUNCTION zz_knn_wonderful_points (geometry) RETURNS
geometry AS $$

-- Here are my wonderful points to KNN search:
WITH index_query AS (

SELECT edge.geom AS geom
FROM (SELECT * FROM regions_dpoints WHERE gid = 882) AS edge
-- This is my query point
ORDER BY $1
<->
edge.geom LIMIT 2
)
SELECT ST_MakeLine(geom) FROM index_query

$$ LANGUAGE SQL;

The intent is to implement angle values as an importance criterion for
simplifying the skeleton as described at minute 2:20 in Balint Miklos video
on the scale axis transform:
http://balintmiklos.com/scale-axis/The_Scale_Axis_Picture_Show.mp4

Actually, in the final function, I will likely directly calculate the angle
and return that instead of the geometry, but I digress.

Regardless, when I use this function as is, it works great in drawing lines
connecting the two nearest points to the vertex in the medial axis:

DROP TABLE IF EXISTS test CASCADE;
CREATE TABLE test AS
WITH returnline AS (
SELECT oid, gid,
ST_Union(
ST_MakeLine( ST_StartPoint(zz_knn_wonderful_points(subset.geom)),
subset.geom),
ST_MakeLine( ST_EndPoint(zz_knn_wonderful_points(subset.geom)), subset.geom)
)
AS geom FROM
(SELECT * FROM regions_points WHERE oid = 882) subset
)
SELECT * FROM returnline
;

However, as implemented, this will not work in the case of overlapping
geometries, such as Natural Earth's label polygon dataset
"ne_10m_geography_regions_polys":

https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-33-01-am.png

And so I add a constraint using an integer ID:

CREATE OR REPLACE FUNCTION zz_knn(integer,geometry) RETURNS geometry AS $$

-- Here are my wonderful points to KNN search:
WITH index_query AS (

SELECT edge.geom AS geom
FROM (SELECT * FROM regions_dpoints WHERE gid = $1) AS edge
-- This is my query point
ORDER BY $2
<->
edge.geom LIMIT 2
)
SELECT ST_MakeLine(geom) FROM index_query

$$ LANGUAGE SQL;

And now it takes much longer and fails with

"

ERROR:  index returned tuples in wrong order

CONTEXT:  SQL function "zz_knn" statement 1

********** Error **********


ERROR: index returned tuples in wrong order

SQL state: XX000

Context: SQL function "zz_knn" statement 1

"

Thanks!
Cheers,
Best,
Steve Mather
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20160103/60fc0994/attachment.html>