idea ST_Geometry — Don't allow null geometry in Data Management Ideas

ST_Geometry — Don't allow null geometry

Bud — Sat, 18 Nov 2023 05:40:50 GMT

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

If I understand correctly, a feature can either have:

Null SHAPE field, or
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?

Re: ST_Geometry — Don't allow empty geometry (not to be confused with null SHAPE column)

VinceAngelo — Fri, 27 Oct 2023 20:48:19 GMT

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

Re: ST_Geometry — Don't allow empty geometry

Bud — Tue, 31 Oct 2023 18:48:44 GMT

@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).

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.

Re: ST_Geometry — Don't allow empty geometry

VinceAngelo — Wed, 01 Nov 2023 03:07:41 GMT

I don't use regularly use SDE.ST_GEOMETRY.

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

- V

Re: ST_Geometry — Don't allow empty geometry

Bud — Sat, 18 Nov 2023 05:44:47 GMT

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).

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.