Object Tables of Sdo_Geometry

This little tutorial shows what can be done using object tables with Sdo_Geometry.

Let us imagine that we have a table of FAUNA observations and wish to relate the observation to the cartographic MAPSHEET it falls within. Yes, this can be done via attribute based foreign keys but I thought I would show how to do it via Oracle Objects and Object References.

Drop our test tables.

Prompt 1. Drop tables...
DROP TABLE FAUNA;
DROP TABLE MapSheet;
PURGE RECYCLEBIN;

Generate an object table of mapsheet indexes.

Prompt 2. Create an object table of mapsheet indexes ...
CREATE TABLE MapSheet OF sdo_geometry;
Prompt 2.1 Describe it...
DESCRIBE MapSheet
Prompt 2.2 Generate our MapSheet data...
INSERT INTO MapSheet
 SELECT *
   FROM Table(codesys.TESSELATE.RegularGrid(
              MDSYS.SDO_POINT_TYPE(200000,5000000,NULL),
              MDSYS.SDO_POINT_TYPE(600000,6000000,NULL),
              MDSYS.SDO_POINT_TYPE(10000,10000,NULL),
              Null ) );
COMMIT;
Prompt 2.3 How many MapSheets do we have?
SELECT COUNT(*)
  FROM MapSheet;

We create metadata in the usual way before trying to create an index.

Prompt 3. Create SDO_GEOM_METADATA so we can index the mapsheet data...
Prompt 3.1 Delete any existing metadata...
DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'MAPSHEET';
COMMIT;
Prompt 3.2 Generate metadata in my usual manner...
DECLARE
  v_geom sdo_geometry;
BEGIN
  SELECT SDO_AGGR_MBR(
         SDO_GEOMETRY(SDO_GTYPE,SDO_SRID,SDO_POINT,SDO_ELEM_INFO,SDO_ORDINATES)
                     )
    INTO v_geom
    FROM MAPSHEET;
  -- Note: MDSYS. prefix to SDO_GEOMETRY constructor has to be there because the RTree indexing requires it.
  INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO,SRID)
     VALUES ('MAPSHEET',
             'MDSYS.SDO_GEOMETRY(SDO_GTYPE,SDO_SRID,SDO_POINT,SDO_ELEM_INFO,SDO_ORDINATES)',
             MDSYS.SDO_DIM_ARRAY(
     MDSYS.SDO_DIM_ELEMENT('X',v_geom.SDO_ORDINATES(1)-1,v_geom.SDO_ORDINATES(3)+1,0.5),
     MDSYS.SDO_DIM_ELEMENT('Y',v_geom.SDO_ORDINATES(2)-1,v_geom.SDO_ORDINATES(4)+1,0.5)),NULL);
END;
/
SHOW ERRORS
COMMIT;
Prompt 3.3. Check it was created properly...
SELECT table_name,
       column_name,
       diminfo
  FROM user_sdo_geom_metadata
 WHERE table_name = 'MAPSHEET';

Now we can index the mapsheet obect table.

 Prompt 4. Index the mapsheet geometry objects ....
 Prompt 4.1. Drop any existing index (it does not exist due to DROP TABLE above)...
 DROP INDEX MapSheet_Geom;
 Prompt 4.2. Create the index...
 Prompt Note: MDSYS. prefix to SDO_GEOMETRY constructor has to be there because the RTree indexing requires it.
 CREATE INDEX MapSheet_Geom
        ON MapSheet(MDSYS.SDO_GEOMETRY(SDO_GTYPE,SDO_SRID,SDO_POINT,SDO_ELEM_INFO,SDO_ORDINATES))
        INDEXTYPE is mdsys.spatial_index
        parameters('sdo_indx_dims=2, layer_gtype=polygon');

Some quick SQL checks on our MAPSHEET object table showing how to query it and access the sdo_geometry object.

 Prompt 5. Some queries.
 Prompt 5.1 Let us look at the geometry associated with the first 10 map sheets using VALUE()...
 SELECT VALUE(ms) AS geometry
   FROM MapSheet ms
  WHERE rownum < 10;
 Prompt 5.2 Find mapsheets inside a search area...
 SELECT count(*)
   FROM MapSheet ms
  WHERE SDO_ANYINTERACT(SDO_GEOMETRY(SDO_GTYPE,SDO_SRID,SDO_POINT,SDO_ELEM_INFO,SDO_ORDINATES),
                        MDSYS.SDO_GEOMETRY(2003,NULL,NULL,
                                           MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                           MDSYS.SDO_ORDINATE_ARRAY(300000,5400000,320000,5600000))) = 'TRUE';

Now we generate a table of fauna observations.

 Prompt 6. Create our FAUNA table with an object reference to our MapSheet geometries...
 CREATE TABLE FAUNA (
  id    integer,
  geom  sdo_geometry,
  MapSheet REF sdo_geometry SCOPE IS MapSheet );

 Prompt 7. Create FAUNA records (we will populate the mapsheet references later)...
 INSERT INTO fauna (ID, GEOM)
        SELECT rownum,
             sdo_geometry(2001,NULL,
                   MDSYS.SDO_POINT_TYPE(
                         ROUND(dbms_random.value(358880 - ( 10000 / 2 ),
                                                 358880 + ( 10000 / 2 )),1),
                         ROUND(dbms_random.value(5407473 - ( 5000 / 2 ),
                                                 5407473 + ( 5000 / 2 )),1),
                         NULL),
                   NULL,NULL)
         FROM DUAL 
       CONNECT BY LEVEL <= 500;
 COMMIT;
 Prompt 8. Check if successful by selecting the first 10...
 SELECT *
   FROM Fauna
  WHERE rownum < 10;

Again, create metadata before indexing.

 Prompt 9. Create SDO_GEOM_METADATA so we can index the mapsheet data...
 Prompt 9.1 Delete any existing metadata...
 DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'FAUNA';
 COMMIT;
 Prompt 9.2 Generate metadata in my usual manner...
 DECLARE
   v_geom sdo_geometry;
 BEGIN
   SELECT SDO_AGGR_MBR(geom) INTO v_geom FROM FAUNA;
   INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO,SRID)
     VALUES ('FAUNA','GEOM',MDSYS.SDO_DIM_ARRAY(
     MDSYS.SDO_DIM_ELEMENT('X',v_geom.SDO_ORDINATES(1)-1,v_geom.SDO_ORDINATES(3)+1,0.05),
     MDSYS.SDO_DIM_ELEMENT('Y',v_geom.SDO_ORDINATES(2)-1,v_geom.SDO_ORDINATES(4)+1,0.05)),NULL);
 END;
 /
 SHOW ERRORS
 Prompt 9.3. Check it was created properly...
 SELECT table_name,
        column_name,
        diminfo
   FROM user_sdo_geom_metadata
  WHERE table_name = 'FAUNA';

Then index the fauna geometry column.

 Prompt  10. Index the FAUNA geometry objects ....
 Prompt  10.1. Drop any existing index (it does not exist due to DROP TABLE above )...
 DROP    INDEX Fauna_Geom;
 Prompt  10.2. Create the index...
 CREATE  INDEX Fauna_Geom on Fauna(geom)
         INDEXTYPE is mdsys.spatial_index
         parameters('sdo_indx_dims=2, layer_gtype=point');

Linking the two tables is done via the object reference. We show this firstly as a result column in a query, then we use a traditional UPDATE statement to populate the link.

 Prompt 11. We will now populate the mapsheet column in the FAUNA table so that it references the mapsheet the observation falls in...
 Prompt 11.1 Firstly, show how to create a reference via use of the REF() operator in this query...
 Prompt      Note the way in which we reference the MapSheet object table...
 SELECT REF(ms)
 FROM   fauna fa,
        MapSheet ms
 WHERE  fa.id < 10
    AND SDO_CONTAINS(SDO_GEOMETRY(SDO_GTYPE,SDO_SRID,SDO_POINT,SDO_ELEM_INFO,SDO_ORDINATES), fa.geom) = 'TRUE';
 Prompt 11.2 Now, create the mapsheet reference via the REF() operator via a standard UPDATE SQL statement...
 UPDATE fauna fa
    SET fa.MapSheet = ( SELECT REF(ms)
                          FROM MapSheet ms
                         WHERE SDO_CONTAINS(SDO_GEOMETRY(SDO_GTYPE,SDO_SRID,SDO_POINT,SDO_ELEM_INFO,SDO_ORDINATES), fa.geom) = 'TRUE'
                      );
 COMMIT;

Some final queries….

 Prompt  12. Compare performance of traditional method of finding the mapsheet in which a fauna object exists via a spatial query ...
 Prompt  12.1 Show how to query the FAUNA table to return the actual ordinates of its associated mapsheet via the object reference...
 set timing on
 SELECT  fa.id,
         fa.geom.sdo_point.x,
         fa.geom.sdo_point.y,
         DEREF(fa.MapSheet).sdo_ordinates
   FROM  fauna SAMPLE (1) fa;
 set timing off
 Prompt  12.2 Now do the same thing but with a traditional Spatial Search ...
 set timing on
 SELECT  fa.id,
         fa.geom.sdo_point.x,
         fa.geom.sdo_point.y,
         VALUE(ms).sdo_ordinates
   FROM  fauna sample (1) fa ,
         MapSheet ms
   WHERE SDO_CONTAINS(SDO_GEOMETRY(SDO_GTYPE,SDO_SRID,SDO_POINT,SDO_ELEM_INFO,SDO_ORDINATES), fa.geom) = 'TRUE';
 set timing off

 Comment

The use of object tables and references is very unrelational. Yes, the SQL3 standard includes it but SQL is not synonymous with relational theory or databases (see Darwen and Date’s “Third Manifesto”). These ref types are there to appease the the objest orientation world view; for an old timer like me, they harken back to my early days on network databases. In summary, a ref type is not a relational join. Use only if you have to.