Hello Oracle users! I'm reaching out about a bug that has been tormenting me for quite some time. We're in the middle of migrating our Oracle 11g databases onto newer versions. In this email I'm sharing my findings. I hope to generate some input on this issue. Ultimately I wish a solution to make its way into the next release of geotools.
For easy reproduction I've pushed an unit test demonstrating it on a branch (see reproduction). Symptoms observed: Failed tests with connection closed exception. This happens during read (SELECT) of spatial data. * When browsing testdata with our desktop application the same exception occurs. * The oracle db logs reads ORA-00600 The testdata persisted are somewhat unique as they contain polygons as "perfect rectangles". Findings: At first it was believed to be a bug in oracle, but after an in depth reading on the oracle documentation I find it otherwise. The issue is the way polygons are stored. The implementation has introduced an "rectangle optimization" when building a struct from a jts geometry. This optimization leads to a records with the following spatial representation: MDSYS.SDO_GEOMETRY(2003, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 3), MDSYS.SDO_ORDINATE_ARRAY(16.88, 4.11, 16.89, 4.15)) The oracle documentation names the interpretation "1003, 3" optimized rectangle. It also states the following: "The rectangle type can be used with geodetic or non-geodetic data. However, with geodetic data, use this type only to create a query window (not for storing objects in the database)." This clearly states that geometry being persisted for geodetic data, in my case SRID 4326, can't be encoded as optimized rectangle. Persisting data this way goes unnoticed, but the error arise when querying it later. Usage: The way my project is consuming "org.geotools.jdbc:gt-jdbc-oracle" is: GeometryFactory geometryFactory = new GeometryFactory(precisionModel, 4326); GeometryConverter geometryConverter = new GeometryConverter(oracleConnection, geometryFactory); Geometry jtsGeometry = geometryFactory.createGeometry((Geometry) value); OracleStruct sdoStruct = geometryConverter.toSDO(jtsGeometry); ps.setObject(index, sdoStruct, Types.STRUCT); Reproduction: I've put together a unit test and pushed it to https://github.com/kartverket/geotools/commits/oracle_perfect_rectangles See OraclePolygonPersitenceOnlineTest.java Current workaround: Remove this if-block from GeometryConverter.toSDO(geom, srid): if (env.getWidth() > 0 && env.getHeight() > 0 && !(geom instanceof GeometryCollection) && geom.isRectangle()) { // rectangle optimization. Actually, more than an optimization. A few operators // do not work properly if they don't get rectangular geoms encoded as rectangles // SDO_FILTER is an example of this silly situation I hope this issue gets attention as there's no obvious fix. Removing the "optimization" mentioned previously is a behavioral change and therefore not a quick fix. Thank you for your interest on this topic. Regards Leif Lislegård
_______________________________________________ GeoTools-Devel mailing list GeoTools-Devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
