How To: Register an Oracle table with an SDO_GEORASTER column as a raster catalog
Instructions provided describe how to register a raster catalog to the geodatabase that was created with SQL and uses an ORACLE SDO_GEORASTER column.
Oracle Corporation added the SDO_GEORASTER data type during the release of Oracle 10G. Esri added support for the SDO_GEORASTER data type during the release of ArcGIS Server ArcSDE 9.2. A table can be created with a raster column defined as SDO_GEORASTER using Structured Query Language (SQL); example scripts are provided by Oracle. Once the table is created and loaded with data, register the table with ArcSDE as a raster column and register it to the Enterprise Geodatabase as a raster catalog.
Create the table in the Oracle database using the example provided by Oracle Corporation, found under: %ORACLE_HOME%\md\demo\georaster\plsql\create_georaster_table.sql.
Note: The georaster demos scripts are installed from the Oracle 10G companion CD or may be downloaded from the Oracle Technology Network (OTN).
Import image files into the SDO_GEORASTER column, using the 'mdsys.sdo_geor.importFrom' stored procedure. Use the example script provided by Oracle Corporation, found under : %ORACLE_HOME%\md\demo\georaster\plsql\import_georaster.sql.
Warning: VERY IMPORTANT: Be sure to include the world file when loading the image file, otherwise the image loads with cell picture coordinates rather than world coordinates.
The Oracle 'importFrom' stored procedure stores the SDO_GEORASTER object in BIP (Band Interleaved Pixel) format. ArcGIS does not read this format. It uses BSQ (Band Sequential). Therefore, if a true color image (3 band, 8-bit) has been stored in an SDO_GEORASTER using the Oracle 'importFrom' stored procedure, it will not readable from ArcGIS until it has been converted to BSQ.
The Oracle 'changeFormatCopy' stored procedure can be used to convert from BIP to BSQ. To convert the SDO_GEORASTER from BIP to BSQ using 128 by 128 block size, use the following storage parameter argument in the 'changeFormatCopy' Stored Procedure:
Code: 'blockSize=(128,128,1) interleaving=BSQ'
See the Oracle documentation for an example of how to implement the 'changeFormatCopy' stored procedure.
Another way to convert the data to BSQ is to use the ArcSDE sderaster command line tool. See optional step 6 for instructions.
Register the table containing the raster column to ArcSDE using the 'sderaster -o add ...' command. Be sure to include the correct coordinate system with the '-G' option that corresponds to the coordinate system of the image world files.
Note: If the '-G' option is not specified, the ArcGIS geodatabase registration operation described in the next step fails with a -51 error and an extended Oracle error of ORA-00932 is returned.
This next step is optional, because step 6, the registration of the raster column to the Enterprise Geodatabase, automatically adds a system-maintained rowid, (called OBJECTID by default), to the your table.
If you wish to use your own column as a user-maintained rowid column, complete this step, otherwise continue on to step 6.
Use the sdetable command to register a user-maintained rowid column with ArcSDE. For example:
To register the ArcSDE raster column to the Enterprise Geodatabase, right-click the table object (in ArcCatalog) and select the 'Register with the Geodatabase' option.
The ArcSDE raster column is registered as a raster catalog.
Note: It is not possible to register the raster column as a raster dataset in this workflow, because the '-g' option cannot be specified with the 'sderaster -o add ...' command. Raster datasets must be created by an ArcGIS geoprocessing tool or the 'sderaster -o import ...' command. However, it is not possible to mosaic to a raster dataset stored as an SDO_GEORASTER; therefore, a raster catalog should be created.
An alternative way to convert a true color raster from BIP to BSQ is to use the sderaster command line tool after it has been registered with ArcSDE.
The sderaster command line tool does not copy SDO_GEORASTER srid metadata since it is not used by ArcSDE. Therefore, if the Oracle SRID has been set, it also needs to be copied from the original raster.
SQL> declare 2 geor mdsys.sdo_georaster; 3 begin 4 select georaster into geor from tgrimp where rownum = 1; 5 mdsys.sdo_geor.setModelSRID(geor, 8307); 6 update tgrimp set georaster = geor where rownum = 1; 7 commit; 8 update tgrimp grt 9 set grt.georaster.spatialExtent = sdo_geor.generateSpatialExtent(georaster) 10 where rownum = 1; 11 commit; 12 end; 13 /