An overview of SQL functions used with ST_Geometry types

An overview of SQL functions used with ST_Geometry types
Release 9.3

Note: This topic was updated for 9.3.1.

Structured Query Language (SQL) is a standardized language used to select and manipulate data stored in a database management system (DBMS).

This book contains topics for each of the functions implemented with the ST_Geometry spatial type in an ArcSDE geodatabase in Oracle or PostgreSQL. These functions are stored in the schema of the sde user and provide operations that work on and return information about geometries.

The function topics are structured as follows:

Definition

Syntax

Return type

Example

NOTE: The samples in this section do not build spatial indexes on the tables created. If you want to create a spatial index, see Creating spatial indexes on tables with an ST_Geometry column for instructions.

For the IBM Functional References for ST_Geometry, see chapter 8, "SQL Functions," of the IBM Informix Spatial DataBlade Module User's Guide and chapters 22, "Spatial functions: Categories and uses," and 23, "Spatial functions: Syntax and parameters," in the IBM DB2 Spatial Extender and Geodetic Extender User's Guide and Reference.

Spatial reference ID

Before you can create a geometry and insert it into a table, you must have a valid spatial reference ID (SRID) to utilize. For the spatial type for Oracle, the SRID must be in the ST_SPATIAL_REFERENCES table and have a matching record in the SDE.SPATIAL_REFERENCES table. For the spatial type for PostgreSQL, it must be in the public.sde_spatial_references table.

The easiest way to do this is to load or create a feature class with the valid spatial reference values you want to use using ArcGIS Desktop. Be sure the feature class created is using ST_Geometry storage. You could create an empty feature class with the spatial reference information you need as a template. This creates a record in the SDE.SPATIAL_REFERENCES and ST_SPATIAL_REFERENCES tables in Oracle or a record in the public.sde_spatial_references table in PostgreSQL. You query the LAYERS (Oracle) or sde_layers (PostgreSQL) table to discover the SRID assigned to the layer. You could then use that SRID when you create spatial tables and insert data using SQL. See Creating feature classes for instructions on creating a feature class using ArcGIS Desktop.

For the purpose of using the samples in this help system, a record has been added to the ST_SPATIAL_REFERENCES and sde_spatial_references tables to denote an unknown spatial reference. This record has an SRID of 0. You can use this SRID for the examples in the help. This is not an official SRID—it is provided for the purpose of performing example SQL code. It is highly recommended you do not use this SRID for your production data.

NOTE: The 0 SRID is present in ArcSDE geodatabases in Oracle beginning with ArcGIS 9.3.

If you decide to use a different SRID value already present in your database to do these samples, you must change the examples' coordinates to reflect the spatial reference of your SRID value.

If you want to add a spatial reference to the ST_SPATIAL_REFERENCES or sde_spatial_references table using SQL, see Creating spatial references using SQL.

List of SQL functions

Click the links below to go to the function for the spatial type in Oracle and PostgreSQL in which you are interested.

Beginning with ArcSDE 9.3, when using these functions in a new installation of ArcSDE for Oracle, you must qualify the functions and operators with "sde.". For example, ST_Buffer would be sde.ST_Buffer. Adding "sde." indicates to the software that the function is stored in the schema of the sde user. This is required because the public synonyms that were used to map to the functions are no longer supported by Oracle. For upgraded ArcSDE for Oracle 10g or Oracle9i and ArcSDE for PostgreSQL, the qualification is optional, but it is a good practice to include the qualifier.

ST_Aggr_ConvexHull (Oracle only)

ST_Aggr_Intersection (Oracle only)

ST_Aggr_Union (Oracle only)

ST_Curve (Oracle only)

ST_Entity (Oracle only)

ST_Envelope

ST_EnvIntersects (Oracle only)

ST_Equals

ST_Equalsrs (PostgreSQL only)

ST_ExteriorRing

ST_GeomCollection

ST_GeomCollFromShape (PostgreSQL only)

ST_GeomCollFromWKB (PostgreSQL only)

ST_Geometry

ST_GeometryN

ST_GeometryType

ST_GeomFromShape (PostgreSQL only)

ST_GeomFromText (Oracle only)

ST_GeomFromWKB

ST_GeoSize (PostgreSQL only)

ST_InteriorRingN

ST_Intersection

ST_Intersects

ST_Is3d (Oracle only)

ST_IsClosed

ST_IsEmpty

ST_IsMeasured (Oracle only)

ST_IsRing

ST_IsSimple

ST_Length

ST_LineFromShape (PostgreSQL only)

ST_LineFromText (Oracle only)

ST_MLineFromShape (PostgreSQL only)

ST_MLineFromText (Oracle only)

ST_MLineFromWKB

ST_MPointFromShape (PostgreSQL only)

ST_MpointFromText (Oracle only)

ST_MpointFromWKB

ST_MpolyFromText (Oracle only)

ST_MpolyFromWKB

ST_MultiCurve (Oracle only)

ST_MultiLineString

ST_MultiPoint

ST_MultiPolygon

ST_MultiSurface (Oracle only)

ST_PointFromShape (PostgreSQL only)

ST_PointFromText (Oracle only)

ST_PointFromWKB

ST_PointN

ST_PointOnSurface

ST_PolyFromShape (PostgreSQL only)

ST_PolyFromText (Oracle only)

ST_Surface (Oracle only)

ST_SymmetricDiff

ST_Touches

ST_Transform (Oracle only)

Tip

For spatial types other than ST_Geometry, such as the PostGIS geometry type or Oracle SDO_Geometry type, consult the PostGIS or Oracle Spatial documentation, respectively, for information on the functions used by each of these. PostGIS documentation can be found at www.postgis.org. Oracle documentation can be found on the Oracle Web site.

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