3.3 Creating the GeoRaster Table and Raster Data Tables

Before you can work with GeoRaster objects, you must create a GeoRaster table and one or more raster data tables, if they do not already exist.

• Creating a GeoRaster Table
  
• Creating Raster Data Tables
  
• Creating GeoRaster DML Triggers
  

3.3.1 Creating a GeoRaster Table

A GeoRaster table is any table that includes at least one column of type SDO_GEORASTER. The column can be an attribute column of another user-defined object type. Example 3-1 creates a GeoRaster table named CITY_IMAGES, which contains a column named IMAGE for storing GeoRaster objects.

Example 3-1 Creating a GeoRaster Table for City Images

CREATE TABLE city_images (image_id NUMBER PRIMARY KEY, image_description VARCHAR2(50), image SDO_GEORASTER);

For more information about GeoRaster tables, see GeoRaster Physical Storage.

3.3.2 Creating Raster Data Tables

After creating a GeoRaster table, you should create one or more raster data tables (RDTs) to be used with the objects in the GeoRaster table. You can create the RDT as an object table or as a relational table. You should use the LOB storage format SecureFiles LOBs (SecureFiles) when creating RDTs. Using SecureFiles significantly improves the performance of GeoRaster operations, compared to using the original LOB storage paradigm BasicFiles LOBS (BasicFiles).

Note:

The RDT names must be unique in the database as described in Raster Data Table.

Example 3-2 creates an RDT using SecureFiles. The RDT will be used to store all raster blocks of one or many GeoRaster objects in the CITY_IMAGES table or other GeoRaster tables. (The association between a GeoRaster object and the RDT is not made until you create a GeoRaster object, as explained in Creating New GeoRaster Objects.)

Example 3-2 Creating a Raster Data Table Using SecureFiles

CREATE TABLE city_images_rdt OF SDO_RASTER
  (PRIMARY KEY (rasterID, pyramidLevel, bandBlockNumber,
    rowBlockNumber, columnBlockNumber))
  TABLESPACE im_tbs_2
  LOB(rasterBlock) STORE AS SECUREFILE
    (CACHE);

Example 3-3 Creating a Raster Data Table (Relational) Using SecureFiles

Example 3-3 creates an RDT with the same name as in Example 3-2, also using SecureFiles, but creating it as a relational table instead of an object table.

CREATE TABLE city_images_rdt
  (rasterID NUMBER,
  pyramidLevel NUMBER,
  bandBlockNumber NUMBER,
  rowBlockNumber NUMBER,
  columnBlockNumber NUMBER,
  blockMBR SDO_GEOMETRY,
  rasterBlock BLOB,
  CONSTRAINT pkey PRIMARY KEY (rasterId, pyramidLevel, bandBlockNumber,
    rowBlockNumber, columnBlockNumber))
  LOB (rasterblock) STORE AS SECUREFILE(cache);

The CREATE TABLE statement for the RDT must include the following clause (which is included in the preceding examples):

  (PRIMARY KEY (rasterID, pyramidLevel, bandBlockNumber,
    rowBlockNumber, columnBlockNumber))

This PRIMARY KEY clause creates a B-tree index on the raster data table, and this index is essential for optimal query performance.

When you use BasicFiles, you can specify a larger CHUNK size (16 or 32 KB) for the LOB storage to improve performance. With SecureFiles, there is no need to specify the CHUNK size parameter, and there are few other storage parameters to consider. Raster data tables using SecureFiles LOBs must be created in a tablespace with the automatic segment space management option. For information about using Oracle SecureFiles and performance considerations for BasicFiles LOBs, see Oracle Database SecureFiles and Large Objects Developer's Guide.

For reference information about creating tables, including specifying LOB storage, see the section about the CREATE TABLE statement in Oracle Database SQL Language Reference.

For more information about the keywords and options when creating an RDT, see Raster Data Table.

3.3.3 Creating GeoRaster DML Triggers

To ensure the consistency and integrity of internal GeoRaster tables and data structures, GeoRaster automatically creates a unique DML trigger for each GeoRaster column whenever a user creates a GeoRaster table (that is, a table with at least one GeoRaster column). This implies that you do not need to manually create the GeoRaster DML triggers in general.

However, there is an exception. That is, if you use the ALTER TABLE statement to add one or more GeoRaster columns, you must call the SDO_GEOR_UTL.createDMLTrigger procedure to create the DML trigger on each added GeoRaster column. For example, if you added a new column added_geor_col to your table my_geor_tab, you must run the following command:

EXECUTE SDO_GEOR_UTL.createDMLTrigger('MY_GEOR_TAB', 'ADDED_GEOR_COL');

Also, in some scenarios, such as a database upgrade or a data migration, you can call the SDO_GEOR_UTL.recreateDMLTriggers procedure to re-create the DML triggers on all GeoRaster columns.

The trigger is fired after each of the following data manipulation language (DML) operations affecting a GeoRaster object: insertion of a row, update of a GeoRaster object, and deletion of a row.

GeoRaster automatically performs the following actions when the trigger is fired:

• After an insert operation, the trigger inserts a row with the GeoRaster table name, GeoRaster column name, raster data table name, and rasterID value into the USER_SDO_GEOR_SYSDATA view (described in GeoRaster System Data Views (xxx_SDO_GEOR_SYSDATA)). If an identical entry already exists, an exception is raised.

• After an update operation, if the new GeoRaster object is null or empty, the trigger deletes the old GeoRaster object. If there is no entry in the USER_SDO_GEOR_SYSDATA view for the old GeoRaster object (that is, if the old GeoRaster object is null), the trigger inserts a row into that view for the new GeoRaster object. If there is an entry in the USER_SDO_GEOR_SYSDATA view for the old GeoRaster object, the trigger updates the information to reflect the new GeoRaster object.

• After a delete operation, the trigger deletes raster data blocks for the GeoRaster object in its raster data table, and it deletes the row in the USER_SDO_GEOR_SYSDATA view for the GeoRaster object.