Community

Find multi-part features using SQL (ST_GEOMETRY)

577
2
06-25-2022 08:41 PM
Bud
by
Notable Contributor
‎06-25-2022 08:41 PM

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)

 

 

Tags (1)
0 Kudos
2 Replies
Bud
by
Notable Contributor
‎06-25-2022 08:41 PM

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

 

Bud
by
Notable Contributor
‎03-02-2023 06:53 AM

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

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

Additional information

  1. Not reproducible in SQL Server (i.e. "SHAPE.STLength() > 400"
  2. Similar to BUG-000109287
  3. 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)
  4. 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.
0 Kudos