0

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?

6
  • Wow, just soooo unsupported. Even if it did work (which I doubt), there's no saying if it will continue to work with the next DLL patch. Why not create a parallel materialized view instead?
    – Vince
    Commented Apr 19, 2022 at 1:55
  • @Vince Good idea about the materialized view. I mentioned the MV idea, and other ideas in this Esri Community post: ST_GEOMETRY FC: Options for using Oracle Spatial functions.
    – User1974
    Commented May 1, 2022 at 3:13
  • @Vince I wonder if an INVISIBLE column might be an option. I would explicitly select the column in queries. And yet, it'd be invisible to ArcGIS, so it wouldn't cause problems. Invisible Columns in Oracle Database. Thoughts?
    – User1974
    Commented May 19, 2022 at 19:13
  • How many INVISIBLE columns do you think have been tested in Redlands? I expect that will fall in the "Good luck with that" support class.
    – Vince
    Commented May 19, 2022 at 19:29
  • My thoughts are: invisible columns were designed to be hidden from existing applications: "Making new columns invisible means they won't be seen by any existing applications, but can still be referenced by any new applications, making the migration of applications much simpler"(link). So that seems promising. ArcGIS would have no way of finding an invisible column, not even with a SELECT *. So, if ArcGIS has no way of seeing an invisible column, then the column couldn't break ArcGIS.
    – User1974
    Commented May 20, 2022 at 6:15

1 Answer 1

0

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)

Your Answer

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

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