English

How To: Register an SDO_GEORASTER layer with an SDE geodatabase and keep the user-maintained ROWID

Summary

Instructions provided describe how to register an SDO_GEORASTER layer with an SDE geodatabase and keep the user-maintained ROWID.

Procedure

Follow these steps to create an SDO_RASTER layer within Oracle, register it with ArcSDE, and keep the ROWID column as user-maintained.

  1. Load data through SQL*Plus.

    A) Login as sysdba.

    Code:
    conn /@orcl as sysdba


    B) Permissions for loading data.

    Code:
    call dbms_java.grant_permission('MDSYS','SYS:java.io.FilePermission','C:\bugs\i8050\eu.tif', 'read' );
    call dbms_java.grant_permission('MDSYS','SYS:java.io.FilePermission','C:\bugs\i8050\eu.tfw', 'read' );

    call dbms_java.grant_permission('TEST','SYS:java.io.FilePermission','C:\bugs\i8050\eu.tif', 'read' );
    call dbms_java.grant_permission('TEST','SYS:java.io.FilePermission','C:\bugs\i8050\eu.tfw', 'read' );

    drop user test cascade;
    create user test identified by test default tablespace users;
    grant connect,resource to test;


    C) Login as TEST user to load the raster data.

    Code:
    conn test/test@orcl


    D) Drop existing tables, this one is not needed if the TEST user has been re-created from scratch.

    Code:
    drop table test_raster;
    drop table test_raster_rdt;

    prompt Create Tables
    CREATE TABLE TEST_RASTER (GEORID NUMBER(38) PRIMARY KEY, GEORDNAME VARCHAR(64) , GEORASTER SDO_GEORASTER);

    E) Create table.

    Code:
    CREATE TABLE TEST_RASTER_RDT OF SDO_RASTER
    (PRIMARY KEY (rasterID, pyramidLevel, bandBlockNumber, rowBlockNumber, columnBlockNumber))
    LOB(rasterBlock) STORE AS (NOCACHE NOLOGGING);


    F) Insert.

    Code:
    INSERT INTO TEST_RASTER (GEORID, GEORDNAME, GEORASTER) VALUES (1,'TIFF', sdo_geor.init('TEST_RASTER_RDT',1));


    G) Import the TIFF image and world file.

    Code:
    DECLARE
    geor SDO_GEORASTER;
    BEGIN
    SELECT georaster INTO geor from TEST_RASTER where GEORID = 1 FOR UPDATE;
    mdsys.sdo_geor.importFrom(geor, NULL, 'TIFF', 'file','C:\bugs\i8050\eu.tif','WORLDFILE','FILE','C:\bugs\i8050\eu.tfw');
    UPDATE TEST_RASTER SET georaster = geor where georId = 1;
    COMMIT;
    END;
    /


    H) Convert from BIP to BSQ for ArcSDE.

    Code:
    DECLARE
    gr1 sdo_georaster;
    BEGIN
    SELECT georaster INTO gr1 from TEST_RASTER WHERE georid=1 FOR UPDATE;
    mdsys.sdo_geor.changeFormat(gr1, 'blockSize=(128,128,1) interleaving=BSQ');
    UPDATE TEST_RASTER SET georaster=gr1 WHERE georid=1;
    COMMIT;
    END;
    /

    I) Create Pyramids.

    Code:
    DECLARE
    gr mdsys.sdo_georaster;
    BEGIN
    select GEORASTER into gr from TEST_RASTER
    where GEORID = 1 for update;
    mdsys.sdo_geor.generatePyramid(gr, 'rLevel=2 resampling=NN');
    update TEST_RASTER set GEORASTER = gr where GEORID = 1;
    COMMIT;
    END;
    /

    describe test_raster;
    quit;

  2. Register the SDO_GEORASTER layer with ArcSDE.
    Code:
    sderaster -o add -i 5151 -l test_raster,georaster -type=georaster -u test -p test -k SDO_GEORASTER -G 4326

    sdetable -o alter_reg -i 5151 -t test_raster -c GEORID -C USER -u test -p test -k SDO_GEORASTER -N

  3. Register the SDO_GEORASTER layer with the geodatabase via ArcCatalog. The layer can be previewed from ArcCatalog/ArcMap as expected.