SDO_GEOMETRY table — Catalog should recognize geometry type (point, line, polygon) when constrained by spatial index parameter

Bud — Wed, 10 May 2023 21:10:10 GMT

ArcGIS Pro 2.6.8; Oracle 18c SDO_GEOMETRY

I have an Oracle table that is not registered with the geodatabase:

CREATE TABLE sdo_geom_line (objectid     NUMBER(*,0), 
                            shape        SDO_GEOMETRY);
CREATE UNIQUE INDEX sdo_geom_line_objectid ON sdo_geom_line (objectid);
INSERT INTO user_sdo_geom_metadata (table_name,column_name,diminfo,srid) 
VALUES (
    'sdo_geom_line',
    'SHAPE',
    sdo_dim_array(
        sdo_dim_element(NULL, 0, 100, 0.001), 
        sdo_dim_element(NULL, 0, 100, 0.001)
    ),
    26917
    );
COMMIT;
CREATE INDEX sdo_geom_line_shape ON sdo_geom_line (shape) INDEXTYPE IS mdsys.spatial_index_v2;

This is what the table looks like in the Catalog pane:

Catalog seems to use a generic rounded rectangle as the icon. Likely because SDO_GEOMETRY columns can accept features with multiple geometry types (point, line, polygon) within the same column. So Catalog doesn't know for sure what kind of data might be stored in the column, and therefore can't display a specific icon for points, lines, or polygons.

Whereas an SDO_GEOMETRY feature class that was created using ArcGIS Pro does get registered with the geodatabase. So Catalog knows what geometry type it is.

That all makes sense.

However, it turns out it is possible to limit an SDO_GEOMETRY column to a certain geometry type, by specifying the geometry type parameter when creating the spatial index:

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.

For example, constrain the SDO_GEOMETRY column to lines:

INDEXTYPE IS mdsys.spatial_index_v2 PARAMETERS('layer_gtype=line');

As expected, I can insert lines successfully:

INSERT INTO sdo_geom_line (objectid, shape) 
    VALUES (1, sdo_geometry('LINESTRING (30 10, 10 30, 40 40)', 26917));
COMMIT;
1 row inserted.

But I can't insert features that are other geometry types, such as points:

INSERT INTO sdo_geom_line (objectid, shape) 
    VALUES (2, sdo_geometry('POINT (30 10)', 26917));

ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
ORA-13375: the layer is of type [2002] while geometry inserted has type [2001]
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 976
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 488

Idea:

Since my SDO_GEOMETRY column is constrained to a specific geometry type (point, line, polygon), and since there is only one geometry column in the table, I'm wondering if Catalog could be enhanced so that it recognizes that constraint, and displays the appropriate symbol (point symbol, line symbol, or polygon).

That would make it easier to determine what the geometry type is when using Catalog (assuming the table name doesn't give us a hint). Otherwise, with the generic rounded rectangle icon, we don't have an easy way to determine the geometry type from within ArcGIS Pro.

Similarly, I wonder if the Catalog Properties could also indicate the geometry type if it is constrained by the index. Currently, the SDO_GEOMETRY column is listed as Any, not as Line.

idea SDO_GEOMETRY table — Catalog should recognize geometry type (point, line, polygon) when constrained by spatial index parameter in ArcGIS Pro Ideas

SDO_GEOMETRY table — Catalog should recognize geometry type (point, line, polygon) when constrained by spatial index parameter