[postgis-users] Re: bug in bbox containment operators

[David Blasby]

Greg Janée wrote:
> David Blasby wrote:
> 
>> So, A @ B implies B ~ A, which is what you're seeing.
> 
> 
> Sorry I wasn't clear on what the problem is.  The bug I'm reporting is 
> that Postgres is returning items that are not contained within the query 
> region.  Look closely at the coordinates in my original email.

Hum -- there was an old bug which I thought we had fixed.

select
geometry_contain(
'POLYGON((-120 30,-119 30,-119 31,-120 31,-120  30))'::geometry,
'POLYGON((-180 -56,-180 85,180 85,180 -56,-180  -56))'::geometry
);

select
'POLYGON((-120 30,-119 30,-119 31,-120 31,-120  30))'::geometry     ~
'POLYGON((-180 -56,-180 85,180 85,180 -56,-180  -56))'::geometry
;

These should both give false.

select
geometry_contained(
'POLYGON((-120 30,-119 30,-119 31,-120 31,-120  30))'::geometry,
'POLYGON((-180 -56,-180 85,180 85,180 -56,-180  -56))'::geometry
);

select

'POLYGON((-120 30,-119 30,-119 31,-120 31,-120  30))'::geometry     @
'POLYGON((-180 -56,-180 85,180 85,180 -56,-180  -56))'::geometry
;


These should both give true.

If this all works, then try removing the index ("DROP INDEX <index 
name>") from your table and running your query again.

If this gives the correct answer, then its a problem with the RTree 
implementation.  Try this fix:

Modify postgis_gist_72.c, line about 204 that is currently:
     case RTContainsStrategyNumber:
       retval = DatumGetBool( DirectFunctionCall2( box_contain, 
PointerGetDatum(key), PointerGetDatum(query) ) );

change the "box_contain" to "box_contained", recompile, install, create 
an index on your table, vacuum analyse and re-try your query.

Does that fix it?

dave