A quick tour of using SQL with ST_Geometry

You can use the database management system's (DBMS) Structured Query Language (SQL), data types, and table formats to work with the information stored in a geodatabase or database where the ST_Geometry type is installed. SQL is a database language that supports data definition and data manipulation commands.

Accessing the data via SQL allows external applications to work with the tabular data managed by the geodatabase or database. These external applications can be nonspatial database applications or custom spatial applications developed in an environment other than ArcObjects.

When inserting data to or editing data in a geodatabase or database using SQL, issue a COMMIT or ROLLBACK statement after the SQL statement has been executed to be sure changes are either committed to the database or undone. This helps prevent locks from being held on the rows, pages, or tables you are editing.

Inserting ST_Geometry data using SQL

You can use SQL to insert spatial data to a database or geodatabase table that has an ST_Geometry column. You use ST_Geometry constructor functions to insert specific geometry types. You can also specify that the output of certain spatial operation functions be output to an existing table.

When you insert a geometry to a table using SQL, be aware of the following:

• You must specify a valid spatial reference ID (SRID).
• To continue using the table with ArcGIS, the field being used as the ObjectID cannot be null.

Spatial reference IDs

The SRID you specify when inserting a geometry to a table in Oracle that uses the ST_Geometry spatial type must be in the ST_SPATIAL_REFERENCES table and have a matching record in the SDE.SPATIAL_REFERENCES table. The SRID you specify when inserting a geometry to a table in PostgreSQL that uses the ST_Geometry spatial type must be in the public.sde_spatial_references table. Beginning with ArcGIS 10.1, these tables are prepopulated with spatial references and SRIDs.

The SRID you specify when inserting a geometry to a table in SQLite that uses the ST_Geometry spatial type (a geometryblob) must be in the st_spatial_reference_systems table.

If you need to use a custom spatial reference that is not present in the table, the easiest way to do this is to use ArcGIS for Desktop to load or create a feature class that has the spatial reference values you want. Be sure the feature class you create is using ST_Geometry storage. This creates a record in the SDE.SPATIAL_REFERENCES and ST_SPATIAL_REFERENCES table in Oracle, a record in the public.sde_spatial_references table in PostgreSQL, or a record in the st_aux_spatial_reference_systems_table in SQLite.

In geodatabases, you can query the LAYERS (Oracle) or sde_layers (PostgreSQL) table to discover the SRID assigned to the spatial table. You could then use that SRID when you create spatial tables and insert data using SQL.

Alternatively, you can add a spatial reference to the ST_SPATIAL_REFERENCES or sde_spatial_references table using SQL. See Creating spatial references using SQL for more information.

For an explanation of SRIDs and spatial reference systems, see What is an SRID? and Spatial references.

ObjectIDs

For ArcGIS to query data, it requires that the table contain a unique identifier field.

Feature classes created with ArcGIS always have an ObjectID field that is used as the identifier field. When inserting records to the feature class using ArcGIS, a unique value is always inserted to the ObjectID field. The ObjectID field in a geodatabase table is maintained by ArcGIS. The ObjectID field in a database table created from ArcGIS is maintained by the DBMS.

When you insert records to a geodatabase table using SQL, you must use the Next_RowID function to get and insert a valid ObjectID value. When you use SQL to insert records to a database table that was created in ArcGIS, the DBMS will populate the ObjectID field with a value.

Database tables you create outside of ArcGIS must have a field (or set of fields) that ArcGIS can use as an ObjectID. If you use your database's native autoincrementing data type for the ID field in your table, this field will be populated by the DBMS when you insert a record using SQL. If you are manually maintaining the values in your unique identifier field, be sure to provide a unique value for the ID when editing the table from SQL.

Note:

You cannot publish data from tables that have a user-maintained unique identifier field.

See What is an ObjectID? for more information.

Editing ST_Geometry data using SQL

SQL edits to existing records often affect the nonspatial attributes stored in the table; however, you can edit the data in the ST_Geometry column using constructor functions inside SQL UPDATE statements.

If the data is stored in a geodatabase, there are additional guidelines you should follow when editing with SQL:

• Do not update records using SQL if the data has been versioned unless you use SQL in conjunction with a versioned view.
• Do not modify any attributes that affect other objects in the database that participate in geodatabase behavior such as relationship classes, feature-linked annotation, topology, or networks.

Caution:

Using SQL to access the geodatabase bypasses geodatabase functionality, such as versioning, topology, networks, terrains, feature-linked annotation, or other class or workspace extensions. It may be possible to use DBMS features such as triggers and stored procedures to maintain the relationships between tables needed for certain geodatabase functionality. However, executing SQL commands against the geodatabase without taking this extra functionality into account—for example, issuing INSERT statements to add records to a versioned business table or adding a column to an existing feature class—will circumvent geodatabase functionality and possibly corrupt the relationships between data in your geodatabase.