Oracle Spatial Topological Relations

This script shows the different topological relations in Oracle Spacial. For example, when you want to select geometries that overlap (like A with C,D,E), you would use the filter:

• inside+coveredby+overlapbdyintersect+overlapbdydisjoint+equal+on

        ___
       | F |
       |___|
   _________________
  | A   ___      ___|___ 
  |    | D |    | E | B |
  |    |___|    |___|___|
  |     ___         |
  |____|   |________|
       | C |
       |___|

drop table t_geo;
create table t_geo(naam varchar2(30), geometrie mdsys.sdo_geometry);

define SRID=NULL
define allLEFT=0
define allRIGHT=100
define allBOTTOM=0
define allTOP=100
define accX=1
define accY=1
define geotabel=t_geo
define geokolom=geometrie
DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME=Upper('&geotabel');
--DROP INDEX &geotabel._SP_IDX;
INSERT INTO USER_SDO_GEOM_METADATA VALUES
  (Upper('&geotabel'),Upper('&geokolom'), MDSYS.SDO_DIM_ARRAY
    (MDSYS.SDO_DIM_ELEMENT('X', &allLEFT, &allRIGHT, &accX),
      MDSYS.SDO_DIM_ELEMENT('Y', &allBOTTOM, &allTOP, &accY) ), &SRID );
CREATE INDEX &geotabel._SP_IDX ON &geotabel. ( &geokolom ) INDEXTYPE IS MDSYS.SPATIAL_INDEX;


insert into t_geo values ('A'
  , MDSYS.SDO_GEOMETRY(2003, &SRID, NULL
    , MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1)
    , MDSYS.SDO_ORDINATE_ARRAY(
       10,10
       ,40,10
       ,40,20
       ,10,20
       ,10,10
       )));
insert into t_geo values ('B-touch'
  , MDSYS.SDO_GEOMETRY(2003, &SRID, NULL
    , MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1)
    , MDSYS.SDO_ORDINATE_ARRAY(
       40,13
       ,45,13
       ,45,17
       ,40,17
       ,40,13
       )));
insert into t_geo values ('C-overlapBdyInterect'
  , MDSYS.SDO_GEOMETRY(2003, &SRID, NULL
    , MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1)
    , MDSYS.SDO_ORDINATE_ARRAY(
       15,5
       ,20,5
       ,20,13
       ,15,13
       ,15,5
       )));
insert into t_geo values ('D-contains'
  , MDSYS.SDO_GEOMETRY(2003, &SRID, NULL
    , MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1)
    , MDSYS.SDO_ORDINATE_ARRAY(
       15,13
       ,20,13
       ,20,17
       ,15,17
       ,15,13
       )));
insert into t_geo values ('E-touchInside'
  , MDSYS.SDO_GEOMETRY(2003, &SRID, NULL
    , MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1)
    , MDSYS.SDO_ORDINATE_ARRAY(
       35,13
       ,40,13
       ,40,17
       ,35,17
       ,35,13
       )));
insert into t_geo values ('F-disjoint'
  , MDSYS.SDO_GEOMETRY(2003, &SRID, NULL
    , MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1)
    , MDSYS.SDO_ORDINATE_ARRAY(
       15,25
       ,20,25
       ,20,30
       ,15,30
       ,15,25
       )));
commit;

select g1.naam as geo1, g2.naam as geo2
  , SDO_RELATE(g1.geometrie, g2.geometrie, 'mask=inside') as inside
  , SDO_RELATE(g1.geometrie, g2.geometrie, 'mask=contains') as contains
  , SDO_RELATE(g1.geometrie, g2.geometrie, 'mask=coveredby') as coveredby
  , SDO_RELATE(g1.geometrie, g2.geometrie, 'mask=covers') as covers
  , SDO_RELATE(g1.geometrie, g2.geometrie, 'mask=touch') as touch
  , SDO_RELATE(g1.geometrie, g2.geometrie, 'mask=overlapbdyintersect') as overlapbdyintersect
  , SDO_RELATE(g1.geometrie, g2.geometrie, 'mask=overlapbdydisjoint') as overlapbdydisjoint
  , SDO_RELATE(g1.geometrie, g2.geometrie, 'mask=equal') as equal
  , SDO_RELATE(g1.geometrie, g2.geometrie, 'mask=disjoint') as disjoint
  , SDO_RELATE(g1.geometrie, g2.geometrie, 'mask=on') as on_
  , SDO_RELATE(g1.geometrie, g2.geometrie, 'mask=inside+coveredby+overlapbdyintersect+overlapbdydisjoint+equal+on') as combined
  from t_geo g1, t_geo g2
  where g2.naam='A'
  ;