ST_GEOMETRY feature class: Add a hidden SDO_GEOMETRY column

Question

I have an SDE.ST_GEOMETRY FC in an Oracle 18c EGDB. I want to use Oracle Spatial functions on the data (calculate polyline midpoint, M-values, dynamic segmentation, etc.).

Ideally, I'd convert the shape column in the table to SDO_GEOMETRY. But that's not possible due to dependencies on the data.

---

I've explored converting from ST_GEOMETRY to SDO_GEOMETRY on-the-fly in a query.

--Source: https://gis.stackexchange.com/a/429145/62572
select
    objectid,
    --Oracle misinterprets the Ms as Zs. So we need to change the GTYPE after-the-fact (from 3006 to 3306).
    sdo_geometry(a.shape.sdo_gtype + 300,
                 a.shape.sdo_srid,
                 a.shape.sdo_point,
                 a.shape.sdo_elem_info,
                 a.shape.sdo_ordinates) as shape
from
    (
    select
        cast(objectid as number(38,0)) as objectid,
        --Oracle doesn't support the 'LINESTRING M' WKT syntax
        sdo_geometry(replace(sde.st_astext(shape),'LINESTRING M','LINESTRING'),sde.st_srid(shape)) shape 
    from
        my_st_geom_fc
    ) a

That query works, but the conversion is complicated and slow. I don't want to do that conversion every time a user queries the data.

---

As an alternative, I'm considering adding a hidden SDO_GEOMETRY column to the table:

1. Using SQL, add an additional geometry column (SDO_GEOMETRY) called SDO_GEOM.
   • The column wouldn't be registered in the system tables as the FC's true shape column. It would just be an extra column; hopefully ArcGIS would just ignore it.
2. I'd create a trigger that would automatically populate the column after an INSERT, or after an UPDATE to the ST_GEOMETRY column. I'd use the logic in the query mentioned above. So the two columns would always be in sync.

---

Would that extra geometry column cause any problems in the enterprise geodatabase (10.7.1) or in ArcGIS Pro (2.9.2)?

Related: Where do the docs say we can only have a single geometry column per table?

Answer 1

Unfortunately, creating a invisible SDO_GEOMETRY column doesn't seem to be supported by Oracle 18c:

ALTER TABLE ST_GEOM_ROADS_SDO_INVIS 
        ADD SDO_SHP SDO_GEOMETRY INVISIBLE;

ORA-54041: Visibility of an abstract data type column cannot be changed.
54041. 0000 - "Visibility of an abstract data type column cannot be changed."
*Cause:  An attempt was made to alter the visibility of an abstract data type column.
*Action:  Specify the column visibility change with a supported data type.

db<>fiddle

---

Idea: Support invisible SDO_GEOMETRY columns

Support invisible abstract datatypes (such as invisible SDO_GEOMETRY)