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

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

NoteNote:

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

NoteNote:

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

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.


8/19/2013