Indexing GeoRaster Objects

3.9 Indexing GeoRaster Objects

GeoRaster data can be indexed in various ways. The most important index you can create on a GeoRaster object is a spatial (R-tree) index on the spatial extent (footprint) geometry of the GeoRaster object (spatialExtent attribute, described in spatialExtent Attribute). For large-scale geospatial image and raster databases, you should always create spatial indexes on the GeoRaster columns. The following are the basic steps to create a spatial index on GeoRaster column. (The examples assume that the GeoRaster table name is CITY_IMAGES and its GeoRaster column name is IMAGE.)

Insert a row into the USER_SDO_GEOM_METADATA view with the georaster table name (CITY_IMAGES in this example) and the spatial extent of the GeoRaster column name (IMAGE.SPATIALEXTENT). Be sure that the correct SRID value (3371 in this example) is registered.

INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
VALUES (
     'city_images',
     'image.spatialextent',
     SDO_DIM_ARRAY(
        SDO_DIM_ELEMENT('X', -1000000000, 1000000000, 0.005),
        SDO_DIM_ELEMENT('Y', -1000000000, 1000000000, 0.005)),
     3371
);

Create a spatial index on the GeoRaster column, as in the following example which creates a spatial index named CITY_IMAGES_IDX on the spatial extents of the images using default values for all parameters.
```
CREATE INDEX city_images_idx 
      ON city_images (image.spatialextent)
      INDEXTYPE IS MDSYS.SPATIAL_INDEX;
```
The preceding statement may fail if there are some invalid spatial extents or if the SRID values in the GeoRaster table do not match the SRID registered in the preceding step. If the statement fails, ensure that all GeoRaster objects have a valid spatialExtent geometry attribute and that all spatialExtent geometries have the same SRID. (Null for the spatialExtent values is acceptable.) Then re-create the spatial index.

See also Special Considerations if the GeoRaster Table Has a Spatial Index for special considerations if the GeoRaster table already has a spatial index. For more information about creating spatial indexes and about advanced capabilities, see Oracle Spatial Developer's Guide.

You can also create one or more other indexes, such as:

Function-based indexes on metadata objects using the Oracle XMLType or Oracle Text document indexing functionality
Standard indexes on other user-defined columns of the GeoRaster table, such as cloud coverage, water coverage, or vegetation

You should also create a single B-tree index on the rasterId, pyramidLevel, bandBlockNumber, rowBlockNumber, and columnBlockNumber columns of each raster data table. This should be done using PRIMARY KEY (rasterID, pyramidLevel, bandBlockNumber,rowBlockNumber, columnBlockNumber), as shown in Example 3-2 and Example 3-3.

Parent topic: GeoRaster Database Creation and Management