Create a table with multiple st_geometry attributes in Oracle and work with views in ArcGIS

Summary

Some workflows require the ability to work with one table or layer in ArcGIS containing multiple st_geometry attributes. Instructions provided describe how to create a table in Oracle with multiple st_geometry attributes, multiple st_geometry_index indexes, Oracle views, and how to register the views with ArcSDE to make the data accessible in ArcGIS.

Procedure

1. Create the table in Oracle using SQL*Plus. This example demonstrates creating the table with three st_geometry attributes.
   

   Code:
   SQL> CREATE TABLE spatial_objects
     2    (objectid   NUMBER(38) NOT NULL,
     3     shape_pt   sde.st_geometry,
     4     shape_ln   sde.st_geometry,
     5     shape_py   sde.st_geometry);
   
   Table created.

   
   
2. The next step is to populate the table by inserting one row and updating the two additional geometry attributes using SQL*Plus. Since working with multiple geometries on the same feature class is not directly supported by ArcGIS, and the following steps create Oracle views, the ObjectID attribute must be user maintained and the values must be explicitly set in the INSERT statements.
   
   

   Code:
   SQL> INSERT INTO spatial_objects VALUES
     2    (1, sde.st_point(1395735.6, 405588.4,1), NULL, NULL);
   
   1 row created.

   
   Next, the shape_ln and shape_py attributes are updated using st_linestring and st_polygon constructor operators.
   
   

   Code:
   SQL> UPDATE spatial_objects SET
     2    shape_ln = sde.st_linestring('linestring (1395735.6 405588.4, 1395767.3 405421.1)',1),
     3    shape_py = sde.st_polygon('polygon ((1395652.5 405380.6, 1395652.5 405880.6, 1396152.5 405580.6,
     4    1396152.5 405380.6, 1395652.5 405380.6))',1)
     5    WHERE objectid = 1;
   
   1 row updated.
   
   SQL> COMMIT;

   
   
3. The next step is to create spatial indexes for each st_geometry attribute. In this example, the spatial reference SRID 1 already exists and is the SRID value, which was used to create each st_geometry attribute (in step 2).
   
   

   Code:
   SQL> CREATE INDEX shape_pt_idx
     2    ON spatial_objects (shape_pt)
     3    INDEXTYPE IS sde.st_spatial_index
     4    parameters ('st_grids=100 st_srid=1');
   
   Index created.
   
   SQL> CREATE INDEX shape_ln_idx
     2    ON spatial_objects (shape_ln)
     3    INDEXTYPE IS sde.st_spatial_index
     4    parameters ('st_grids=100 st_srid=1');
   
   Index created.
   
   SQL> CREATE INDEX shape_py_idx 
     2    ON spatial_objects (shape_py)
     3    INDEXTYPE IS sde.st_spatial_index
     4    parameters ('st_grids=100 st_srid=1');
   
   Index created.

   
   
4. The next step is to create an Oracle view that references the ObjectID attribute (required to be used as the sde_row_id attribute) and each st_geometry attribute. Three views must be created to allow each one to be uniquely registered with ArcSDE.
   
   

   Code:
   SQL> CREATE OR REPLACE VIEW spatial_view_pt AS
     2    SELECT objectid, shape_pt 
     3    FROM spatial_objects;
   
   View created.
   
   SQL> CREATE OR REPLACE VIEW spatial_view_ln AS
     2    SELECT objectid, shape_ln 
     3    FROM spatial_objects;
   
   View created.
   
   SQL> CREATE OR REPLACE VIEW spatial_view_py AS
     2    SELECT objectid, shape_py 
     3    FROM spatial_objects;
   
   View created.

   
   
5. The final step is to register each Oracle view with ArcSDE. The registration process adds the necessary records to ArcSDE's metadata tables which then allows ArcGIS to work with each view as a feature class in ArcMap.
   
   

   Code:
   D:\> sdelayer -o register -l spatial_view_pt,shape_pt -e p -u tomb -i 5151 -t st_geometry -C objectid,user
   
   ArcSDE 9.2 for Oracle10g Build 508 Thu Apr 17 12:23:18  2008
   Layer Administration Utility
   -----------------------------------------------------
   Successfully Created Layer.
   
   D:\> sdelayer -o register -l spatial_view_ln,shape_ln -e l -u tomb -i 5151 -t st_geometry -C objectid,user
   
   ArcSDE 9.2 for Oracle10g Build 508 Thu Apr 17 12:23:18  2008
   Layer Administration Utility
   -----------------------------------------------------
   Successfully Created Layer.
   
   D:\> sdelayer -o register -l spatial_view_py,shape_py -e a -u tomb -i 5151 -t st_geometry -C objectid,user
   
   ArcSDE 9.2 for Oracle10g Build 508 Thu Apr 17 12:23:18  2008
   Layer Administration Utility
   -----------------------------------------------------
   Successfully Created Layer.

   
   The registration process requires the name of the view and shape attribute (-l argument), the entity type (-e argument), a value to indicate the table contains a st_geometry attribute (-t argument), and an identifier as to what attribute should be used as the sde_row_id attribute and if the attribute is SDE or user maintained (-C argument).
   
   Once the data is registered with ArcSDE, it becomes visible in ArcCatalog as a feature class and can be used as a layer in ArcMap. The views cannot be registered with the geodatabase or edited in ArcGIS.