An overview of working with a geodatabase using SQL
Release 9.2 Last modified June 27, 2008	Print all topics in : "Working with geodatabases using SQL"

For geodatabases implemented in a relational database management system (DBMS) using DBMS data types and table formats, the DBMS’s own Structured Query Language (SQL) may be used 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. Be aware, though, that SQL access to the geodatabase bypasses geodatabase functionality, such as topology, networks, terrains, 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—will circumvent geodatabase functionality and possibly corrupt the relationships between data in your geodatabase.

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. 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. For example, all of the functions described in this portion of the help are for the ST_Geometry implementation in Oracle. 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, consult the documentation for those products.

Using SQL functions with ST_Geometry in Oracle

To access, analyze, and manipulate data using SQL, you need to utilize the functions installed with the ArcSDE component of ArcGIS Server Enterprise edition.
These functions can be grouped based on their use.

• Functions that test spatial relationships
  
These functions take geometries as input and determine if a specific relationship exists between the geometries. If the conditions of spatial relationship are met, these functions return 1 or t for TRUE. If the conditions are not met (no relationship exists), these functions return 0 or f for FALSE.

See Spatial relationships for a description of each of these functions.


• Functions that perform spatial operations
  
These functions take spatial data, perform analyses on it, and return new spatial data. For a description of these functions, see Spatial operations.


• Functions that return properties of a geometry
  
There are a number of functions that take a geometry or geometries as input and return specific information about them.
Some of these functions actually check to see if a feature or features meet certain criteria. If the geometry meets the criteria, the function returns 1 or t for TRUE. If the geometry does not meet the critera, it returns 0 or f for FALSE. These functions include the following:

ST_Is3d
ST_IsClosed
ST_IsEmpty
ST_IsMeasured
ST_IsRing
ST_IsSimple
ST_OrderingEquals


• Functions that create spatial data or perform spatial transformations
  
These functions take one type of geometry or a description of geometry and return a geometry of a different type. One of these functions, ST_Transform, actually alters the spatial reference of a geometry in Oracle from one spatial reference in the geographic datum to another spatial reference in the same geographic datum.

The functions to create geometry from another type of geometry or a text description are listed in the following table:



ST_AsBinary	ST_LineFromWKB	ST_MultiCurve	ST_PolyFromText
ST_AsText	ST_LineString	ST_MultiLineString	ST_PolyFromWKB
ST_Curve	ST_MLineFromText	ST_MultiPoint	ST_Polygon
ST_GeomCollection	ST_MLineFromWKB	ST_MultiPolygon	ST_Surface
ST_Geometry	ST_MPointFromText	ST_MultiSurface	ST_Transform
ST_GeomFromText	ST_MPointFromWKB	ST_Point	ST_WKBToSQL
ST_GeomFromWKB	ST_MPolyFromText	ST_PointFromText	ST_WKTToSQL
ST_LineFromText	ST_MPolyFromWKB	ST_PointFromWKB

Please visit the Feedback page to comment or give suggestions on ArcGIS Desktop Help.
Copyright © Environmental Systems Research Institute, Inc.