Alternatives for Ingesting Oracle SDO_GEOMETRY Data into GeoMesa

Question

I am currently working on a project that involves ingesting spatial data from an Oracle database into GeoMesa using the JDBC converter. My source data is in the Oracle-specific SDO_GEOMETRY format.

My initial approach was to convert the SDO_GEOMETRY data to the Well-Known Text (WKT) representation using the SDO_UTIL.TO_WKTGEOMETRY function. Afterwards, I planned to convert this WKT into a string format using the TO_CHAR function so I can ingest it into GeoMesa. However, I encountered a challenge with this approach. Oracle's TO_CHAR function can handle only up to 4000 characters, but some of my converted WKT data exceeds this limit. As a result, I am unable to convert and ingest my data.

I am looking for alternative solutions to this problem. Is there a more efficient way to convert the SDO_GEOMETRY data into a format that can be ingested by GeoMesa, without hitting the character limit? Is there a direct method to convert the Oracle SDO_GEOMETRY into a format (like Well-Known Binary or others) which can be handled directly by GeoMesa without the 4000 character limit issue?

Any help or guidance would be greatly appreciated. Thank you!

Answer 1

You should be able to use SDO_UTIL.TO_WKTGEOMETRY, which returns a CLOB instead of a VARCHAR. GeoMesa doesn't have any CLOB handling though, unless .toString returns something useful. You'd probably need to implement a custom transformer function to turn the CLOB into a String.

A function like that would be generally useful, so please contribute it back to the GeoMesa project if you implement it!