ST_Geometry — Don't allow null geometry

692
4
10-27-2023 12:33 PM
Status: Open
Labels (1)
Bud
by
Notable Contributor

ArcGIS Pro 2.6.8, Oracle 18c 10.7.1 EGDB; SDE.ST_Geometry


If I understand correctly, a feature can either have:

  1. Null SHAPE field, or
  2. SHAPE field is not null, only the geometry is null

It is confusing when we need to account for both A and B in SQL queries. I can't think of a use case where B is wanted.

Could the SDE.ST_Geometry database datatype be enhanced so that B is prevented/not allowed?


Related: Attribute Table — Indicate if SHAPE field is null vs. object's geometry is empty

4 Comments
VinceAngelo

A NIL (zero vertex) geometry is a valid shape type. It is required for some operations (e.g., the result of the intersection of two disjoint features).   The shapefile specification permits Nil paired with any other one geometry type as the supported types in shapefiles.

How would it be an "enhancement" to remove this capability?

- V

Bud
by

@VinceAngelo To clarify:

I think I understand your point about Nil geometry (row #2 below).

However, I'm referring to cases where the geometry is truly null/no value (row #3 below).

Bud_0-1698778016602.png

select
    objectid,
    shape,
    (shape).points as geometry,
    sde.st_area(shape) as area,
    sde.st_isempty(shape) as is_empty,
    comment3,
    (shape).entity,
    sde.st_entity(shape),
    (shape).numpts
from
    infrastr.inf_record_sp

Let me know if I've misunderstood something.

VinceAngelo

I don't use regularly use SDE.ST_GEOMETRY.

How did you create those rows, because at least one of them seems malformed.

- V

Bud
by

Regarding,"How did you create those rows, because at least one of them seems malformed."
The features were all created using OOTB functionality in ArcMap or ArcGIS Pro. We don't create features using code (Python, SQL, etc.). Beyond that, I don't have information about how specific features were formed.

 

A follow-up point regarding the original idea: The ST_GEOMETRY db datatype allows a feature to be constructed with a null geometry using SQL:

insert into infrastr.inf_record_sp (objectid, shape) values (
    sde.gdb_util.next_rowid('MY_OWNER', 'MY_TABLE'),                     --objectid
    sde.st_geometry(16, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 300046, null)); --shape
commit;   

I can query the new row to see what it looks like. It is in state #3 (geometry is null).

Bud_0-1699030194686.png


To me, the ST_GEOMETRY db datatype should not allow a feature to be constructed with a null geometry using SQL. Maybe that's a bug.
I wonder if there should be a constraint: If the shape is not null, then don't allow the geometry to be null when creating features.