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

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

A table with an SDO_GEOMETRY column is created.

If you specify a DBTUNE configuration keyword that has the GEOMETRY_STORAGE parameter set to SDO_GEOMETRY when you create a feature class in a geodatabase in Oracle, ArcGIS uses an SDO_GEOMETRY column for the shape field.

The following example feature class has a unique identifier (ObjectID) field, name and population attributes, and an SDO_GEOMETRY shape column.

Name	Data type	Null?
NAME	VARCHAR2(32)*
POPULATION	NUMBER(11)
SHAPE	MDSYS.SDO_GEOMETRY
OBJECTID	NUMBER(38)	NOT NULL

Note:

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

A spatial index is created on the SDO_GEOMETRY column.

ArcGIS creates an R-tree spatial index on the SDO_GEOMETRY column of a feature class by default. ArcGIS uses Oracle's SDO_FILTER function to perform spatial queries on the feature class; therefore, a spatial index is required for spatial queries.

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 creates a feature class that contains an SDO_GEOMETRY column, the required Oracle Spatial metadata record is added to the USER_SDO_GEOM_METADATA view. This metadata includes the table name, SDO_GEOMETRY column name, spatial reference ID, and coordinate dimension information.

When you use ArcGIS to delete the feature class, the Oracle Spatial metadata is also deleted.

A coordinate dimension is specified.

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

• The x-coordinate is the first dimension.
• The y-coordinate is the second dimension.
• The z-coordinate is the third dimension if the feature class is defined as having elevations.
• The m-coordinate is the last dimension (third or fourth, depending on the presence or absence of a z-coordinate) if the feature class is defined as having measures.

The SDO_GEOMETRY column is populated.

When storing geometry in the geodatabase, ArcGIS populates the SDO_GEOMETRY value from an 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:

• Create four-digit SDO_GTYPE based on the geometry's entity type.
• Set the SDO_SRID type. See What is an SRID? for a list of possible values used.
• Write coordinate values in the appropriate coordinate reference system.
• Write coordinates in the order x, y, z, and m, defining elevation and measure coordinates only if present in the source SE_SHAPE object.
• If elevations and/or measure coordinates are present in the source SE_SHAPE object, store all coordinates with an elevation and/or measure coordinate.
• Set elevations and measure coordinates to NaN ("not a number") if specific coordinates in the geometry contain undefined elevation or measure values.
• Allow measures on any geometry type, not just linestrings. The first and last coordinates need not contain measure values.
• Do not restrict measure values to ascending or descending order.
• Write circular curves to the SDO_GEOMETRY type when the feature class is registered with the CAD entity mask (c); otherwise, convert the curves to densified straight-edged linestrings.
• Convert noncircular arcs (such as cubic spline or Bézier) to straight-edged linestrings. When the feature class is registered with the CAD entity mask (c), also store the curved representation in SE_ANNO_CAD_DATA.
• Validate all features before writing them to the database.
• Use SDO_POINT to store single-part x,y or x,y,z point in the SDO_GEOMETRY object. For other types of point feature classes, store the point vertices in the SDO_ORDINATE_ARRAY.

Note:

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)
SHAPE	MDSYS.SDO_GEOMETRY
SE_ANNO_CAD_DATA	BLOB
OBJECTID	NUMBER(38)	NOT NULL

Note:

*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:

• Parametric objects such as cubic splines and Bézier curves from ArcMap
• Surface patches from ArcGIS Spatial Analyst extension

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.