Prevent different SRID values from being inserted into a st_geometry attribute in Oracle

Summary

A st_geometry attribute without a spatial index allows geometries to be inserted that contain different spatial reference identifier (SRID) values.

Inserting geometries with different SRID values may be acceptable in some data models and applications, but ArcGIS can only support one entity type and spatial reference for a geometry attribute.

As well, when creating a spatial index on a st_geometry attribute, the spatial index can only support one SRID value for all the st_geometry attributes being indexed.

Instructions provided describe how to create a check constraint on the st_geometry SRID attribute to prevent different SRID values from being set when inserting new attributes into a table without an existing spatial index.

Procedure

1. Create the table in SQL*Plus.
   
   

   Code:
   SQL> CREATE TABLE mobile_locations (
     2  objectid  NUMBER(38) NOT NULL,
     3  shape     sde.st_geometry);
   
   Table created.

   
   
2. Add the check constraint, which validates every new geometry that uses the same SRID value, 1.
   
   

   Code:
   SQL> ALTER TABLE mobile_locations
     2  ADD CONSTRAINT loc_srid_chk 
     3  CHECK (shape.srid = 1);
   
   Table altered.

   
   With the check constraint present, insert two new rows. The first row uses a SRID = 1 and the second row, a SRID, which causes the check constraint to raise an error.
   
   

   Code:
   SQL> INSERT INTO mobile_locations VALUES
     2  (1,sde.st_point(10,10,1));
   
   1 row created.
   
   SQL> INSERT INTO mobile_locations VALUES
     2  (2,sde.st_point(20,20,2));
   insert into mobile_locations values
   *
   ERROR at line 1:
   ORA-02290: check constraint (TOMB.LOC_SRID_CHK) violated

   
   
3. Optionally, the check constraint can be specified when a table is being created.
   
   

   Code:
   SQL> CREATE TABLE mobile_locations (
     2  objectid  NUMBER(38) NOT NULL,
     3  shape     sde.st_geometry,
     4  CONSTRAINT loc_srid_chk
     5  CHECK (shape.srid = 1));
   
   Table created.