Current Oracle Spatial Blog Articles
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):
1. Check constraint.
One method for implementing this constraint is to use a standard column check constraint as follows:
Now if we try and insert data into the table…
2. RTree index.
Before we describe this method we will first drop the check constraint from above.
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):
Now attempt to insert a record.
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.