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.
Insert 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 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 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.
See What is an ObjectID? for more information.
Edit 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.