Failure when inserting some big geometries into OCI

[sebastianovide]

Importing some shapefiles would fail if there are some problems in some geometries. note: The tool shp2sdo would skip those geometries

[sebastianovide]

it wont load into oracle

[sebastianovide]

second part

[ilucena]

That is what I've got so far:

C:\Data>ogr2ogr -f oci oci:scott/tiger@orcl broken.shp -lco broken_t1
ERROR 1: ORA-01438: value larger than specified precision allowed for this colum
n
 in OCIStmtExecute

WinXP, GDAL 1.8Dev (trunk)

[ilucena]

Sebastian,

Correct me if I am wrong bu I found that there are just to features on that file. One has about 1.800.000 ordinates and the other one just 39. Does it seems correct?

The error that I got is a ORA-01438, as you can see on my previous post, but that is not what you are getting. Is it?

I am running on Oracle 10g, by the way. I am going to try again on OpenSUSE and Oracle 11g later.

[ilucena]

Here is the deal.

The shape file dimension is 3 and the number of ordinates on the first feature is 1,825,680 that represent 608,560 3D coordinates. That exceed the limit of Oracle Spatial SDO_GEOMETRY, more specifically on SDO_ORDINATE_ARRAY.

There is a discussion about that on OTN:

​http://forums.oracle.com/forums/thread.jspa?threadID=571462

From there you you see a link to a workaround available for 11R2:

​http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11830/sdo_migrat.htm#CHDBCHBG

Do you think that there is something else that we can do for you?

[sebastianovide]

In my case this is not important as I am tiling and single parting the shape file before loading it into the DB and therefore I would never have big polygons anymore.

Nevertheless making Gdal do the same thing that shp2sdo is doing would not be a bad idea: Do not load those too big shapes... (and save them in a file.bad) and avoid the pain of seeing an ugly error on the screen and consequence by hand fixing (deleting the partially built index etc...)

[ilucena]

Years ago, decades more precisely, I faced a problem like that when generating HPGL and Postscript printing output for SPRING-GIS. The solution was to automatically break down the polygons but I don't think we can do that with live data on a database :)

I don't know about writing a .bad file. It doesn't looks like a ogr2ogr or GDAL style. The shp2sdo is a dedicated tool and doesn't have a driver architecture to deal with.

Nevertheless, I think that the warning message should precede the attempt to issue the statement. In fact, it should avoid trying to load that feature so that you wouldn't need to clean the index. The problem is what you are going to do with that useless layer?

Anyway, I think we would need to check what is the current limitation of SDO_ORDINATE_ARRAY on the server and take that as a clue. That fixed limit can be altered by that sdoupggeom.sql script. After that we can issue a proper warning.

Frank,

Should I proceed with that?

[ilucena]

The solution for that bug still lies on the data type change on the server as it was mentioned before. More discussion about it can be found at OTN Oracle Spatial discussion forum.

[Jukka Rahkonen]

If ogr2ogr skips nicely oversized geometries with -skipfailures I would close the ticket as "wontfix". This is not the only example when source data must be processed somehow with external tools before using ogr2ogr and splitting huge geometries into smaller but still valid geometries is not trivial especially when dealing with polygons with lots of holes. But before closing the effect of -skipfailures should be checked.

[Jukka Rahkonen]

I made a test and it appeared that -skipfailures did not work nicely. Conversion was truncated and just an empty layer was created. I was hoping that the small feature would have inserted. However, it some feature has more than 600000 vertices it is probably so important for the user that skipping it would probably mean a non-usable result.

I consider that Oracle users just must accept this 1,048,576 ordinates limit and edit the original data if necessary. After all, the error "ORA-01438: value larger than specified precision allowed for this column" describes the situation quite well.

[ilucena]

"Increasing the Size of Ordinate Arrays to Support Very Large Geometries" ​http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11830/sdo_migrat.htm#CHDBCHBG