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:
A description of the function
The proper syntax to use the function
Note that with relational operators, the order in which the parameters are specified is important: The first parameter should be for the table from which the selection is being made, and the second parameter should be for the table that is being used as a filter.
What type of data is returned when the function is issued
An example scenario of using that function
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.
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.
ST_Curve (Oracle only)
ST_GeomCollFromShape (PostgreSQL only)
ST_GeomCollFromWKB (PostgreSQL only)
ST_GeomFromShape (PostgreSQL only)
ST_GeomFromText (Oracle only)
ST_LineFromShape (PostgreSQL only)
ST_LineFromText (Oracle only)
ST_MLineFromShape (PostgreSQL only)
ST_MLineFromText (Oracle only)
ST_MPointFromShape (PostgreSQL only)
ST_MpointFromText (Oracle only)
ST_MpolyFromText (Oracle only)
ST_MultiCurve (Oracle only)
ST_MultiSurface (Oracle only)
ST_PointFromShape (PostgreSQL only)
ST_PointFromText (Oracle only)
ST_PolyFromShape (PostgreSQL only)
ST_PolyFromText (Oracle only)
ST_Surface (Oracle only)
ST_Entity (Oracle only)
ST_GeoSize (PostgreSQL only)
ST_Is3d (Oracle only)
ST_IsMeasured (Oracle only)
ST_EnvIntersects (Oracle only)
ST_Aggr_ConvexHull (Oracle only)
ST_Aggr_Intersection (Oracle only)
ST_Aggr_Union (Oracle only)
ST_Equalsrs (PostgreSQL only)
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:
Oracle and SQL Server functions
ST_PixelData.getValueByLoc (SQL Server only)
ST_Raster.getValueByLoc (SQL Server only)
ST_Raster_Util.checkLibraryVersion (Oracle only)
ST_Raster_Util.getLibraryVersion (Oracle only)