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:
- 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.
- 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)?