<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" data-hsystem="true"></head>
<body><style>p{margin: 0;padding: 0;}
</style>
<p>(you can get the natural_earth2 data set from the OSGeo Live -- its one of
the main sample databases)</p>
<p> --Brian<br><br>On Tue, 5 Jan 2016 23:04:32 -0500, Paragon Corporation
<lr@pcorp.us> wrote:</p>
<blockquote style="border-left: 2px solid #000000; padding-right: 0px;
padding-left: 5px; margin-left: 5px; margin-right: 0px;">
<div id="html-message"><meta>
<meta name="Generator">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;">Gives me a 404.</span></p>
<p> </p>
<p> </p>
<p class="MsoNormal"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;">Thanks,</span></p>
<p> </p>
<p class="MsoNormal"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;">Regina</span></p>
<p> </p>
<p> </p>
<p class="MsoNormal" style="margin-left: .5in;"><strong><span style="font-size:
11.0pt; font-family: 'Calibri',sans-serif;">From:</span></strong><span style="font-size: 11.0pt; font-family: 'Calibri',sans-serif;"> postgis-devel
[mailto:postgis-devel-bounces@lists.osgeo.org] <strong>On Behalf Of
</strong>Stephen Mather<br><strong>Sent:</strong> Tuesday, January 05, 2016
10:31 PM<br><strong>To:</strong> PostGIS Development Discussion
<postgis-devel@lists.osgeo.org><br><strong>Subject:</strong> Re:
[postgis-devel] KNN and 9.5</span></p>
<p> </p>
<p> </p>
<div>
<p class="MsoNormal" style="margin-left: .5in;">Hi Regina,</p>
<p> </p>
<div>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="margin-left: .5in;">The dataset is from Natural
Earth: <a href="http://www.naturalearthdata.com/http/www.naturalearthdata.com/download/10m/physical/ne_10m_geography_regions_polys.zip" target="_blank">http://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/physical/ne_10m_geography_regions_polys.zip</a></p>
<p> </p>
</div>
<div>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="margin-left: .5in;">I imported that dataset and
transformed to 3857 just to get it in planar coordinates. I affectionately
called it regions_polys_subset.</p>
<p> </p>
</div>
<div>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="margin-left: .5in;"><a href="https://gist.github.com/smathermather/320273309124bf8b80a7" target="">https://gist.github.com/smathermather/320273309124bf8b80a7</a></p>
<p> </p>
</div>
<div>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="margin-left: .5in;">But honestly, as long as you
throw the knn in a function, any point dataset will do it, I think.</p>
<p> </p>
</div>
<div>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="margin-left: .5in;">Hope this helps (and helps in
time!)!</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="margin-left: .5in;">Cheers,</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="margin-left: .5in;">Best,</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="margin-left: .5in;">Steve</p>
<p> </p>
</div>
<div>
<p> </p>
</div>
<div>
<p> </p>
</div>
<div>
<p> </p>
</div>
<div>
<p> </p>
</div>
<div>
<p> </p>
</div>
<div>
<p> </p>
</div>
</div>
<div>
<p> </p>
<div>
<p class="MsoNormal" style="margin-left: .5in;">On Sun, Jan 3, 2016 at 9:24 PM,
Paragon Corporation <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:</p>
<p> </p>
<blockquote style="border: none; border-left: solid #CCCCCC 1.0pt; padding: 0in
0in 0in 6.0pt; margin-left: 4.8pt; margin-right: 0in;">
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: .5in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;">Steve,</span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: .5in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;"> </span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: .5in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;">Well that sucks. Can I borrow your
dataset to test with?</span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: .5in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;"> </span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: .5in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;">Thanks,</span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: .5in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;">Regina</span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: .5in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;"> </span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><strong><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif;">From:</span></strong><span style="font-size: 11.0pt;
font-family: 'Calibri',sans-serif;"> postgis-devel [mailto:<a href="mailto:postgis-devel-bounces@lists.osgeo.org" target="_blank">postgis-devel-bounces@lists.osgeo.org</a>] <strong>On Behalf Of
</strong>Stephen Mather<br><strong>Sent:</strong> Sunday, January 03, 2016 10:41
AM<br><strong>To:</strong> PostGIS Development Discussion <<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a>><br><strong>Subject:</strong>
Re: [postgis-devel] KNN and 9.5</span></p>
<p> </p>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;">Hi Regina,</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;">On a lark, I also tested with 2.3.0dev r14538
(9.5rc1)</p>
<p> </p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;">"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"</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;">Same result.</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;">Cheers,</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;">Best,</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;">Steve</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;">On Sun, Jan 3, 2016 at 9:34 AM, Stephen Mather <<a href="mailto:stephen@smathermather.com" target="_blank">stephen@smathermather.com</a>> wrote:</p>
<p> </p>
<blockquote style="border: none; border-left: solid #CCCCCC 1.0pt; padding: 0in
0in 0in 6.0pt; margin: 5.0pt 0in 5.0pt 4.8pt;">
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;">2.2.0 and RC1. Should I be trying 2.2 branch?</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;">SELECT version();</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;">"PostgreSQL 9.5rc1 on x86_64-pc-linux-gnu, compiled
by gcc (Debian 4.7.2-5) 4.7.2, 64-bit"</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;">SELECT postgis_full_version();</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;">"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"</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
<div>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;">On Sun, Jan 3, 2016 at 2:18 AM, Paragon Corporation
<<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:</p>
<p> </p>
</div>
</div>
<blockquote style="border: none; border-left: solid #CCCCCC 1.0pt; padding: 0in
0in 0in 6.0pt; margin: 5.0pt 0in 5.0pt 4.8pt;">
<div>
<div>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;">Steve,</span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;"> </span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;">Haven't had a chance to look thru what
you have here and test it out myself. </span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;"> </span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;">The issue I flagged in the ticket was
fixed in PostgreSQL 9.5 code base upstream:</span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;"> </span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;"><a href="http://www.postgresql.org/message-id/55630D9D.3090508@iki.fi" target="_blank">http://www.postgresql.org/message-id/55630D9D.3090508@iki.fi</a></span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;"> </span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;">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.</span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;"> </span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;">Are you running PostgreSQL 9.5RC1 and
latest PostGIS 2.2 branch (or 2.2.0?).</span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;"> </span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;">Thanks,</span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;">Regina</span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif; color: #1f497d;"> </span></p>
<p> </p>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"><strong><span style="font-size: 11.0pt; font-family:
'Calibri',sans-serif;">From:</span></strong><span style="font-size: 11.0pt;
font-family: 'Calibri',sans-serif;"> postgis-devel [mailto:<a href="mailto:postgis-devel-bounces@lists.osgeo.org" target="_blank">postgis-devel-bounces@lists.osgeo.org</a>] <strong>On Behalf Of
</strong>Stephen Mather<br><strong>Sent:</strong> Sunday, January 03, 2016 1:56
AM<br><strong>To:</strong> PostGIS Development Discussion <<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a>><br><strong>Subject:</strong>
[postgis-devel] KNN and 9.5</span></p>
<p> </p>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">Hi All,</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">Short version:</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">Does Regina's comment on <a href="http://trac.osgeo.org/postgis/ticket/2703" target="_blank">http://trac.osgeo.org/postgis/ticket/2703</a> regarding <span style="font-size: 10.0pt; color: black; background: #F7F7F7;">ERROR: index
returned tuples in wrong order</span> apply to any uses of indices in
conjunction with KNN?</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">Long version:</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">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.</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"><a href="https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-19-26-am.png" target="_blank">https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-19-26-am.png</a></p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">I've got a function that does my KNN in the usual
style, finds the nearest 2 points, and makes a line from them:</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">CREATE OR REPLACE FUNCTION zz_knn_wonderful_points
(geometry) RETURNS geometry AS $$</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">-- Here are my wonderful points to KNN search:</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">WITH index_query AS (</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> SELECT edge.geom AS geom</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> FROM (SELECT * FROM
regions_dpoints WHERE gid = 882) AS edge</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">-- This is my query point</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> ORDER BY $1</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">
<-></p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> edge.geom LIMIT 2</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> )</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">SELECT ST_MakeLine(geom) FROM index_query</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">$$ LANGUAGE SQL;</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">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: <a href="http://balintmiklos.com/scale-axis/The_Scale_Axis_Picture_Show.mp4" target="_blank">http://balintmiklos.com/scale-axis/The_Scale_Axis_Picture_Show.mp4</a></p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">Actually, in the final function, I will likely
directly calculate the angle and return that instead of the geometry, but I
digress.</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">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:</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">DROP TABLE IF EXISTS test CASCADE;</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">CREATE TABLE test AS</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">WITH returnline AS (</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> SELECT oid, gid,</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">
ST_Union(</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">
ST_MakeLine( ST_StartPoint(zz_knn_wonderful_points(subset.geom)),
subset.geom),</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">
ST_MakeLine( ST_EndPoint(zz_knn_wonderful_points(subset.geom)), subset.geom)</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> )</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> AS
geom FROM</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">
(SELECT * FROM regions_points WHERE oid = 882) subset</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">)</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">SELECT * FROM returnline</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">;</p>
<p> </p>
</div>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">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":</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"><a href="https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-33-01-am.png" target="_blank">https://smathermather.files.wordpress.com/2016/01/screen-shot-2016-01-03-at-1-33-01-am.png</a></p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">And so I add a constraint using an integer ID:</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">CREATE OR REPLACE FUNCTION zz_knn(integer,geometry)
RETURNS geometry AS $$</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">-- Here are my wonderful points to KNN search:</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">WITH index_query AS (</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> SELECT edge.geom AS geom</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> FROM (SELECT * FROM
regions_dpoints WHERE gid = $1) AS edge</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">-- This is my query point</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> ORDER BY $2</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">
<-></p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> edge.geom LIMIT 2</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> )</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">SELECT ST_MakeLine(geom) FROM index_query</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">$$ LANGUAGE SQL;</p>
<p> </p>
</div>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">And now it takes much longer and fails with </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">"</p>
<p> </p>
</div>
<div>
<p style="mso-margin-top-alt: 5.0pt; margin-right: 0in; margin-bottom: .0001pt;
margin-left: 1.5in;"><span style="font-size: 9.0pt;">ERROR: index returned
tuples in wrong order</span></p>
<p> </p>
<p style="mso-margin-top-alt: 5.0pt; margin-right: 0in; margin-bottom: .0001pt;
margin-left: 1.5in;"><span style="font-size: 9.0pt;">CONTEXT: SQL function
"zz_knn" statement 1</span></p>
<p> </p>
<p style="mso-margin-top-alt: 5.0pt; margin-right: 0in; margin-bottom: .0001pt;
margin-left: 1.5in;"><span style="font-size: 9.0pt;">********** Error
**********</span></p>
<p> </p>
<p style="mso-margin-top-alt: 5.0pt; margin-right: 0in; margin-bottom: .0001pt;
margin-left: 1.5in; min-height: 16px;"><span style="font-size:
9.0pt;"> </span></p>
<p> </p>
<p style="mso-margin-top-alt: 5.0pt; margin-right: 0in; margin-bottom: .0001pt;
margin-left: 1.5in;"><span style="font-size: 9.0pt;">ERROR: index returned
tuples in wrong order</span></p>
<p> </p>
<p style="mso-margin-top-alt: 5.0pt; margin-right: 0in; margin-bottom: .0001pt;
margin-left: 1.5in;"><span style="font-size: 9.0pt;">SQL state: XX000</span></p>
<p> </p>
<p style="mso-margin-top-alt: 5.0pt; margin-right: 0in; margin-bottom: .0001pt;
margin-left: 1.5in;"><span style="font-size: 9.0pt;">Context: SQL function
"zz_knn" statement 1</span></p>
<p> </p>
<p style="mso-margin-top-alt: 5.0pt; margin-right: 0in; margin-bottom: .0001pt;
margin-left: 1.5in; min-height: 16px;"><span style="font-size:
9.0pt;">"</span></p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;"> </p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">Thanks!</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">Cheers,</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">Best,</p>
<p> </p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.5in;">Steve Mather</p>
<p> </p>
</div>
</div>
</div>
</div>
</div>
</div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
</div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left:
1.0in;">_______________________________________________<br>postgis-devel mailing
list<br><a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-devel</a></p>
<p> </p>
</blockquote>
</div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
</blockquote>
</div>
<p class="MsoNormal" style="mso-margin-top-alt: auto; mso-margin-bottom-alt:
auto; margin-left: 1.0in;"> </p>
<p> </p>
</div>
</div>
</div>
</div>
</div>
<p class="MsoNormal" style="margin-left:
.5in;"><br>_______________________________________________<br>postgis-devel
mailing list<br><a href="mailto:postgis-devel@lists.osgeo.org" target="">postgis-devel@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-devel</a></p>
<p> </p>
</blockquote>
</div>
<p> </p>
</div>
</div>
</div>
<br><hr>
<br> _______________________________________________<br> postgis-devel mailing
list<br> postgis-devel@lists.osgeo.org<br><a href="../hwebmail/services/go.php?url=http%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fpostgis-devel" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-devel</a>
</blockquote>
<p><br><br></p>
<p><br> --<br>Brian M Hamlin<br> OSGeo California Chapter<br>
blog.light42.com<br></p>
<p> </p>
</body>
</html>