Select to view content in your preferred language

Select features that have blank shapes

1136
8
11-07-2023 03:49 PM
Labels (1)
Bud
by
Honored Contributor

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:

  1. 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)".
  2. Has shape and geometry, but AREA and NUMPTS are zero (geometry is NIL/zero vertex).
  3. 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).
  4. 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:

Bud_0-1699401530063.png


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.


Related:

Oracle Docs: 2.1.1 Null Objects and Attributes

8 Replies
Bud
by
Honored Contributor

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.

0 Kudos
VinceAngelo
Esri Esteemed Contributor

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

Bud
by
Honored Contributor

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.

0 Kudos
Bud
by
Honored Contributor

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

Bud_0-1699895709629.png

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:

Bud_1-1699895835726.png

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

 

0 Kudos
ChristopherCounsell
MVP Regular Contributor

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?

0 Kudos
Bud
by
Honored Contributor

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

0 Kudos
MarceloMarques
Esri Regular Contributor

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

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
0 Kudos
Bud
by
Honored Contributor

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.