The MDSYS.SDO_GEOR_UTL package contains subprograms (functions and procedures) for utility operations related to GeoRaster. This chapter presents reference information, with one or more examples, for each subprogram.
SDO_GEOR_UTL.calcOptimizedBlockSize(
dimensionSize IN SDO_NUMBER_ARRAY,
blockSize IN OUT SDO_NUMBER_ARRAY,
pyramidLevel IN number default 0);
Calculates an optimal blockSize value that will use less padding space in the GeoRaster object storage, based on the GeoRaster dimension sizes and the user-specified block size values.
Dimension size array of the GeoRaster object.
Block size array, which holds the user-specified block size values and into which the procedure outputs the adjusted optimal block size values.
Maximum pyramid level. The default value is 0.
This procedure enables you to give desired block size values (which may not be optimal), automatically adjust them, and then determine the block size array values for a specified GeoRaster dimension size array that will be optimal for reducing the amount of padding space in GeoRaster object storage. The adjustment is always made around the user-specified values. For more information, see the explanations of the blocking and blockSize keywords in Table 1-1, "storageParam Keywords for Raster Data" in Section 1.4.1.
An exception is generated if the input dimensionSize or blockSize parameter contains any invalid values.
The following example calculates and displays an optimal block size value, based on a specified dimension size array of (12371,11261,13) and a specified block size array of (512,512,5). Note that the optimal rowBlockSize value returned is 538 as opposed to the original value of 512, and the optimal bandBlockSize value returned is 1 as opposed to the original value of 5.
DECLARE
dimensionSize sdo_number_array;
blockSize sdo_number_array;
BEGIN
dimensionSize:=sdo_number_array(12371,11261,13);
blockSize:=sdo_number_array(512,512,5);
sdo_geor_utl.calcOptimizedBlockSize(dimensionSize,blockSize);
dbms_output.put_line('Optimized rowBlockSize = '||blockSize(1));
dbms_output.put_line('Optimized colBlockSize = '||blockSize(2));
dbms_output.put_line('Optimized bandBlockSize = '||blockSize(3));
END;
/
Optimized rowBlockSize = 538
Optimized colBlockSize = 512
Optimized bandBlockSize = 1
SDO_GEOR_UTL.calcRasterNominalSize(
geor IN SDO_GEORASTER,
padding IN VARCHAR2 DEFAULT 'TRUE',
pyramid IN VARCHAR2 DEFAULT 'TRUE',
bitmapMask IN VARCHAR2 DEFAULT 'TRUE'
) RETURN NUMBER;
Returns the total raster block length (in bytes) of a GeoRaster object, as if it were not compressed and did not contain any empty raster blocks.
GeoRaster object.
The string TRUE (the default) causes padding in the raster blocks to be considered; the string FALSE causes padding in the raster blocks not to be considered.
The string TRUE (the default) causes the size of any pyramids to be considered; the string FALSE causes the size of any pyramids not to be considered.
The string TRUE (the default) causes any associated bitmap masks to be considered; the string FALSE causes any associated bitmap masks not to be considered. For an explanation of bitmap masks, see Section 1.8.
This function does not consider any LOB storage overhead, so the result is only an approximation of the real storage requirements for the GeoRaster object.
The result of this function will be greater than or equal to the result of the SDO_GEOR_UTL.calcRasterStorageSize function on the same GeoRaster object. If this function returns a larger value than the SDO_GEOR_UTL.calcRasterStorageSize function on the same GeoRaster object, the difference in the values reflects the space saved by the use of compression or empty raster blocks, or both.
For information about GeoRaster compression, see Section 1.10.
The following example calculates the nominal raster size (in bytes) of a GeoRaster object, according to its current blocking scheme. The returned size includes (by default) any padding in the raster blocks, any associated bitmap masks, and any pyramids.
SELECT SDO_GEOR_UTL.calcRasterNominalSize(georaster) nsize FROM georaster_table
WHERE georid=1;
NSIZE
----------
289150
The function calculates the actual length of all raster blocks of a GeoRaster object. It does not consider any LOB storage overhead, so the result is only an approximation of the real storage size of the GeoRaster object. In essence, this function executes the following statement:
EXECUTE IMMEDIATE 'SELECT SUM(DBMS_LOB.getLength(rasterBlock)) FROM ' || geor.rasterDataTable || ' WHERE rasterId=' || geor.rasterId;
The result of this function will be less than or equal to the result of the SDO_GEOR_UTL.calcRasterNominalSize function on the same GeoRaster object. If this function returns a smaller value than the SDO_GEOR_UTL.calcRasterNominalSize function on the same GeoRaster object, the difference in the values reflects the space saved by the use of compression or empty raster blocks, or both.
The following example calculates ratio (as a decimal fraction) of the actual size to the nominal size of a specified GeoRaster object. In this example, the actual size is about one-twentieth (1/20) of the nominal size.
SELECT SDO_GEOR_UTL.calcRasterStorageSize(georaster)/
SDO_GEOR_UTL.calcRasterNominalSize(georaster) ratio
FROM georaster_table WHERE georid=1;
RATIO
----------
.056198816
Creates the required standard GeoRaster data manipulation language (DML) trigger on a GeoRaster column in a GeoRaster table, so that the appropriate operations are performed when its associated trigger is fired.
Name of a GeoRaster table (the table containing rows with at least one GeoRaster object column).
Name of a column of type SDO_GEORASTER in the GeoRaster table.
As explained in Section 3.1.3, 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. In this case, you must call the SDO_GEOR_UTL.createDMLTrigger procedure to create the DML trigger on each added GeoRaster column.
Otherwise, you usually do not need to call this procedure, although but it is still useful for re-creating the DML trigger in some scenarios, such as a database upgrade or a data migration.
SDO_GEOR_UTL.fillEmptyBlocks(
georaster IN OUT SDO_GEORASTER,
bgValues IN SDO_NUMBER_ARRAY DEFAULT NULL);
GeoRaster object in which to fill empty blocks.
Background values for filling empty raster blocks. The number of elements in the SDO_NUMBER_ARRAY object must be either one (same filling value used for all bands) or the band dimension size (a different filling value for each band, respectively). For example, SDO_NUMBER_ARRAY(1,5,10) fills the first band with 1, the second band with 5, and the third band with 10. If this parameter is null, then bgValues will be 0 (zero).
If georaster is null, this procedure performs no operation.
If pyramid data exists for georaster, the pyramid is regenerated based on pyramid information stored in the metadata.
The following example fills all empty blocks with background values (255,0,0).
DECLARE geor SDO_GEORASTER; BEGIN SELECT georaster INTO geor FROM georaster_table WHERE georid = 3 FOR UPDATE; SDO_GEOR_UTL.fillEmptyBlocks(geor, SDO_NUMBER_ARRAY(255,0,0)); UPDATE georaster_table SET georaster = geor WHERE georid = 3; COMMIT; END; /
Renames some existing registered raster data tables that do not have unique names so that all raster data table names are unique within the database, and updates the GeoRaster system data and all affected GeoRaster objects to reflect the new names.
If one or more registered raster data tables have the same name (under different schemas), you can use this procedure or the SDO_GEOR_UTL.renameRDT procedure, or both, to eliminate the duplication.
Run this procedure when you are connected to the database with the DBA role.
This procedure is not transactional, and the result cannot be rolled back.
The following example automatically renames some existing registered raster data tables that do not have unique names so that all registered raster data table names are unique within the database, and it updates the GeoRaster system data and all affected GeoRaster objects to reflect the new names.
EXECUTE sdo_geor_utl.makeRDTNamesUnique;
Renames one or more existing registered raster data tables owned by the current user, and updates the GeoRaster system data and all affected GeoRaster objects to reflect the new names.
Name of the registered raster data table or tables to be renamed. For multiple tables, use a comma-delimited list.
New names to be assigned to the raster data table or tables that are specified in oldRDTs. For multiple tables, use a comma-delimited list with an order exactly reflecting the names in oldRDTs. If this parameter is null, GeoRaster assigns a unique new name to each input raster data table.
If one or more registered raster data tables owned by different users have the same name, you can use this procedure or the SDO_GEOR_UTL.makeRDTNamesUnique procedure, or both, to eliminate the duplication.
Before using this procedure, you must connect to the database as the owner of the raster data table or tables. You cannot use this procedure to rename a raster data table owned by another user.
If any table in oldRDTs is not included in the GeoRaster system data, it is ignored.
If any table in newRDTs conflicts with a name in the GeoRaster system data or with the name of another object owned by the current user, an exception is raised.
This procedure is not transactional, and the result cannot be rolled back.