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.

ArcSDE creates a feature class by adding a geometry column to the specified business table. When the GEOMETRY_STORAGE parameter is set to SDO_GEOMETRY, ArcSDE 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, ArcSDE creates an R-tree index on an SDO_GEOMETRY column.

Alternatively, ArcSDE 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 ArcSDE, 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.

ArcSDE automatically drops and re-creates Oracle Spatial indexes created by ArcSDE 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 ArcSDE switches the feature class to LOAD_ONLY_IO mode.

A record is added to the Oracle Spatial metadata view.

When ArcSDE 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, 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 ArcSDE 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, ArcSDE 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 ArcSDE 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, ArcSDE sets the SRID of each SDO_GEOMETRY value to NULL, as well as the SRID in the corresponding metadata record.

ArcSDE does not require the Oracle Spatial SRID. ArcSDE 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 database, ArcSDE 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, ArcSDE follows a set of rules when storing ArcSDE 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.
• 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:

ArcSDE does not support heterogeneous geometry collection in the SDO_GEOMETRY object, and ArcSDE 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 ArcSDE storage must support. When storage for these elements is required (as determined from the geometry type flags specified when the feature class is created), ArcSDE 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 ArcSDE detects the data source has CAD data, ArcSDE 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, ArcSDE 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 ArcMap Spatial Analyst
• Annotation from ArcInfo Workstation (before ArcGIS 8)

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

ArcSDE uses the Oracle Spatial SDO_FILTER function to perform the primary spatial query. ArcSDE 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 ArcSDE. 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.