A quick tour of using SQL with the ST_Geometry and ST_Raster types

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. 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 work with the tabular data managed by the geodatabase. These external applications can 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 a SQL expression and SQL reference provide you with information about building SELECT statements for different data sources.

Caution:

Be aware 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:

• Do not update records using SQL after the data has been versioned unless you use SQL in conjunction with a multiversioned view.
• When updating nonversioned data using SQL, do not modify any attributes that affect other objects in the database using geodatabase behavior such as relationship classes, feature-linked annotation, or topology.
• 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.

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

The ST_Geometry storage type

The ST_Geometry SQL data type is used in geodatabases stored in DB2, Informix, Oracle, and PostgreSQL. This data type can be used within the geodatabase plus it provides SQL access to simple feature class geometry for third-party applications.

For information on using SQL with the ST_Geomery type in DB2 or Informix, consult the IBM DB2 and Informix documentation. For information on using SQL with the ST_Geometry type in Oracle and PostgreSQL, see the subsequent section.

Tip:

The "Using spatial types with SQL" section of this help does contain some examples for DB2 and Informix; however, for complete information on using ST_Geometry with these databases, read the IBM documentation.

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 ArcSDE.

These functions can be grouped based on their use.

• Functions that test spatial relationships

  These functions take geometries as input and determine whether 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. These are described in Geometry properties.Some of these functions actually check to see whether 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 criteria, it returns 0 or f for FALSE. These functions include the following:ST_EqualSRS (PostgreSQL only), ST_Is3d (Oracle only), ST_IsClosed, ST_IsEmpty, ST_IsMeasured (Oracle only), ST_IsRing, ST_IsSimple, and 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_LineFromShape (PostgreSQL only)	ST_MPolyFromShape (PostgreSQL only)	ST_PointFromText (Oracle only)
  ST_AsText	ST_LineFromText (Oracle only)	ST_MPolyFromText (Oracle only)	ST_PointFromWKB
  ST_Curve (Oracle only)	ST_LineFromWKB	ST_MPolyFromWKB	ST_PolyFromShape (PostgreSQL only)
  ST_GeomCollection	ST_LineString	ST_MultiCurve (Oracle only)	ST_PolyFromText (Oracle only)
  ST_GeomCollFromShape (PostgreSQL only)	ST_MLineFromShape (PostgreSQL only)	ST_MultiLineString	ST_PolyFromWKB
  ST_GeomCollFromWKB (PostgreSQL only)	ST_MLineFromText (Oracle only)	ST_MultiPoint	ST_Polygon
  ST_Geometry	ST_MLineFromWKB	ST_MultiPolygon	ST_Surface (Oracle only)
  ST_GeomFromShape (PostgreSQL only)	ST_MPointFromShape (PostgreSQL only)	ST_MultiSurface (Oracle only)	ST_Transform
  ST_GeomFromText (Oracle only)	ST_MPointFromText (Oracle only)	ST_Point
  ST_GeomFromWKB	ST_MPointFromWKB	ST_PointFromShape (PostgreSQL only)

The ST_Raster storage type

You can use the ST_Raster data type in your ArcSDE geodatabases. This data type can be used within the geodatabase plus it provides SQL access to raster data for third-party applications.

The ST_Raster type is available in ArcSDE geodatabases in Oracle, PostgreSQL, and Microsoft SQL Server.

Two different categories of ST_Raster functions are used to access the ST_Raster type using SQL:

• ST_Raster and ST_PixelData methods
• ST_RasterUtil stored procedures

ST_Raster and ST_PixelData functions

These are methods that instantiate and work with the ST_Raster object.

ST_PixelData	ST_Raster.deleteColormap	ST_Raster.getPixelType
ST_PixelData.getValue	ST_Raster.deleteStats	ST_Raster.getPyramidLevel
ST_PixelData.setExtent	ST_Raster.describe	ST_Raster.getValue
ST_PixelData.setValue	ST_Raster.export	ST_Raster.getVersion
ST_Raster	ST_Raster.getCompressionType	ST_Raster.hasColormap
ST_Raster.buildPyramid	ST_Raster.getInterleaveType	ST_Raster.hasStats
ST_Raster.buildStats	ST_Raster.getInterpolationType	ST_Raster.mosaic
ST_Raster.crop	ST_Raster.getPixelData	ST_Raster.setColormap
		ST_Raster.subset

ST_RasterUtil stored procedure utilities

The ST_RasterUtil stored procedures support the creation and maintenance of the ST_Raster type. Available functions are as follows:

ST_RasterUtil_checkLibraryVersion

ST_RasterUtil.describe

ST_RasterUtil.getVersion

ST_RasterUtil_getLibraryVersion

ST_RasterUtil.initialize