[postgis-devel] KNN and 9.5

[Stephen Mather]

2.2.0 and RC1. Should I be trying 2.2 branch?


SELECT version();

"PostgreSQL 9.5rc1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.7.2-5)
4.7.2, 64-bit"


SELECT postgis_full_version();

"POSTGIS="2.2.0 r14208" GEOS="3.5.0-CAPI-1.9.0 r4084" SFCGAL="1.2.2"
PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26"
LIBXML="2.9.1" LIBJSON="0.11.99" RASTER"



On Sun, Jan 3, 2016 at 2:18 AM, Paragon Corporation <lr at pcorp.us> wrote:

> Steve,
>
>
>
> Haven't had a chance to look thru what you have here and test it out
> myself.
>
>
>
> The issue I flagged in the ticket was fixed in PostgreSQL 9.5 code base
> upstream:
>
>
>
> http://www.postgresql.org/message-id/55630D9D.3090508@iki.fi
>
>
>
> It's my understanding that you should never get that notice again.  So I'm
> a bit concerned you are.  It's possible you found another area where this
> issue arises and is not handled.
>
>
>
> Are you running PostgreSQL 9.5RC1 and latest PostGIS 2.2 branch (or
> 2.2.0?).
>
>
>
> Thanks,
>
> Regina
>
>
>
> *From:* postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] *On
> Behalf Of *Stephen Mather
> *Sent:* Sunday, January 03, 2016 1:56 AM
> *To:* PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
> *Subject:* [postgis-devel] KNN and 9.5
>
>
>
> 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
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20160103/d9845ea5/attachment.html>