FAQ: How does ArcSDE represent NULL and nil geometries in Oracle Spatial?
How does ArcSDE represent NULL and nil geometries in Oracle Spatial?
NULL geometry is geometry that does not exist. Nil geometry exists, but it is empty. Think of geometry as a box that holds geometric elements such as points or polygon rings. If the geometry is nil, then the box is empty. If the geometry is NULL, there is no box.
Nil geometries are useful in various ways. For example, the answer to a geometric intersection query of two disjoint geometries would be an empty geometry. This is a valid answer and the nil geometry is a valid geometry. In another example, if a query encountered some sort of error, it might return a NULL geometry along with an error code, thereby indicating with the NULL that no answer was returned.
ArcSDE represents nil geometry in Oracle Spatial as an SDO_GEOMETRY object with all five attributes of the SDO_GEOMETRY object type set to NULL. ArcSDE represents a NULL geometry by setting the SDO_GEOMETRY column to NULL.
Because the nil representation of a geometry is not the same as NULL, in some ways they are treated differently by Oracle. Nil geometries can be stored in SDO_GEOMETRY NOT NULL columns without violating the NOT NULL constraint. A database query for IS NULL would return NULL geometries but not nil geometries. In other cases, Oracle treats NULL and nil geometry similarly. A table containing regular geometries, NULL geometries, and nil geometries can be spatially indexed by the Oracle Spatial domain spatial index. Oracle Spatial geometry validation does not differentiate between NULL and nil geometries, returning 'NULL' when it encounters either one.
Fetching geometries using the ArcSDE C and Java APIs returns two bits of information to the client application: a NULL indicator and the geometry, such as an SE_SHAPE object. If the NULL indicator is 'true', then the program knows that the geometry is NULL and should not try to interpret the content of the geometry. If the NULL indicator is 'false' and if there are no geometric elements in the geometry, then the geometry is nil. One way to find out if the fetched geometry is empty is to use the function SE_shape_is_nil.