Select features that have blank shapes
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 (
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).
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;
- 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.
Related:
Oracle Docs: 2.1.1 Null Objects and Attributes
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
