Top 5 Recent Articles

ARTICLES CATEGORIES

Constraining geometry type for sdo_geometry column in a table.

Other blog postings of mine have shown how the layer_gtype keyword in the PARAMETERS clause of an RTree spatial index can be used to constrain the sdo_geometry type being stored in a column.

Remember, SDO_GEOMETRY is singly inherited: there is no POINT, CURVE, POLYGON etc sub-type. For this one must use the SQL/MM Type shipped with 10g and upwards.

Because we cannot do this …

 CREATE TABLE my_points (
   ID INTEGER,
   GEOM SDO_POINT
 );
 .
 Error starting at line 87 IN command:
 CREATE TABLE my_points (
   ID INTEGER,
   GEOM SDO_POINT
 )
 Error at Command Line:89 COLUMN:7
 Error report:
 SQL Error: ORA-00902: invalid datatype
 00902. 00000 -  "invalid datatype"

… We must constrain the column in some way.

Using the spatial index layer_gtype parameter is one way, but it is “external to the model” and not a fundamental part of the model.

(In database theory, the model should be self-referential: it should need no external elements, software, or application to define it: in my view this could include indexes.)

So, how can we add the constraint and keep it as a fundamental part of the model (and accessible via the model’s metadata)?

To do this, we need to use a CHECK constraint of some description.

Here is one method:

 CREATE TABLE my_points (
   id         INTEGER,
   point      SDO_GEOMETRY,
   CONSTRAINT my_points_pk PRIMARY KEY ( id ),
   CONSTRAINT my_points_point_ck CHECK ( point.sdo_gtype = 1 )
 );
 .
 TABLE MY_POINTS created.
 .
 SET NULL '*NULL*'
 SELECT constraint_name, constraint_type, index_name, search_condition
   FROM user_constraints
  WHERE TABLE_NAME = 'MY_POINTS';
 .
 CONSTRAINT_NAME    CONSTRAINT_TYPE INDEX_NAME   SEARCH_CONDITION
 ------------------ --------------- ------------ -------------------
 MY_POINTS_POINT_CK C               *NULL*       point.sdo_gtype = 1
 MY_POINTS_PK       P               MY_POINTS_PK *NULL*

And here is another method.

 CREATE TABLE my_points (
   id         INTEGER,
   point      SDO_GEOMETRY
 );
 .
 TABLE MY_POINTS created.
 .
 ALTER TABLE my_points ADD CONSTRAINT my_points_pk PRIMARY KEY ( id );
 .
 TABLE MY_POINTS altered.
 .
 ALTER TABLE my_points ADD CONSTRAINT my_points_point_ck CHECK ( point.sdo_gtype = 1 );
 .
 TABLE MY_POINTS altered.
 .
 SET NULL '*NULL*'
 SELECT constraint_name, constraint_type, index_name, search_condition
   FROM user_constraints
  WHERE TABLE_NAME = 'MY_POINTS';
 .
 CONSTRAINT_NAME    CONSTRAINT_TYPE INDEX_NAME   SEARCH_CONDITION
 ------------------ --------------- ------------ -------------------
 MY_POINTS_POINT_CK C               *NULL*       point.sdo_gtype = 1
 MY_POINTS_PK       P               MY_POINTS_PK *NULL*
 .
 -- Now test the constraint
 --
 INSERT INTO uniquePointTest(id,geom) VALUES (11,MDSYS.SDO_GEOMETRY(2002, 8311, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(129.28,-11.06,128.28,-12.06)));
 .
 Error starting at line 110 IN command:
 INSERT INTO uniquePointTest(id,geom) VALUES (11,MDSYS.SDO_GEOMETRY(2002, 8311, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(129.28,-11.06,128.28,-12.06)))
 Error report:
 SQL Error: ORA-02290: CHECK CONSTRAINT (CODESYS.UNIQUEPOINTTEST_POINT_GTYPE_CK) violated
 02290. 00000 -  "check constraint (%s.%s) violated"
 *Cause:    The VALUES being inserted do NOT satisfy the named CHECK
 *Action:   do NOT INSERT VALUES that violate the CONSTRAINT.

Belt and Suspenders

Should I now do away with the RTree layer_gtype parameter? Of course not as it triggers RTree index optimizations that improve query performance. By adding it in one creates a “belt and suspenders” (Both/And) approach to data quality. Doesn’t hurt!

I hope this is of interest to someone.