ST_Geometry: Function-based spatial indexes

Bud

With Oracle's SDO_GEOMETRTY, we can create function-based spatial indexes that vastly improve performance for computed geometry columns.

Could Esri add that functionality for SDE.ST_GEOMETRY in Oracle?

Bud · ‎05-10-2022

For what it's worth, I can in theory create a ST_GEOMETRY function-based spatial index:

create index atn_startpoint_shape on infrastr.active_transportation 
    (sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(sde.st_geometryn(shape,1))),300125)) 
    indextype is SDE.ST_SPATIAL_INDEX parameters ('ST_GRIDS=320 ST_SRID=300125');

Index ATN_STARTPOINT_SHAPE created.

But there doesn't seem to be a way to utilize that index in the map in ArcGIS Pro. For example, I wouldn't insert a row into USER_SDO_GEOM_METADATA for ST_GEOMETRY spatial index, since that's designed for SDO_GEOMETRY columns only.

When I try to test a view that would use the index in ArcMap...

create or replace view atn_startpoint_vw as (
select
    objectid,
    sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(sde.st_geometryn(shape,1))),300125) shape
from
    infrastr.active_transportation
where
    sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(sde.st_geometryn(shape,1))),300125) is not null
)

...it just crashes.

Similar to this issue: ArcGIS Pro crashes when copying Oracle view to FGDB.

Equivalent to USER_SDO_GEOM_METADATA for SDE.ST_GEOMETRY? (function-based spatial indexes)