3 GeoRaster Operations

This chapter describes how to perform the main kinds of GeoRaster operations. A typical GeoRaster workflow consists of most or all of the following steps:

Create the GeoRaster table, GeoRaster DML trigger, and raster data table (see Section 3.1).
Initialize or create GeoRaster objects (see Section 3.2).
Adjust the Java pool size before importing GeoRaster data, if necessary (see Section 3.3).
Load raster imagery or grids (see Section 3.4).
Validate GeoRaster objects, if they have not already been validated (see Section 3.5).
Georeference the GeoRaster objects, if necessary (see Section 3.6).
Set the spatial extents of the GeoRaster objects (see Section 3.7).
Create spatial indexes or other indexes, or both (see Section 3.8).
Change the GeoRaster storage format, if necessary (see Section 3.9).
Query and update the GeoRaster metadata (see Section 3.10).
Query and update cell data (see Section 3.11).
Process GeoRaster objects (see Section 3.12).
Compress GeoRaster objects, if appropriate (see Section 3.13).
View GeoRaster objects (see Section 3.14).
Export GeoRaster objects (see Section 3.15).
Update GeoRaster objects before committing the transaction (see Section 3.16).
Transfer GeoRaster data between databases (see Section 3.17).
Ensure raster data table name uniqueness, if necessary (see Section 3.18).
Manually maintain the GeoRaster system, if necessary (see Section 3.19).
Deal with possible GeoRaster data problems, if necessary (see Section 3.20).

After you create the GeoRaster objects, load the data, and validate the GeoRaster objects, you can perform the remaining operations in any order, depending on your application needs. You may also be able to skip certain operations.

Some operations can be performed using SQL, and some operations must be performed using PL/SQL blocks. For examples of these operations, see the demo files described in Section 1.11 and the examples in Chapter 4.

This chapter contains the sections that explain the main kinds of GeoRaster operations.

Chapter 4 contains detailed reference information about the SDO_GEOR package, which contains subprograms (functions and procedures) to work with GeoRaster data and metadata.

3.1 Creating the GeoRaster Table, Trigger, and Raster Data Table

Before you can work with GeoRaster objects, you must create a GeoRaster table, the GeoRaster DML trigger for that table, and one or more raster data tables if they do not already exist. Follow these steps:

Create a GeoRaster table with a column of type SDO_GEORASTER. 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, image_description VARCHAR2(50), image SDO_GEORASTER);
```
For more information about GeoRaster tables, see Section 1.4.
Create the standard GeoRaster DML trigger for the GeoRaster table. Example 3-2 creates the standard GeoRaster DML trigger for the table named CITY_IMAGES that contains a GeoRaster column named IMAGE.
Example 3-2 Creating the GeoRaster DML Trigger for the City Images Table
```
EXECUTE sdo_geor_utl.createDMLTrigger('CITY_IMAGES', 'IMAGE');
```
For more information about the standard GeoRaster DML trigger, see Section 3.1.1.
Create a raster data table (or several raster data tables) to be used with the objects in the GeoRaster table. Example 3-3 creates a raster data table named CITY_IMAGES_RDT, which will be used to store information about each block of each GeoRaster object in the CITY_IMAGES table. (The association between a GeoRaster table and a raster table is not made until you create a GeoRaster object, as explained in Section 3.2.)
Example 3-3 Creating a Raster Data Table for City Images
```
CREATE TABLE city_images_rdt OF SDO_RASTER
  (PRIMARY KEY (rasterID, pyramidLevel, bandBlockNumber,
    rowBlockNumber, columnBlockNumber))
  TABLESPACE tbs3 NOLOGGING
  LOB(rasterBlock) STORE AS lobseg
    (TABLESPACE  tbs3_2
     CHUNK 8192
     CACHE READS
     NOLOGGING
     PCTVERSION 0
     STORAGE (PCTINCREASE 0)
    );
```
For more information about the keywords and options when creating a raster data table, see Section 1.4.2.

3.1.1 GeoRaster DML Trigger

To ensure the consistency and integrity of internal GeoRaster tables and data structures, GeoRaster supplies a trigger that performs necessary actions 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. You must ensure that the trigger is used properly by calling the SDO_GEOR_UTL.createDMLTrigger procedure (described in Chapter 5) to create a trigger on each GeoRaster column in each GeoRaster table. For example, if a table contains two GeoRaster columns, you must call the SDO_GEOR_UTL.createDMLTrigger procedure twice (once for each combination of table name and GeoRaster column) before you perform any DML operations on the table.

You should create the necessary DML trigger or triggers immediately after you create a GeoRaster table, and you must create the trigger or triggers before you perform any operations on the table.

Each time you call the SDO_GEOR_UTL.createDMLTrigger procedure successfully, GeoRaster creates a trigger with a unique name. (All GeoRaster DML trigger names start with the string GRDMLTR_.) When you drop a GeoRaster table, all GeoRaster triggers associated with the table are automatically dropped also.

If you have created the GeoRaster DML trigger on a column, GeoRaster automatically performs the following actions when the trigger is fired as a result of a DML operation affecting that column:

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 Section 2.4). 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.

3.2 Creating New GeoRaster Objects

Before you can store a GeoRaster image in a GeoRaster table, you must create the GeoRaster object. To create a new GeoRaster data object, you have the following options:

Initialize an empty GeoRaster object, using the SDO_GEOR.init function.
Create a blank GeoRaster object, using the SDO_GEOR.createBlank function.

You cannot perform any GeoRaster operations if the object has not been properly created (that is, if the object is an atomic null). The SDO_GEOR.init and SDO_GEOR.createBlank functions initialize GeoRaster objects with their raster data table and raster ID values if these are not already specified, and ensure that the raster data table name and raster ID value pair is unique for the current user.

If the new GeoRaster object will hold raster cell data (resulting from another GeoRaster procedure, such as SDO_GEOR.importFrom, SDO_GEOR.subset, or SDO_GEOR.copy), and if the raster data table for this new GeoRaster object does not exist, you must first create the raster data table. For information about creating a raster data table, see Section 1.4.2, especially Example 1-2.

To avoid potential GeoRaster data problems (some of which are described in Section 3.20), always register an initialized GeoRaster object in the GeoRaster system views by inserting the GeoRaster object into a GeoRaster table, and do this before you perform any other operations on the GeoRaster object.

3.3 Adjusting Java Pool Size Before Importing GeoRaster Objects

Loading GeoRaster objects with the SDO_GEOR.importFrom procedure may require you to increase the size of the Java pool. This section briefly explains how to configure the Java pool size.

There are four auto-tuned SGA initialization parameters: DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, and JAVA_POOL_SIZE. You can also set these parameters with the ALTER SYSTEM command. Depending on whether automatic shared memory management is enabled, these parameters behave differently, as explained in the following sections.

3.3.1 Using Automatic Shared Memory Management

You can enable the automatic shared memory management feature by setting the SGA_TARGET parameter to a value greater than zero. For example:

ALTER SYSTEM SET SGA_TARGET=248M;

The value specified for SGA_TARGET cannot exceed the value specified for SGA_MAX_SIZE. In addition, you must ensure that the STATISTICS_LEVEL initialization parameter is set to TYPICAL (the default) or ALL.

After the parameter SGA_TARGET is set at a value greater than zero, DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE and JAVA_POOL_SIZE parameters specify the minimum sizes for the four associated SGA components. To allow the Java pool size to grow during the SDO_GEOR.importFrom procedure, you might need to set some combination of the DB_CACHE_SIZE, SHARED_POOL_SIZE, and LARGE_POOL_SIZE parameters to smaller values (if they are not already zero). The following example sets three parameters to relatively small values and the JAVA_POOL_SIZE parameter to 80 megabytes:

ALTER SYSTEM SET DB_CACHE_SIZE=8M;
ALTER SYSTEM SET SHARED_POOL_SIZE=50M;
ALTER SYSTEM SET LARGE_POOL_SIZE=0;
ALTER SYSTEM SET JAVA_POOL_SIZE=80M;

3.3.2 Using Manual Memory Management

If the SGA_TARGET parameter is not set or is set to zero, automatic shared memory management is disabled, and the DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, and JAVA_POOL_SIZE parameters behave as they did in releases before Oracle10g. That is, in this case these parameters specify the maximum sizes for the associated SGA components. Because the sum of the sizes for the different SGA components is bounded by the SGA_MAX_SIZE value, before you can increase the JAVA_POOL_SIZE value with the ALTER SYSTEM command, you might have to decrease the values of the other SGA-related parameters. The following example shows the general format for statements to achieve this result:

ALTER SYSTEM SET DB_CACHE_SIZE=<current_db_cache_size> - m1;
ALTER SYSTEM SET SHARED_POOL_SIZE=<current_shared_pool_size> - m2;
ALTER SYSTEM SET LARGE_POOL_SIZE=<current_large_pool_size> - m3;
ALTER SYSTEM SET JAVA_POOL_SIZE=<current_java_pool_size> + m4;

You can also increase the SGA_MAX_SIZE value by shutting down the database and updating the initialization parameter file. After the database is restarted, you can also resize the SGA components as described in this section and in Section 3.3.1.

3.4 Loading GeoRaster Data

To load GeoRaster data, you have the following options:

Call the SDO_GEOR.importFrom procedure to load images into GeoRaster objects.
Use the GeoRaster loader tool or viewer tool, which are described in Section 1.10.

With both options, you can do the following:

Compress raster data and store the data in JPEG-compressed or DEFLATE-compressed GeoRaster objects.
Load an ESRI world file into an existing GeoRaster object, and georeference the raster data without reloading it. You can also specify an SRID with the world file and generate the spatial extent of the data.

Because an ESRI world file does not contain coordinate system information, you can specify the SRID value of a coordinate reference system for the load operation. If you do not specify an SRID, the model SRID of the GeoRaster objects is set to 0 (zero) by default, which means that the model space is the same as the raster (cell) space. However, if you do not want to set the model space SRID to zero, but you do not yet know the coordinate system of the model space, you can specify the SRID value as 999999, which means that the coordinate reference system is unknown. (Specifically, SRID 999999 is associated with a coordinate reference system named unknown CRS.) Later, when you know the actual coordinate reference system of the model space, you can set the SRID value accordingly.

For more information about the unknown CRS (SRID 999999) coordinate reference system, see Oracle Spatial User's Guide and Reference.

3.5 Validating GeoRaster Objects

Before you use a GeoRaster object, you should ensure that it is valid. Validation for a GeoRaster object includes checking the metadata and the raster cell data, and making sure that they are consistent. For example, validation checks the raster type, dimension information, and the actual sizes of cell blocks, and it performs other checks.

If you used the GeoRaster loader tool described in Section 1.10, the GeoRaster objects were validated during the load operation.

GeoRaster provides the following validation subprograms:

SDO_GEOR.validateGeoraster validates the GeoRaster object, including cell data and metadata. It returns TRUE if the object is valid; otherwise, it returns one of the following: an Oracle error code indicating why the GeoRaster object is invalid, FALSE if validation fails for an unknown reason, or NULL if the GeoRaster object is null. You should always use this function after you create a GeoRaster object.
SDO_GEOR.schemaValidate validates the metadata against the GeoRaster XML schema. You can use this function to locate errors if the SDO_GEOR.validateGeoraster function returned the error code 13454. The SDO_GEOR.schemaValidate and SDO_GEOR.validateGeoraster functions do not validate the spatial extent geometry.

3.6 Georeferencing GeoRaster Objects

Georeferencing, as explained in Section 1.6, establishes the relationship between cell coordinates of GeoRaster data and real-world ground coordinates (or some local coordinates). If you need to georeference GeoRaster objects, the following approaches are available:

If the original image is already georeferenced and if the georeferencing information is stored in an ESRI world file, you can use the SDO_GEOR.importFrom procedure to load an ESRI world file from a file or from a CLOB object, along with the image data itself (in either FILE or BLOB format). You can also use the GeoRaster client-side loader tool (described in Section 1.10) to load an ESRI world file from a file, along with the image file itself.

Because an ESRI world file does not specify the model coordinate system, you can set the model space of the georeferenced GeoRaster object using an Oracle SRID in either of the following ways: specify the SRID along with the world file as a parameter to the SDO_GEOR.importFrom procedure or the GeoRaster client-side loader (described in Section 1.10); or, after loading the world file, call the SDO_GEOR.setModelSRID procedure. You can also call the SDO_GEOR.setModelSRID procedure to change the model space of a georeferenced GeoRaster object.
You can use the SDO_GEOR.setSRS procedure to add, modify, and delete georeferencing information. For example, you can create an SDO_GEOR_SRS object and assign the coefficients and related georeferencing information, and then call the SDO_GEOR.setSRS procedure to add or update the spatial reference information of any GeoRaster object. If you know that one GeoRaster object has the same SRS information as another GeoRaster object, you can call the SDO_GEOR.getSRS function to retrieve an SDO_GEOR_SRS object from this GeoRaster object, and then call the SDO_GEOR.setSRS procedure to georeference the first GeoRaster object.
You can call the SDO_GEOR.georeference procedure to georeference a GeoRaster object directly. This function takes the coefficients A, B, C, D, E, F (described in a formula in Section 1.6.1) and other information, converts them into the coefficients a, b, c, d, e, f, and stores them in the spatial reference information of a GeoRaster object. If the original raster data is rectified and if the model coordinate of its origin (upper-left corner) is (x0, y0) and its spatial resolution or scale is s, then the following are true: A = s, B = 0, C = x0, D = 0, E = -s, F = y0.

Based on the SRS information of a georeferenced GeoRaster object, transforming GeoRaster coordinate information means finding the model (ground) coordinate associated with a specific cell (raster) coordinate, and the reverse. That is, you can do the following:

Given a specific cell coordinate, you can find the associated model space coordinate using the SDO_GEOR.getModelCoordinate function. For example, if you identify a point in an image, you can find the longitude and latitude coordinates associated with that point.

Given a model space coordinate, you can find the associated cell coordinate using the SDO_GEOR.getCellCoordinate function. For example, if you identify longitude and latitude coordinates, you can find the cell in an image associated with those coordinates.

3.7 Generating and Setting Spatial Extents

When a GeoRaster object is created, its spatial extent (spatialExtent attribute, described in Section 2.1.2) is not necessarily the enclosing geometry in its model space coordinate system. The spatial extent (footprint) geometry might initially be null, or it might reflect the cell space coordinate system or some other coordinate system. The ability to generate and set spatial extents is useful for building large GeoRaster databases of a global or large regional scope, in which the spatial extents are in one global geodetic coordinate system while the GeoRaster objects (imagery, DEMs, and so on) are in different projected coordinate systems. In such a case, you can create a spatial (R-tree) index on the spatial extents, which requires that all spatial extent geometries have the same SRID value.

To ensure that the spatial extent geometry of each GeoRaster object in a table is correct for its model space coordinate system (or for any other coordinate system that you may want to use), you must set the spatial extent. Moreover, to use a spatial index on the spatial extent geometries (described in Section 3.8), all indexed geometries must be based on the same coordinate system (that is, have the same SRID value).

You can set the spatial extent in either of the following ways: specify spatialExtent=TRUE as a storage parameter to the SDO_GEOR.importFrom procedure or the GeoRaster client-side loader (described in Section 1.10), or use the SQL UPDATE statement. If you use the SDO_GEOR.importFrom procedure or the loader, the SRID cannot be null or 0 (zero), and if there is an R-tree index on the GeoRaster spatial extent, the SRID of the spatial extent must match the SRID of the existing spatial index; otherwise, the spatial extent is set to a null value.

In addition, if you do not already have the spatial extent geometry, you can generate it using the SDO_GEOR.generateSpatialExtent function, and use that geometry to update the GeoRaster object. The following example updates the spatial extent geometry of a specified GeoRaster object in the CITY_IMAGES table (created in Example 3-1 in Section 3.1) to the generated spatial extent (reflecting the model coordinate system) of that object:

UPDATE city_images c
  SET c.image.spatialExtent = sdo_geor.generateSpatialExtent(image)
    WHERE c.image_id = 100;
COMMIT;

If you already know the spatial extent geometry for a GeoRaster object, or if you want the spatial extent geometry to be based on a coordinate system other than the one for the model space, construct the SDO_GEOMETRY object or select it from a table, and then update the GeoRaster object to set its spatial extent attribute to that geometry, as shown in the following example:

DECLARE
 geom sdo_geometry;
BEGIN
-- Set geom to an SDO_GEOMETRY object that covers the spatial extent
-- of the desired GeoRaster object. If necessary, perform coordinate
-- system transformation before setting geom.
-- geom := sdo_geometry(...);
  UPDATE city_images c
    SET c.image.spatialExtent = geom WHERE c.image_id = 100;
  COMMIT;
END;

3.8 Indexing GeoRaster Data

GeoRaster data can be indexed in various ways. The most important index you can create on a GeoRaster object is a spatial index on the spatial extent (footprint) geometry of the GeoRaster object (spatialExtent attribute, described in Section 2.1.2). For information about creating spatial indexes, see Oracle Spatial User's Guide and Reference.

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

In addition to any indexes that you may create, a B-tree index for Oracle internal use is built on each raster data table.

3.9 Changing Raster Storage

You can change some aspects of the way raster image data is stored: the raster blocking size, cell depth, interleaving type, and other aspects. To make such changes, use the SDO_GEOR.changeFormatCopy procedure, and specify the desired storage parameter values with the storageParam parameter. You can also specify storage parameters with several other functions and procedures that load and process a GeoRaster object to create another GeoRaster object.

For information about the storage parameters that you can specify, see Section 1.4.1.

3.10 Querying and Updating GeoRaster Metadata

You can query metadata for a GeoRaster object, and you can update many attributes of the metadata.

You can use many functions, most of whose names start with get, to query the metadata and ancillary information (for example, SDO_GEOR.getTotalLayerNumber and SDO_GEOR.hasPseudoColor).

You can use several subprograms, most of whose names start with set, to update metadata and ancillary data (for example, SDO_GEOR.setSRS and SDO_GEOR.setColorMap).

See Section 1.9.3 for categories and lists of subprograms that get and set GeoRaster metadata and cell data.

3.11 Querying and Updating Cell Data

To display part or all of a raster image, you can query the data for a cell (pixel), a range of cells, or the entire image associated with a GeoRaster object:

SDO_GEOR.getCellValue returns the value of a single cell of the GeoRaster object.
SDO_GEOR.getRasterSubset creates a single BLOB object containing all cells of a precise subset of the GeoRaster object (as specified by a window, layer or band numbers, and pyramid level). This BLOB object contains only raster cells and no related metadata.
SDO_GEOR.getRasterData creates a single BLOB object containing all cells of the GeoRaster object at a specified pyramid level. This BLOB object contains only raster cells and no related metadata.
SDO_GEOR.getRasterBlocks returns an object that includes all image data inside or touching a specified window. Specifically, it returns an object of the SDO_RASTERSET collection type that identifies all blocks of a specified pyramid level that are inside or touch a specified window.

You can also use the SDO_GEOR.exportTo procedure to export all or part of a raster image to a BLOB object (binary image format) or to a file of a specified file format type.

To change the value of raster cells in a specified window to a single value, you can use the SDO_GEOR.changeCellValue procedure.

Note:

If you use any procedure that adds or overwrites data in the input GeoRaster object, you should make a copy of the original GeoRaster object and use the procedure on the copied object. After you are satisfied with the result of the procedure, you can discard the original GeoRaster object if you wish.

See Section 1.9.3 for categories and lists of subprograms that get and set GeoRaster metadata and cell data.

3.12 Processing GeoRaster Objects

You can perform a variety of processing operations on GeoRaster data, including changing the format, subsetting (cropping), scaling, and generating pyramids. See the GeoRaster PL/SQL demo files, described in Section 1.11, for examples and explanatory comments.

3.13 Compressing and Decompressing GeoRaster Objects

You can reduce the storage space requirements for GeoRaster objects by compressing them using JPEG-B, JPEG-F, or DEFLATE compression. You can decompress any compressed GeoRaster object, although this is not required for any GeoRaster operations, because any GeoRaster operation that can be performed on an uncompressed (decompressed) GeoRaster object can be performed on a compressed GeoRaster object.

To compress or decompress a GeoRaster object, use the compression keyword in the storageParam parameter with the SDO_GEOR.changeFormatCopy procedure, or with several other procedures that load and process a GeoRaster object to create another GeoRaster object, including SDO_GEOR.importFrom, SDO_GEOR.mosaic, SDO_GEOR.scaleCopy, and SDO_GEOR.subset. (There are no separate procedures for compressing and decompressing a GeoRaster object.)

For more information about GeoRaster compression and decompression, see Section 1.8.

3.14 Viewing GeoRaster Objects

To view GeoRaster objects, you have the following options:

Call the SDO_GEOR.exportTo procedure to export GeoRaster objects to image files, and then display the images using image tools or a Web browser.
Use the standalone GeoRaster viewer tool (one of the tools described in Section 1.10).

With the GeoRaster viewer tool, you can select any GeoRaster object of a database schema (user), query and display the whole or a subset of a GeoRaster object, zoom in and zoom out, scroll, and perform other basic operations. The pyramid level, cell coordinates, and model coordinates (if the object is georeferenced) are displayed for the point at the mouse pointer location. You can display individual cell values and choose different layers of a multiband or hyperspectral image for RGB full color display. The blocking boundaries can be overlapped on the top of the display. Depending on the data and your requests, the viewer can display the raster data in grayscale, pseudocolor, and 24-bit true color over an intranet or the Internet. (For the current release, bitmap, two-dimensional grayscale, pseudocolor, and three-band full color are supported.) Some of the basic GeoRaster metadata is also displayed.

In the GeoRaster viewer tool, the data displayed by every operation is retrieved from the GeoRaster server; it is not generated in memory.

The GeoRaster viewer tool also includes menu commands to call the GeoRaster loader and exporter tools, thus enabling you to use a single tool as an interface to the capabilities of all the GeoRaster tools.

3.15 Exporting GeoRaster Objects

To export GeoRaster objects to image files, you have the following options:

Call the SDO_GEOR.exportTo procedure (which can export either to a file or to a BLOB object).
Use the GeoRaster exporter tool or viewer tool, which are described in Section 1.10.

3.16 Updating GeoRaster Objects Before Committing

Before you commit a database transaction that inserts, updates, or deletes GeoRaster cell data or metadata, you should update the GeoRaster object. If you do not update the GeoRaster object after changing cell data, one or more of the following can result: an invalid GeoRaster object, dangling raster data, and inconsistent metadata. If you do not update the GeoRaster object after changing GeoRaster metadata, the metadata changes will not take effect.

If you decide to roll back the transaction instead of committing it, an UPDATE statement is not needed.

In Example 3-4, the UPDATE statement is required after the call to the SDO_GEOR.changeFormatCopy procedure and before the COMMIT statement.

Example 3-4 Updating a GeoRaster Object Before Committing

DECLARE
    gr1 sdo_georaster;
    gr2 sdo_georaster;
BEGIN
    SELECT georaster INTO gr2 from georaster_table WHERE georid=11 FOR UPDATE;
    SELECT georaster INTO gr1 from georaster_table WHERE georid=1;
    sdo_geor.changeFormatCopy(gr1, 'blocksize=(2048,2048)', gr2);
    UPDATE georaster_table SET georaster=gr2 WHERE georid=11;
    COMMIT;
END;
/

3.17 Transferring GeoRaster Data Between Databases

You can use either the Data Pump Export and Import utilities or the original Export and Import utilities to transfer GeoRaster data between databases. You must export and import rows from both the GeoRaster table and its related raster data table or tables. After the transfer, you may also need to insert the GeoRaster system data for the imported GeoRaster objects into the USER_SDO_GEOR_SYSDATA view (described in Section 2.4) in the target schema, and you should use the SDO_GEOR.validateGeoraster function to check the validity of imported GeoRaster objects.

For information about the Data Pump Export and Import utilities and the original Export and Import utilities, see Oracle Database Utilities.

To transfer GeoRaster data between databases, follow these general steps:

Check for and resolve any conflicts, as explained in Section 3.17.1.
Perform the data transfer, as explained in Section 3.17.2.

3.17.1 Checking for and Resolving Conflicts

For a successful import of GeoRaster data into a target schema, there must be no conflicts in the target schema's GeoRaster system data. The following conditions can cause a conflict:

A raster data table with the same name is already defined in another schema in the target database.
Any pairs of raster data table name and raster ID to be inserted into the target schema's USER_SDO_GEOR_SYSDATA view are not unique.

To check for possible raster data table name conflicts, connect to the target database as a user with the DBA role and enter a query in the following form:

SELECT UNIQUE owner FROM all_sdo_geor_sysdata 
  WHERE rdt_table_name=UPPER('<rdt_to_be_exported>') 
    AND owner<>UPPER('<target_schema>');

In the preceding example, replace <rdt_to_be_exported> with the name of the raster data table to be exported, and replace <target_schema> with the target schema name.

If the query returns one or more rows, the export operation would cause a conflict between raster data table names in the target database; and if the query returns more than one row, there is already a conflict between raster data table names in the target database. To resolve each conflict, call the SDO_GEOR_UTL.renameRDT procedure (documented in Chapter 5) in either the source or target database before you perform the data transfer.

If any pairs of raster data table name and raster ID to be inserted into the target schema's USER_SDO_GEOR_SYSDATA view are not unique, you must modify one of the GeoRaster objects involved in the conflict in either the source or the target schema. To avoid moving data around, fixing a conflict usually means changing the raster ID of one GeoRaster object to another number. For example:

Find a raster ID that is not being used (in either the source or the target schema) in the raster data table involved in the conflict.

Modify one of the GeoRaster objects. For example:

UPDATE georaster_table t SET t.georaster_col.rasterid=new_raster_id 
  WHERE t.georaster_col.rasterid=old_raster_id ;

After the UPDATE statement, if the required standard GeoRaster data manipulation language (DML) trigger has been created and enabled, the raster ID value shown in the USER_SDO_GEOR_SYSDATA view for the fixed GeoRaster object is updated correspondingly. You should validate the fixed GeoRaster object before performing a commit or any other operation.

3.17.2 Performing the GeoRaster Data Transfer

To export GeoRaster data from one database and import it into another, you must export and import rows from both the GeoRaster table and its associated raster data table or tables. You can export and import raster data tables just as you would other data tables; however, special considerations apply to importing a GeoRaster table. You have the following basic options for each GeoRaster table:

Importing the GeoRaster table definition and table row data in separate steps

This approach ensures that all system data related to the GeoRaster table is automatically maintained during the import operation. However, the GeoRaster table may need to be imported separately from the raster data table or tables associated with it (for example, to achieve acceptable import performance with very large raster data tables), although the tables can be exported together and several GeoRaster tables can be grouped together for an import operation.
Importing the GeoRaster table definition and table row data in a single step

This approach allows you to import the GeoRaster table with its associated raster data table or tables, and it usually provides better performance. However, you must take specific actions to update the GeoRaster system data in the target schema after the import operation.

You must re-create the required GeoRaster DML trigger on each GeoRaster table after the import operation. If you imported the GeoRaster table with its triggers, you must drop its GeoRaster DML trigger and then re-create that trigger. (Depending on whether other triggers are also defined on a GeoRaster table, you can choose to import the table either with or without its triggers.)

To import the GeoRaster table definition and table row data in separate steps, follow this procedure:

Import the table definition, with or without triggers. The following command (to be entered on one line) imports the table definition, without triggers, for a GeoRaster table named GEORASTER_TABLE:
```
impdp gr/gr TABLES=georaster_table CONTENT=METADATA_ONLY EXCLUDE=TRIGGER DUMPFILE=dump_dir:exp.dmp
```
If you included triggers in the import operation, drop the GeoRaster DML trigger on the table.
Create the required GeoRaster DML trigger on the GeoRaster table by calling the SDO_GEOR_UTL.createDMLTrigger procedure.
Import the table row data of the GeoRaster table. The following command imports the table data of a GeoRaster table named GEORASTER_TABLE:
```
impdp gr/gr TABLES=georaster_table CONTENT=DATA_ONLY DUMPFILE=dump_dir:exp.dmp
```

To import the GeoRaster table definition and table row data in a single step, follow this procedure:

For each target schema, create a table to hold all related GeoRaster system data. For example:
```
CREATE TABLE tmp_sysdata_table
  AS SELECT * FROM all_sdo_geor_sysdata
    WHERE table_name=UPPER('<table_to_be_exported>')
      AND owner=(UPPER('<source_schema>');
```
In the preceding example, replace <table_to_be_exported> with the name of the GeoRaster table to be exported, and replace <source_schema> with the source schema name.

If you use the QUERY parameter to filter the GeoRaster objects to be exported, add corresponding conditions in the WHERE clause in the preceding example to filter the GeoRaster system data as well.
Export and import the table that you created in Step 1, together with GeoRaster tables and their related raster data tables. You can use any mode for the import operation.
If you included triggers in the import operation, drop the GeoRaster DML trigger on each GeoRaster table.
In the target schema, create the required GeoRaster DML trigger on each GeoRaster table-column pair by calling the SDO_GEOR_UTL.createDMLTrigger procedure.

Insert the rows in the table that you created in Step 1 back into the USER_SDO_GEOR_SYSDATA view in the target schema. For example:

INSERT INTO user_sdo_geor_sysdata
  SELECT table_name, column_name, metadata_column_name, rdt_table_name, 
      raster_id, other_table_names
    FROM tmp_sysdata_table;

Drop the table that you created in Step 1.

3.18 Ensuring Raster Data Table Name Uniqueness

Each raster data table name must be unique in the database. To check if any raster data table name conflicts exist in the database, connect to the database as a user with the DBA role, and enter the following query:

SELECT UNIQUE owner, rdt_table_name
  FROM (SELECT rdt, count(*) count
        FROM (SELECT UNIQUE owner, rdt_table_name rdt
              FROM all_sdo_geor_sysdata)
        GROUP BY rdt) a, all_sdo_geor_sysdata b
  WHERE a.rdt = b.rdt_table_name AND a.count > 1
  ORDER BY rdt_table_name, owner;

If this query returns any rows, one or more raster data table name conflicts exist in the database. To resolve all conflicts, use the SDO_GEOR_UTL.makeRDTNamesUnique procedure; or use the SDO_GEOR_UTL.renameRDT procedure as needed to resolve specific conflicts. Both procedures are documented in Chapter 5.

3.19 Manually Maintaining GeoRaster System Data

In general, you should not change the name of a GeoRaster table or its GeoRaster column. If you want to change the name of a GeoRaster table or GeoRaster column, the recommended approach is to create a new GeoRaster table with the desired table and column names, create the required GeoRaster DML trigger on the table, and copy each GeoRaster object from the old table to the new table by using the SDO_GEOR.copy procedure.

However, if you instead decide to use SQL statements to rename a GeoRaster table or Georaster column, you must manually maintain the GeoRaster system data to reflect the change. For example, use an UPDATE statement of the following general form:

UPDATE USER_SDO_GEOR_SYSDATA
   SET TABLE_NAME=UPPER('new_table_table'), COLUMN_NAME=UPPER('new_column_name')
 WHERE TABLE_NAME=UPPER('old_table_name') 
   AND COLUMN_NAME=UPPER('old_column_name');

You must also drop and re-create the required GeoRaster DML trigger specifying the new GeoRaster table and GeoRaster column names.

If you insert into or update the USER_SDO_GEOR_SYSDATA view, you must ensure the following for each new or changed row:

The TABLE_NAME column specifies a valid table owned by the current user.
The COLUMN_NAME column specifies a valid column of type SDO_GEORASTER in the specified table.
The RDT_TABLE_NAME column specifies a valid raster data table owned by the current user.
The name of the table specified in the RDT_TABLE_NAME column is unique in the database. (That is, no other user owns a table having this name.)
The RDT_TABLE_NAME and RASTER_ID columns specify a unique pair of values.

If you use the FLASHBACK TABLE statement to restore an earlier state of a GeoRaster table, you must manually restore the GeoRaster system data. You should also drop and re-create any required GeoRaster DML triggers defined on the table after a FLASHBACK TABLE TO BEFORE DROP statement, because the original trigger names cannot be restored.

3.20 Dealing with Possible GeoRaster Data Problems

If you do not perform GeoRaster operations in the required sequence, or if you perform an incorrect or inappropriate operation, some data problems can occur. For example:

A nonblank GeoRaster object might have been created, but no rows or an incorrect number of rows exist in the raster data table for that object, or the raster data blocks associated with the object have an incorrect length.
Raster data table rows might exist for a nonexistent GeoRaster object. The raster blocks associated with such rows are referred to as dangling blocks.

If a GeoRaster object is invalid because of a raster data error, delete the GeoRaster object and create it again.

If dangling raster blocks exist, they cause wasted disk space in the raster data table, although otherwise they do not present a problem as long as the necessary primary key is defined on the raster data table. If you want to remove the raster data table rows associated with dangling raster blocks, you can try to find rows associated with problems and to fix the problems. To find rows associated with problems, follow these steps:

To determine whether any dangling raster block data exists in a raster data table, issue a query of the following general form:

SELECT unique rasterid FROM rdt_tab 
 WHERE rasterid NOT IN
       (SELECT RASTER_ID FROM USER_SDO_GEOR_SYSDATA
         WHERE RDT_TABLE_NAME=UPPER('rdt_tab'));

To determine whether the dangling raster block data belongs to some GeoRaster object in a GeoRaster table, issue a query of the following general form:

SELECT t.georaster_col FROM georaster_tab t
 WHERE UPPER(t.georaster_col.rasterDataTable) = UPPER('rdt_name') AND
       t.georaster_col.rasterId = dangling_raster_id;

If multiple rows are returned from the preceding query, the GeoRaster-related DML trigger associated with this specific GeoRaster column is either missing or disabled and the returned GeoRaster objects are corrupted. In this case, the data is beyond repair. Delete the corrupted GeoRaster objects and clean up the dangling raster block data.

To remove the dangling raster block data from a raster data table, delete the rows associated with problems.

If no data is missing, you can manually repair a GeoRaster object by establishing the relationship between a GeoRaster object and some dangling raster block data. To do so, execute a statement of the following general form:

INSERT INTO USER_SDO_GEOR_SYSDATA 
   VALUES (UPPER('georaster_table'), UPPER('georaster_column'), NULL, 
           UPPER('rdt_name'), dangling_raster_id, NULL);

Always use the SDO_GEOR.validateGeoraster function to check the validity of a GeoRaster object after attempting any repair operation.