ArcGIS Server Banner

An overview of SQL functions used with ST_Geometry types

An overview of SQL functions used with ST_Geometry types

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
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:

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_Area
ST_AsBinary
ST_AsText
ST_Boundary
ST_Buffer
ST_Centroid
ST_Contains
ST_ConvexHull
ST_CoordDim
ST_Crosses
ST_Curve (Oracle only)
ST_Difference
ST_Dimension
ST_Disjoint
ST_Distance
ST_EndPoint
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_LineFromWKB
ST_LineString
ST_M
ST_MaxM
ST_MaxX
ST_MaxY
ST_MaxZ
ST_MinM
ST_MinX
ST_MinY
ST_MinZ
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_NumGeometries
ST_NumInteriorRing
ST_NumPoints
ST_OrderingEquals
ST_Overlaps
ST_Point
ST_PointFromShape (PostgreSQL only)
ST_PointFromText (Oracle only)
ST_PointFromWKB
ST_PointN
ST_PointOnSurface
ST_PolyFromShape (PostgreSQL only)
ST_PolyFromText (Oracle only)
ST_PolyFromWKB
ST_Polygon
ST_Relate
ST_SRID
ST_StartPoint
ST_Surface (Oracle only)
ST_SymmetricDiff
ST_Touches
ST_Transform (Oracle only)
ST_Union
ST_Within
ST_X
ST_Y
ST_Z

Tip