OCI driver: loading into Oracle fails with ORA-29855 and ORA-13375

[agodfrin]

When loading a polygon layer into Oracle, and that layer contains multi-polygons, the creation of the spatial index fails with:

ERROR 1: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine ORA-13249: internal error in Spatial index: [mdidxrbd] ORA-13249: Error in Spatial index: index build failed ORA-13249: Error in spatial index: [mdrcrtxfergm] ORA-13249: Error in spatial index: [mdpridxtxfergm] ORA-13200: internal error [ROWID:AAAaU8AAGAAANcwAAF] in spatial indexing. ORA-13206: internal error [Invalid geometry; check ROWID] while creating the spatial index ORA-13375: the layer is of type [2003] while geometry inserted has type [2007] ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10

in CREATE INDEX "FRA_ADM5_IDX" ON FRA_ADM5("GEOMETRY") INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS( 'LAYER_GTYPE=POLYGON' )

ANALYZIS:

The reason is that the OCI driver wants to create the index with the LAYER_GTYPE=POLYGON parameter. This parameter restricts the index to only support single-element polygons. The presence of any multi-polygon in the input prevents the index to be created. That parameter is essentially used as a way to restrict a table in Oracle to geometries of a certain kind (points, lines, polygons, surfaces, solids ...). The parameter can be set to further restrict to single-instance geometries. For example POLYGON only allows single-element polygons (but they can contain any number of holes). MULTIPOLYGON allows any kind of polygon (single or multi-element).

Since GDAL 2.0 a new CO parameter ADD_LAYER_GTYPE controls whether LAYER_GTYPE should be added to the index creation. The trouble is that the default is "-co ADD_LAYER_GTYPE=YES".

IMPACT:

This bug is critical. It impacts the loading of spatial data from any format into Oracle, especially for data such as natural features (coat lines with islands) or administrative boundaries that contain multi-polygons: existing loading scripts now fail when a user switches to ogr 2.0.

WORKAROUNDS:

1) Disable the use of the LAYER_GTYPE index parameter by adding this to the ogr2ogr command:

-co ADD_LAYER_GTYPE=NO

then redo the load, or

2) Drop and recreate the spatial index

POSSIBLE SOLUTIONS:

There are several ways to correct this issue:

1) Change the default of -co ADD_LAYER_GTYPE to NO instead of YES.

or

2) Always provide it in the plural: LAYER_GTYPE=MULTIPOLYGON (or MULTILINE, or MULTIPOINT etc). This still restricts a table to contains only polygons (or lines or points), but allows the objects to be single or multi-polygons.

or

3) Let the user explicitly specify the parameter setting (via a -CO LAYER_GTYPE= parameter)

[Jukka Rahkonen]

Have you tried ogr2ogr with -nlt MULTIPOLYGON or with -nlt PROMOTE_TO_MULTI? Also -nlt GEOMETRY may make sense with Oracle sometimes.

I wouldn't call this as a critical bug and I am not sure if it is a bug at all. Other databases have similar behaviour, at least PostGIS and Spatialite. Perhaps the OCI manual page should be edited to emphasize that ADD_LAYER_GTYPE=YES may prevent writing multi-variants into the table.

1) ADD_LAYER_GTYPE=YES is better default for my mind and usage is documented.

2) Users may want to create layers which accept only simple versions.

3 a) Is already supported through INDEX_PARAMETERS. Should be checked what happens if GTYPE in INDEX_PARAMETERS and ADD_LAYER_GTYPE do not match. I would say that INDEX_PARAMETERS should be used in such case.

3 b) Not sure but I believe that -nlt parameter of ogr2ogr has an effect also with OCI. Should be tested, if I remember right -dim 2 does not work with OCI but -LCO DIM=2 must be used instead.

[Even Rouault]

I agree with Jukka's above analysis and solutions. The new behaviour is intended. The layer geometry type passed at layer creation is now honoured.

Regarding 3a), I confirm that if INDEX_PARAMETERS is defined and contains a LAYER_GTYPE option, it will indeed override the effect of ADD_LAYER_GTYPE=YES.

[Even Rouault]

No further comments from reported. Closing