5

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 227

at 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

Improve this question
1
  • 1
    I don't think SDO_GEOMETRY supports the concept of a NIL geometry. In the past I've needed to pass a NULL instead.
    – Vince
    Jun 28, 2016 at 17:07

2 Answers 2

Reset to default
4

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.

Improve this answer
1
  • 2
    Using NULL instead of EMPTY mostly works fine, particularly for things like measurements, etc. So Area(NULL) = NULL, etc. Where it falls apart is when you chain geometric operations. Imagine three mutually disjoint geometries, A, B, C. What is the answer to Union(Intersection(A, B), C)? Spatially, it's clearly C (just draw a picture). But with NULLs you get back NULL. With EMPTY as a concept you get back C, since Union(EMPTY,C) = C. Whether this added correctness is worth all the pain supporting EMPTY causes I leave as an exercise to the reader (PostGIS supports EMPTY, so does SQLServer).
    – Paul Ramsey
    Jun 29, 2016 at 20:44
2

As Aileen said, Oracle has no specific concept of an EMPTY geometry as differentiated from a straight NULL geometry. All spatial operations involving NULL geometries behave according to the regular rules for NULLs in relational algebra - put simply: anything combined with NULL is NULL.

I just realized that there is specific syntax in the WKT notation to designate an empty geometry - as for example "POINT EMPTY". We could fairly easily extend our WKT parsing and generation to use transform any WKT like " EMPTY" to NULL and vice versa. But for now that is not the case.

By the way this notation is really odd: why POINT EMPTY ? Is that different from POLYGON EMPTY ? If it is EMPTY then it cannot possibly be qualified as of a certain type ...

Anyway, the solution for your example is to use this:

select null from PARISH_BOUNDARIES_BUP;

Then again I don't understand what that hopes to achieve.

To set a geometry to empty, do this:

insert into my_table(id, geom) values (42, null);

Empty (i.e. NULL) geometries are not indexed. They will therefore obviously not be returned by any spatial query. But you can of course query the rows. For example to find out how many NULL geometries you have in your table, do this:

select count(*) from my_table where geom is null;

Also by default, all columns allow nulls. If you want to make sure all your objects always have a geometry, you just need to use a NOT NULL constraint:

create table my_table (
  id number primary key,
  ....
  geom sdo_geometry not null
);

Any attempt to insert an object without a geometry or to set a geometry to null will now fail.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.