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

Mon Feb 3 22:10:59 PST 2014

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