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 )
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 , 15 years ago
Cc: | added |
---|---|
Description: | modified (diff) |
Keywords: | georaster added |
Owner: | changed from | to
Priority: | high → normal |
comment:2 by , 15 years ago
Status: | new → assigned |
---|
comment:3 by , 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 , 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 , 9 years ago
Resolution: | → invalid |
---|---|
Status: | assigned → closed |
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.
Complementary information:
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.
Or:
[1] http://forums.esri.com/Thread.asp?c=2&f=1722&t=206080#620248