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

[Paragon Corporation]

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) ( 
 
<http://postgis.net/docs/doxygen/2.2/de/dc0/lwgeodetic__tree_8c_a15d46de8726
4f168426b23b4b34d75ab.html#a15d46de87264f168426b23b4b34d75ab>
circ_tree_distance_tree(tree_cache->
<http://postgis.net/docs/doxygen/2.2/d1/d12/structCircTreeGeomCache_af627139
6dbfa5645b4265f825f0e2015.html#af6271396dbfa5645b4265f825f0e2015> index,
circ_tree, s,
<http://postgis.net/docs/doxygen/2.2/d2/d54/liblwgeom__internal_8h_a946f0acf
f4fd16a65859479e9e0b9513.html#a946f0acff4fd16a65859479e9e0b9513>
FP_TOLERANCE);
 
and 
 
http://postgis.net/docs/doxygen/2.2/de/dc0/lwgeodetic__tree_8c_a15d46de87264
f168426b23b4b34d75ab.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



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140201/68d80e06/attachment.html>