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

Mon Feb 3 17:30:06 PST 2014

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140203/4152afe5/attachment.html>