I'm trying to fix a GeoTools issue but I'm running into problems putting empty geometries into my Oracle database. Basically executing:
select SDO_UTIL.FROM_WKTGEOMETRY('POINT EMPTY') from PARISH_BOUNDARIES_BUP;
gives me the ever so helpful error message:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.RuntimeException ORA-06512: at "MDSYS.SDO_UTIL", line 137
29532. 00000 – "Java call terminated by uncaught Java exception: %s"
*Cause: A Java exception or error was signaled and could not be
resolved by the Java code.
*Action: Modify Java code, if this behavior is not intended.
if I test in eclipse with the actual code I get:
java.io.IOException: Error inserting features at
org.geotools.jdbc.JDBCDataStore.insert(JDBCDataStore.java:1697) at
org.geotools.jdbc.JDBCInsertFeatureWriter.flush(JDBCInsertFeatureWriter.java:125)
at
org.geotools.jdbc.JDBCInsertFeatureWriter.close(JDBCInsertFeatureWriter.java:152)
at
org.geotools.data.InProcessLockingManager$1.close(InProcessLockingManager.java:350)
at
org.geotools.data.store.ContentFeatureStore.addFeatures(ContentFeatureStore.java:263)
at
org.geotools.data.oracle.OracleGeometryOnlineTest.testInsertEmptyGeometry(OracleGeometryOnlineTest.java:96)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at
java.lang.reflect.Method.invoke(Unknown Source) at
junit.framework.TestCase.runTest(TestCase.java:176) at
junit.framework.TestCase.runBare(TestCase.java:141) at
junit.framework.TestResult$1.protect(TestResult.java:122) at
junit.framework.TestResult.runProtected(TestResult.java:142) at
junit.framework.TestResult.run(TestResult.java:125) at
junit.framework.TestCase.run(TestCase.java:129) at
org.geotools.test.OnlineTestCase.run(OnlineTestCase.java:123) at
junit.framework.TestSuite.runTest(TestSuite.java:255) at
junit.framework.TestSuite.run(TestSuite.java:250) at
org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:84)
at
org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at
org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: java.sql.BatchUpdateException: ORA-29875: failed in the
execution of the ODCIINDEXINSERT routine ORA-13354: incorrect offset
in ELEM_INFO_ARRAY ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line
623 ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 227at
oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:12296)
at
oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:246)
at
org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at
org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at
org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at org.geotools.jdbc.JDBCDataStore.insertPS(JDBCDataStore.java:1762)
at org.geotools.jdbc.JDBCDataStore.insert(JDBCDataStore.java:1683)
… 25 more
Best Answer
This is the question/response I got from oracle's spatial expert in Europe in 2014.
q: Does oracle have the concept of an empty geometry? If so can you give me an example? Would this be a valid way of creating an empty point? SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(NULL, NULL, NULL), NULL, NULL)
My initial reaction was there is no such thing as an empty geometry and therefore it should be represented as a NULL, but thought I'd ask you as you might have come across the concept.
A: You are correct that we do not have a specific structure to represent an empty geometry. We choose instead to take advantage of the SQL NULL, which works fine.
For example intersecting two disjoint geometries returns NULL. So is generating a buffer around a null geometry: it returns null too. Unioning a geometry with a null also produces a null. And so is computing the length of a null geometry, or measuring the distance between a geometry and null.
This has the benefit of making the use of nulls for geometries fully orthogonal with the traditional use of SQL: anything combined with NULL is NULL. Geometric aggregations ignore NULLs - just like numerical aggregations.
Note however that measuring the area of a line or a point returns 0 - not NULL.
The use of NULL to represent empty geometries is probably open to arguing. There used to be discussions about the relational model arguing that there should be multiple kinds of NULL: typically to distinguish between an unknown value and a missing value. For example a NULL postal code may mean that I don't know the postal code for an address, or it may mean that the country does not use postal codes.