ST_GEOMETRY FC: Options for using Oracle Spatial functions

Question

I have SDE.ST_GEOMETRY polyline FCs (tables) in an Oracle 18c geodatabase.

I'm building various SQL queries on the tables. It would help if I could use Oracle Spatial/SDO_GEOMETRY functionality like Spatial's linear referencing functions.

* Unfortunately, it's not possible for me to change the datatype in the table from SDE.ST_GEOMETRY to MDSYS.SDO_GEOMETRY, due to existing dependencies on the ST_GEOMETRY shape column.
* Also, it seems like ArcGIS only supports a singe geometry column per table. So it's not like I can just add a SDO_GEOMETRY column to the table, without risking problems in ArcGIS.

Alternatively, what are my options for generating a separate SDO_GEOMETRY column so that I can use Oracle Spatial functions?

For example:

* Convert from ST_GEOMETRY to SDO_GEOMETRY in a query on-the-fly. 
Downside: the query is complicated/fragile/slow. I don't want to make the conversions every time the queries are used.
* Add an invisible SDO_GEOMETRY column to the table. Update the column with a db trigger. Add a spatial index.
Downside: Invisible SDO_GEOMETRY columns aren't supported in Oracle. Also: Idea: Support invisible SDO_GEOMETRY columns.
* Create a parallel table that has a common ID column and a SDO_GEOMETRY column. Create a trigger on the ST_GEOMETRY table that updates the parallel table after INSERT, UPDATE, or DELETE. Or use calculation attribute rules in ArcGIS Pro to do something similar.
Downside: Adds extra tables/complexity that need to be managed. Joining from the ST_GEOMETRY table to the parallel SDO_GEOMETRY table might be slow for large datasets. 
* Parallel materialized view with an SDO_GEOMETRY column.
Downside: A different department would need to create and manage the the materialized view, since GDB data creators/owners don't have CREATE MATERIALIZED VIEW privileges. (It's always a pain when you can't manage your own stuff.)

Does anyone have any thoughts on those options? Or are there any other options that I've overlooked?

Related: Choose spatial column when adding data with more then one spatial column

Bud · Answer

Related post: Idea: Support invisible SDO_GEOMETRY columns

(That title is misleading — the page does have a related discussion...about why I can't just switch to SDO_GEOMETRY.)

Bud · Answer

Yeah. That would be nice.

My organization chose ST_GEOMETRY years ago...and I think it was a perfectly reasonable decision at that time. 
 - ST_GEOMETRY was recommended by Esri.
 - Oracle Spatial wasn’t free then.
 - ST_GEOMETRY is easier for non-SQL experts to use than SDO_GEOMETRY (ST_GEOMETRY is simpler and the docs are easier to parse).

Even if that wasn’t the case, it would take some effort to switch all the FCs over to a new spatial type (and would likely have unforeseen complications). So I think I’ll be stuck with ST_GEOMETRY for the foreseeable future. Despite the fact that we will be upgrading our GIS system soon (in theory, that’d be the time to switch).

So all that considered, I think my only option is to create “helper” SDO_GEOMETRY columns for a few polyline FCs…so that I can do my linear referencing SQL work.

But yes, for organizations that are starting from scratch, or companies that are project-based, or for organizations who are switching database vendors …then I would agree that going with SDO_GEOMETRY would be the right choice.

Related: What spatial type does Esri recommend for Oracle EGDBs?

ClaudioRuschel · Answer

If your spatial queries are performed by ArcGIS tools it would be no problem, because the tool would perform the needed conversions. But If you use SQL with ST_Geometry it seems a real issue to deal. The set of native functions of spatial databases such Oracle Spatial/Graph and PostGIS are much more complete than the ST_Geometry ones. So, if one intends to use SQL, it seems to choose the native spatial database geometry format at the beginning of the project is the best choice.