FAQ: How does autoregistration of Oracle Spatial tables work?


How does autoregistration of Oracle Spatial tables work?


ArcSDE can automatically register some tables containing Oracle SDO_GEOMETRY columns that were not created using ArcSDE client software. Once registered, these tables can be accessed through ArcSDE as layers. This process is called autoregistration, or automatic discovery. This article explains the autoregistration process for Oracle tables with SDO_GEOMETRY columns.

What is autoregistration?

Registering a table with ArcSDE involves placing information about the table in a number of the ArcSDE system tables. An entry is placed in SDE.TABLE_REGISTRY, which contains information about all tables registered with ArcSDE. An entry is also made in SDE.LAYERS and SDE.GEOMETRY_COLUMNS, two tables that maintain lists of tables or views containing spatial columns. A new entry is also placed in SDE.SPATIAL_REFERENCES, which describes the spatial reference used by the layer’s spatial column. This process can be done manually with sdelayer -o register, or it can occur automatically.

Autoregistration of third party tables containing Oracle SDO_GEOMETRY columns occurs each time a connection to the ArcSDE server occurs and requests a list of layers on the server. For example, ArcCatalog requests a list of layers when connecting to an ArcSDE server. Users can display a list of layers available on the server using the administration commands 'sdelayer -o describe'. Other operations also request a list of layers.

Selecting tables to autoregister

ArcSDE determines which tables it should autoregister by performing queries on the SDE.LAYERS table and on the view ALL_SDO_GEOM_METADATA. SDE.LAYERS is an ArcSDE system table containing a list of all ArcSDE layers. ALL_SDO_GEOM_METADATA is an Oracle view containing entries for each SDO_GEOMETRY column in tables that you have SELECT access to and that have Oracle Spatial metadata. Entries in ALL_SDO_GEOM_METADATA may represent tables or views.

When a new layer list is requested, ArcSDE queries ALL_SDO_GEOM_METADATA for entries that are not already listed in SDE.LAYERS and that have only one entry in ALL_SDO_GEOM_METADATA, that is, that have only a single SDO_GEOMETRY column. If entries are found matching these criteria, ArcSDE considers them as candidates for autoregistration.

Finding the Registered Row ID Column

The registered row ID column is very important to ArcSDE. Not every table registered with ArcSDE has a row ID column, and some operations require this unique identifier for each row in the table or layer. Without a registered row ID column, operations that perform queries, such a panning, zooming, and identifying features will not work.

The registered row ID numbers must be stored as unique, NOT NULL integers. During autoregistration, ArcSDE attempts to find an existing column in the table to register as the row ID column. If it finds a suitable column, it will register this choice in SDE.TABLE_REGISTRY. Autoregistration searches for a pre-existing row ID column by looking for the first column in the table that is 'NUMBER(38) NOT NULL PRIMARY KEY'. If no such column is found, ArcSDE looks for the first column that is 'NUMBER(38) NOT NULL UNIQUE'.

If no column exists in the table matching either of these cases, the table is autoregistered as a layer but without a registered row ID column. If this happens, the layer is viewable at full extent but ArcSDE cannot perform spatial queries on it. To remedy this situation, alter the autoregistered layer to specify a registered row ID column. Either create a new row ID column, or use an existing non-NULL, unique integer column as the registered row ID column. Use ArcCatalog to register the layer with the geodatabase, or use 'sdetable -o alter_reg' to specify the registered row ID column.

Discovering the feature type

ArcSDE examines the first row in the table to determine what type of features the spatial column contains. Autoregistration assumes that the table contains the same type of features in all the rows of the table. If this is not the case, the entity type mask can be changed after autoregistration using 'sdelayer -o alter –e ...'. The entity type flags used to indicate the feature type are recorded in SDE.LAYERS.


SDO_GTYPE Feature Type Autoregistered as
--------- ------------ -----------------
nnn0 Unknown type (Not registered)
nnn1 Point pc
nnn2 Line sc+
nnn3 Polygon ac+
nnn4 Heterogeneous (Not registered)
nnn5 Multipoint pc+
nnn6 Multiline sc+
nnn7 Multipolygon ac+

If Z (elevation) ordinate is present, "3" is added.
If measure (LRS) ordinate is present, "M" is added.

If there are no rows in the table or if the feature in the first row is a feature type that is not supported by ArcSDE, the table is not autoregistered.

Setting the ArcSDE offset and scale

The offset and scale are calculated and registered for the layer based on the upper and lower bounds each dimension recorded in the Oracle metadata for the table. If these values cannot be used, then ArcSDE will calculate the extent of the layer and use that information to calculate the offsets and scales.

Determining the measure dimension

If there are three or four dimensions in each feature, the third or fourth dimensions are Z (elevation) or measure. Either or both may be present. If both are present, either may be the measure or Z dimension. Autoregistration uses the following rules to determine the meaning of the third and fourth dimensions.

Since Oracle 9i Release 2, the second digit of the four-digit SDO_GTYPE can specify which of the dimensions contains the measure (LRS) values. Valid values are 3, 4 or 0. If zero, ArcSDE uses other rules to determine which dimension contains measure values.

If there are three dimensions in the feature, and the SDO_DIM_NAME for the third dimension starts with the letter 'M', then ArcSDE assumes the third dimension contains measure values. Otherwise, the third dimension is used as Z.

If there are four dimensions in the feature, then if the SDO_DIM_NAME for either dimension starts with the letter 'M', the first one starting with 'M' is treated as the measure dimension. If neither SDO_DIM_NAME starts with 'M', then the fourth dimension is used as the measure dimension.

Disabling autoregistration

Autoregistration can be disabled. Disabling autoregistration prevents ArcSDE from querying for and registering unregistered tables. This can reduce processing when connecting, but new tables will not be automatically discovered and registered.

See Related Information below for the knowledge base article about how to "Disable autoregistration of Oracle Spatial tables" for instructions.

Related Information