A quick tour of SQL functions used with ST_Geometry and ST_Raster types

Structured Query Language (SQL) is a standardized language used to select and manipulate data stored in a database management system (DBMS). ArcSDE installs specific SQL functions and types in geodatabases in Oracle, PostgreSQL, and Microsoft SQL Server. You can use SQL and these functions and types to query and edit data.

This book of the help contains topics for each of the functions implemented with the ST_Geometry spatial type in an ArcSDE geodatabase in Oracle and PostgreSQL and the ST_Raster type in Oracle, PostgreSQL, and SQL Server. These functions are stored in the schema of the ArcSDE administrator (usually the sde user) and provide operations that work on and return information about geometries and rasters.

The function topics are structured as follows:

NoteNote:

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

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 that you do not use this SRID for your production data.

NoteNote:

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 functions you can use with ST_Geometry in Oracle and PostgreSQL and ST_Raster in Oracle, PostgreSQL, and SQL Server.

Beginning with ArcSDE 9.3, when using the ST_Geometry 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 Oracle 9i, and ArcSDE for PostgreSQL, the qualification is optional, but it is a good practice to include the qualifier.

When using ST_Raster functions in all databases, you must qualify the function with the ArcSDE administrator's schema. In most cases, this is sde. However, in SQL Server databases, it may be dbo.

Constructor functions

ST_Curve (Oracle only)

ST_GeomCollection

ST_GeomCollFromShape (PostgreSQL only)

ST_GeomCollFromWKB (PostgreSQL only)

ST_Geometry

ST_GeomFromShape (PostgreSQL only)

ST_GeomFromText (Oracle only)

ST_GeomFromWKB

ST_LineFromShape (PostgreSQL only)

ST_LineFromText (Oracle only)

ST_LineFromWKB

ST_LineString

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_Point

ST_PointFromShape (PostgreSQL only)

ST_PointFromText (Oracle only)

ST_PointFromWKB

ST_PolyFromShape (PostgreSQL only)

ST_PolyFromText (Oracle only)

ST_PolyFromWKB

ST_Polygon

ST_Surface (Oracle only)

TipTip:

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.

The names of the functions vary depending on the DBMS implementation. In Oracle and SQL Server, the functions are member methods of the ST_Raster or ST_PixelData object. In PostgreSQL, they are SQL functions implemented at the PL/SQL package level. Be aware that you must use the case shown when using the functions with SQL Server. When used with Oracle or PostgreSQL, whether you use all lower, all upper, or mixed case does not matter.

Equivalent functions are shown in the following table:


Copyright © 1995-2013 Esri. All rights reserved.
11/18/2013