FAQ: Why are spatial indexes and metadata important for storing data in SDO_GEOMETRY columns?


Why are spatial indexes and metadata important for storing data in SDO_GEOMETRY columns?


Many GIS operations such as zooming and panning displays, identifying features and performing analysis depend on spatial queries. Spatial queries, in turn, depend on spatial indexes. If the spatial index is not present or is corrupt, operations or the queries they rely on can fail, or they can produce incorrect results.

For SDO_GEOMETRY data in Oracle Spatial or Oracle Locator, creating a spatial index relies on having correct metadata information in the table owner’s USER_SDO_GEOM_METADATA view. There should be one row in this view for every SDO_GEOMETRY column in tables owned by the user.

The person or process that creates a new table with an SDO_GEOMETRY column is responsible for adding the metadata to USER_SDO_GEOM_METADATA. Oracle does not generate the metadata automatically. If a user creates a new table using SQL or some other software, the user should also make sure the metadata is created correctly. ArcSDE creates the metadata for all new tables it creates.

While Oracle does not generate the metadata for each new table automatically, it can provide help to determine what the metadata should be. The object type method GET_DIMS returns the number of dimensions in a feature by examining its SDO_GTYPE. SDO_AGGR_MBR or SDO_TUNE.EXTENT_OF can be used to find the extent of features in an SDO_GEOMETRY column.

If any of the dimensional metadata is invalid, it may not be possible to create a spatial index on the column, or the new spatial index may be invalid. For example, it is not possible to create a spatial index on a 3-D spatial column if there is only metadata for two dimensions.

To create the metadata for an Oracle SDO_GEOMETRY column, you need to know:

1. The number of dimensions in each feature.

2. The extent of each dimension of the data in the column, including the expected extent if new features will be added later.

3. The tolerance of each dimension, which is the maximum distance along each dimension that two points can be apart and still be considered to be in the same place.

4. The Oracle spatial reference identifier (SRID) to use with the column, if any.

A detailed discussion of the contents of the metadata and how to create it can be found in the Oracle Spatial Users Guide and Reference.