topic Re: Difference between ST_IsClosed vs. ST_IsRing in Geodatabase Questions

Difference between ST_IsClosed vs. ST_IsRing

Bud — Wed, 01 Mar 2023 16:05:45 GMT

Oracle 18c; 10.7.1 Enterprise Geodatabase

What's the difference between ST_IsClosed vs. ST_IsRing in SDE.ST_Geometry?

For example, I want to query for lines that are near-circles like this:
(in other words, I want to select roundabouts)

I don't want to select lines that close in on themselves midway like this:

I tested ST_IsClosed and ST_IsRing. Both of the lines in the screenshots above were identified as closed and as rings. So it's not clear to me what the difference between the two functions is.

 objectid in (
    select
        objectid
    from
        roads
    where
        sde.st_isClosed(sde.st_geometryn(shape,1)) = 1
    )

 objectid in (
    select
        objectid
    from
        roads
    where
        sde.st_isRing(sde.st_geometryn(shape,1)) = 1
    )

Reason for wrapping the WHERE clause in a subquery:

There is an Esri bug where ST_GEOMETRY functions can’t be used in a SQL Expression (definition query, attribute table, etc.) when the FC is versioned. Wrapping the WHERE clause in a subquery lets us work around that bug.
Esri Case #03080875 - ST_GEOMETRY functions used in Select By Attributes tool on Shape field generate error "ORA-00904 Invalid Identifier "SHAPE"
BUG-000150273
https://community.esri.com/t5/arcgis-pro-questions/find-multi-part-features-using-sql-st-geometry/m-p/1186467/highlight/true#M56563

Re: Difference between ST_IsClosed vs. ST_IsRing

Bud — Wed, 01 Mar 2023 16:09:19 GMT

It turns out the cul-de-sac in the second example is actually a multi-part feature:

Re: Difference between ST_IsClosed vs. ST_IsRing

Bud — Wed, 01 Mar 2023 16:44:46 GMT

I think the answer is: I should be using isRing. The word "Ring" is self-explanatory.

I was originally thrown off by the fact that some of my lines are multi-parts. So, an individual part could technically be a ring, even though the feature as a whole didn't appear to be a ring.
So, both of my example features were being identified as closed and as rings (my query only looked at the first part of multi-part features; in my examples, the first part was a ring).

I've accounted for the multi-part quirk, and other quirks, in the following query:

objectid in (
    select
        objectid
    from
        roads
    where
        sde.st_numgeometries(shape) = 1   --Needs to be evaluated before isRing. Otherwise, isRing throws an error since it can't be used on multi-part features.
        and sde.st_isRing(shape) = 1
        and name not like ('% CT')        --Some courts are literally just a loop with no straight portion. They are technically legitimate rings. So I'll omit them since I'm specifically looking for roundabouts, not courts.
        --and ownership = 'ABC'
        and sde.st_length(shape) <= 200   --If a road is longer than 200m, then it probably isn't a roundabout.
                                          --In the future, if needed, I could omit roads with this text in the name (similar to what I did with CT): PL, CIRCLE, DR, TRAIL, CRES, LOOP, SQUARE. 
                                          --However, right now, that doesn’t seem to be necessary. Those road types get successfully excluded via the “must be shorter than 200m” criteria.
    )

Re: Difference between ST_IsClosed vs. ST_IsRing

Bud — Wed, 01 Mar 2023 16:01:58 GMT

Is the following correct?

All rings are closed, but not all closed are rings.

Re: Difference between ST_IsClosed vs. ST_IsRing

JoshuaBixby — Thu, 02 Mar 2023 00:18:14 GMT

According to the documentation you link to, ST_IsRing is a subset of ST_IsClosed that is also ST_IsSimple. So, a ring must be closed but not all closed geometries have to be rings.