Show Navigation | Hide Navigation
You are here:
Geodatabases and ArcSDE > Geodatabase data storage and schema > Inside a geodatabase in Oracle

Using the Oracle Spatial geometry type

Release 9.3
Last modified October 12, 2010
E-mail This Topic Printable Version Give Us Feedback

Print all topics in : "Inside a geodatabase in Oracle"

Note: This topic was updated for 9.3.1.

ArcSDE supports Oracle Spatial's Object Relational Model as an optional method to store spatial data. Specifically, Oracle Spatial or Locator geometry can be used to store and manage the feature and raster contents of datasets within ArcSDE geodatabases.

Oracle Spatial is an extension to the Oracle DBMS that adds a spatial type and spatial query functions to Oracle. It is offered by Oracle using two primary options.

Oracle Spatial shares duplicate functionality with much GIS technology including ArcGIS. The primary way in which most ArcGIS users apply Oracle Spatial is as an optional mechanism for geodatabase storage and access in an Oracle DBMS. Essentially, one of the choices that ArcGIS users can make each time they create a new feature class or raster dataset in a geodatabase in Oracle is how to store feature or raster geometry—they can use either the geodatabase or the Oracle Spatial data storage options.

See An overview of feature geometry and raster data storage for a list of feature and raster storage options when using an Oracle DBMS.

For additional information on Oracle Spatial, refer to the Oracle Spatial User's Guide and Reference for your Oracle release.

You can use the following links to navigate this topic:

An overview of Oracle Spatial data storage

Oracle Spatial extends the Oracle database with the addition of spatial data management functions. Oracle Spatial provides a spatial geometry type (MDSYS.SDO_GEOMETRY), spatial metadata schema, indexing methods, functions, and implementation rules, which are described below.

NOTE: Oracle Locator is a subset of Oracle Spatial. Oracle Locator includes the SDO_GEOMETRY data type along with some of the functionality provided with Oracle Spatial. Oracle Locator may be used with ArcSDE. Please refer to Oracle's documentation for an explanation of the difference between Oracle Spatial and Oracle Locator. Throughout this topic, "Oracle Locator" can be used in place of references to "Oracle Spatial."

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.

Making Oracle Spatial your default geometry schema

When you first install the 9.3 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.

Changing the default ArcSDE geometry storage to use Oracle Spatial is easy. For the DEFAULTS keyword, change 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: Use the sdedbtune administration command to alter DBTUNE settings. For details on using the sdedbtune command, consult the ArcSDE Administration Command Reference installed with ArcSDE.

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.

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 will be created with an SDO_GEOMETRY column. In the dbtune file, the SDO_GEOMETRY keyword appears as follows:

UI_TEXT          "User Interface text description for SDO_GEOMETRY"

COMMENT        "Any general comment for SDO_GEOMETRY keyword"


Top of page

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:

Using existing Oracle Spatial tables

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:

Performing manual registration of third-party tables containing SDO_GEOMETRY columns

The ArcSDE administration command sdelayer –o register manually registers a table as a feature class. Manually registering tables as feature classes gives you more control over how a table is registered. Here is an example of registering a table called TBL containing point geometries (–e p) in a spatial column called SHAPE. The table has an integer column called PID that will be used as a user-maintained unique feature identifier column (–C pid,USER).

sdelayer –o register –l tbl,shape –e p –C pid,USER –u <user> –p <pw>

To manually register these tables, the prerequisites described above must be met.

Using autoregistration for third-party tables containing SDO_GEOMETRY columns

Autoregistration is a feature of ArcSDE that can find and register unregistered Oracle Spatial tables, allowing ArcSDE to access these tables as feature classes.

Autoregistration is controlled by the ArcSDE initialization parameter DISABLEAUTOREG, which is set to TRUE by default. This means the default state for Autoregistration is "off." To turn on autoregistration, use the following administration command:

sdeconfig –o alter –v DISABLEAUTOREG=FALSE –u sde –p <sde_password>

ArcSDE provides a list of registered feature classes to client applications at certain times, such as when a user connects to the server using ArcCatalog or when running the command sdelayer –o describe. In the process of providing this list, ArcSDE autoregistration searches the Oracle Spatial metadata view, ALL_SDO_GEOM_METADATA, for tables containing SDO_GEOMETRY columns on which the user has SELECT privileges and that are not already registered as feature classes. When a table is discovered that matches these criteria, ArcSDE automatically registers it, making the table available to ArcSDE client applications.

To register the table, ArcSDE must know the geometry type. Autoregistration looks at the first row in a newly discovered table to establish the ArcSDE geometry type. Autoregistration will not register empty tables, but empty tables can be registered manually with sdelayer –o register. If you know the table contains rows with differing geometry types, the sdelayer command can be used to alter the geometry type definition after autoregistration completes. ArcSDE with Oracle Spatial requires a column to identify each row. See "Unique feature identifier column (OBJECTID)." Autoregistration searches for a column in the table to use as a unique feature identifier column. To qualify, the column must be defined as NUMBER(38) UNIQUE NOT NULL. If such a column is found, ArcSDE records it in the ArcSDE table registry as the unique feature identifier column for the table. If a suitable unique feature identifier column is not found, the table is registered, but operations requiring a unique feature identifier are unavailable.

For SDO_GEOMETRY columns that have an Oracle Spatial SRID, ArcSDE stores the information in the ArcSDE SPATIAL_REFERENCES table. ArcSDE sets its spatial reference AUTH_NAME field to ORACLE and the AUTH_SRID field to the SRID value. ArcSDE tests the coordinate reference description and, if it is valid, sets the SRTEXT field to the Oracle Spatial coordinate reference description.

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 the following:

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 may want to enforce 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 to enforce geometry types.

Oracle Spatial does not automatically enforce geometry validation on the insert or update of an SDO_GEOMETRY value.
ArcSDE validates geometry when inserting or updating geometries. However, 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.

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.

NOTE: Starting with ArcSDE 9.1, ArcSDE no longer validates SDO_GEOMETRY features as they are fetched from the database. This change was done to improve fetch performance.

There are two tools available for validating geometry. Oracle's VALIDATE_GEOMETRY_WITH_CONTEXT will check geometries using Oracle's shape validation rules, and sdelayer –o feature_info will check them using ArcSDE's 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, please 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.

ArcSDE does not support heterogeneous geometry collections in an SDO_GEOMETRY object.

Most applications reading SDO_GEOMETRY objects would not know how 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 known only to the application that generated that special SDO_GEOMETRY object. Such application-specific data cannot be reliably supported in an interoperable environment. Applications reading SDO_GEOMETRY objects probably would not know how to interpret SDO_ETYPE 0 data created by other applications. Applications updating SDO_GEOMETRY objects would not know how to edit or preserve the SDO_ETYPE 0 data.

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

When updating SDO_GEOMETRY objects containing SDO_ETYPE 0 elements, ArcSDE will not preserve the SDO_ETYPE 0 data. Therefore, applications that need to ensure that SDO_ETYPE 0 data is preserved should make sure that users do not have 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 defined in the Oracle Spatial User's Guide and Reference.

Prior to Oracle9i, Oracle Spatial does not automatically enforce spatial reference ID validation on the insertion 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. Starting with Oracle9i, 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 assumes the third dimension is a measure if the dimension name (in the spatial metadata) starts with an 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 whichever dimension contains a measure ordinate. The second digit of the four-digit SDO_GTYPE may be 0, 3, or 4. If it is 3 or 4, this indicates which dimension contains the measure ordinate. ArcSDE 9.0 and above recognizes this encoding. Otherwise, if the SDO_GEOMETRY object has four dimensions, the measure is expected to be 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.

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 non-NULL, 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 referred to as the registered row ID column. This column is often called OBJECTID, but it can have another name. Or, an existing column can be used for the registered row ID column so 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.

Multiversioned views are available for SQL access to the multiversioned database.
ArcGIS uses a multiversioned database implemented through ArcSDE for editing operations. The multiversioned database provides long transaction support for multiple simultaneous design alternatives.

Multiversioned views are available for SQL access to the multiversioned database, including Oracle Spatial tables registered as versioned. See Using database views for more information.

Modification of Oracle Spatial feature classes participating in networks or topologies 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 manages the relationships and maintains the topological integrity of the data—modifications to the underlying features through ArcGIS are reflected in these integrated networks.

Modification of Oracle Spatial feature classes participating in these networks or topologies should be restricted to ArcGIS applications. Other applications can freely read the data, but any modifications they make are not reflected in the networks or topologies.

Modification of Oracle Spatial feature classes participating in relationships and constraints should be restricted to ArcGIS applications.
ArcGIS enforces relationships and constraints across many different data sources. Feature classes containing an SDO_GEOMETRY type may be included in relationships and may have constraints defined on them.

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


Please visit the Feedback page to comment or give suggestions on ArcGIS Desktop Help.
Copyright © Environmental Systems Research Institute, Inc.