ArcGIS Server Banner

An overview of working with ST_Geometry storage using SQL

An overview of working with ST_Geometry storage using SQL
Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
Note: This topic was updated for 9.3.1.

For geodatabases implemented in a database management system (DBMS) using DBMS data types and table formats, you can use the DBMS’s own Structured Query Language (SQL) to work with the information stored in the database. SQL is a database language that supports data definition and data manipulation commands.

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

You can issue SELECT statements against any of the supported DBMS types for geodatabases, as well as against file-based data sources. The topics About building an SQL expression and SQL reference provide you with information about building SELECT statements for different data sources.

Be aware, though, that 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 database without taking this extra functionality into account—for example, issuing INSERT statements to add records to a 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.

In general, the following guidelines apply when using SQL to modify information in the geodatabase:

The preceding guidelines apply to any attribute, spatial or nonspatial. This book of the help focuses on using SQL for spatial selections and altering data in an ST_Geometry spatial column.

The ST_Geometry storage type

Geodatabases in DB2 and Informix use the ST_Geometry SQL data type. You can also use the ST_Geometry spatial data type in your geodatabases stored in Oracle or PostgreSQL. This data type can be used within the geodatabase plus it provides SQL access to feature class geometry for third-party applications.

ST_Geometry implements the OGC and ISO SQL Multimedia Specification for Spatial. The OGC reference is OpenGIS Implementation Specification for Geographic information - Simple feature access - Part 2: SQL option. The ISO reference is ISO/IEC 13249-3 SQL multimedia and application packages - Part 3: Spatial.

This portion of the help system primarily addresses using SQL with the ST_Geometry type as implemented in Oracle and PostgreSQL. For example, all of the functions described in this portion of the help are for the ST_Geometry implementation in Oracle and PostgreSQL. Though some information is provided on using SQL with DB2 and Informix, you should consult the IBM DB2 and Informix documentation for more complete information on using SQL with the ST_Geometry type in these databases. For using SQL with other spatial types, such as Oracle Spatial and PostGIS, consult the documentation for those products.

Using SQL functions with ST_Geometry in Oracle and PostgreSQL

To access, analyze, and manipulate data using SQL, you need to utilize the functions installed with the ArcSDE component of ArcGIS Server Enterprise.

These functions can be grouped based on their use.

See Also

  • An overview of SQL functions used with ST_Geometry types