[postgis-users] Error with ~ and @ operators and GIST indexes?

[Michael O'Sullivan]

Hi all,

I seem to have crossed a bug that was discussed on the list a while back 
trying to use the ~ and @ operators on GIST geometry based indexes.

Firstly, reading the doco for the ~ operator it states

A ~ B

The "~" operator returns true of A's bounding box is completely 
contained by B's bounding box.

However I see the opposite results

e.g.

A = BOX 11 11, 14 14
B = BOX 10 10, 15 15

A is definitely contained by B.

newnavitag=# select GeometryFromText('BOX3D(11 11, 14 14)',-1) ~ 
GeometryFromText('BOX3D(10 10, 15 15)',-1) as contained;
  contained
-----------
  f
(1 row)


newnavitag=# select GeometryFromText('BOX3D(10 10, 15 15)',-1) ~ 
GeometryFromText('BOX3D(11 11, 14 14)',-1) as contained;
  contained
-----------
  t

swapping ~ for @ gives the expected results


newnavitag=# select GeometryFromText('BOX3D(11 11, 14 14)',-1) @ 
GeometryFromText('BOX3D(10 10, 15 15)',-1) as contained;
  contained
-----------
  t
(1 row)

Reading postgis.sql I see that

@ calls procedure geometry_contained

and

~ calls procedure geometry_contain.


I have assumed a typo and have moved on to using @ in place of ~ the 
operator.


Now I have come to creating and using GIST indexes and I see the query 
on my dataset returning different results depending on whether I have 
the data indexed (gist) or not.


Reading a post in the list from a while back I see that there was a 
problem with postgis and postgres mixing the ~ and @ (contains and 
contained) operators. I thought this would explain the doco error and my 
data error.

I now find that the @ operator seems to do what the ~ is documented to 
do, however it still gives different data based on whether there is a 
GIST index on the geometries or not.

e.g.

newnavitag=# select name from geo_locations where the_geom @ 
GeometryFromText('BOX3D(150.0 -35.0, 152.0 -32.0 )', 4269);
    name
-----------
  NEWCASTLE
  SYDNEY
(2 rows)

newnavitag=# create INDEX geo_locations_gist_idx ON geo_locations USING 
GIST( the_geom GIST_GEOMETRY_OPS );
CREATE INDEX
newnavitag=# select name from geo_locations where the_geom @ 
GeometryFromText('BOX3D(150.0 -35.0, 152.0 -32.0 )', 4269);
  name
------
(0 rows)

newnavitag=# \d geo_locations
           Table "public.geo_locations"
    Column    |         Type          | Modifiers
-------------+-----------------------+-----------
  gid         | integer               | not null
  name        | character varying(64) |
  country     | character varying(96) |
  type        | character varying(32) |
  radius      | integer               |
  external_id | integer               |
  the_geom    | geometry              |
Indexes: geo_locations_pkey primary key btree (gid),
          geo_locations_gist_idx gist (the_geom)
Check constraints: "$1" (srid(the_geom) = 4269)
                    "$2" ((geometrytype(the_geom) = 'POINT'::text) OR 
(the_geom IS NULL))


My data is a set of POINT geometries in lat long.


I have tried reversing the arguments and using the ~ operator and I get 
the same results. When I perform the query with an index, I do not get 
the same results as when I have no ndex.


any ideas?


thanks,

Michael

-- 
Michael O'Sullivan <mos at i3sp.com>
"A good man always knows his limitations" - HC