Find multi-part features using SQL (ST_GEOMETRY)

Question

How can I query for multi-part features in an SDE.ST_GEOMETRY FC using SQL? (in the Select By Attributes window or in a definition query)

BB1234 · Answer

BUG-000150273

Status
In Review

Synopsis
ST_Geometry functions applied to the Shape field in Select By Attributes return the error message, "Error 000358: Invalid expression" if the feature class is registered as traditional versioned.

Environment

Reproduced in: ArcGIS Pro 3.0 or ArcGIS Pro 2.9.3 or ArcMap 10.8.2 or ArcMap 10.7.1
Geodatabase version 10.9.2 or 10.7.1
Oracle 18c

Steps to Reproduce

* Create a line feature class
* Add some features to feature class
* Use Select by Attributes to query fc Where: "sde.st_length(shape) > 400" (use whatever value makes sense for the length of the lines)
* The selection will work
* Register the feature class as versioned
* Repeat the Select by Attribute query from step 3 - you will receive an error "Error 000358: Invalid expression"

Additional information

* Not reproducible in SQL Server (i.e. "SHAPE.STLength() > 400"
* Similar to BUG-000109287
* Reproducible with other queries (i.e. query customer was using "abs(sde.st_maxm(shape) - sde.st_length(shape))  > 1" - find polylines where the maximum M-value doesn't equal the length)
* Error in ArcMap is: [ORA-00904: "SHAPE": invalid identifier]

Workarounds

* Place the expression in subquery. For example:

OBJECTID IN

(SELECT

OBJECTID

FROM

USER1.ACTIVE_TRANSPORTATION

where

sde.st_length(shape) > 400

)

* [OR]
Use calculate geometry or calculate field to populate new fields with the values. Then, apply any further ST_Geometry functions on the fields instead of using the shape field.

BB1234 · Answer

Use this WHERE clause in a definition query or the Select By Attributes window:

sde.st_numgeometries(shape) > 1

There is a known bug in versioned Oracle SDE.ST_GEOMETRY FCs. The above WHERE clause will throw an error: ORA-00904 Invalid Identifier "SHAPE".

* Defect #: BUG-000150273
"ST_GEOMETRY functions used in Select By Attributes tool on Shape field generate error "ORA-00904 Invalid Identifier "SHAPE""
* Esri Canada case #: 03080875

Workaround:

Use a subquery.

objectid in 
    (select
        objectid
    from
        my_owner.my_fc
    where
        sde.st_numgeometries(shape) > 1)

That bug only happens for SDE.ST_GEOMETRY.

Whereas if the FC was SDO_GEOMETRY, then spatial functions would work fine — even if the FC was versioned.

sdo_util.getnumelem(shape) > 1