What happens when you create feature classes with SDO_GEOMETRY geometry storage?

When you create a feature class with SDO_GEOMETRY storage, the following happens:

An SDO_GEOMETRY column is added to the business table of the feature class.

ArcGIS creates a feature class by adding a geometry column to the specified business table. When the GEOMETRY_STORAGE parameter is set to SDO_GEOMETRY, ArcGIS adds an SDO_GEOMETRY column to the business table.

In the following example, the business table has name and population properties. After adding a geometry column, it also has an SDO_GEOMETRY column named Borders. If needed, a unique feature identifier column (OBJECTID in this example) is added and populated.

Name

Data type

Null?

NAME

VARCHAR2(32)*

POPULATION

NUMBER(11)

BORDERS

MDSYS.SDO_GEOMETRY

OBJECTID

NUMBER(38)

NOT NULL

NoteNote:

*NVARCHAR2(32) is used instead of VARCHAR2(32) if using Unicode strings.

A geometry column can be added to the business table using ArcCatalog, the sdelayer administration utility, or the ArcSDE C and Java APIs.

A spatial index is created on the SDO_GEOMETRY column.

When an SDO_GEOMETRY column is added to a business table, a spatial index on that geometry column is usually created. By default, ArcGIS creates an R-tree index on an SDO_GEOMETRY column.

Alternatively, ArcGIS can create feature classes with no spatial index; however, spatial queries cannot be supported until a spatial index is created. Oracle's SDO_FILTER function, used by ArcGIS, requires the presence of a spatial index.

You can create a spatial index several ways—in ArcCatalog, with the sdelayer administration utility, using Oracle Spatial Index Advisor, using SQL, or programmatically using the ArcSDE C and Java APIs.

ArcGIS automatically drops and re-creates Oracle Spatial indexes created by ArcGIS whenever the feature class is switched between LOAD_ONLY_IO and NORMAL_IO mode. Spatial indexes defined by the Oracle Spatial Index Advisor application or created using SQL are not dropped when ArcGIS switches the feature class to LOAD_ONLY_IO mode.

A record is added to the Oracle Spatial metadata view.

When ArcGIS adds an SDO_GEOMETRY column to a business table, it also adds the required Oracle Spatial metadata record to the USER_SDO_GEOM_METADATA view. This metadata includes the table name, SDO_GEOMETRY column name, spatial reference ID, and coordinate dimension information.

If a third-party application or SQL is used to create the table, the spatial column, and its metadata, and the table is then registered with ArcSDE using the sdelayer command line tool, ArcSDE will not delete the metadata when unregistering the table with ArcSDE. However, ArcSDE will always delete the metadata when deleting the table, such as it does with the sdetable -o delete command.

A coordinate dimension is specified.

You can create ArcGIS geometry as 2D (x,y), 2D with measures, 3D (x,y,z), or 3D with measures. When creating new feature classes or adding an SDO_GEOMETRY column to an existing table, ArcGIS places the Oracle Spatial dimension information in the DIMINFO column of the metadata view.

The SRID of each SDO_GEOMETRY value is set based on the coordinate reference.

Oracle Spatial provides predefined coordinate references in the MDSYS.CS_SRS table. When using ArcGIS to create a new feature class to set a specific SRID for the SDO_GEOMETRY column, identify the appropriate Oracle Spatial coordinate reference description, then set the feature class's SDO_SRID DBTUNE storage parameter to that value.

For example:

#MY_SDO_KEYWORD
GEOMETRY_STORAGE SDO_GEOMETRY
SDO_SRID 8307
UI_TEXT "MY_SDO_KEYWORD"
END

If the SDO_SRID storage parameter is not set, ArcGIS sets the SRID of each SDO_GEOMETRY value to NULL, as well as the SRID in the corresponding metadata record.

ArcGIS does not require the Oracle Spatial SRID; ArcGIS maintains the coordinate reference information for each feature class in its own SPATIAL_REFERENCES table independently from Oracle Spatial. Consult your Oracle Spatial User's Guide and Reference for information on supported coordinate references.

The SDO_GEOMETRY column is populated.

When storing geometry in the geodatabase, ArcGIS populates the SDO_GEOMETRY value from an ArcSDE API object called SE_SHAPE. The SE_SHAPE object can contain simple and complex geometry that may include elevations, measures, CAD data, annotation, and surface patches. The SDO_GEOMETRY data type supports a subset of these geometric properties. Because there is not a one-to-one mapping of the components in the SDO_GEOMETRY and the SE_SHAPE object, ArcGIS follows a set of rules when storing data in Oracle Spatial tables:

NoteNote:

ArcGIS does not support heterogeneous geometry collection in the SDO_GEOMETRY object, and ArcGIS does not encode SDO_ETYPE 0 elements in the SDO_GEOMETRY object. SDO_ETYPE 0 elements are application specific.

An additional column is added to the business table if storing CAD and annotation properties.

The SDO_GEOMETRY type cannot store all types of geometric elements that ArcGIS storage must support. When storage for these elements is required (as determined from the geometry type flags specified when the feature class is created), ArcGIS adds a column called SE_ANNO_CAD_DATA to the business table. Using the feature class example from the first section in this topic, the business table would now contain the following:

Name

Data type

Null?

NAME

VARCHAR2(32)*

POPULATION

NUMBER(11)

BORDERS

MDSYS.SDO_GEOMETRY

SE_ANNO_CAD_DATA

BLOB

OBJECTID

NUMBER(38)

NOT NULL

NoteNote:

*NVARCHAR2(32) is used instead of VARCHAR2(32) if using Unicode strings.

Whenever ArcGIS detects the data source has CAD data, ArcGIS writes a simple geometric representation of the CAD data into the SDO_GEOMETRY value and writes the unmodified CAD data into the SE_ANNO_CAD_DATA value. If the data source does not have CAD data, ArcGIS sets the SE_ANNO_CAD_DATA value to NULL. The SE_ANNO_CAD_DATA property contains data from numerous ArcGIS components:

Spatial queries on the feature class are performed using Oracle Spatial filter functions.

ArcGIS uses the Oracle Spatial SDO_FILTER function to perform the primary spatial query. ArcGIS performs secondary filtering of SDO_GEOMETRY based on the spatial relationship requested by the application.

Applications may also include Oracle Spatial primary and secondary filter functions in the SQL WHERE clause supplied to ArcGIS. Using spatial filters in the WHERE clause, applications can distribute the spatial query to the database server, the ArcSDE application server, and the application itself.

12/16/2013