Opened 15 years ago

Closed 9 years ago

#3095 closed defect (invalid)

Can't import raster to Oracle after use ArcSDE regester the georaster table

Reported by: bicealyh Owned by: ilucena
Priority: normal Milestone:
Component: GDAL_Raster Version: 1.6.1
Severity: major Keywords: georaster
Cc: warmerdam

Description (last modified by warmerdam)

When I use the ArcSDE regester the georaster table,it can't import the data through the GDAL georaster,the error like below:

D:\bin>gdal_translate -of georaster
D:\image2\Extract_20.img georaster:sde/123,,city_images,image -co "DESCRIPTION=
(image_id NUMBER, filename VARCHAR2(200), image MDSYS.SDO_GEORASTER)" -co "INSER
T=VALUES(15,'IMG.img',SDO_GEOR.INIT('city_images_rdt',15))" -co "BLOCKXSIZE=512"
 -co "BLOCKYSIZE=512"
GDAL: GDALOpen(D:\image2\Extract_20.img, this=00B35058) succeeds as HFA.
Input file size is 8534, 6478
GDAL: QuietDelete(georaster:sde/123,,city_images,image) invoking Delete()
GDAL: GDALOpen(georaster:sde/123,,city_images,image, this=00BCE678) succeeds as
GeoRaster.
PL/SQL:
DECLARE
  TAB VARCHAR2(68)  := UPPER(:1);
  COL VARCHAR2(68)  := UPPER(:2);
  CNT NUMBER        := 0;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES
    WHERE TABLE_NAME = :1 ' INTO CNT USING TAB;
  IF CNT = 0 THEN
    EXECUTE IMMEDIATE 'CREATE TABLE '||TAB||' (image_id NUMBER, filename VARCHAR
2(200), image MDSYS.SDO_GEORASTER)';
    SDO_GEOR_UTL.createDMLTrigger( TAB,  COL );
  END IF;
END;

PL/SQL:
DECLARE
  TAB  VARCHAR2(68)    := UPPER(:1);
  COL  VARCHAR2(68)    := UPPER(:2);
  CNT  NUMBER          := 0;
  GR1  SDO_GEORASTER   := NULL;
BEGIN

  GR1 := SDO_GEOR.createBlank(21001, SDO_NUMBER_ARRAY(0, 0, 0), SDO_NUMBER_ARRAY
(6478, 8534, 3), 0, 'CITY_IMAGES_RDT', 15);

  GR1.spatialExtent := NULL;

  INSERT INTO city_images VALUES(15,'IMG.img',GR1) RETURNING image INTO GR1;

  SELECT GR1.RASTERDATATABLE INTO :rdt FROM DUAL;
  SELECT GR1.RASTERID        INTO :rid FROM DUAL;

  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_OBJECT_TABLES
    WHERE TABLE_NAME = :1' INTO CNT USING :rdt;

  IF CNT = 0 THEN
    EXECUTE IMMEDIATE 'CREATE TABLE '||:rdt||' OF MDSYS.SDO_RASTER
      (PRIMARY KEY (RASTERID, PYRAMIDLEVEL, BANDBLOCKNUMBER,
      ROWBLOCKNUMBER, COLUMNBLOCKNUMBER))
      LOB(RASTERBLOCK) STORE AS (NOCACHE NOLOGGING)';
  END IF;

  SDO_GEOR.createTemplate(GR1, 21001, 'dimSize=(6478,8534,3) blockSize=(512,512,
1) cellDepth=8BIT_U interleaving=BSQ compression=NONE ', null, 'TRUE');

  UPDATE city_images T SET image = GR1 WHERE T.image.RasterDataTable = :rdt AND
T.image.RasterId = :rid;
END;


ERROR 1: ORA-06550: 12  rows, 15 columns:
PL/SQL: ORA-00947: not enough value

ORA-06550: 12  rows, 3 columns:
PL/SQL: SQL Statement ignored

ERROR 1: Failure to initialize GeoRaster
GDAL: GDALClose(georaster:sde/123,,city_images,image, this=00BCE678)
GDAL: GDALClose(D:\image2\Extract_20.img, this=00B35058)
GDAL: GDALDeregister_GTiff() called.

Change History (5)

comment:1 by warmerdam, 15 years ago

Cc: warmerdam added
Description: modified (diff)
Keywords: georaster added
Owner: changed from warmerdam to ilucena
Priority: highnormal

comment:2 by ilucena, 15 years ago

Status: newassigned

Complementary information:

--georaster table
create table city_images(image_id NUMBER primary key, filename VARCHAR2(200), image MDSYS.SDO_GEORASTER) TABLESPACE tbs_gr1;

--Raster table
CREATE TABLE city_images_rdt OF MDSYS.SDO_RASTER(PRIMARY KEY(rasterID, pyramidLevel, bandBlockNumber, rowBlockNumber,
columnBlockNumber))
TABLESPACE tbs_gr1 NOLOGGING
LOB(rasterBlock) STORE AS lobseg
(
CHUNK  32768
CACHE  READS
NOLOGGING
PCTVERSION  0
STORAGE(PCTINCREASE  0)
);
-- ArcSDE command line tool:

sderaster -o add -G 4326 -l city_images,image -u db_name -p password -type=georaster -i 5151
ArcSDE 9.3  for Oracle11g Build 508 Thu Apr 17 12:23:18  2008
Attribute        Administration Utility
-----------------------------------------------------

Table city_images:
Column name             Attribute type   Null?      Length,DPs    RowID Column?
-------------------------------------------------------------------------------
IMAGE_ID                SE_FLOAT64       NOT NULL       38,10
FILENAME                SE_STRING        NULL          200
IMAGE                   SE_RASTER        NULL            0
FOOTPRINT               SE_SHAPE         NULL            0
OBJECTID                SE_INT32         NOT NULL       10        SDE Set
SE_ANNO_CAD_DATA        SE_BLOB          NULL            0

After the sderaster register the GeoRaster the tables gain same new columns and that is what this exactly what the error message is about: "PL/SQL: ORA-00947: not enough value"

I believe that this process need to be revised by someone with access to ArcSDE and Oracle but in the meantime there is a discussion on that forum [1] that says that the registration is not necessary.

A workaround would be to add the missing columns to the INSERT options or specify only the columns that will be update. Both options are probably a problem for the consistency of ArcSDE even though those columns are useless for Oracle.

-co INSERT"=VALUES(15,'IMG.img',SDO_GEOR.INIT('city_images_rdt',15),NULL,15,NULL)"

Or:

-co INSERT"=(image_id, filename, image) VALUES(15,'IMG.img',SDO_GEOR.INIT('city_images_rdt',15))"

[1] http://forums.esri.com/Thread.asp?c=2&f=1722&t=206080#620248

comment:3 by ilucena, 11 years ago

I hope that issue was solved so we can close that ticket.

The values on the INSERT create-options should work, but I am also concerned about the BLOCK{X,Y,B}SIZE. As I understand there are some limitation on the ArcSDE on the size and shape of the blocks that it support specifically for SDO_GEORASTER. So you might load without a problem and use with MapViewer or other products but it might not work with ArcSDE.

comment:4 by Jukka Rahkonen, 9 years ago

I share the wish of ilucena: "I hope that issue was solved so we can close that ticket."

Because there has been no complaints in one and a half year, if ilucena or bicealyh won't close the ticket within a few days I'll do it.

comment:5 by ilucena, 9 years ago

Resolution: invalid
Status: assignedclosed

One little detail that was overlooked here is that the create-option DESCRIPTION is ignored if the table already exist. That is the intended behavior, so that you can run loops with the same gdal_translate command where only the first will create the tables.

So the table was *not* created by this gdal_translate command but probably by some ArcSDE tool.

Another big detail that was also overlooked, is that on the existing city_images there absolute *no* column of type sdo_georaster. It might be using Oracle but GeoRaster.

Maybe the intention was to define the column IMAGE as SDO_GEORASTER. If that is the case then the ArcSDE blocking limitation will apply.

If the column was to be used ad SE_RASTER, then I believe that there is ArcSDE raster driver to load that.

Note: See TracTickets for help on using tickets.