How To: Maintain Oracle Spatial Geometry Type data using ArcInfo Desktop and non-ESRI applications


Oracle Spatial is an extension to the Oracle RDBMS that provides a geometry type, indexing on the type, functions that operate on the type, and a metadata schema to identify tables that contain an SDO_GEOMETRY type.

Applications can use Oracle Spatial to manage their geometry data in the RDBMS. When applications follow the same schema and rules, it is possible for multiple applications to access the same geometry data. Therefore, you could expect to operate on an Oracle Spatial schema where non-ESRI software was used to load simple feature geometry, and where ArcInfo was used to display and query that data. If a non-ESRI application modified some of these simple features, ArcInfo would see the changes. Likewise, ArcInfo could be used to process and load simple feature geometry data, and non-ESRI software could be used to display and query the data.


GIS applications should not have problems sharing database schemas built around simple features (points, lines, and polygons) as defined by OGC and ISO, as long as the following four conditions are met:

1. The database schema consists of simple features.

In an SQL Database, the feature collection is a table, the features are the rows in the table, and the properties are the attribute columns of the table. One of the properties is the geometry. In the case of Oracle Spatial, that property is defined as an SQL type named SDO_GEOMETRY. DB2 and Informix are similar, except that the geometry type is named ST_GEOMETRY or one of its instantiable subtypes, such as ST_POLYGON, ST_LINESTRING, etc.

Simple features are defined by ISO and OGC as being geographic objects with properties, one of which is a geometry. The geometry must be single or multi-part points, lines, or polygons, with linear interpolation between
coordinates (straight segments, not curves between the coordinates).

2. The applications follow the same geometry rules.

Applications that are going to share geometry stored in a database must follow the same geometry construction rules. When using Oracle Spatial, it is the responsibility of the application to compose the insert, update, and fetch
SQL statements to access geometry properties from the SDO_GEOMETRY type. This means the application must properly define geometry properties, such as orientation, ordinate counts, interpolation, part separators, etc.

3. There are no dependencies between objects.

Simple features that will be shared between applications should not have dependencies on other features. For example, a simple feature collection of roads is just a bunch of individual road objects. Updates to individual
roads are isolated transactions and do not imply other dependent changes.

Editing a collection of roads used to define a network for routing requires the network be maintained as well as the roads. In this scenario, both applications would have to know how to maintain the roads and the network dependent on the roads. If an application deleted a road without updating the network, vehicles could be routed down the missing road.

The ArcInfo geodatabase works with both simple feature collections, and geographic objects with behavior, validation rules and dependencies. Applications can share simple feature collections that have no dependencies.
Non-geodatabase applications should not modify geographic objects with behavior, validation rules, dependencies, or that participate in a ArcInfo geographic network.

4. Multi-version views are used by non-geodatabase applications.

This requirement applies to simple feature collections edited by ArcInfo. ArcInfo performs all edits within a multi-user RDBMS in a versioned database. The versioned database allows ArcInfo to isolate user edit sessions, and to support multiple, persistent versions of the database.

The versioned geodatabase is implemented as a set of tables to keep track of changes. To retrieve the correct set of features for a specific version, the appropriate changes against the base set of features must be included.

Some third party applications may be able to access simple features in a versioned database using multi-version views. These views ensure that the proper set of features is identified for use by an application. Multi-version views are built on tables that participate in a versioned database. Multi-version views support queries, inserts, updates, and deletions. Applications define the desired version of the database using an SQL package, then execute an SQL expression against the multi-version view. If no version is specified by the application, the default version is returned.