Desktop Help 10.0 - How does ArcSDE use Oracle Spatial?

ArcSDE supports Oracle Spatial or Oracle Locator for storing and managing geometry in an Oracle database. To use it, you must have SDO_GEOMETRY specified for the GEOMETRY_STORAGE parameter of one of your configuration keywords. If you want to use Oracle Spatial geometry storage most of the time, specify SDO_GEOMETRY for the GEOMETRY_STORAGE parameter in your DEFAULTS configuration keyword. If you want to use it for only some datasets, utilize the SDO_GEOMETRY keyword when creating each individual dataset.

Using Oracle Spatial in ArcGIS

When you first install the ArcSDE component, ST_Geometry is the default geometry storage type. The default settings for ArcSDE storage are defined in the DBTUNE table by the GEOMETRY_STORAGE parameters.

If you want to store just some of your feature classes with the Spatial Type for Oracle storage, you can specify the keyword SDO_GEOMETRY when you create the feature class. If you do this, that particular feature class is created with an SDO_GEOMETRY column. In the dbtune file, the SDO_GEOMETRY keyword appears as follows:

##SDO_GEOMETRY
GEOMETRY_STORAGE    "SDO_GEOMETRY"
ATTRIBUTE_BINARY    "BLOB"
RASTER_STORAGE	    "SDO_GEORASTER"
SDO_COMMIT_INTERVAL  1000
UI_TEXT          "User Interface text description for SDO_GEOMETRY"

COMMENT        "Any general comment for SDO_GEOMETRY keyword"

END

If you plan to use the SDO_Geometry storage type for most of your feature classes, you can change the default ArcSDE geometry storage to use the Oracle Spatial type.

To change the default geometry storage, use the sdedbtune administration command to alter the DBTUNE DEFAULTS keyword, changing the GEOMETRY_STORAGE parameter from ST_GEOMETRY to SDO_GEOMETRY. After the default GEOMETRY_STORAGE setting has been changed to SDO_GEOMETRY, ArcSDE creates feature classes with SDO_GEOMETRY columns by default.

Note:

For details on using the sdedbtune command, consult the ArcSDE Administration Command Reference installed with the ArcSDE component of ArcGIS Server at the Enterprise level.

ArcSDE for Oracle supports a number of different geometry storage schemas—these different schemas can all be used together in the same database. While there can only be one default geometry schema, individual tables can be created using different geometry schemas.

Prerequisites for using existing Oracle Spatial tables with ArcSDE

ArcSDE can successfully use tables containing SDO_GEOMETRY columns created externally by other applications or using SQL (sometimes referred to as third-party tables) as long as the tables meet certain prerequisites:

They must be owned by the user registering the table.
They must have a single SDO_GEOMETRY column.
They must have no other columns of a user-defined type.
They must have a valid entry in the view USER_SDO_GEOM_METADATA.
They must have a single type of geometry (points, lines, or polygons). Geometry can be multipart.
They must have an integer, unique, not-NULL column suitable as a registered row ID column.
They should have a spatial index.
They should pass Oracle's geometry validation tests; otherwise, accessing these geometries may have unexpected results.
All the spatial records must have nonnull valid number values in the SDO_ORDINATES array.

Interoperability considerations

A common misconception is that applications can interoperate simply because they support the same underlying geometry type. However, the geometry type is only one aspect of the geodatabase as well as other GIS database schemas. Gaining a common understanding of rules, constraints, schema, and implementation is also required. Some things you should consider when using Oracle Spatial with ArcSDE and ArcSDE clients are as follows:

ArcSDE and ArcGIS do not support multiple geometry columns in a table.

Tables with multiple SDO_GEOMETRY columns should be accessed through views that contain only one SDO_GEOMETRY column.

To use data in an Oracle Spatial table that contains multiple spatial columns, create a spatial view using SQL. This view should only contain one spatial column. Next, register the spatial view with ArcSDE using sdelayer –o register.

To learn how to create and register a view containing only one SDO_GEOMETRY column, see the Knowledge Base article Create an Oracle view of an Oracle Spatial layer (containing multiple geometry columns) and register it with ArcSDE.

ArcSDE and ArcGIS only support a single geometry type in an SDO_GEOMETRY column.

If a single SDO_GEOMETRY column in a table contains multiple geometry types (for example, some records are points and some are polygons) the table cannot be registered with ArcSDE or the geodatabase; the feature class must be registered as containing one geometry type.

Oracle Spatial does not necessarily enforce geometry type constraints on an SDO_GEOMETRY column. Without this enforcement, one application might attempt to uphold a single geometry type constraint, but another application could insert different geometry types. Beginning with Oracle9i, you have the option to constrain the geometry type on insert into a table with a spatial index creation parameter. For earlier Oracle versions, another option to enforce geometry type constraints is to create an insert-update trigger that checks the SDO_GEOMETRY GTYPE property.

Geometry validation is not the same between ArcSDE and SDO_GEOMETRY.

ArcSDE validates geometry when inserting or updating geometries. In Oracle 10g, Oracle Spatial itself does not automatically enforce geometry validation on the insert or update of an SDO_GEOMETRY value if you insert or update geometries using methods other than ArcSDE clients, such as SQL. In Oracle 11g, Oracle Spatial validates geometries on index inserts.

Problems would occur if illegal or poorly formed geometry values were passed to ArcSDE client applications. To reduce the occurrences of these potential problems, you should validate any geometries created or changed by any method other than an ArcSDE client application.

Legacy:

In ArcSDE 9.1, SDO_GEOMETRY features are not validated as they are fetched from the database. This change was made to improve fetch performance.

Starting with ArcSDE 9.2, shapes are validated when they are fetched, but only if the spatial table is created outside ArcSDE then registered with ArcSDE. If the layer is created using ArcGIS or the ArcSDE API, the shapes are not validated on fetch.

There are two tools available for validating geometry. Oracle's VALIDATE_GEOMETRY_WITH_CONTEXT checks geometries using Oracle's shape validation rules, and sdelayer –o feature_info checks them using ArcSDE rules. The feature_info operation optionally includes, as part of its output, information on whether or not a shape's geometry is valid for ArcSDE. For details on the use of this operation, refer to the Administration Command Reference documentation included with your ArcSDE installation.

ArcSDE geometry validation is not the same as Oracle Spatial geometry validation. While it is important that geometries pass Oracle Spatial validation, successfully validating geometries with Oracle's validation routines does not guarantee that the geometries will be validated by ArcSDE.

You can create an insert-update trigger to fire the SDO_VALIDATE function to enforce validation of SDO_GEOMETRY types.

The ArcSDE C API can be used to write an application that retrieves features even if they fail ArcSDE shape validation. However, it is then up to the client application to determine the suitability of the geometry, or how to fix it.

Most applications reading SDO_GEOMETRY objects would not be able to interpret SDO_ETYPE 0 data created by other applications.

Oracle Spatial allows applications to insert application-specific data into an SDO_GEOMETRY object. Applications do this by embedding their data using an SDO_ETYPE value of 0. This allows applications great flexibility to store many types of unconventional geometry and other data. However, the meaning of the application-specific data is significant only to the application that generated the special SDO_GEOMETRY object. Such application-specific data cannot be reliably supported in an interoperable environment. Applications reading SDO_GEOMETRY objects probably would not be capable of interpreting SDO_ETYPE 0 data created by other applications. Applications updating SDO_GEOMETRY objects would be unable to edit or preserve the SDO_ETYPE 0 data.

When reading SDO_GEOMETRY objects containing SDO_ETYPE 0 elements, ArcSDE ignores the SDO_ETYPE 0 data and passes only the simple feature geometry elements it supports to the application.

When updating SDO_GEOMETRY objects containing SDO_ETYPE 0 elements, ArcSDE does not preserve the SDO_ETYPE 0 data. Therefore, applications requiring that SDO_ETYPE 0 data is preserved should prevent users from having UPDATE access to the table.

ArcSDE requires that all SDO_GEOMETRY values in a column be in the same coordinate reference system.

If the coordinate reference is undefined, the SRID value should be NULL as described in the Oracle Spatial User's Guide and Reference.

Prior to Oracle9i, Oracle Spatial does not automatically enforce spatial reference ID validation on insert or update of an SDO_GEOMETRY value. To enforce validation of SDO_GEOMETRY types on these older Oracle instances, you can create an insert-update trigger to fire the SDO_VALIDATE function. Oracle Spatial requires that the SRID in each geometry match each other and the SRID in the spatial metadata, even if it is NULL.

Oracle Spatial and ArcSDE both assume that the first and second dimensions of the SDO_GEOMETRY are x and y, respectively.

If an SDO_GEOMETRY object has three dimensions, ArcSDE interprets the third dimension as a measure if the dimension name (in the spatial metadata) starts with the letter "m"; otherwise, the third dimension is assumed to be elevation. You can control how ArcSDE interprets the third dimension by setting the feature class's entity flag to have either elevations or measures.

Oracle9i Release 2 extended the SDO_GTYPE member of the SDO_GEOMETRY type to allow encoding of which dimension contains a measure ordinate. The second digit of the four-digit SDO_GTYPE can be 0, 3, or 4. If it is 3 or 4, this indicates which dimension contains the measure ordinate. ArcSDE 9 and later versions read this encoding. Otherwise, if the SDO_GEOMETRY object has four dimensions, the measure is interpreted as the last ordinate.

In this example, sdelayer sets the entity type of a feature class to store linestrings and elevations:

sdelayer –o add –e l3

In this example, sdelayer sets the entity type of a feature class to store linestrings and measures:

sdelayer –o add –e lM

Oracle Spatial (but not Oracle Locator) provides functions for linear reference system (LRS) calculations using measure values.

The Oracle Spatial LRS functions require that all measure values in a geometry be monotonically ascending or descending without NaN values. Oracle Spatial LRS also restricts measure values to linestrings.

ArcSDE allows measures and LRS calculations on all geometric types, with support for arbitrarily ordered measure values and NaN values.

If you intend to use Oracle Spatial LRS functions, be sure to design your application and database to operate within the Oracle Spatial LRS constraints.

ArcSDE needs a unique feature identifier in the spatial table to perform spatial queries, log file queries, single-row operations, and multiversioned database operations.

With ArcSDE compressed binary schema, the geometry column can serve this purpose because it is a foreign key into the feature table and is defined as a nonNULL unique integer value. SDO_GEOMETRY does not include a unique identifying value, so when ArcSDE adds the SDO_GEOMETRY column to an existing table, it may also add a unique identifying column. Such a column is the registered row ID column. This column is often named OBJECTID, but it can have another name. Or an existing column can be used for the registered row ID column as long as it is indexed and is an integer, UNIQUE, and NOT NULL. Registered row ID columns maintained by ArcSDE must be NUMBER(38) UNIQUE NOT NULL.

A registered row ID column can be added using the sdetable administration command or the ArcCatalog application. Many applications, such as ArcGIS, require a registered row ID column in each table or feature class. Each application has its own requirements and limitations.

Modification of Oracle Spatial feature classes participating in networks, topologies, relationships, or constraints should be restricted to ArcGIS applications.

ArcGIS can create and maintain networks and integrated topological feature classes from simple feature classes that use the SDO_GEOMETRY type. ArcGIS enforces relationships and constraints across many different data sources. ArcGIS manages the relationships and maintains the topological integrity of the data; modifications to the underlying features through ArcGIS are reflected in these integrated networks, topologies, and relationships.

Modification of Oracle Spatial feature classes participating in networks, topologies, relationships, and constraints should be restricted to ArcGIS applications. Other applications can freely read the data, but their edits are not properly reflected in the geodatabase.

How does ArcSDE use Oracle Spatial?