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

Mon Feb 3 12:55:06 PST 2014

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