[postgis-users] Odd Behavior with ST_Intersects (PostGIS 2.1, Ubuntu 12.04)

Tue Feb 4 08:22:18 PST 2014

Paul,

Agreed.  I suspect that this was masking another, more subtle issue in 2.1.

And I can confirm that 2.0.x has been working as designed.  We've been
running it for just over a year at this point, and have yet to see the
random failures that I reported with the brute force change in 2.1.  We met
here, and decided to stay with 2.0.4 for the time being, patching it to
work with PG9.3.  2.0.x has been rock solid for us, and has worked very
well for our use case.

I am happy to try the 2.1 SVN branch, but will only be able to spend
limited time with it.  I will still try to isolate a test case if I can.

On Mon, Feb 3, 2014 at 10:10 PM, Paul Ramsey <pramsey at cleverelephant.ca>wrote:

> That's really quite disturbing, since it would seem to indicate that
> even the core algorithms are messed up. And yet you say 2.0 was fine?
> Because _ST_DistanceUncached is functionally "I want 2.0" (brute force
> algorithm all the time).
>
> In other news, do try the 2.1 SVN branch, it contains a fix for the
> cache problem that "small polygon in big polygon" cases exposed.
>
> P.
>
>
> On Mon, Feb 3, 2014 at 5:30 PM, Jerry Sievert
> <jerry at legitimatesounding.com> wrote:
> > Paul,
> >
> > Unfortunately, when we use the _ST_DistanceUncached via the
> _st_distance()
> > stored procedure change, we start to see failures in other places.  As I
> > said, I am trying to isolate them, but the failures that we start to see
> are
> > fairly random: the initial issue is fixed, but we start to receive bad
> data
> > back from other queries, and not repeatable through two test runs.  That
> > makes it much harder to isolate, given the size of our application.
> >
> > Thus, this fix introduces a bit of "randomness".
> >
> >
> > On Mon, Feb 3, 2014 at 4:19 PM, Paul Ramsey <pramsey at cleverelephant.ca>
> > wrote:
> >>
> >> Jerry,
> >>
> >> Pretty important to isolate them, as that function's at the bottom of
> >> the cached tree algorithm, so even if I fix the gap in the cache logic
> >> I think I have found, you'll still run into other issues later.
> >>
> >> _ST_DistanceUncached uses brute force (aka 2.0) methods, and should
> >> work without change from previous versions (would be nice to hear
> >> confirmation of *that* assumption at least)
> >>
> >> P.
> >>
> >> On Mon, Feb 3, 2014 at 12:55 PM, Jerry Sievert
> >> <jerry at legitimatesounding.com> wrote:
> >> > 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.
> >> >
> >> > On Feb 1, 2014, at 6:55 PM, "Paragon Corporation" <lr at pcorp.us>
> wrote:
> >> >
> >> > Jerry,
> >> > 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.
> >> >
> >> >
> >> >
> http://postgis.net/docs/doxygen/2.2/d8/de3/geography__measurement__trees_8c_a399a5e30071a3979b414ce094ff94de9.html#a399a5e30071a3979b414ce094ff94de9
> >> > (where it computes the last distance) (
> >> >
> >> > circ_tree_distance_tree(tree_cache->index, circ_tree, s,
> FP_TOLERANCE);
> >> >
> >> >
> >> > and
> >> >
> >> >
> >> >
> http://postgis.net/docs/doxygen/2.2/de/dc0/lwgeodetic__tree_8c_a15d46de87264f168426b23b4b34d75ab.html#a15d46de87264f168426b23b4b34d75ab
> >> > (circ_tree_distance_tree)
> >> >
> >> > 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:
> >> >
> >> > CREATE OR REPLACE FUNCTION _st_distance(geography, geography, double
> >> > precision, boolean)
> >> >   RETURNS double precision AS
> >> > '$libdir/postgis-2.2', 'geography_distance_uncached'
> >> >   LANGUAGE c IMMUTABLE STRICT
> >> >   COST 100;
> >> >
> >> >
> >> > See my note here:
> >> > http://trac.osgeo.org/postgis/ticket/2556
> >> >
> >> > Thanks for your patience,
> >> > Regina
> >> >
> >> > http://www.postgis.us
> >> > http://postgis.net
> >> >
> >> > ________________________________
> >> > From: postgis-users-bounces at lists.osgeo.org
> >> > [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Jerry
> >> > Sievert
> >> > Sent: Saturday, February 01, 2014 1:24 PM
> >> > To: PostGIS Users Discussion
> >> > Subject: Re: [postgis-users] Odd Behavior with ST_Intersects (PostGIS
> >> > 2.1,
> >> > Ubuntu 12.04)
> >> >
> >> > 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.
> >> >
> >> >
> >> >
> >> > On Fri, Jan 31, 2014 at 8:33 PM, Paragon Corporation <lr at pcorp.us>
> >> > wrote:
> >> >>
> >> >> ah found the other one:
> >> >>
> >> >> http://trac.osgeo.org/postgis/ticket/2556
> >> >>
> >> >> ________________________________
> >> >> From: postgis-users-bounces at lists.osgeo.org
> >> >> [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Paragon
> >> >> Corporation
> >> >> Sent: Friday, January 31, 2014 11:27 PM
> >> >> To: 'PostGIS Users Discussion'
> >> >> Cc: 'PostGIS Development Discussion'
> >> >> Subject: Re: [postgis-users] Odd Behavior with ST_Intersects (PostGIS
> >> >> 2.1,Ubuntu 12.04)
> >> >>
> >> >> Jerry,
> >> >>
> >> >> 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.
> >> >>
> >> >> I have added to my original ticket:
> >> >> http://trac.osgeo.org/postgis/ticket/2422
> >> >>
> >> >> I think there is another ticket in our bug tracker that might be the
> >> >> same
> >> >> issue too but can't recall the number.
> >> >>
> >> >> Thanks very very much for this great example.
> >> >>
> >> >> Paul -- if you are watching you have work to do :)
> >> >>
> >> >> Thanks,
> >> >> Regina
> >> >> http://www.postgis.us
> >> >> http://postgis.net
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> ________________________________
> >> >> From: postgis-users-bounces at lists.osgeo.org
> >> >> [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Jerry
> >> >> Sievert
> >> >> Sent: Friday, January 31, 2014 6:25 PM
> >> >> To: postgis-users at lists.osgeo.org
> >> >> Subject: [postgis-users] Odd Behavior with ST_Intersects (PostGIS
> >> >> 2.1,Ubuntu 12.04)
> >> >>
> >> >> Hello,
> >> >>
> >> >> I am using Ubuntu 12.04 with the official PostgreSQL apt repo (via
> >> >> https://wiki.postgresql.org/wiki/Apt).  I am running into an easily
> >> >> reproducible issue, and was hoping for some help to solve this.
> >> >>
> >> >> When using ST_Intersects() not all rows that intersect are returned.
> >> >> This
> >> >> was not the case in previous versions that we have upgraded from.
> >> >>
> >> >> These are the steps to reproduce on a fresh install of Ubuntu 12.04
> >> >> with
> >> >> all packages updated and PostGIS/PostgreSQL 9.3 installed:
> >> >>
> >> >> test=# CREATE TABLE test (id serial, condition_geo geography);
> >> >> CREATE TABLE
> >> >> test=# INSERT INTO test (condition_geo) VALUES
> >> >> (ST_Buffer(ST_GeogFromWKB(ST_MakePoint(20.0,30.0)),10.0));
> >> >> INSERT 0 1
> >> >> test=# SELECT id FROM test WHERE ST_Intersects("condition_geo",
> >> >> ST_Buffer(ST_GeogFromText('POINT(20.0 30.0)'), 20.0)) IS TRUE;
> >> >>  id
> >> >> ----
> >> >>   1
> >> >> (1 row)
> >> >>
> >> >> test=# INSERT INTO test (condition_geo) VALUES
> >> >> (ST_Buffer(ST_GeogFromWKB(ST_MakePoint(20.0,30.0)),10.0));
> >> >> INSERT 0 1
> >> >> test=# SELECT id FROM test WHERE ST_Intersects("condition_geo",
> >> >> ST_Buffer(ST_GeogFromText('POINT(20.0 30.0)'), 20.0)) IS TRUE;
> >> >>  id
> >> >> ----
> >> >>   1
> >> >> (1 row)
> >> >>
> >> >> 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;
> >> >>  id
> >> >> ----
> >> >>   2
> >> >> (1 row)
> >> >>
> >> >> Note that the SELECT should return both rows 1 and 2 in the first
> >> >> SELECT.
> >> >>
> >> >> Any thoughts?
> >> >>
> >> >>
> >> >>
> >> >> _______________________________________________
> >> >> postgis-users mailing list
> >> >> postgis-users at lists.osgeo.org
> >> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >> >
> >> >
> >> > _______________________________________________
> >> > postgis-users mailing list
> >> > postgis-users at lists.osgeo.org
> >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >> >
> >> >
> >> > _______________________________________________
> >> > postgis-users mailing list
> >> > postgis-users at lists.osgeo.org
> >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at lists.osgeo.org
> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at lists.osgeo.org
> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140204/64795664/attachment.html>