SDO_GEOMETRY FC should constrain geometry type using spatial index parameter

Bud — Thu, 06 Apr 2023 22:30:36 GMT

When an SDO_GEOMETRY feature class is created using Catalog in ArcGIS Pro, a spatial index is automatically created:

CREATE INDEX "INFRASTR"."A7437_IX1" ON "INFRASTR"."A_TEST_SDO_GEOM_LINE_FC" ("SHAPE") 
    INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" ;

Idea:

Could the auto-created spatial indexes be enhanced so that they constrain the column to only accept the correct geometry type? (in this case, lines)

5.1.2 Constraining Data to a Geometry Type
When you create or rebuild a spatial index, you can ensure that all geometries that are in the table or that are inserted later are of a specified geometry type. To constrain the data to a geometry type in this way, use the layer_gtype keyword in the PARAMETERS clause of the CREATE INDEX or ALTER INDEX REBUILD statement, and specify a value from the Geometry Type column of the Valid SDO_GTYPE Values table described in SDO_GTYPE.

Like this:

CREATE INDEX "INFRASTR"."A7437_IX1" ON "INFRASTR"."A_TEST_SDO_GEOM_LINE_FC" ("SHAPE") 
    INDEXTYPE IS "MDSYS"."SPATIAL_INDEX_V2 PARAMETERS('layer_gtype=line');
    --SPATIAL_INDEX_V2 is optional/just a best practice.

Otherwise, it's possible to insert features that aren't lines using SQL.

--ArcGIS isn't designed to handle multiple geometry types in the same geometry column.

INSERT INTO a_test_sdo_geom_line_fc (objectid, shape) 
    VALUES (sde.gdb_util.next_rowid('INFRASTR', 'A_TEST_SDO_GEOM_LINE_FC'), 
            sdo_geometry('POINT (30 10)', 26917));

1 row inserted.

Inserting features using SQL into an un-versioned table is a valid usage pattern, as per the docs: Editing nonversioned geodatabase data in Oracle using SQL.

Constraining the SDO_GEOEMTRY column to the correct FC geometry type would have the following benefits:

Avoid developers accidently inserting the wrong geometry type into the column. While that usage pattern is rare, it seems like a simple constraint like this could go a long way in terms of preventing future headaches. And if properly constrained, is one less thing to troubleshoot when dealing with data problems.
The SDO_GEOMETRY docs say constraining the geometry type can improve spatial query performance in some scenarios.

Could ArcGIS Pro be enhanced so that spatial indexes in SDO_GEOMETRY FCs include the GTYPE parameter -- to constrain the SDO_GEOMETRY geometry type so that it's consistent with the FC geometry type?

idea SDO_GEOMETRY FC should constrain geometry type using spatial index parameter in ArcGIS Enterprise Ideas

SDO_GEOMETRY FC should constrain geometry type using spatial index parameter