<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 10.00.9200.16750"></HEAD>
<BODY>
<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> postgis-users-bounces@lists.osgeo.org
[mailto:postgis-users-bounces@lists.osgeo.org] <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></BODY></HTML>