<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body dir="auto"><div>The workaround as provided exposes multiple other issues for us. I can try to isolate them, but essentially it causes random looking test failures in our app.</div><div><br>On Feb 1, 2014, at 6:55 PM, "Paragon Corporation" <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>> wrote:<br><br></div><blockquote type="cite"><div>
<meta content="text/html; charset=us-ascii" http-equiv="Content-Type">
<meta name="GENERATOR" content="MSHTML 10.00.9200.16750">
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial">Jerry,</font></span></div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial">We know it's a PostGIS bug. The bug I think came in
2.1.0. It was the change in geography to use geography cache /tree as I
recall. You aren't the first to notice (though you provided a very
succinct example). It's a hard bug to nail where it's
happening. My naive guess is that it's somewhere around here - that's about
as far into tracing I've gotten unfortunately.</font></span></div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial"><a href="http://postgis.net/docs/doxygen/2.2/d8/de3/geography__measurement__trees_8c_a399a5e30071a3979b414ce094ff94de9.html#a399a5e30071a3979b414ce094ff94de9">http://postgis.net/docs/doxygen/2.2/d8/de3/geography__measurement__trees_8c_a399a5e30071a3979b414ce094ff94de9.html#a399a5e30071a3979b414ce094ff94de9</a> (where
it computes the last distance) ( <pre class="fragment"><a class="code" href="http://postgis.net/docs/doxygen/2.2/de/dc0/lwgeodetic__tree_8c_a15d46de87264f168426b23b4b34d75ab.html#a15d46de87264f168426b23b4b34d75ab"><font size="4">circ_tree_distance_tree</font></a><font size="4">(tree_cache-></font><a class="code" href="http://postgis.net/docs/doxygen/2.2/d1/d12/structCircTreeGeomCache_af6271396dbfa5645b4265f825f0e2015.html#af6271396dbfa5645b4265f825f0e2015"><font size="4">index</font></a><font size="4">, circ_tree, s, </font><a title="Floating point comparitors." class="code" href="http://postgis.net/docs/doxygen/2.2/d2/d54/liblwgeom__internal_8h_a946f0acff4fd16a65859479e9e0b9513.html#a946f0acff4fd16a65859479e9e0b9513"><font size="4">FP_TOLERANCE</font></a><font size="4">);</font></pre></font></span></div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial">and </font></span></div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial"><a href="http://postgis.net/docs/doxygen/2.2/de/dc0/lwgeodetic__tree_8c_a15d46de87264f168426b23b4b34d75ab.html#a15d46de87264f168426b23b4b34d75ab">http://postgis.net/docs/doxygen/2.2/de/dc0/lwgeodetic__tree_8c_a15d46de87264f168426b23b4b34d75ab.html#a15d46de87264f168426b23b4b34d75ab</a> (circ_tree_distance_tree)</font></span></div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial">As a work around if you are in a rush and until we have
the issue resolved, you could swap out the definition of _ST_Distance with
the _ST_DistanceUncached. It will be slower but should produce correct
results. Basically run this:</font></span></div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial">CREATE OR REPLACE FUNCTION _st_distance(geography, geography,
double precision, boolean)<br> RETURNS double precision
AS<br>'$libdir/postgis-2.2', 'geography_distance_uncached'<br> LANGUAGE c
IMMUTABLE STRICT<br> COST 100;</font></span></div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial">See my note here:</font></span></div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial"><a href="http://trac.osgeo.org/postgis/ticket/2556">http://trac.osgeo.org/postgis/ticket/2556</a></font></span></div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial">Thanks for your patience,</font></span></div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial">Regina</font></span></div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial"><a href="http://www.postgis.us">http://www.postgis.us</a></font></span></div>
<div dir="ltr" align="left"><span class="466413502-02022014"><font color="#0000ff" size="2" face="Arial"><a href="http://postgis.net">http://postgis.net</a></font></span></div><br>
<div lang="en-us" class="OutlookMessageHeader" dir="ltr" align="left">
<hr tabindex="-1">
<font size="2" face="Tahoma"><b>From:</b> <a href="mailto:postgis-users-bounces@lists.osgeo.org">postgis-users-bounces@lists.osgeo.org</a>
[<a href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Jerry
Sievert<br><b>Sent:</b> Saturday, February 01, 2014 1:24 PM<br><b>To:</b>
PostGIS Users Discussion<br><b>Subject:</b> Re: [postgis-users] Odd Behavior
with ST_Intersects (PostGIS 2.1, Ubuntu 12.04)<br></font><br></div>
<div></div>
<div dir="ltr">After spending some time trying to track down which package/version
the bug occurs in, I found that it is actually from the upgrade of PostGIS from
2.0.4 to 2.1.1. I will see if I can spend more time to isolate it further,
but I did confirm that it is PostGIS, not one of the dependencies.
<div><br></div></div>
<div class="gmail_extra"><br><br>
<div class="gmail_quote">On Fri, Jan 31, 2014 at 8:33 PM, Paragon Corporation
<span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid"><u></u>
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">ah found the
other one:</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"><a href="http://trac.osgeo.org/postgis/ticket/2556" target="_blank">http://trac.osgeo.org/postgis/ticket/2556</a></font></span></div><br>
<div lang="en-us" dir="ltr" align="left">
<hr>
<font face="Tahoma"><b>From:</b> <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a> [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of
</b>Paragon Corporation<br><b>Sent:</b> Friday, January 31, 2014 11:27
PM<br><b>To:</b> 'PostGIS Users Discussion'<br><b>Cc:</b> 'PostGIS Development
Discussion'<br><b>Subject:</b> Re: [postgis-users] Odd Behavior with
ST_Intersects (PostGIS 2.1,Ubuntu 12.04)<br></font><br></div>
<div>
<div class="h5">
<div></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">Jerry,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">Thanks very much
for the report. I had noticed what I think might be the same issue, but
couldn't find such a short and succinct example as yours to
reproduce.</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">I have added to
my original ticket: <a href="http://trac.osgeo.org/postgis/ticket/2422" target="_blank">http://trac.osgeo.org/postgis/ticket/2422</a></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">I think there is
another ticket in our bug tracker that might be the same issue too but can't
recall the number.</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">Thanks very very
much for this great example.</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">Paul -- if you
are watching you have work to do :)</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">Thanks,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">Regina</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"><a href="http://www.postgis.us" target="_blank">http://www.postgis.us</a></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"><a href="http://postgis.net" target="_blank">http://postgis.net</a></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"></font></span> </div><br>
<div lang="en-us" dir="ltr" align="left">
<hr>
<font face="Tahoma"><b>From:</b> <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a> [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of
</b>Jerry Sievert<br><b>Sent:</b> Friday, January 31, 2014 6:25
PM<br><b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><b>Subject:</b>
[postgis-users] Odd Behavior with ST_Intersects (PostGIS 2.1,Ubuntu
12.04)<br></font><br></div>
<div></div>
<div dir="ltr">Hello,
<div><br></div>
<div>I am using Ubuntu 12.04 with the official PostgreSQL apt repo (via <a href="https://wiki.postgresql.org/wiki/Apt" target="_blank">https://wiki.postgresql.org/wiki/Apt</a>). I am running
into an easily reproducible issue, and was hoping for some help to solve
this.</div>
<div><br></div>
<div>When using ST_Intersects() not all rows that intersect are returned.
This was not the case in previous versions that we have upgraded
from.</div>
<div><br></div>
<div>These are the steps to reproduce on a fresh install of Ubuntu 12.04 with
all packages updated and PostGIS/PostgreSQL 9.3 installed:</div>
<div><br></div>
<div>
<div><font face="courier new, monospace">test=# CREATE TABLE test (id serial,
condition_geo geography);</font></div>
<div><font face="courier new, monospace">CREATE TABLE</font></div>
<div><font face="courier new, monospace">test=# INSERT INTO test
(condition_geo) VALUES
(ST_Buffer(ST_GeogFromWKB(ST_MakePoint(20.0,30.0)),10.0));</font></div>
<div><font face="courier new, monospace">INSERT 0 1</font></div>
<div><font face="courier new, monospace">test=# SELECT id FROM test WHERE
ST_Intersects("condition_geo", ST_Buffer(ST_GeogFromText('POINT(20.0 30.0)'),
20.0)) IS TRUE;</font></div>
<div><font face="courier new, monospace"> id</font></div>
<div><font face="courier new, monospace">----</font></div>
<div><font face="courier new, monospace"> 1</font></div>
<div><font face="courier new, monospace">(1 row)</font></div>
<div><font face="courier new, monospace"><br></font></div>
<div><font face="courier new, monospace">test=# INSERT INTO test
(condition_geo) VALUES
(ST_Buffer(ST_GeogFromWKB(ST_MakePoint(20.0,30.0)),10.0));</font></div>
<div><font face="courier new, monospace">INSERT 0 1</font></div>
<div><font face="courier new, monospace">test=# SELECT id FROM test WHERE
ST_Intersects("condition_geo", ST_Buffer(ST_GeogFromText('POINT(20.0 30.0)'),
20.0)) IS TRUE;</font></div>
<div><font face="courier new, monospace"> id</font></div>
<div><font face="courier new, monospace">----</font></div>
<div><font face="courier new, monospace"> 1</font></div>
<div><font face="courier new, monospace">(1 row)</font></div>
<div><font face="courier new, monospace"><br></font></div>
<div><font face="courier new, monospace">test=# SELECT id FROM test WHERE
ST_Intersects("condition_geo", ST_Buffer(ST_GeogFromText('POINT(20.0 30.0)'),
20.0)) IS TRUE AND id = 2;</font></div>
<div><font face="courier new, monospace"> id</font></div>
<div><font face="courier new, monospace">----</font></div>
<div><font face="courier new, monospace"> 2</font></div>
<div><font face="courier new, monospace">(1 row)</font></div></div>
<div><br></div>
<div>Note that the SELECT should return both rows 1 and 2 in the first
SELECT.</div>
<div><br></div>
<div>Any thoughts?</div>
<div><br></div>
<div><br></div></div></div></div></div><br>_______________________________________________<br>postgis-users
mailing list<br><a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
</div></blockquote><blockquote type="cite"><div><span>_______________________________________________</span><br><span>postgis-users mailing list</span><br><span><a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a></span><br><span><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></span></div></blockquote></body></html>