Go to content Go to navigation and search

Current Oracle Spatial Blogs

Search

RSS / Atom

Email me

textpattern

Oracle's SQL/MM Compliant Types · Nov 9, 01:28 pm by Simon Greener

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 >>
  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 >>
      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 < 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...
  Textile Help

<<Tips and Tricks >>Tip #3: Generating sample (test) point data