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

Note:

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

• Create four-digit SDO_GTYPE based on the geometry's entity type.
• Set the SDO_SRID to NULL if no Oracle Spatial coordinate reference is provided in a DBTUNE parameter.
• 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. The validation rules are described in the ArcSDE Developer Help, which is provided with the ArcSDE Client Software Developer Kit (SDK).
• 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)
BORDERS	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:

• AutoCAD or MicroStation data from ArcSDE CAD Client
• 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.