ST_Geometry: Function-based spatial indexes

439
1
05-10-2022 09:28 AM
Status: Open
Bud
by
Notable Contributor

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?

1 Comment
Bud
by

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)