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

Tue Feb 4 09:51:59 PST 2014

Jerry,
So far you're the only one to report issues w/ the core functions.
Both _ST_DistanceTreea and _ST_DistanceUncached have worked for folks
who ran into the caching problem. And as I noted, _ST_DistanceUncached
is the old 2.0 logic. So I'd very much like to hear more about issues
w/ _ST_DistanceUncached.
P.

On Tue, Feb 4, 2014 at 8:22 AM, Jerry Sievert
<jerry at legitimatesounding.com> wrote:
> 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
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users