[postgis-devel] KNN and 9.5

[Stephen Mather]

Hi Regina,

On a lark, I also tested with 2.3.0dev r14538 (9.5rc1)

"POSTGIS="2.3.0dev r14538" 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"

Same result.

Cheers,
Best,
Steve




On Sun, Jan 3, 2016 at 9:34 AM, Stephen Mather <stephen at smathermather.com>
wrote:

> 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/6fc27feb/attachment.html>