Forcing an Sdo_Geometry object to contain only points, lines or areas

Data quality in databases means using the power of the database’s referential integrity functionality to ensure that data is correct regardless as to what client is creating, updating or deleting data.

One common requirement for spatial data in Oracle is to be able to ensure that the values of an sdo_geometry column in a table contain only points, lines or areas. For example, a column describing a road centreline should contain only 2002 (single linestrings) or 2006 (multi-part linestrings) type sdo_geometries; or a table/column containing polygon and multi-polygon sdo_geometries only etc.

This can be achieved in two ways: via a check constraint and via a constraining index. These will now be demonstrated on the following table (part of the tables loaded with my pl/sql packages):

 codesys@XE> desc projpoly2d
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------
 POLYTYPE                                                                         VARCHAR2(40)
 GEOM                                                                             MDSYS.SDO_GEOMETRY

1. Check constraint.

One method for implementing this constraint is to use a standard column check constraint as follows:

 codesys@XE> alter table projpoly2d add constraints geom_ck check ( geom.sdo_gtype in (2003,2007) );
 Table altered.

Now if we try and insert data into the table…

 codesys@XE> insert into projpoly2d values('POINT',mdsys.sdo_geometry(2001,null,mdsys.sdo_point_type(1,1,null),null,null));
 insert into projpoly2d values('POINT',mdsys.sdo_geometry(2001,null,mdsys.sdo_point_type(1,1,null),null,null))
 *
 ERROR at line 1:
 ORA-02290: check constraint (CODESYS.GEOM_CK) violated

2. RTree index.

Before we describe this method we will first drop the check constraint from above.

codesys@XE> alter table projpoly2d drop constraint geom_ck;
Table altered.

This method uses the layer_gtype parameter in the mdsys.spatial_index’s parameter clause to restrict the spatial types that can be stored in a table. The method involves creating a spatial index that can only index sdo_geometry data of the required type. Thus, when an insert or update of the geometry column occurs the index checks the geometry type of the new sdo_geometry object and if it is of the required type indexes it; if it is not of the required type it will throw an error.

Firstly, create the index with the appropriate layer_gtype (note if a table contains mutil-part geometries and single-part geometries the multi-part geometry layer_gtype should be specified):

codesys@XE> create index PROJPOLY2D_GEOM
  2 on projpoly2d(geom)
  3 indextype is mdsys.spatial_index
  4 parameters('sdo_indx_dims=2, layer_gtype=multipolygon');
Index created.

Now attempt to insert a record.

 codesys@XE> insert into projpoly2d values('POINT',mdsys.sdo_geometry(2001,null,mdsys.sdo_point_type(1,1,null),null,null));
 insert into projpoly2d values('POINT',mdsys.sdo_geometry(2001,null,mdsys.sdo_point_type(1,1,null),null,null))
 *
 ERROR at line 1:
 ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
 ORA-13375: the layer is of type [2007] while geometry inserted has type [2001]
 ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 623
 ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 227

Note that the returned error message is slightly more informative than the check constraint as it tells us that 2007 ie multi-polygon data is expected but that we tried to index a 2001 object ie a point.

I hope this is of value to you the reader.