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

Mon Feb 3 16:19:31 PST 2014

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