topic Find multi-part features using SQL (ST_GEOMETRY) in ArcGIS Pro Questions

Find multi-part features using SQL (ST_GEOMETRY)

Bud — Sun, 26 Jun 2022 03:56:15 GMT

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)

Re: Find multi-part features using SQL (ST_GEOEMTRY)

Bud — Sun, 26 Jun 2022 03:55:30 GMT

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

Re: Find multi-part features using SQL (ST_GEOMETRY)

Bud — Thu, 02 Mar 2023 14:53:29 GMT

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.