Evaluating Oracle GeoRaster

Please make sure that Oracle 10g and Spatial have already been installed (see InstallingOracleSpatial).

The GeoRaster feature of oracle spatial provides facilities to store, index, query, analyse and deliver GeoRaster data. However, currently only image data files are supported : "TIFF","JPEG", "BMP", "PNG". We tried to export RAW Data from HDF4 files by writing our own loaders. So far,they were unsuccessful. However, tests with image raster files (for e.g. TIFF) were successfully implemented. A possible alternative is to convert HDF to TIFF format but there may be loss of precision.

The following is a detailed description of our experiences so far with GeoRaster objects using image files.In this case, we are using TIFF files.

Preparing the database

• Creating a table that contains at least a column of type MDSYS.SDO_GEORASTER. A georaster table is a table which has a column of SDO_GEORASTER object type.

create table georaster_table 
georid number primary key, type varchar2(32),georaster  mdsys.sdo_georaster);
 

• Creating a DML trigger on the above georaster table by calling sdo_geor_utl.createDMLtrigger('tableName','georasterColumn'). This is a MUST for all georaster tables. They are used to manage the georaster sysdata table.

call sdo_geor_utl.createDMLTrigger('georaster_table', 'georaster');
 

• Creating one or more tables of type MDSYS.SDO_RASTER, with primary key constraints enforced on the five attributes of MDSYS.SDO_RASTER. These tables are called "raster data tables",or RDT's for short. They are used to store cell data of GeoRaster objects. This step is not a MUST requirement. If the RDT table doesn't exist, the GeoRaster procedures or functions will generate it automatically whenever needed. But for huge GeoRaster objects, some tuning and setup on those tables can improve the scalability and performance significantly. In those cases, it's better for users to create the RDTs.The primary key must be added to the RDT if user creates it.

create table rdt_1 of mdsys.sdo_raster (primary key (rasterId, pyramidLevel, bandBlockNumber,
rowBlockNumber, columnBlockNumber))lob(rasterblock) store as (nocache nologging);
 

• Inserting "initial" entries into the table from first part, corresponding to each image that you plan to load. You must specify the name of the RDT (and optionally, the desired rasterid) in which you wish to store the georaster object. Querying the database to discover the rasterid assigned to the initial entry that you have created. This step only applies if you used the SDO_GEORASTER.init('rdtName') function to initialize the georaster object.

• Querying the database to discover the rasterid assigned to the initial entry that you have created. This step only applies if you used the SDO_GEORASTER.init('rdtName') function to initialize the georaster object.

• Before storing image files, we MUST have the correct images ready somewhere. Then you replace img1.tif and img2.tif with their correct paths in the script below.Individual files or a whole directory (external objects) must be granted reading permissions before calling importFrom. This is due to Oracle RDBMS server security concern.

connect system/manager; 
call dbms_java.grant_permission ('PUBLIC','SYS:java.io.FilePermission',
'/sdo/demos/georaster/data/img1.tif', 'read' );
call dbms_java.grant_permission('HERMAN','SYS:java.io.FilePermission',
'/sdo/demos/georaster/data/img2.tif', 'read' );
call dbms_java.grant_permission('MDSYS','SYS:java.io.FilePermission', 
'/sdo/demos/georaster/data/img2.tif', 'read' );
 

Using the GeoRaster Tools provided by Oracle

The client library georaster_tools.jar also defines three individual clients: GeoRasterLoader, GeoRasterExporter, and GeoRasterViewer. These tools are stand-alone client-side java executables.They simplify the functionalities by providing a GUI. To make proper use of these tools, the JVM and appropriate jar files must be installed. Please make sure that you are using Java Virtual Machine versions 1.3.1 or higher.Make sure that the CLASSPATH contains a path to these libraries:

classes12.jar, jai_codec.jar, jai_core.jar, xdb.jar, xmlcomp.jar, xmlparserv2.jar, 
xschema.jar, jewt4.jar, sdoapi.jar, sdoutl.jar
 

These libraries are standard in the Oracle environment, and can be found at:

${ORACLE_HOME}/ord/jlib/jai_codec.jar
${ORACLE_HOME}/ord/jlib/jai_core.jar
${ORACLE_HOME}/jdbc/lib/classes12.jar
${ORACLE_HOME}/rdbms/jlib/xdb.jar
${ORACLE_HOME}/lib/xmlparserv2.jar
${ORACLE_HOME}/lib/xmlcomp.jar
${ORACLE_HOME}/lib/xschema.jar
${ORACLE_HOME}/jlib/jewt4.jar
${ORACLE_HOME}/md/lib/sdoapi.jar
${ORACLE_HOME}/md/lib/sdoutl.jar

where ${ORACLE_HOME} specifies the complete path to the top-level directory that holds 
the Oracle environment. Next, add the client library to your CLASSPATH. The client library is called 
georaster_tools.jar. 
 

Brief description of each tool.

• The GeoRasterLoader.The GeoRasterLoader takes an image file (and an optional world file, if specified) and loads it into the database as a GeoRaster object. Currently suported image formats are: "TIFF", "JPEG", "GIF", "BMP", and "PNG". The world file must be in ESRI format.To run GeoRasterLoader, use the following command line invocation format:

java GeoRasterLoader "hostname" "instanceName" "portNum" "dbuser" "dbpassword" "clienttype" "rowPref" 
"trueIfClientSide" "imageTableName" "georColName" "storageParams" "pathToImageFile1,rasterid1,
rdtName1[,worldFileName1]" "pathToImageFile2,rasterid2, rdtName2 ,worldFileName2]" 
"pathToImageFile3,rasterid3,rdtName3" ... "pathToImageFileN,rasteridN,rdtNameN"
 

The parameters for ImageFile2 through ImageFileN are optional, used if you want to make just one database connection to load several georaster objects.

*Specified only when tool is run from client side. For server side execution,
this parameter should be specified as "". 
e.g.
java -cp $JORACLE GeoRasterLoader marlow.nerc-essc.ac.uk test 1521 scott tiger thin 32 T images image    
"blocking=true,blocksize=(512,512,1)" "/users/ads/ccd/galaxy.tif,1,rdt1"
 

• The GeoRasterViewer.The GeoRasterViewer takes georaster objects that have been loaded into the database and displays them using a Java viewer. The GeoRasterViewer can be invoked in several ways:

java -cp $JORACLE GeoRasterViewer
or
java -cp $JORACLE GeoRasterViewer 192.171.166.118 test 1521 scott tiger thin 32 T 
or
java GeoRasterViewer "hostname" "instanceName" "portNum" "dbuser" "dbpassword" "clienttype" 
"rowPref" "trueIfClientSide" "imageTableName" "georColName" "rasterid1, rdtName1, pyramidLevel1" 
"rasterid2, rdtName2, pyramidLevel2" ... "rasteridN, rdtNameN, pyramidLevelN"
 

Alternatively, the table, column, rasterid, and rdt can be specified through the GeoRasterViewer's GUI, rather than from the command line.

• The GeoRaster Exporter.The GeoRasterExporter takes a georaster object that has been loaded into the database and exports it to a file according to the specified image file format.

java GeoRasterExporter "hostname" "instanceName" "portNum" "dbuser" "dbpassword" "clienttype" 
"rowPref" "clienttype" "imageTableName" "georColName" "rasterid,rdtName" "exportFormat" "outputFilePath"    
["storageParams" "worldFileFormat" "outputWorldFilePath"]
 

For all other parameters, see definitions in GeoRasterLoader instructions above

Using SQL Scripts

• Calling importFrom to import image files into GeoRaster objects

declare 
  geor MDSYS.SDO_GEORASTER;
begin

  -- initialize empty georaster objects to which the external images
  -- are to be imported
  delete from georaster_table where georid = 1 or georid = 2;
  insert into georaster_table values( 1, 'TIFF', mdsys.sdo_geor.init('rdt_1', 1) );
  insert into georaster_table values( 2, 'TIFF', mdsys.sdo_geor.init('rdt_1', 2) );
  --
  -- import the first TIFF image
  --
  select georaster into geor from georaster_table where georid = 1 for update;
  mdsys.sdo_geor.importFrom(geor, '', 'TIFF', 'file','/sdo/demos/georaster/data/img1.tif');
  update georaster_table set georaster = geor where georid = 1;
  commit;
  --
  -- import another TIFF image
  --
  select georaster into geor from georaster_table where georid = 2 for update;
  mdsys.sdo_geor.importFrom(geor, '', 'TIFF','file', '/sdo/demos/georaster/data/img2.tif');
  update georaster_table set georaster = geor where georid = 2;
  commit;
end;
/
show errors;
  

• Revoking reading permissions on external image files for security

connect system/manager;
call dbms_java.revoke_permission('PUBLIC', 'SYS:java.io.FilePermission',
'/sdo/demos/georaster/data/img1.tif', 'read' );
call dbms_java.revoke_permission('HERMAN','SYS:java.io.FilePermission',
'/sdo/demos/georaster/data/img2.tif', 'read' );
call dbms_java.revoke_permission('MDSYS','SYS:java.io.FilePermission',
'/sdo/demos/georaster/data/img2.tif', 'read' );
  

• Some quick checks to see if the data is loaded correctly

connect herman/vampire;
-- Displaying georaster metadata
column georaster format a70 heading "The GeoRaster object"
select georid, georaster from georaster_table where georid = 1 or georid = 2 order by georid;

-- Displaying each cell block of the georaster objects
column BLOCKMBR format a40 heading "    BLOCK MBR"
select rdt.rasterid, rdt.rowBLockNumber rowblockNum,rdt.columnBlockNumber colBlockNum,    
rdt.blockMBR blockmbr,dbms_lob.getLength(rdt.rasterBlock) cellLength
from rdt_1 rdt, georaster_table grt
where rdt.rasterid = grt.georaster.rasterid
order by rdt.rasterid, rdt.rowblocknumber asc;
  

• Validating GeoRaster objects. The validateGeoRaster function is used to validate a column of GeoRaster objects.If the GeoRaster object is not valid,an Oracle error code is returned. Check the documentation for detailed explanation for each error code

column isvalid format a10
select t.georid,mdsys.sdo_geor.validategeoraster(t.georaster) isvalid
from georaster_table t order by georid;
  

• GeoRaster objects can also be validated against the GeoRaster XML schema. If any GeoRaster object is not valid and the error code is 13454, the metadata is not valid against the xml schema. Then call function schemaValidate to find out detailed errors and their locations in the GeoRaster metadata document

select t.georid,mdsys.sdo_geor.schemavalidate(t.georaster)
from georaster_table t 
where t.georid = 1;
  

• Querying GeoRaster Object Metadata and Ancillary Data.The whole metadata document can be displayed as an XML document

column georid heading "georId"
column meta format a100 heading "The Metadata of the GeoRaster object"
select a.georaster.metadata meta
from georaster_table a where georid = 4;
  

• Querying the version of the GeoRaster object

column Version format a20
select georid, sdo_geor.getVersion(georaster) "Version"
from georaster_table where georid = 4;
  

• Querying the ID of the GeoRaster object

column ID format a10 word_wrapped
select georid,sdo_geor.getID(georaster) ID 
from georaster_table where georid = 4;
  

• Querying if the GeoRaster object is blank. Printing the cell value if it is blank.

column isblank format a10 
select georid,sdo_geor.isBlank(georaster) "isBlank",
sdo_geor.getBlankCellValue(georaster) "blankCellValue"
from georaster_table
order by georid;
  

• Querying the default color layers of a true color GeoRaster object

column defaultColorLayers format a30 heading "Default RGB Layers"
select georid,sdo_geor.getDefaultColorLayer(georaster) defaultColorLayers
from georaster_table where georid = 4;
   
select georid,sdo_geor.getDefaultRed(georaster) "defaultRedLayer",
sdo_geor.getDefaultGreen(georaster) "defaultGreenLayer",sdo_geor.getDefaultBlue(georaster)       
"defaultBlueLayer"
from georaster_table where georid = 4;
  

• Querying the total number of spatial and band dimensions and their sizes which tell the size of the GeoRaster object

column spatialDimSizes format a30 heading "rowSize and columnSize"
select georid,sdo_geor.getSpatialDimNumber(georaster) "totalSpatialDimNumber",
sdo_geor.getSpatialDimSizes(georaster) spatialDimSizes, sdo_geor.getBandDimSize(georaster) "bandSize"
from georaster_table
order by georid;
  

• Querying cell depth

select georid,sdo_geor.getCellDepth(georaster) "cellDepth"
from georaster_table
order by georid;
  

• Querying the Upper-Left-Top point coordinate as (row, col) if one layer or (row, column, band) if multi-layer

column ULTCoordinate format a30
select georid,sdo_geor.getULTCoordinate(georaster) "ULTCoordinate"
from georaster_table
order by georid;
  

• Querying interleaving type

column InterleavingType format a30
select georid,sdo_geor.getInterleavingType(georaster) "InterLeavingType"
from georaster_table
order by georid;
  

• Querying cell blocking

column BlockingType format a15
column BlockSizes format a30 heading "BlockSizes(row, col, band)"
select georid,sdo_geor.getBlockingType(georaster) "BlockingType",
sdo_geor.getBlockSize(georaster) BlockSizes
from georaster_table
order by georid;
  

• Querying Spatial Reference System information

column isSpatialReferenced format a10
column isRectified         format a10
column isOrthoRectified    format a10
column resolutions format a40 heading "Spatial Resolutions"
select georid,sdo_geor.isSpatialReferenced(georaster) "isSpatialReferenced",
sdo_geor.isRectified(georaster) "isRectified",
sdo_geor.isOrthoRectified(georaster) "isOrthoRectified"
from georaster_table
order by georid;

select georid,sdo_geor.getModelSRID(a.georaster) "ModelSRID",
sdo_geor.getSpatialResolutions(a.georaster) resolutions
from georaster_table a
where sdo_geor.isSpatialReferenced(a.georaster) = 'TRUE' and georid = 4;
  

• For all layer-related get and set functions, layerNumber = 0 means the object layer for subLayers,layerNumber is from 1 ~ n

Querying total layer number. It equals the total band number
select georid,sdo_geor.getTotalLayerNumber(georaster) "TotalLayerNumber"
from georaster_table
order by georid;
  

• Querying layer ID which is typically a user-specified unique string

column layer1 format a10 heading "layer1 ID"
column layer2 format a10 heading "layer2 ID"
column layer3 format a10 heading "layer3 ID"
select georid,sdo_geor.getLayerID(georaster, 1) "layer1", sdo_geor.getLayerID(georaster, 2) "layer2", 
sdo_geor.getLayerID(georaster, 3) "layer3"
from georaster_table
where georid = 4;
  

• Querying scaling function for a specific layer

column scalingFunction format a30
select georid, sdo_geor.getScaling(georaster, 1) "scalingFunction"
from georaster_table
where georid = 4;
  

• Querying whether or not the specified layer is (or say, can be considered as) a grayscale image

column hasGrayScale format a10
select georid,sdo_geor.hasGrayScale(georaster, 1) "hasGrayScale"
from georaster_table
order by georid;
  

• Querying whether or not the specified layer is (or say, can be considered as) a pseudocolor image

column hasPseudoColor format a10
select georid, sdo_geor.hasPseudoColor(georaster, 1) "hasPseudoColor"
from georaster_table
order by georid;
  

• Querying GeoRaster Object Cell (or say, Raster) Data. Blank GeoRaster object means all its cells have the same value. Querying a single cell value by specifying cell coordinate in cell space and physical band number

select georid,sdo_geor.getCellValue(georaster, 0, 100, 300, 0) "Value of Cell(100,300,0)"
from georaster_table where georid = 4;
  

• Querying a single cell value by specifying cell coordinate in cell space and logical layer number

select georid,sdo_geor.getCellValue(georaster, 0,
sdo_geometry(2001, null, null, mdsys.sdo_elem_info_array(1,1,1),
mdsys.sdo_ordinate_array (100,300)),1) "Value of Cell(100,300,0)"
from georaster_table where georid = 4;
  

• Copying GeoRaster Objects

declare
  gr1 mdsys.sdo_georaster;
  gr2 mdsys.sdo_georaster;
  cnt integer := 0;
begin
  --
  -- copy a blank georaster object
  --
 
  -- 1. initialize an empty georaster to hold the copy
  delete from georaster_table where georid = 5;
  insert into georaster_table values (5, 'Blank Copy', sdo_geor.init('RDT_1', 5))
  returning georaster into gr2;

  -- 2. the source blank georaster object
  select georaster into gr1 from georaster_table where georid = 3;

  -- 3. make copy
  sdo_geor.copy(gr1, gr2);

  -- 4. store it into the georaster table
  update georaster_table set georaster = gr2 where georid = 5;

  --
  -- copy a truecolor georaster object
  --

  -- 1. initialize an empty georaster to hold the copy if not exist
  select count(*) into cnt from georaster_table where georid = 6;
  if (cnt = 0) then
     insert into georaster_table values (6, 'Truecolor Reformat', sdo_geor.init ('RDT_2', 6));
  end if;
        
  select georaster into gr2 from georaster_table where georid = 6 for update;
 
  -- 2. the source 3-band truecolor georaster object
  select georaster into gr1 from georaster_table where georid = 4;

  -- 3. make copy 
  sdo_geor.copy(gr1, gr2);

  -- 4. store it into the georaster table
  update georaster_table set georaster = gr2 where georid = 6;

  commit;
end;
/

-- quick check on the results
-- georid 5 is identical to georid 3
-- georid 6 is identical to georid 5
  

• Changing the format of a GeoRaster Object

declare
  gr1 mdsys.sdo_georaster;
  gr2 mdsys.sdo_georaster;
  cnt integer := 0;
  begin
   --
   -- changeFormat of a georaster object
   --

   -- 1. the source georaster object
   select georaster into gr1 from georaster_table where georid = 6;

   -- 2. make changes
   sdo_geor.changeFormat(gr1, 'blocksize=(128,128,3) interleaving=BIP');

   -- 3. update it in the georaster table
   update georaster_table set georaster = gr1 where georid = 6;

   commit;

   --
   -- changeFormatCopy a georaster object
   --

   -- 1. initialize an empty georaster to hold the copy if not exist
   select count(*) into cnt from georaster_table where georid = 7;
   if (cnt = 0) then
      insert into georaster_table values (7, 'TRUECOLOR', sdo_geor.init('RDT_3', 7));
   end if;
  
   select georaster into gr2 from georaster_table where georid = 7 for update;

   -- 2. the source georaster object
   select georaster into gr1 from georaster_table where georid = 4;

   -- 3. make format changes and copy 
   sdo_geor.changeFormatCopy(gr1, 'blocksize=(128,128,3) interleaving=BIP', gr2);

   -- 4. update it in the georaster table
   update georaster_table set georaster = gr2 where georid = 7;

   commit;
 end;
/

-- quick check on the results
-- georid 6 should be identical to georid 7
  

• Subset GeoRaster Objects. sdo_geor.subset is called to crop a small area, subset a few layers of the GeoRaster object. It will create a new GeoRaster object. You can specify various storage format (blocking and interleaving) to store the result GeoRaster object.

declare
  gr1 mdsys.sdo_georaster;
  gr2 mdsys.sdo_georaster;
  cnt integer := 0;
  begin
    --
    -- subset a blank georaster object
    --
    -- 1. initialize an empty georaster to hold the copy
    delete from georaster_table where georid = 8;
    insert into georaster_table values (8, 'Blank Subset', sdo_geor.init('RDT_3', 8))
    returning georaster into gr2;

    -- 2. the source blank georaster object
    select georaster into gr1 from georaster_table where georid = 3 for update;

    -- 3. crop 
    sdo_geor.subset(gr1, 
    sdo_geometry(2003, NULL, NULL,
    mdsys.sdo_elem_info_array(1, 1003, 3),
    mdsys.sdo_ordinate_array(100,256,500,1000)),
    null, null, gr2);

    -- 4. store it into the georaster table
    update georaster_table set georaster = gr2 where georid = 8;

    --
    -- subset a truecolor georaster object
    --

    -- 1. initialize an empty georaster to hold the subset if not exist
    select count(*) into cnt from georaster_table where georid = 9;
    if (cnt = 0) then
        insert into georaster_table values (9, 'Truecolor Subset', sdo_geor.init('RDT_3',9));
    end if;
  
    select georaster into gr2 from georaster_table where georid = 9 for update;
 
    -- 2. the source 3-band truecolor georaster object
    select georaster into gr1 from georaster_table where georid = 4;

    -- 3. make crop and subset 2 bands
    sdo_geor.subset(gr1, 
    sdo_geometry(2003, NULL, NULL,
    mdsys.sdo_elem_info_array(1, 1003, 3),
    mdsys.sdo_ordinate_array(100,200,355,455)),
    '2,1', 'blocksize=(32,32,2)', gr2);

     -- 4. store it into the georaster table
    update georaster_table set georaster = gr2 where georid = 9;
  
   commit;
 end;
/

select sdo_geor.getCellValue(georaster,0,254,224,1) v1,
sdo_geor.getCellValue(georaster,0,231,290,1) V2,
sdo_geor.getCellValue(georaster,0,245,375,1) V3,
sdo_geor.getCellValue(georaster,0,245,420,1) V4,
sdo_geor.getCellValue(georaster,0,224,454,1) v5
from georaster_table where georid=9;
  

• Enlarging or shrinking GeoRaster objects. A GeoRaster object can be scaled at different scale factors along different dimensions. You can also choose different resampling. The storage format for the results can be specified freely.

Scaling and scaleCopy GeoRaster Objects

declare
  gr1 mdsys.sdo_georaster;
  gr2 mdsys.sdo_georaster;
  cnt integer := 0;
begin
  --
  -- enlarge a truecolor georaster object
  --

  -- 1. initialize an empty georaster to hold the scaled copy
  delete from georaster_table where georid = 10;
  insert into georaster_table values (10, 'Blank Subset', sdo_geor.init('RDT_4', 10))
  returning georaster into gr2;

   -- 2. the source 3-band truecolor georaster object
   select georaster into gr1 from georaster_table where georid = 4;

   -- 3. enlarge 2 times 
   sdo_geor.scaleCopy(gr1, 'scaleFactor=2', 'resampling=BILINEAR','blocksize=(256,256,3) 
   interleaving=BIP', gr2);

   -- 4. store it into the georaster table
   update georaster_table set georaster = gr2 where georid = 10;

   commit;
 end;
 /
  

• Generating pyramids for a GeoRaster object.If rLevel is not specified , it generates pyramid until the size of the smallest pyramid layer is right smaller than the blocking size if it is blocked, otherwise the size of the smallest level is around 64X64. You can also choose different resampling.

declare
  gr mdsys.sdo_georaster;
begin
  -- 1. the source 3-band truecolor georaster object
  select georaster into gr from georaster_table where georid = 6 for update;

  -- 2. generate pyramids
  sdo_geor.generatePyramid(gr, 'resampling=NN');

  -- 3. update the original georaster object
  update georaster_table set georaster = gr where georid = 6;

  commit;
 end;
 /   
 -- quick check on the results

 select pyramidLevel, count(*) from rdt_2 group by pyramidLevel;
 select unique pyramidLevel,dbms_lob.getlength(rasterblock) from rdt_2 order by    
 pyramidLevel;

 select substr(sdo_geor.getPyramidType(georaster),1,10) pyramidType,
 sdo_geor.getPyramidMaxLevel(georaster) maxLevel
 from georaster_table where georid=6;

 -- Querying a single cell value by specifying cell coordinate in cell space and physical band number
   
 select georid, sdo_geor.getCellValue(georaster, 1, 100, 100, 0)
 "Cell value at pyramid level 1",sdo_geor.getCellValue(georaster, 2, 100, 100, 0)
 "Cell value at pyramid level 2"
 from georaster_table where georid = 6;
  

• Deleting pyramids of a GeoRaster object

declare
  gr mdsys.sdo_georaster;
begin
  -- 1. the source 3-band truecolor georaster object
  select georaster into gr from georaster_table 
  where georid = 6 for update;

   -- 2. generate pyramids
   sdo_geor.deletePyramid(gr);

   -- 3. update the original georaster object
   update georaster_table set georaster = gr where georid = 6;

   commit;
 end;
 /
   -- quick check on the results

   select pyramidLevel, count(*) from rdt_2 group by pyramidLevel;   
   select unique pyramidLevel, dbms_lob.getlength(rasterblock) 
   from rdt_2
   order by pyramidLevel;

   select substr(sdo_geor.getPyramidType(georaster),1,10) pyramidType,
   sdo_geor.getPyramidMaxLevel(georaster) maxLevel
   from georaster_table where georid=6;
  

-- AditSantokhee - 29 Mar 2004