Oracle’s SQL/MM Compliant Types

Folks, there has been much made that Oracle Spatial is proprietary and that alternate implementations had to be done because the Sdo_Geometry implementation is not SQL/MM compliant.

What many do not know is that, since 10gR1, Oracle has implemented a wrapper set of objects over the top of their Sdo_Geometry implementation (limitations in Oracle objects made this more difficult in earlier versions).

To illustrate this I have constructed a very simple sql script which I now include in-line. If you cut it out you will discover that it works. However, you have to install my packages into a schema call CODESYS to make it work as I do use the QuadTree generation code in the TESSELATE package to generate the ST_Polygon data.

My tests all ran fine on XE and 10gR2.

In summary. If you want to use SQL/MM compliant types then you can. There are some idiosyncrasies (standards are notorious for the “wiggle-room” they provide implementors) and some limitations that can and will be fixed but, as someone once famous said: “it just works!” (Aside: Though Oracle could have saved themselves a lot of grief by naming Sdo_Geometry as ST_Geometry 10 years ago.)

 set timing on
 set linesize 120
 set pagesize 1000
 drop table my_points;
 create table my_points (
   point_id     RAW(64) primary key ,
   geometry     mdsys.ST_Point
 ) PCTUSED 99;

 Declare
   Type row_t is table of my_points%ROWTYPE index by PLS_INTEGER;
   v_Rows      row_t;
   v_Row       my_points%ROWTYPE;
   v_pt        MDSYS.SDO_Point_Type := MDSYS.SDO_Point_Type(NULL,NULL,NULL);
   v_lo        MDSYS.SDO_Point_Type := MDSYS.SDO_POINT_TYPE(100000,5200000,NULL);
   v_hi        MDSYS.SDO_Point_Type := MDSYS.SDO_POINT_TYPE(300000,5300000,NULL);
   v_pk        RAW(64);
   v_RowCount  PLS_INTEGER := 1;
   v_Commit    PLS_INTEGER := 2000;
   v_MaxPts    PLS_INTEGER := 200000;
   bulk_errors EXCEPTION;
   PRAGMA      EXCEPTION_INIT (bulk_errors, -24381);
 Begin
   << point_generation_loop &amp;gt;&amp;gt;
   For i In 1..v_MaxPts Loop
      v_pt.x := dbms_random.value(v_lo.x,v_hi.x);
      v_pt.y := dbms_random.value(v_lo.y,v_hi.y);
      v_Row.geometry := MDSYS.ST_Point(v_pt.x,v_pt.y);
      v_Row.point_id := codesys.tesselate.Space_Key(v_pt,v_lo,v_hi);
      v_Rows(v_RowCount) := v_Row;
      If ( Mod(v_RowCount,v_Commit) = 0 ) Then
        ForAll i In 1..v_RowCount
           Save Exceptions
           Insert Into my_points Values v_Rows(i);
        Commit;
        v_rowCount := 0;
     End If;
     v_rowCount := v_rowCount + 1;
   End Loop point_generation_loop;
   Exception
     When BULK_ERRORS Then
        << bulk_errors_loop &amp;gt;&amp;gt;
       For j In 1 .. SQL%BULK_EXCEPTIONS.COUNT Loop
         DBMS_OUTPUT.PUT_LINE ( 'Error from element #' ||
                     TO_CHAR(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' ||
                     SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
 End Loop bulk_errors_loop;
 End;
 /

 Prompt Get Extent of data to test the insert ...
 SELECT min( a.geometry.st_x() ) as minx,
        max( a.geometry.st_x() ) as maxx,
        min( a.geometry.st_y() ) as miny,
        max( a.geometry.st_y() ) as maxy,
        count(*) as NumRecs
   FROM my_points a;

 Prompt Create Oracle Metadata Entry...
 DELETE FROM user_sdo_geom_metadata
 WHERE table_name = 'MY_POINTS';
 COMMIT;
 INSERT INTO user_sdo_geom_metadata (
 table_name,
 column_name,
 diminfo,
 srid)
 SELECT 'MY_POINTS','GEOMETRY.GEOM',
 MDSYS.SDO_DIM_ARRAY(
 MDSYS.SDO_DIM_ELEMENT('X', minx, maxx, 0.5), MDSYS.SDO_DIM_ELEMENT('Y', miny, maxy, 0.5)),
 NULL
   FROM (
   SELECT TRUNC( min( a.geometry.st_x() ) - 1,0) as minx, 
          ROUND( max( a.geometry.st_x() ) + 1,0) as maxx,
          TRUNC( min( a.geometry.st_y() ) - 1,0) as miny,
          ROUND( max( a.geometry.st_y() ) + 1,0) as maxy
     FROM my_points a);

 Prompt Create RTree index on point data ...
 DROP INDEX my_points_geometry;

 CREATE INDEX my_points_geometry ON my_points(geometry.geom)
 INDEXTYPE IS MDSYS.SPATIAL_INDEX
 PARAMETERS('sdo_indx_dims=2, layer_gtype=point');

 set serveroutput on size 100000
 Prompt Now create some polygons by quadtree tesselation of the space my_points occupies
 DECLARE
   v_quadid INTEGER;
   v_diminfo MDSYS.SDO_DIM_ARRAY;
 BEGIN
   SELECT diminfo
     INTO v_diminfo
     FROM user_sdo_geom_metadata
    WHERE table_name = 'MY_POINTS';
   codesys.Tesselate.SetGeom2SQLMM;
   codesys.Tesselate.Initialise(5000,
                                'MY_POINTS',
                                'GEOMETRY.GEOM',
                                20);
   v_QuadId := codesys.Tesselate.QuadTree( v_diminfo );
 END;
 /
 Prompt Create Primary Key on Quad.Quad_Id data ...
 ALTER TABLE quad
   ADD CONSTRAINT quad_quad_id_pk PRIMARY KEY (quad_id);

 Prompt Create RTree index on st_polygon data ...
 DROP INDEX quad_geometry;

 CREATE INDEX quad_geometry ON quad(geometry.geom)
 INDEXTYPE IS MDSYS.SPATIAL_INDEX
 PARAMETERS('sdo_indx_dims=2, layer_gtype=polygon');

 Prompt Some ST_Polygon inspectors...
 SELECT q.geometry.ST_CoordDim()
   FROM quad q
  WHERE rownum < 2;

 SELECT q.geometry.ST_Dimension()
   FROM quad q
  WHERE rownum < 2;

 SELECT distinct q.geometry.ST_Area()
   FROM quad q;

 SELECT q.geometry.ST_Envelope()
   FROM quad q
  WHERE rownum < 2;

 SELECT q.geometry.ST_IsSimple()
   FROM quad q
  WHERE rownum < 2;

 SELECT q.geometry.ST_ExteriorRing()
   FROM quad q
  WHERE rownum < 2;

 SELECT distinct q.geometry.ST_ExteriorRing().ST_Length()
   FROM quad q;

 SELECT q.geometry.ST_Buffer(10)
   FROM quad q
  WHERE rownum < 2;

 SELECT q.geometry.ST_NumInteriorRing()
   FROM quad q
  WHERE rownum < 2;

 SELECT q.geometry.ST_Centroid()
   FROM quad q
  WHERE rownum < 2;

 set serveroutput on size 100000
 declare
   v_coord      number;
   v_point      mdsys.st_point;
   v_linestring mdsys.st_linestring;
 begin
   SELECT q.geometry.ST_ExteriorRing()
     INTO v_linestring
     FROM quad q
    WHERE rownum &amp;lt; 2;
   For v_i in 1..v_linestring.ST_NumPoints() Loop
     v_point := v_linestring.st_PointN(v_i);
     dbms_output.put_line('x='||v_point.St_X()||' y='||v_point.st_y());
   End Loop;
 end;
 /

 Prompt This is where the problem is... no public methods for executing spatial searches using ST_* methods...
 Prompt If I am wrong... email me and I will change this posting.
 explain plan

 set statement_id = 'SQL/MM QUERY'
 for
 SELECT count(*)
   FROM quad q,
        my_points p
  WHERE q.quad_id = 43
    AND p.geometry.ST_Within(q.geometry) = 1;

 SELECT *
   FROM TABLE(dbms_xplan.display('PLAN_TABLE','SQL/MM QUERY','TYPICAL')); 

 Prompt One can get around it mixing Sdo_Geometry searching and ST_* geometric methods ...

 set timing on
 SELECT count(*)
    FROM quad q,
        my_points p
  WHERE q.quad_id = 43
    AND SDO_Filter(p.geometry.geom,q.geometry.geom) = 'TRUE'
    AND p.geometry.ST_Within(q.geometry) = 1;

 Prompt Perhaps in 11g?
 Prompt Until then, I am looking at building a custom domain index and methods that use it... perhaps I will get this done in the next 3 months.. I have made a start but...