This chapter describes how to perform the main kinds of basic GeoRaster operations. A typical workflow to build and manage a GeoRaster database consists of most or all of the following steps:
Create the GeoRaster table and raster data table (see Section 3.1).
Initialize or create GeoRaster objects (see Section 3.2).
Adding temporary tablespaces for GeoRaster users (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).
Query and search GeoRaster objects (see Section 3.9).
Change and optimize the GeoRaster storage format, if necessary (see Section 3.10).
Copy GeoRaster objects (see Section 3.11).
Query and update the GeoRaster metadata (see Section 3.12).
Query and update cell data (see Section 3.13).
Interpolate cell values (see Section 3.14).
Process and analyze GeoRaster objects (see Section 3.15).
Monitor and Report GeoRaster operation progress (see Section 3.16).
Compress GeoRaster objects, if appropriate (see Section 3.17).
Delete GeoRaster objects, and drop GeoRaster tables and RDTs (see Section 3.18).
View GeoRaster objects (see Section 3.19).
Export GeoRaster objects (see Section 3.20).
Update GeoRaster objects before committing the transaction (see Section 3.21).
Use template-related subprograms to develop GeoRaster applications (see Section 3.22).
Use GeoRaster with Workspace Manager and Label Security (see Section 3.23).
Maintain efficient tablespace use by GeoRaster objects (see Section 3.24).
Maintain GeoRaster objects and system data in the database (see Section 3.25).
Transfer GeoRaster data between databases (see Section 3.26).
Use the Oracle Database transportable tablespaces feature with GeoRaster data (see Section 3.27).
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. You must update the GeoRaster object after you insert, update, reformat, compress, decompress, or delete the metadata or cell data of the GeoRaster object and before you commit the changes (see Section 3.21). For some examples of these operations, see the demo files described in Section 1.18 and the examples in Chapter 6.
Subsequent chapters in this book cover advanced topics (Raster Algebra and Analytics and Image Processing and Virtual Mosaic), and provide detailed reference information about GeoRaster PL/SQL packages (SDO_GEOR Package Reference, SDO_GEOR_ADMIN Package Reference, SDO_GEOR_AGGR Package Reference, SDO_GEOR_RA Package Reference, and SDO_GEOR_UTL Package Reference).
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.
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 Section 1.4.
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 a raster data table 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).
Example 3-2 creates a raster data table 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 a raster data table is not made until you create a GeoRaster object, as explained in Section 3.2.)
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 creates a raster data table with the same name as in Example 3-2, also using SecureFiles, but creating it as a relational table instead of an object table.
Example 3-3 Creating a Raster Data Table (Relational) Using SecureFiles
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 a raster data table 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 a raster data table, see Section 1.4.2.
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), with the following exception: 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. 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 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.
Before you can store a GeoRaster image in a GeoRaster table, you must create the GeoRaster object and insert it into a GeoRaster table before you start working on it. To create a new GeoRaster 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 the GeoRaster DML trigger ensures 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, including examples, see Section 3.1.2.
To avoid potential GeoRaster data problems (some of which are described in Section 3.25), an initialized GeoRaster object must be registered in the GeoRaster system views, which is done automatically when you insert the GeoRaster object into a GeoRaster table. This should be done before you perform any other operations on the GeoRaster object. Any GeoRaster operations that need to manipulate the raster data table raise an exception if the source or target GeoRaster object is not registered.
A GeoRaster database is typically very large and may use a temporary tablespace for some operations. When compression is involved in GeoRaster operations, particularly for large scale mosaicking operations, some temporary spaces are needed to store intermediate compressed or uncompressed data. If the GeoRaster user does not have a temporary tablespace, the database system temporary tablespace is used. This is not efficient and may slow down the mosaicking and other operations. Therefore, you should always create temporary tablespaces for GeoRaster users. For example:
CONNECT system/<password>; CREATE TEMPORARY TABLESPACE geor_temp TEMPFILE 'geor_temp_1.f' SIZE 1G AUTOEXTEND ON; ALTER USER <georaster_user> TEMPORARY TABLESPACE geor_temp;
In general, the amount of temporary space needed is limited. However, for large scale mosaicking, if the result is to be compressed, the temporary space needed is equal to the uncompressed image size of the result. Therefore, specify AUTOEXTEND ON when you create temporary tablespaces for GeoRaster users.
To load and export imagery or raster data, always consider third-party ETL tools (see the note in Section 1.17)
If you use features in GeoRaster to load raster data, you have the following options:
Call the SDO_GEOR.importFrom procedure to load images into GeoRaster objects.
Use the GeoRaster JAI-based loader tool or viewer tool, which are described in Section 1.17.1.
Use the GDAL based ETL tool for concurrent batch loading and exporting. This tool is described in Section 1.17.2.
With the first two options (SDO_GEOR.importFrom and JAI-based tool), 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 or a Digital Globe RPC text file (.rpb) 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.
Load a GeoTIFF format file with georeferencing, with or without raster data. To load and export the georeferencing information of GeoTIFF images, the GeoTIFF libraries are required. See Section 3.6, "Georeferencing GeoRaster Objects" for instructions.
When you load an image or raster file into GeoRaster object, always consider and apply appropriate blocking of the data, because file formats might have very different blocking schemes. In general, blocking sizes should be 256x256 or larger. There is no absolute rule for the blocking sizes, but the larger the raster, the larger the blocking sizes you might use. For regular rasters, 512x512 to 2048x2048 is appropriate. For very small images (less than 1024x1024x3), no blocking may be a good choice. Avoid blocking sizes that are either too small (such as 64x64 and 128x128) or too large, and avoid extreme blocking sizes such as 0.5 (one-half), 1, or 8 rows of pixels per block. Generally, the rectangular shape of blocks should be a square or close to a square. For different applications, you might tune the blocking to balance efficient storage with optimal performance.
After loading raster data into a GeoRaster object, you must ensure that the object is valid by calling the SDO_GEOR.validateGeoRaster function, as explained in Section 3.5.
Because an ESRI world file or .rpb file does not contain coordinate system information, you can specify the SRID value of a coordinate reference system for the load operation. However, if you do not specify an SRID, the model SRID of the GeoRaster objects is set to 0 (zero) by the loader, which means that the GeoRaster object is invalid, and therefore you must use the SDO_GEOR.setModelSRID procedure to specify a valid model space for this object. If 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 and Graph Developer's Guide.
The GeoRaster JAI-based loader does not support source raster files in BSQ interleaving, and it might raise an "insufficient memory" error if the files are too big, and it might have other restrictions. To avoid such problems, you can reformat and reblock the source files so that they can be properly loaded.
As an example, one way to do this is to use GDAL, an Open Source raster transformation library available from http://www.gdal.org, to reformat or reblock the image or raster file so that JAI (Java Advanced Imaging) can handle it. GDAL supports GeoRaster natively and can import and export GeoRaster objects directly, and can also process GeoRaster objects; for more information, see http://www.oracle.com/technetwork/database/enterprise-edition/getting-started-with-gdal-133874.pdf. You can also use GDAL to generate TFW files. For example, execute commands such as the following two (each command on a single line) using the GDAL command line or (for batch conversion) shell:
gdal_translate -of GTiff -co "TFW=YES" -co "INTERLEAVE=PIXEL" -co "TILED=YES" D:\my_image.tif D:\my_new_image.tif gdal_translate -of GTiff -co "TILED=YES" -co "TFW=YES" D:\my_image.ecw D:\my_new_image.tif
In the preceding example, the first command generates a TFW file, changes the interleaving to BIP (which is supported by JAI), and reblocks the image to 256x256. The second command converts ECW to TIFF, generates TFW, and reblocks the image.
Then use the GeoRaster loader tool (described in Section 1.17) , specifying reblocking so that the image can be loaded successfully and later retrieved from the database efficiently, as in the following example (a single command):
java -Xmx1024m oracle.spatial.georaster.tools.GeoRasterLoader mymachine db11 6521 georaster georaster thin 32 T globe image "blocking=true, blocksize=(512,512,3)" "D:my_image.tif,2,RDT_15, D:\my_image.tfw,82213"
If you receive an "insufficient memory" error when calling SDO_GEOR.importFrom to load a very large image, try loading the image with a different blocking size parameter or reblock the image into smaller internal tile sizes using GDAL before loading. For extremely large images, you can also use GDAL to tile the image into multiple smaller image files with sizes that JAI can handle, or you use GDAL to load and export the images directly.
Before you use a GeoRaster object or after you manually edit the raster data and metadata of a GeoRaster object, you should ensure that the object is valid. Validation for a GeoRaster object includes checking the registration of the GeoRaster object, checking the metadata and the raster cell data, and making sure that the metadata and data 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.17, 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.
SDO_GEOR.validateBlockMBR validates the blockMBR geometry associated with each raster block stored in the raster data table. If there are any invalid blockMBR geometries, call the SDO_GEOR.generateBlockMBR procedure to regenerate them.
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 or .rpb file containing RPC coefficients you can use the SDO_GEOR.importFrom procedure to load an ESRI world file or .rpb 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.17) to load an ESRI world file or .rpb file from a file, along with the image file itself.
Because an ESRI world file or .rpb 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.17); 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.
If the original image is a georeferenced GeoTIFF image, you can use the SDO_GEOR.importFrom procedure to load the image with georeferencing, by specifying GEOTIFF as the input format. To load only the georeferencing information from a GeoTIFF image, without the raster image data, into an existing GeoRaster object, add the raster=false storage parameter. You can specify a backup SRID with the srid storage parameter, in case the GeoTIFF configuration values do not match any SRID recognized by Oracle Spatial and Graph.
The GeoTIFF PixelIsArea raster space is equivalent to the GeoRaster upperleft-based cell coordinate system. An export to GeoTiff is always in PixelIsArea raster space, with a half-pixel adjustment of the affine transformation if the GeoRaster object is in center-based cell coordinate system. An import from GeoTIFF is always to the GeoRaster center-based cell coordinate system, with a half-pixel adjustment of the affine transformation if the GeoTIFF file is specified in PixelIsArea raster space.
You can also use the GeoRaster client-side loader tool (described in Section 1.17) to load GeoTIFF images with georeferencing, using the storage parameter geotiff=true. If you omit this parameter or specify geotiff=false, the image is loaded as a simple TIFF image without georeferencing. The raster and srid storage parameters also apply to the client-side loader tool.
To load or export GeoTIFF images with the GeoRaster client-side tools, add the following GeoTIFF libraries to your CLASSPATH definition:
xtiff-jai.jar (available from the SourceForge Extensible-TIFF-JAI group)
geotiff-jai.jar (available from the SourceForge GeoTIFF-JAI group)
To load or export GeoTIFF images with the SDO_GEOR.importFrom or SDO_GEOR.exportTo procedure, load these libraries into the MDSYS schema by connecting to the database as the SYSTEM user, editing $ORACLE_HOME/md/admin/sdoldgtf.sql as needed to reflect the paths to the xtiff-jai.jar and geotiff-jai.jar files, and running the sdoldgtf.sql SQL*Plus script. As an alternative to using the sdoldgtf.sql script, you can enter the following commands:
loadjava -user system/password@database -resolve -force -synonym -schema MDSYS -grant PUBLIC xtiff-jai.jar loadjava -user system/password@database -resolve -force -synonym -schema MDSYS -grant PUBLIC geotiff-jai.jar
If the database is downgraded to a release before Oracle Database 11g, these libraries should be uninstalled according to the script in $ORACLE_HOME/md/admin/sdormgtf.sql, editing it as needed to reflect the paths to the xtiff-jai.jar and geotiff-jai.jar files, and either running the sdormgtf.sql script or entering the following commands:
dropjava -user system/password@database -resolve -force -synonym -schema MDSYS -grant PUBLIC xtiff-jai.jar dropjava -user system/password@database -resolve -force -synonym -schema MDSYS -grant PUBLIC geotiff-jai.jar
You can use the SDO_GEOR.setSRS procedure to add, modify, and delete georeferencing information by directly accessing the GeoRaster SRS metadata. 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. You can use the SDO_GEOR.setSRS procedure to set up the spatial reference information for all supported functional fitting georeferencing models. Examples of setting up the SRS information from an existing DLT model and from an existing RPC model are included in reference section for the SDO_GEOR.setSRS procedure.
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.
If the GeoRaster object can be georeferenced using an affine transformation, you can call the SDO_GEOR.georeference procedure to georeference a GeoRaster object directly. As described in the reference information for the SDO_GEOR.georeference, this procedure takes the coefficients A, B, C, D, E, F 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.
If you have ground control points (GCPs) or want to collect GCPs yourself, you can call the SDO_GEOR.georeference procedure to georeference the GeoRaster object. For more information, see Section 5.1, "Advanced Georeferencing".
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.
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.17), 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.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;
If you create a spatial R-tree index on the GeoRaster spatial extents (as described in Section 3.8), all spatial extent geometries must have the same SRID value. However, the GeoRaster objects may have different model SRIDs, and most GeoRaster operations automatically generate a spatial extent for the output GeoRaster objects based on the model SRID of the source GeoRaster object or objects. This can cause problems when the resulting GeoRaster object with a spatial extent is updated into a GeoRaster table, which might already have a spatial index built on its spatialExtent attribute but using a different SRID.
In such cases, you must transform the spatial extent to the same SRID as that of the spatial index before the insert or update operation. The following example performs a mosaic operation, but then transforms the spatial extent of the resulting GeoRaster object to SRID 8307 before updating the GeoRaster table with that object.
DECLARE
gr sdo_georaster;
BEGIN
SELECT georaster INTO gr FROM mosaic_test WHERE georid=1 FOR UPDATE;
sdo_geor.mosaic('mosaic_data', 'georaster', gr, 'blocksize=(512,512)');
-- Transform the spatial extent geometry, if ncessary.
-- In this example example, the modelSRID of the mosaic is 27302,
-- but the SRID of the spatial index on mosaic_test is 8307.
gr.spatialExtent := sdo_cs.transform(gr.spatialExtent, 8307);
UPDATE mosaic_test SET georaster=gr WHERE georid=1;
END;
/
If a spatial R-tree index exists, a commit operation after an insert or update operation causes the index to be updated if the inserted or updated GeoRaster object has a spatial extent geometry. This could slow some operations if you perform a commit after each operation, particularly for batch jobs such as batch image loading. It is usually more efficient to balance the performance of index updates with GeoRaster operations, and to commit only in batches after the operations.
For example, image data loading (the SDO_GEOR.importFrom procedure and the GeoRaster loader) is followed by an internal commit operation, so it would be inefficient to load while generating spatial extents by specifying spatialExtent=TRUE. Instead, you should probably specify spatialExtent=FALSE, and then update the spatialExtent attribute afterward, to speed the loading process.
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 Section 2.1.2). 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 Section 3.7.1 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 and Graph 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, "Creating a Raster Data Table Using SecureFiles" and Example 3-3, "Creating a Raster Data Table (Relational) Using SecureFiles".
GeoRaster tables are regular relational tables that can have various columns, such as an ID number, a name, a timestamp, and a unique description in the form of a string. These columns can be indexed, and GeoRaster objects can be queried using the standard database indexing and query statements, as shown in many examples in this manual.
After the GeoRaster tables are spatially indexed (see Section 3.8, "Indexing GeoRaster Objects"), you can quickly query or search GeoRaster objects using a geometry as well. For example, you may want to find all images (maybe hundreds or more) inside a specific region and then generate full pyramids for each image, as in the following example:.
DECLARE
type curtype is ref cursor;
my_cursor curtype;
stmt varchar2(1000);
id number;
gr sdo_georaster;
gm sdo_geometry;
BEGIN
-- 1. Define the query area in WGS84 coordinate system
gm := sdo_geometry(2003, 8307, null,
sdo_elem_info_array(1,1003,3),
sdo_ordinate_array(5,6,30,30));
-- 2. Define the query statement on the GeoRaster table (city_images) and column (image)
stmt := 'select id, t.image from city_images t ' ||
'where sdo_inside(t.image.spatialextent, :1)=''TRUE''';
-- 3. Spatially query all images INSIDE the query area
-- and generate full pyramids for each of the images
open my_cursor for stmt using gm;
loop
fetch my_cursor into id, gr;
exit when my_cursor%NOTFOUND;
sdo_geor.generatePyramid(gr, 'resampling=bilinear');
execute immediate 'update city_images set image=:1
where id=:2' using gr, id;
commit;
end loop;
close my_cursor;
END;
You can also wrap up such blocks into a PL/SQL procedure and store it in the database, then call the stored procedure directly. These features enable you to organize complex processes and automate database administration tasks.
You can change or specify some aspects of the way raster image data is or will be stored: the raster blocking size, cell depth, interleaving type, and other aspects. To make such changes on an existing GeoRaster object, use the SDO_GEOR.changeFormatCopy procedure, and specify the desired storage parameter values with the storageParam parameter. You can specify storage parameters with most subprograms that load and process a GeoRaster object to create another GeoRaster object. That is, you can specify the output format when you call functions or procedures such as SDO_GEOR.importFrom, SDO_GEOR.subset, SDO_GEOR.mosaic, SDO_GEOR.reproject, SDO_GEOR.scaleCopy, SDO_GEOR.mergeLayers, and SDO_GEOR.createTemplate.
The storageParam parameter for the resulting GeoRaster objects should be based on factors such as the data size, dimension sizes, and application needs, as you determine them. However, the block sizes can also be optimized automatically based on the dimension sizes of the GeoRaster object and the desired output required by users, so that each GeoRaster object uses only minimum padding space but still meets the application requirements.
For more information, see Section 1.4.1, especially Table 1-1, "storageParam Keywords for Raster Data".
To copy a GeoRaster object, you must either copy it into an empty GeoRaster object or overwrite an existing valid GeoRaster object. (Empty GeoRaster objects are explained in Section 1.4.3.) To make an identical copy of the source GeoRaster object, use the SDO_GEOR.copy procedure; to make a copy that includes storage format changes, use the SDO_GEOR.changeFormatCopy procedure (see Section 3.10).
To copy a GeoRaster object using an empty GeoRaster object, follow these steps:
Initialize an empty GeoRaster object while inserting it into the destination table, returning the empty GeoRaster object.
Use the SDO_GEOR.copy or SDO_GEOR.changeFormatCopy procedure to copy the GeoRaster object into the returned empty GeoRaster object.
Use UPDATE statement to update the desired row in the destination table so that its GeoRaster column contains the copied GeoRaster object.
When you are ready to commit the transaction, use the COMMIT statement.
For an example of copying using an empty GeoRaster object, see the example for the SDO_GEOR.copy procedure inChapter 6.
To copy a GeoRaster object so that it overwrites (replaces) an existing GeoRaster object, follow these steps:
Select the existing GeoRaster object for update.
Use the SDO_GEOR.copy or SDO_GEOR.changeFormatCopy procedure to copy the selected GeoRaster object into either a valid existing GeoRaster object or an empty GeoRaster object.
Use UPDATE statement to update the desired row in the destination table so that its GeoRaster column contains the copied GeoRaster object.
When you are ready to commit the transaction, use the COMMIT statement.
For an example of copying to replace an existing GeoRaster object and to change its storage format, see the example for the SDO_GEOR.changeFormatCopy procedure in Chapter 6.
Parallel copying and subsetting are supported with the SDO_GEOR_AGGR.mosaicSubset procedure. For parallelized copying and change format copying, See Example 5-22 in Section 5.15.1.
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).
For many of the get functions, there is a corresponding procedure, whose name starts with set, to set, modify, or delete the value of a metadata attribute. For most set procedures, to delete the value of the metadata attribute that the procedure is designed to modify, specify a null value for the attribute. For example, to delete the bin table for a layer of a GeoRaster object, call the SDO_GEOR.setBinTable procedure and specify a null tableName parameter. However, in most cases you cannot specify a null value for other related attributes. For example, you cannot specify a null layerNumber parameter in a call to the SDO_GEOR.setBinTable procedure.
Note the following recommendations, requirements, and restrictions:
Most GeoRaster metadata can also be retrieved and modified using XMLType methods or XML-specific SQL functions, such as extract and updateXML. However, if a GeoRaster get or set subprogram exists for the metadata attribute you want to retrieve or change, use the GeoRaster subprogram instead of an XMLType interface, because the GeoRaster subprograms validate any changes before they are made. If you do call XMLType methods or XML-specific SQL functions to update metadata, you should validate the GeoRaster object before you commit the transaction.
Never directly set the metadata to be null.
Do not directly update the rasterType attribute of a GeoRaster object; instead, call the SDO_GEOR.setRasterType procedure.
To change the raster data table name, use the SDO_GEOR_UTL.renameRDT procedure.
In general, you should not directly update the attributes of a GeoRaster object, except for the spatialExtent attribute.
After updating a GeoRaster object's metadata or cell data (or both) and before you commit a database transaction, you should call the SQL UPDATE statement to update the GeoRaster object (see Section 3.21, "Updating GeoRaster Objects Before Committing").
To query cell (pixel) data of a GeoRaster object for processing and visualization, you can query the raster data for a cell (pixel), a range of cells, or the entire raster of a GeoRaster object:
SDO_GEOR.getCellValue returns cell values of one or multiple layers or bands for a specified location.
SDO_GEOR.evaluateDouble evaluates a direct location based on neighboring cell values by using a specified interpolation method, and returns the raster values (double precision numbers) for the specified bands or layers for that location. (See Section 3.14 for more information.)
SDO_GEOR.getRasterSubset creates a single BLOB object containing all cells of a precise subset of the GeoRaster object (as specified by a rectangular window or a clipping polygon geometry, 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.
SDO_GEOR.reproject not only transforms a whole GeoRaster object from one projected coordinate system to another, but can also include the same capability as SDO_GEOR.getRasterSubset by directly transforming the query result (a single BLOB) into a different coordinate system on-the-fly.
SDO_GEOR.rectify performs reprojection, rectification, or orthorectification on all or part of a georeferenced GeoRaster object based on a query window. The resulting object can be a new GeoRaster object (for persistent storage) or a BLOB (for temporary use).
SDO_GEOR_RA.findCells generates a new GeoRaster object based on the cell values using the GeoRaster Raster Algebra language. (See Section 4.2, "Cell Value-Based Conditional Queries" for more information.)
SDO_GEOR_AGGR.mosaicSubset mosaics a number of GeoRaster objects into one GeoRaster object.
SDO_GEOR_AGGR.getMosaicSubset lets you query a virtual mosaic and returns a mosaicked subset on-the-fly.
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.
There are two types of raster updates: space-based and cell value-based
Space-based raster update allows you update a GeoRaster object's raster data inside a specified window entirely using either a single value or another GeoRaster object.
To update or change the value of raster cells in a specified window to a single value, you can use the SDO_GEOR.changeCellValue procedure. You can call the SDO_GEOR.updateRaster procedure to update a specified pyramid of a specified area, or the overlapping parts of one GeoRaster object, with a specified pyramid and specified bands or layers of another GeoRaster object. Both the SDO_GEOR.changeCellValue and the SDO_GEOR.updateRaster procedures support all pyramid levels, including the original raster data (that is, pyramid level 0).
The SDO_GEOR_AGGR.append procedure can also be used to update an existing image with a new image (see Section 5.14, "Image Appending").
Cell value-based raster update allows you update a GeoRaster object's raster data based on the cell values using the GeoRaster Raster Algebra language.
SDO_GEOR_RA.rasterUpdate selects cells from the specified GeoRaster object based on Boolean strings specified in the conditions parameter, and updates corresponding cell values by calculating expression strings specified in the vals parameter. Both the conditions and vals parameters can be complicated expressions using the raster algebra language. (See Section 4.3, "Cell Value-Based Conditional Updates (Edits)" for more information.)
If statistics are already set in the GeoRaster object when you perform space-based or raster cell value-based updates, the statistics are not removed or updated automatically after you run the raster update procedures. If necessary, you should remove or regenerate the statistics.
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.If you want to change the raster data table name, the attributes of a GeoRaster object, or any other metadata, see the recommendations, requirements, and restrictions noted in Section 3.12, "Querying and Updating GeoRaster Metadata".
After updating a GeoRaster object's metadata or cell data (or both) and before you commit a database transaction, you should call the SQL UPDATE statement to update the GeoRaster object (see Section 3.21, "Updating GeoRaster Objects Before Committing").
GeoRaster objects are grid coverages. The "evaluate" operation of a grid coverage is also called grid interpolation, a method for interpolating cell values at point positions between the cells or within the cells. This operation in GeoRaster is performed by the SDO_GEOR.evaluateDouble function, which evaluates any point in the raster and returns a double number value for that location. You can use any one of the six different interpolation methods (listed in Section 1.7, "Resampling and Interpolation") to do the evaluation. For example, if a georaster object is a DEM layer, you can find out the elevation of a random point location, using the following example:
SELECT SDO_GEOR.evaluateDouble(a.georaster, 0,
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(112.704, 41.917, NULL),
NULL, NULL),
'1',
'interpolationMethod=BILINEAR')
FROM georaster_table a WHERE raster_name='myDEM';
If you call SDO_GEOR.evaluateDouble with 'interpolationMethod=NN', the GeoRaster object is treated as a discrete raster and the preceding is the same as calling SDO_GEOR.getCellValue, which gives you the same value (that is, the cell value) at a different point location inside a cell. In this case, you can directly call SDO_GEOR.getCellValue instead, particularly when you query only the cell values of a single band. Other interpolation methods treat the raster as a continuous surface and may give you different values at different point locations inside a cell.
You can perform a variety of raster and image processing operations on GeoRaster data, including changing the internal raster storage format, subsetting (cropping), scaling, masking, stretching, reprojecting (from one coordinate system to another), rectifying, orthorectifying, mosaicking, appending, and generating pyramids. GeoRaster also supports virtual mosaic. Some relevant subprograms are SDO_GEOR.changeFormatCopy, SDO_GEOR.subset, SDO_GEOR.reproject, SDO_GEOR.rectify, SDO_GEOR.generatePyramid, SDO_GEOR.deletePyramid, SDO_GEOR.scaleCopy, SDO_GEOR.mergeLayers, SDO_GEOR_AGGR.mosaicSubset, SDO_GEOR_AGGR.getMosaicSubset, and SDO_GEOR_AGGR.append. For detailed descriptions, see Chapter 5, "Image Processing and Virtual Mosaic", Chapter 6, "SDO_GEOR Package Reference", and Chapter 8, "SDO_GEOR_AGGR Package Reference".
For raster cell value-based algebraic operations and cartographic modeling and analysis, GeoRaster supports a raster algebra language (PL/SQL and Algebraic Expressions) and related raster operations, including conditional queries (SDO_GEOR_RA.findCells), cell value-based updates or edits (SDO_GEOR_RA.rasterUpdate), mathematical operations (SDO_GEOR_RA.rasterMathOp), and image and raster segmentation (SDO_GEOR_RA.classify). The SDO_GEOR.generateStatistics function supports polygon-based statistics and histogram generation. The following on-the-fly functions support interactive statistical analysis of a GeoRaster object or its layers: SDO_GEOR.generateStatisticsMax, SDO_GEOR.generateStatisticsMean, SDO_GEOR.generateStatisticsMedian, SDO_GEOR.generateStatisticsMin, SDO_GEOR.generateStatisticsMode, and SDO_GEOR.generateStatisticsSTD. For detailed descriptions, see Chapter 4, "Raster Algebra and Analytics" and Chapter 9, "SDO_GEOR_RA Package Reference".
See also the GeoRaster PL/SQL demo files, described in Section 1.18, for examples and explanatory comments.
GeoRaster lets you monitor and report the execution progress of many operations (listed in Section 1.14). The following are the basic steps for reporting the progress of an operation:
Use the SDO_GEOR_UTL.createReportTable procedure to create the report table under the appropriate user's schema. (This must be called once before you can monitor any operations.)
EXECUTE SDO_GEOR_UTL.createReportTable;
In the user session where the operations are to be executed and monitored, perform the following actions:
Use SDO_GEOR_UTL.enableReport to enable the monitoring. (You must call this procedure in order to be able to get the status report later.)
EXECUTE SDO_GEOR_UTL.enableReport;
Optionally, use SDO_GEOR_UTL.setClientID to set the client ID. The client ID is used to identify the user session that executes the operation. If this procedure is not called, the client ID defaults to the SQL session ID. For example:
EXECUTE SDO_GEOR_UTL.setClientID(100);
Optionally, use SDO_GEOR_UTL.setSeqID to set the sequence ID. The sequence ID is used to identify the repeated operations in the same SQL session. If this procedure is not called, the sequence ID defaults to 0. For example:
EXECUTE SDO_GEOR_UTL.setSeqID(1);
Execute the operation to be monitored. For example:
-- Generate pyramid for georid=6. The progress of this generatePyramid call
-- can be monitored by step 3.
DECLARE
gr sdo_georaster;
BEGIN
SELECT georaster INTO gr
FROM georaster_table WHERE georid = 6 FOR UPDATE;
sdo_geor.generatePyramid(gr, 'rLevel=5, resampling=NN');
UPDATE georaster_table SET georaster = gr WHERE georid = 6;
COMMIT;
END;
/
Optionally, repeat steps c and d for each additional operation to be monitored. For example:
EXECUTE SDO_GEOR_UTL.setSeqID(2);
-- Generate pyramid for georid=7. The progress of this generatePyramid call
-- can be monitored by step 3.
DECLARE
gr sdo_georaster;
BEGIN
SELECT georaster INTO gr
FROM georaster_table WHERE georid = 7 FOR UPDATE;
sdo_geor.generatePyramid(gr, 'rLevel=5, resampling=NN');
UPDATE georaster_table SET georaster = gr WHERE georid = 7;
COMMIT;
END;
/
Optionally, use SDO_GEOR_UTL.disableReport to disable the monitoring. If this procedure is not called, the monitoring is automatically stopped when the user session ends.
EXECUTE SDO_GEOR_UTL.disableReport;
From another session under the same user, retrieve the execution status report.
To get the progress of a specific operation identified by client ID and sequence ID, use the SDO_GEOR_UTL.getProgress function. This function returns the progress as a number between 0 and 1 reflecting the percentage of completion. For example, the following query shows that the operation if 55% complete:
SELECT sdo_geor_utl.getProgress(100, 2) progress FROM DUAL;
PROGRESS
--------
0.55
1 row selected.
To get the status report of a specific operation identified by client ID and sequence ID, use the SDO_GEOR_UTL.getStatusReport function. This function returns an array of strings describing the progress and other information about the operation. For example:
-- Check the status of the generatePyramid on georid=6
SELECT sdo_geor_utl.getStatusReport(100, 1) FROM DUAL;
SDO_GEOR_UTL.GETSTATUSREPORT(100,1)
-----------------------------------------------------------------------------
SDO_STRING2_ARRAY('31-OCT-11 02.20.04.854558 PM', 'GeneratePyramid', 'RDT:RDT_1', 'RID:6', '100% complete', 'operation completed')
1 row selected.
-- Check the status of the generatePyramid on georid=7
SELECT sdo_geor_utl.getStatusReport(100, 2) FROM DUAL;
SDO_GEOR_UTL.GETSTATUSREPORT(100,2)
-----------------------------------------------------------------------------
SDO_STRING2_ARRAY('31-OCT-11 02.20.08.854558 PM', 'GeneratePyramid', 'RDT:RDT_1', 'RID:7', '55% complete', 'operation completed')
1 row selected.
To get the status of all the monitored operations, enter the following statement:
SELECT * from the (select sdo_geor_utl.getAllStatusReport() FROM DUAL);
COLUMN_VALUE
------------------------------------------------------------------------------
SDO_STRING2_ARRAY('Client:100', 'Sequence:1', '31-OCT-11 02.20.04.854558 PM', 'GeneratePyramid', 'RDT:RDT_1', 'RID:6', '100% complete', 'operation completed')
SDO_STRING2_ARRAY('Client:100', 'Sequence:2', '31-OCT-11 02.20.08.854558 PM', 'GeneratePyramid', 'RDT:RDT_1', 'RID:7', '55% complete', NULL)
2 rows selected.
If you need to clear or drop the report table, use the SDO_GEOR_UTL.clearReportTable or SDO_GEOR_UTL.dropReportTable procedure, respectively:
EXECUTE SDO_GEOR_UTL.clearReportTable; -- or: EXECUTE SDO_GEOR_UTL.dropReportTable;
You can reduce the storage space requirements for GeoRaster objects by compressing them using 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, SDO_GEOR.subset, and SDO_GEOR_AGGR.mosaicSubset. (There are no separate procedures for compressing and decompressing a GeoRaster object.)
For more information about GeoRaster compression and decompression, see Section 1.11, including information about support for third-party compression solutions in Section 1.11.4.
In addition, when compression is used with GeoRaster objects, some special usage considerations apply:
If a large GeoRaster object is to be compressed and will have full pyramids built on it, it is faster to generate pyramids on the uncompressed GeoRaster object first, then apply compression.
For large scale mosaicking, it is faster to mosaic without applying compression first, then generate pyramids, then apply compression.
In some operations, GeoRaster uses temporary tablespaces to compress and decompress data, so adding temporary tablespaces for GeoRaster users is essential for performance (see Section 3.3, "Adding Temporary Tablespaces for GeoRaster Users").
Parallel compression and decompression are supported with the SDO_GEOR_AGGR.mosaicSubset procedure. Parallel compression and decompression significantly improve performance, which is especially useful for large images. See Example 5-21, "Parallel Compression" in Section 5.15.1.
If you want to store compressed GeoRaster objects, make sure you create a temporary tablespace for the users. For more information, see Section 3.3, "Adding Temporary Tablespaces for GeoRaster Users".
GeoRaster automatically maintains the GeoRaster metadata and the relationship between GeoRaster tables and raster data tables (RDTs). Therefore, for most operations you can use the relevant traditional SQL statement.
To delete a GeoRaster object, delete the row containing the object using the DELETE statement (for example, DELETE FROM geor_table WHERE ...;).
After a GeoRaster object is deleted from a GeoRaster table, all related raster data stored in the RDT is deleted automatically. Never insert or delete any rows directly in a raster data table.
To drop a GeoRaster table, use the DROP statement (for example, DROP geor_table;).
After a GeoRaster table is dropped, all raster data associated with GeoRaster objects in the deleted GeoRaster table is deleted automatically.
To drop an RDT, you must first delete all GeoRaster objects that reference the RDT, after which you can use the DROP statement on the RDT.
If you do not delete all GeoRaster objects that reference the RDT before attempting to drop the RDT, an exception is raised.
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.17).
Use Oracle Fusion Middleware MapViewer or its associated Map Builder utility.
With the GeoRaster viewer tool, you can select a 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. Some of the basic GeoRaster metadata is also displayed.
The GeoRaster viewer tool allows you to display a virtual mosaic defined as one or a list of GeoRaster tables or views.
The GeoRaster viewer tool provides a set of image processing operators for enhanced display of the GeoRaster objects, especially for those whose cell depth is greater than 8 or is a floating-point number. It can also display and apply bitmap masks on the GeoRaster objects if they have bitmap masks.
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.
Visualization applications can leverage the default RGBA and default pyramid level specifications in the GeoRaster objects. You can set up different bands in a multiband image as the default Red, Green, Blue, and Alpha channels by calling SDO_GEOR.setDefaultColorLayer or SDO_GEOR.setDefaultRed, SDO_GEOR.setDefaultGreen, SDO_GEOR.setDefaultBlue, and SDO_GEOR.setDefaultAlpha. For large images, you can call SDO_GEOR.setDefaultPyramidLevel to set up the best resolution (pyramid) level of an image for initial display in the applications. For example, for a complete overview of a whole image, it is best to set the top pyramid level as the default pyramid level.
To load and export imagery or raster data, always consider third-party ETL tools (see the note in Section 1.17)
If you use features in GeoRaster 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.17.
Before you commit a database transaction that inserts, updates, reformats, compresses, decompresses, or deletes GeoRaster cell data or metadata, you should use the SQL UPDATE statement to 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;
/
The SDO_GEOR.createTemplate and SDO_GEOR.getRasterBlockLocator subprograms enable you to develop GeoRaster applications, such as ETL tools and image processing systems that work with GeoRaster objects, by reading and writing GeoRaster metadata and binary raster data without dealing directly with the Oracle XMLType, the GeoRaster XML schema, and Oracle BLOBs.
After you create a new GeoRaster object (explained in Section 3.2), you can use the SDO_GEOR.createTemplate function to populate the metadata of the GeoRaster object with basic information, such as raster type, dimension sizes, ultCoordinates, cell depth, interleaving type, blocking and block size, pyramid resampling method and reducing level, and compression method and quality. This function can optionally populate the raster data table with the correct number of rows and row data consisting of raster blocks containing empty BLOBs.
The XML metadata generated by the SDO_GEOR.createTemplate function conforms to the GeoRaster metadata schema. You can then use other GeoRaster subprogams to query or update the metadata (see Section 3.12).
You can use the SDO_GEOR.getRasterBlockLocator procedure to get the raster block locator by specifying the pyramid level and block number. If you have the raster block locator, you can then use the OCI or Java JDBC LOB interfaces to read and write the binary raster data. (The SDO_GEOR.getRasterBlockLocator procedure does not itself read or process LOB data.) To use this approach, you must understand the physical storage of the raster data (explained in Section 1.4), and you must compress and decompress the data as necessary before reading from or writing to the BLOB.
Oracle Workspace Manager provides a versioning capability for the raster blocks of a GeoRaster object. Oracle Label Security supports GeoRaster objects with enhanced security at the row level of raster blocks.
To use GeoRaster with Oracle Workspace Manager or Oracle Label Security, you should create a raster data table (RDT) as a relational table for the GeoRaster objects (see Example 3-3, "Creating a Raster Data Table (Relational) Using SecureFiles"). You do not need to define an object view of SDO_RASTER type on the base relational RDT.
With Workspace Manager, you can conveniently manage changes to the raster data by saving different raster data versions and making modifications in different workspaces. To use GeoRaster with Workspace Manager, you must use relational raster data tables for raster storage and version-enable these relational raster data tables. For example (general format):
EXECUTE DBMS_WM.EnableVersioning (<rdt_relational_table>, 'VIEW_WO_OVERWRITE');
Note:
You can version-enable only raster data tables. Do not version-enable any GeoRaster tables, where GeoRaster objects are stored, and do not perform any operations that will require a GeoRaster table to be modified while you are in a workspace.After you version-enable a relational RDT, you can use the subprograms in the DBMS_WM package to manage changes to the raster data. If you need to directly modify a raster block, call the DBMS_WM.copyForUpdate procedure before the operation, as shown in the following example:
declare
geor sdo_georaster;
cond varchar2(1000);
lb blob;
r1 raw(1024);
amt number;
begin
r1 := utl_raw.copies(utl_raw.cast_to_raw('0'),1024);
select georaster into geor from georaster_table where georid=1;
cond := 'rasterId=' || geor.rasterId || ' AND pyramidLevel=0 AND ' ||
' bandBlockNumber=0 AND rowBlockNumber=0 AND columnBlockNumber=0';
dbms_wm.copyForUpdate(geor.rasterDataTable, cond);
sdo_geor.getRasterBlockLocator(geor, 0, 0, 0, 0, lb, null, 'TRUE');
amt := 1024;
dbms_lob.write(lb, amt, 1, r1);
end;
/
However, if you modify raster data using GeoRaster subprograms, you do not need to call the DBMS_WM.copyForUpdate procedure beforehand.
For information about Workspace Manager, see Oracle Database Workspace Manager Developer's Guide.
Oracle Label Security provides row-level access control for sensitive data based on a user's level of security clearance. To use GeoRaster with Label Security, follow these basic steps:
Create the GeoRaster table and relational RDT or RDTs.
Create an Oracle Label Security policy and define the label components.
Create labeling functions for the GeoRaster table and the relational RDT or RDTs.
The labels for rows in a GeoRaster table should be generated according to the application's requirements. Use the same label for both the row that stores a GeoRaster object and for the GeoRaster object's raster rows in the associated RDT; otherwise, the GeoRaster objects might be invalid or have an inconsistent status.
The following example creates the labeling function for a relational RDT:
CREATE OR REPLACE FUNCTION gen_rdt_label(rdt_name varchar2, rid number)
RETURN LBACSYS.LBAC_LABEL
AS
tabname varchar2(80);
schema varchar2(32);
grcol varchar2(1024);
colname varchar2(30);
label NUMBER;
BEGIN
EXECUTE IMMEDIATE
'SELECT v.owner, v.table_name, v.column_name grcol, p.column_name ' ||
' FROM all_sdo_geor_sysdata v, all_sa_policies p, all_sa_table_policies t '
|| ' WHERE v.rdt_table_name=:1 AND v.raster_id=:2 AND ' ||
' v.owner=t.schema_name AND v.table_name=t.table_name AND ' ||
' p.policy_name=t.policy_name '
INTO schema, tabname, grcol, colname
USING upper(rdt_name), rid;
EXECUTE IMMEDIATE
'SELECT t.' || colname ||
' FROM ' || schema || '.' || tabname || ' t ' ||
' WHERE t.' || grcol || '.rasterdatatable=:1 AND ' ||
' t.' || grcol || '.rasterid=:2'
INTO label
USING upper(rdt_name), rid;
RETURN LBACSYS.LBAC_LABEL.NEW_LBAC_LABEL(label);
END;
/
Apply the Label Security policy to a GeoRaster table and its associated RDT or RDTs.
The following example (general format) applies a Label Security policy to an RDT using the labeling function example from the preceding step.
BEGIN
SA_POLICY_ADMIN.REMOVE_TABLE_POLICY(<policy_name>,<schema_name>,<rdt_relational_table>);
SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
POLICY_NAME => <policy_name>,
SCHEMA_NAME => <schema_name>,
TABLE_NAME => <rdt_relational_table>,
TABLE_OPTIONS => 'READ_CONTROL,WRITE_CONTROL,CHECK_CONTROL',
LABEL_FUNCTION => '<schema_name>.gen_rdt_label(<rdt_relational_table>,:new.rasterid)',
PREDICATE => NULL);
END;
/
Create and authorize users, and complete other administrative tasks related to Label Security.
You can load GeoRaster data before or after applying the policy to the tables.
The ALL_SDO_GEOR_SYSDATA view (described in Section 2.4) contains system data for all GeoRaster objects accessible by the current user, and accessibility in this case is determined by the user's privileges as defined in the context of discretionary access control (DAC).
After the label for a GeoRaster table row is updated, ensure that the related data labels in the RDT are updated, so that the labels are synchronized.
For information about Label Security, see Oracle Label Security Administrator's Guide.
After delete or rollback operations, unused space allocated to a raster data table might not be promptly returned to the underlying tablespace. This could result in wasted tablespace area, and it can be a significant issue if the amount of raster data is large. If the raster data table is created using BasicFiles LOBs in an automatic segment space management tablespace, you can explicitly shrink the rasterBlock LOB segment or the raster data table by altering the raster data table, as shown in Example 3-5 and Example 3-6.
Example 3-5 Shrinking a BasicFile RasterBlock LOB Segment
ALTER TABLE city_images_rdt MODIFY LOB (rasterBlock) (SHRINK SPACE);
Example 3-6 Shrinking a Raster Data Table
ALTER TABLE city_images_rdt ENABLE ROW MOVEMENT; ALTER TABLE city_images_rdt SHRINK SPACE CASCADE;
If you are using SecureFiles, or if you are using BasicFiles allocated in a manual segment space management tablespace, you cannot reclaim unused space using the ALTER TABLE statements as shown in the preceding examples. Instead, you should create some working (for temporary use) raster data tables and try to put any intermittent results in these RDTs, and then drop these working RDTs after they are no longer needed.
Although GeoRaster provides internal database mechanism to prevent the creation of invalid GeoRaster objects and system data, sometimes such GeoRaster objects and system data might exist in the database, especially after an upgrade from a previous release, or after some user errors in operations on GeoRaster system data. Examples of such invalid objects and system data include the following:
An entry in the GeoRaster system data views (xxx_SDO_GEOR_SYSDATA, described in Section 2.4) refers to a nonexistent GeoRaster table or column.
Two or more GeoRaster objects have the same pair of RDT name and raster ID values.
Some GeoRaster objects, tables, columns, or RDTs not registered.
An RDT name is not unique.
A GeoRaster object is non-empty or nonblank, but an associated RDT does not exist.
After a database upgrade, you should call the SDO_GEOR_ADMIN.isUpgradeNeeded function to check for any invalid GeoRaster objects and invalid system data for the current version. If there are any errors or invalid data, call the SDO_GEOR_ADMIN.upgradeGeoRaster function to have the problems automatically corrected. If you connect as user MDSYS, the SDO_GEOR_ADMIN.upgradeGeoRaster function upgrades all GeoRaster objects in the database; otherwise, it upgrades only GeoRaster objects in the schema of the current user. (See the reference and usage information about SDO_GEOR_ADMIN.upgradeGeoRaster in Chapter 7.)
For regular maintenance due to possible user errors, several functions and procedures will be helpful in checking for and correcting invalid GeoRaster objects and system data entries:
To check for errors, call SDO_GEOR_ADMIN.checkSysdataEntries and SDO_GEOR_ADMIN.listUnregisteredRDT.
To check for dangling raster data, call SDO_GEOR_ADMIN.listDanglingRasterData.
To correct all invalid system data entries, call SDO_GEOR_ADMIN.maintainSysdataEntries.
To create correct DML triggers for all GeoRaster columns, call SDO_GEOR_ADMIN.registerGeoRasterColumns.
To register all existing GeoRaster objects in the sysdata table, call SDO_GEOR_ADMIN.registerGeoRasterObjects.
See the reference and usage information about these procedures and functions in Chapter 7.
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 do not 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; however, you should use the SDO_GEOR.validateGeoRaster function to check the validity of imported GeoRaster objects before you perform any operations on these 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.26.1.
Perform the data transfer, as explained in Section 3.26.2.
For a successful import of GeoRaster data into a target database, 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.
For example, you might plan to import a GeoRaster object by creating its raster data table (RDT) in the target schema, but an existing RDT in the target schema might already have the same name. In this case, you should use the SDO_GEOR_ADMIN.listRDT or SDO_GEOR_ADMIN.isRDTNameUnique function to check both source database and target database to see if there are RDT name conflicts; and if there are any conflicts, use the SDO_GEOR_UTL.renameRDT procedure to rename the RDT to a different name in the target database to solve the conflicts before you import the GeoRaster objects.
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.
For example, if you import RDT data by appending to an existing RDT in the target database, this conflict might occur. In this case, before importing the data into the target database, use the SDO_GEOR_ADMIN.listGeoRasterObjects function to list all GeoRaster objects defined in the target schema, and make sure that there are no conflicts in the combination of RDT name and raster ID between existing GeoRaster data and the GeoRaster data to be imported. If there are any conflicts, change the raster ID of the GeoRaster object in the target schema to resolve the conflicts; otherwise, those GeoRaster objects with conflicts in the dump file will get rejected when you perform import process.
If you need to check the raster data table (RDT) name and raster ID (RID) information in the dump file, you have the following options: check the information in the source database; request the information from the provider of the dump file; load the dump file into a separate test database and check the information there; or (if you cannot use a separate database for testing) load the dump file into a test schema in the current database and check the information. To load the dump file into a test schema in the current database and check the information, follow these steps:
Create a test schema in the target database.
Load all GeoRaster tables into this test schema from the dump file, using the Data Pump Import utility with the CONTENT = METADATA_ONLY parameter.
Connect to the database as the MDSYS user, and disable all DML triggers on the GeoRaster tables that were loaded in the preceding step.
Load the data into the GeoRaster tables, using the Data Pump Import utility with the CONTENT = DATA_ONLY parameter.
Retrieve the RDT/RID (raster data table name and raster ID) pairs directly from the GeoRaster tables in the test schema.
After you resolve conflicts, you should ensure the integrity of GeoRaster metadata and data (see Section 3.25). You should also validate any fixed GeoRaster objects before performing a commit or any other operation.
For general information about resolving conflicts during import operations, see the description of the TABLE_EXISTS_ACTION parameter in the Data Pump Import chapter of Oracle Database Utilities.
When you export GeoRaster data from one database and import it into another, the GeoRaster database management system ensures that the necessary DML triggers and system data entries are automatically generated after the GeoRaster tables and objects are imported into the target database. Therefore, all GeoRaster internal DML triggers should be excluded in expdp and impdp operations; otherwise, some impdp errors such as the following will be raised, even though the errors can be safely ignored:
ORA-39083: Object type TRIGGER failed to create with error: ORA-13391: GeoRaster reserved names cannot be used to create regular triggers
To export GeoRaster data, do as you would for other types of data, but exclude the GeoRaster internal DML triggers (whose names start with GRDMLTR_). For example:
expdp scott schemas=scott directory=dump_dir dumpfile=exp.dmp parfile=exclude.par
Enter password: password
where the exclude.par file contains the following:
exclude=trigger:"like 'GRDMLTR_%'"
To import GeoRaster data, do as you would for other types of data, but exclude the GeoRaster internal DML triggers (whose names start with GRDMLTR_) if you did not exclude them in the export operation. For example:
Ensure that no conflicts exist between the GeoRaster data to be imported and the existing GeoRaster data in the target database, as explained in Section 3.26.1.
If any conflicts are not resolved, some exceptions will be raised and only non-conflicted GeoRaster data will be imported into the target database.
Import GeoRaster data as you would for other types of data, but exclude the GeoRaster internal DML triggers (whose names start with GRDMLTR_) if you did not exclude them in the export operation. For example:
impdp scott schemas=scott directory=dump_dir dumpfile=exp.dmp parfile=exclude.par
Enter password: password
where the exclude.par file contains the following:
exclude=trigger:"like 'GRDMLTR_%'"
You can use the Oracle Database transportable tablespaces feature with GeoRaster data.
If a tablespace to be transported contains any spatial indexes on the GeoRaster tables or raster data tables (RDTs), you may have to take some preparatory steps. See the Usage Notes for the SDO_UTIL.PREPARE_FOR_TTS and SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS procedures in Oracle Spatial and Graph Developer's Guide for more information about using the transportable tablespace feature with spatial data.
For a successful import of GeoRaster data into a target database, there must be no conflicts in the target schema's GeoRaster system data. Before you transport the tablespace to another database or schema, it is recommended (but not required) that you check for and resolve such conflicts by following the procedure described in Section 3.26.1. For this reason, you should design GeoRaster tables and RDT tables so as to avoid such foreseeable conflicts before you use such transportable tablespaces in the source database.
Regardless or whether a transported tablespace has any spatial indexes, after transporting the tablespace that contains GeoRaster objects, do the following:
Call the SDO_GEOR_ADMIN.registerGeoRasterObjects procedure (described in Chapter 7) to register all GeoRaster objects in the current schema or new database.
Before you use the transported GeoRaster data, perform the "regular maintenance" operations described in Section 3.25, to maintain GeoRaster objects and system data and to ensure all GeoRaster objects are correctly transported and properly registered.
If you find any conflicts, call the SDO_GEOR_UTL.renameRDT or SDO_GEOR_UTL.makeRDTNamesUnique procedure to solve such conflicts, and validate again.
For detailed information about transportable tablespaces and transporting tablespaces to other databases, see Oracle Database Administrator's Guide.