How To: Insert a NULL or Empty St_geometry Attribute in Oracle

Summary

Instructions provided describe how to use SQL to insert NULL or empty st_geometry attributes in Oracle.

Procedure

Some applications and data models require the ability to insert rows into tables with st_geometry attributes where the geometry is NULL or the geometry is empty.

A NULL geometry indicates no value or unknown value, which is acceptable if the st_geometry attribute allows NULL values. If the attribute is defined with a NOT NULL constraint, the database will not allow a NULL value for the attribute to be inserted or set to NULL during an update.

An empty geometry represents an st_geometry which is NOT NULL, but all the properties of the geometry are not set and the geometry itself is NULL. Empty geometries are not spatially indexed nor returned when executing spatial operators. Empty geometries are necessary in the case when the st_geometry attribute is defined as NOT NULL and the application must set a value for the geometry, but the coordinates are unknown.

The following example demonstrates creating a table in SQL*Plus with an st_geometry attribute and inserting four rows: one row with a NULL geometry, two rows with empty geometries, and a fourth row with a geometry.

Create a table with two attributes, a varchar, and st_geometry attributes.

Code:
SQL> CREATE TABLE geom_test 
  2  (geom_description  VARCHAR2(64),
  3   shape             sde.st_geometry);

Table created.

Insert four rows: the first row with a NULL geometry, the second and third rows with an empty geometry using two methods, and the fourth row with a valid geometry (a point).

Code:
SQL> INSERT INTO geom_test VALUES
  2  ('A NULL geometry',NULL);

1 row created.

SQL> INSERT INTO geom_test VALUES
  2  ('An empty geometry',
  3  sde.st_geometry('point empty',0));

1 row created.

SQL> INSERT INTO geom_test VALUES
  2  ('An empty geometry',
  3  sde.st_geometry(0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL));

1 row created.

SQL> INSERT INTO geom_test VALUES
  2  ('A geometry', 
  3  sde.st_geometry('point (10 10)',0));

1 row created.

Validate the results of the rows inserted by selecting the count of the number of rows that meet each condition.

Code:
SQL> SELECT COUNT(*)
  2  FROM geom_test
  3  WHERE shape IS NULL;

  COUNT(*)
----------
         1

SQL> SELECT COUNT(*)
  2  FROM geom_test
  3  WHERE sde.st_isempty(shape) = 1;

  COUNT(*)
----------
         2

SQL> SELECT COUNT(*)
  2  FROM geom_test
  3  WHERE sde.st_isempty(shape) = 0;

  COUNT(*)
----------
         1

Article ID:000010466

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Insert a NULL or empty st_geometry attribute in Oracle

Summary

Procedure

Discover more on this topic