topic Re: Select features that have blank shapes in Data Management Questions

Select features that have blank shapes

Bud — Tue, 05 Dec 2023 15:07:40 GMT

ArcGIS Pro 2.6.8; Oracle 18c; 10.7.1 EGDB; SDE.ST_GEOMETRY

Background:

There seem to be four different states a feature can be in:

Has shape and geometry. Normal polygon; visible in the map.

Geometry's points blob can be considered a list of coordinates like "(1,2 3,4 5,6)".

Has shape and geometry, but AREA and NUMPTS are zero (geometry is NIL/zero vertex).

Geometry's points blob can be considered an empty list of coordinates like "( , )".
Create NIL (zero vertex) geometry

Has shape, but no geometry.

Geometry is null.
Esri Support thinks these features are valid.
- Esri Case #03477586 - Are these features valid? Features have SHAPE but geometry is null.
- It sounds like ST_GEOMETRY SQL functions have been designed/fixed to handle such features (Support provided details about the bug that would suggest this):
  BUG-000090937: Invalid (NULL) LOB locator (points) errors are returned when running the ST_Geometry function against geometries without binary large object (BLOB) points.
- It is possible to INSERT features that have a null geometry using SQL. The ST_GEOMETRY db datatype does not prevent null geometries from being inserted.
```
insert into infrastr.inf_record_sp (objectid, shape) values (
sde.gdb_util.next_rowid('INFRASTR', 'INF_RECORD_SP'), 
SDE.ST_GEOMETRY(16,0,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,300046,NULL));
commit;
```
  Similarly, it’s possible to import such features from an XML Workspace Document; the geometry remains in state 3. However, other operations in ArcGIS Pro will automatically convert state 3 features to state 2, such as when editing in the attribute table (even when editing a non-spatial field).

The shape field is completely null.

Geometry is null.
Example: If a user hits “Click to add new row” in the attribute table, then the shape will be null unless the user edits/creates a shape.

Summary Query:

Question:

What is the correct way to query for features that have blank shapes? In other words, I want to query for states 2, 3, and 4 (but not state 1). A simple "WHERE SHAPE IS NULL" query won't do what I want; it won't select states 2 and 3 since SHAPE isn't null.

Oracle Docs: 2.1.1 Null Objects and Attributes

Re: Select features that have blank shapes

Bud — Fri, 01 Dec 2023 16:11:31 GMT

This seems to work. I’m not sure if it’s the best way or not.

where 
    (sde.st_isempty(shape) = 1 
     or shape is null)

Edit:

Or, I could count the vertices. And replace null vertex counts with 0 using the NVL function (Oracle-specific) or COALESCE (database agnostic).

where
    nvl(sde.st_numpoints(a.shape), 0) = 0

The benefit of that approach is that it's shorter (only one expression instead of two). But I don't think it's as obvious what it does, so I might stick with the first approach.

Re: Select features that have no spatial information (will not display in the map)

VinceAngelo — Wed, 08 Nov 2023 03:17:45 GMT

This Community is not really the place to find out what Esri Inc recommends. Most staffers here don't have the privilege to speak for the company, so explicitly asking for that is going to suppress responses from non-employees and employees both (probably not what you're shooting for here).

- V

Re: Select features that have no spatial information (will not display in the map)

Bud — Wed, 08 Nov 2023 04:30:45 GMT

Ok, I removed that part. If you want, you can delete your reply, now that it no longer applies. And I can delete this one too.

Re: Select features that have blank shapes

Bud — Sat, 18 Nov 2023 13:46:39 GMT

In my case, I have an issue in my data where selecting WHERE SHAPE IS NULL actually selects rows where shape is not null.

It's a surprising issue; Oracle isn't querying the data correctly due to the SHAPE values being broken.

Esri Case #03477586
BUG-000115133: Incorrect result returned from query "shape is null" after migrating storage from sdelob to st_geometry and transferring Feature Class using Oracle datapump utility.

Best guess is:
My organization moved from SDEBINARY to ST_GEOMETRY as part of an old upgrade project from 9.3.1 to 10.x (7-8 years ago). The transition from SDEBINARY to ST_GEOMETRY must have broken the SHAPES. We don't have the SHAPE IS NULL issue in rows that were created after that migration; we only have the issue in old rows.
With this particular FC, we wouldn't have needed to update the rows using a field calculation, import/export, Catalog copy/paste, etc. Doing operations like that would automatically fix the shapes. Even a simple field calculation on a non-spatial field would automatically fix the shapes. But we likely didn't need to do that for this FC (whereas with other FCs, we do lots of field calculations on the entire table). So that's likely why we didn't catch the issue until now. Further, querying for WHERE SHAPE IS NULL is relatively rare.

So, in this particular scenario, I want to avoid SHAPE IS NULL, since that doesn't work properly (at least not until I fix all our old data). I'll use isEmpty to find null shapes instead:

where
    (sde.st_isempty(a.shape) = 1 
    or sde.st_isempty(a.shape) is null)

For my notes, here is a screenshot of my testing data -- with state #3 split out into #3a and #3b:

select
    a.objectid,
    a.comment2 as state_num,
    a.shape,
    (a.shape).points as geometry,
    sde.st_area(a.shape) as area,
    sde.st_isempty(a.shape) as is_empty,
    a.comment3,
    case 
        when b.objectid is not null then 'NOT NULL'
        when b.objectid is     null then 'NULL'
    end as null_flag_join, --I used a join for this since a regular case statement on table "a" doesn't produce the same result.    
    case 
        when a.shape is not null then 'NOT NULL'
        when a.shape is     null then 'NULL'
    end as null_flag_case,
    sde.st_numpoints(a.shape) as num_points
from
    infrastr.inf_record_sp a
left join
    (select objectid from infrastr.inf_record_sp where shape is not null) b
    on a.objectid = b.objectid
where
    a.comment2 in ('1','2','3a','3b','4')
order by
    state_num

Re: Select features that have blank shapes

ChristopherCounsell — Sun, 19 Nov 2023 01:32:16 GMT

If you upgrade to ArcGIS Pro 2.7 the Check Geometry tool supports enterprise geodatabases.

https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/check-geometry.htm

Running this tool will tell you if the the feature has null geometry when the the feature has no geometry or the shape field is null.

It may be that your scenario is more complicated than this but worth having an attempt?

Re: Select features that have blank shapes

Bud — Tue, 28 Nov 2023 13:40:39 GMT

@ChristopherCounsell
The check geometry tool doesn’t support ESRI’s ST_Geometry datatype (for Oracle), which is the datatype I use.

Re: Select features that have blank shapes

MarceloMarques — Sun, 19 Nov 2023 19:30:17 GMT

ArcGIS Data Reviewer checks—ArcGIS Pro | Documentation
Methods to implement automated review—ArcGIS Pro | Documentation
Reviewer rules in a geodatabase (attribute rule-based workflows)

Check Geometry—ArcGIS Pro | Documentation

Invalid Geometry Check Explained… (esri.com)

The check returns features that meet one of the following conditions:

1. Nothing

2. Empty

3. Has an empty envelope

4. Not simple

Re: Select features that have blank shapes

Bud — Tue, 05 Dec 2023 15:09:47 GMT

Regarding Esri Case 03477586 - Are these features valid? Features have SHAPE but geometry is null:

Support submitted ENH-000163451: Provide confirmation on valid ST_Geometry configurations.