The Spatial filtering of geometries: The effect of tolerances on relationships

The knowledgeable Bryan Hall made a post over on the Oracle Technology Network’s Oracle Spatial Forum reminding users of the “hidden” issues that inform the way Oracle compare’s two geometries when using a secondary filter

Here is the relevant section of the post.

With relationship operators such as sdo_anyinteract that take two parameters for the spatial columns to use for the test. The first must be indexed (a real table), and the second can be indexed, or just a passed or created spatial object. In my cases, both are always indexed, and most spatial items have a very tight tolerance. But for one object, instead of the 0.001 meter tolerance, it had a 0.1 meter tolerance. I didn’t realize that – until I ran the query both ways (using the ORDERED hint) to see which would be quicker for the script – and then came back with a different number of results.

The difference was the order of use in sdo_anyinteract. If the first item listed has a wider tolerance, more results are returned as it is more inclusive. However, if the order is reversed, the more restrictive tolerance is used and you get fewer results.

Let’s look at this in detail.

Some data

 -- Rect1
 DROP   TABLE rect1;
 CREATE TABLE rect1 ( id   INTEGER, geom mdsys.sdo_geometry);
 INSERT INTO rect1 (ID,GEOM) VALUES (1,mdsys.sdo_geometry(2003,NULL,NULL,mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(1,1,10,10))); COMMIT;
 DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'RECT1' AND column_name = 'GEOM'; COMMIT;
 INSERT INTO user_sdo_geom_metadata (TABLE_NAME,column_name,srid,diminfo) VALUES ('RECT1','GEOM',NULL,mdsys.sdo_dim_array(mdsys.sdo_dim_element('X',0,11,0.05),mdsys.sdo_dim_element('Y',0,11,0.05))); COMMIT;
 CREATE INDEX rect1_geom_spdx ON rect1(geom) INDEXTYPE IS mdsys.spatial_index PARAMETERS('sdo_indx_dims=2, layer_gtype=POLYGON');
 -- Rect2
 DROP   TABLE rect2;
 CREATE TABLE rect2 ( id   INTEGER, geom mdsys.sdo_geometry);
 INSERT INTO rect2 (ID,GEOM) VALUES (1,mdsys.sdo_geometry(2003,NULL,NULL,mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(10.01,10.01,20,20))); COMMIT;
 DELETE FROM user_sdo_geom_metadata WHERE TABLE_NAME = 'RECT2' AND column_name = 'GEOM'; COMMIT;
 INSERT INTO user_sdo_geom_metadata (TABLE_NAME,column_name,srid,diminfo) VALUES ('RECT2','GEOM',NULL,mdsys.sdo_dim_array(mdsys.sdo_dim_element('X',10,21,0.0005),mdsys.sdo_dim_element('Y',10,21,0.0005))); COMMIT;
 CREATE INDEX rect2 ON rect2(geom) INDEXTYPE IS mdsys.spatial_index PARAMETERS('sdo_indx_dims=2, layer_gtype=POLYGON');

Note that a tolerance of 0.05 is the same as 0.1 and 0.0005 the same as 0.001.

Relationship Operators/Functions and Parameters

Firstly, let’s use a relationship function that takes a tolerance input to show the differences when the tables have two different tolerances.

 SELECT SDO_GEOM.RELATE(a.geom,'DETERMINE',b.geom,0.5  /* rect1 tolerance */) AS u1relate,
        SDO_GEOM.RELATE(a.geom,'DETERMINE',b.geom,0.005/* rect2 tolerance */) AS u2relate
   FROM rect1 a,
        rect2 b,
        user_sdo_geom_metadata u1,
        user_sdo_geom_metadata u2
  WHERE ( u1.TABLE_NAME = 'RECT1' AND u1.column_name = 'GEOM' )
    AND ( u2.TABLE_NAME = 'RECT1' AND u2.column_name = 'GEOM' );

Notice that at the coarser RECT1 tolerance of 0.5 the objects touch – which is correct.
But at the finer RECT2 tolerance of 0.005 the objects are disjoint – which is also correct.

So, now what happens if we search as per Bryan’s posting?

Firstly, searching for the RECT2’s geometry which has the finer tolerance:

 SELECT /*+ORDERED*/
        a.id, b.id
   FROM rect1 a,
        rect2 b
  WHERE SDO_RELATE(b.geom,a.geom,'mask=anyinteract') = 'TRUE';
 -- Results
 -- NOTHING ie DISJOINT

And the reverse, searching for RECT1’s geometry which has the coarser tolerance:

 SELECT /*+ORDERED*/
        a.id, b.id
   FROM rect2 b,
        rect1 a
  WHERE SDO_RELATE(a.geom,b.geom,'mask=anyinteract') = 'TRUE';
 -- Result
 ID ID
 -- --
 1  1

That is, they have an interaction (ie TOUCH).

Assuming that these Oracle secondary filter functions use SDO_GEOM.RELATE to compare two geometries then we seem to have duplicated Bryan’s problem.

Now, let’s repeat this test using one of the “wrapper” secondary filters.

 SELECT /*+ORDERED*/
        a.id, b.id
   FROM rect1 a,
        rect2 b
  WHERE SDO_ANYINTERACT(b.geom,a.geom) = 'TRUE';
 -- Results
 -- NOTHING ie DISJOINT

And the reverse:

 SELECT /*+ORDERED*/
        a.id, b.id
   FROM rect2 b,
        rect1 a
  WHERE SDO_TOUCH(a.geom,b.geom) = 'TRUE';
 -- Result
 ID ID
 -- --
 1  1

Which is as expected.

Trouble at Mill

However, SDO_GEOM.RELATE does not perform as expected when on uses the version of
the function with two diminfo parameters:

 SELECT SDO_GEOM.RELATE(a.geom,u1.diminfo,'DETERMINE',b.geom,u1.diminfo) AS u11relate,
        SDO_GEOM.RELATE(a.geom,u1.diminfo,'DETERMINE',b.geom,u2.diminfo) AS u12relate,
        SDO_GEOM.RELATE(a.geom,u2.diminfo,'DETERMINE',b.geom,u1.diminfo) AS u21relate,
        SDO_GEOM.RELATE(a.geom,u2.diminfo,'DETERMINE',b.geom,u2.diminfo) AS u22relate
   FROM rect1 a,
        rect2 b,
        user_sdo_geom_metadata u1,
        user_sdo_geom_metadata u2
  WHERE ( u1.TABLE_NAME = 'RECT1' AND u1.column_name = 'GEOM' )
    AND ( u2.TABLE_NAME = 'RECT2' AND u2.column_name = 'GEOM' );
 -- Results
 U11RELATE U12RELATE U21RELATE U22RELATE
 --------- --------- --------- ---------
 TOUCH     TOUCH     TOUCH     TOUCH

Caveat Emptor! (Latin for “Let the buyer beware”)

Hope this helps someone.