ArcGIS Help 10.2 - A quick tour of SQL functions used with ST

Specific SQL functions and types are created when you do any of the following:

Create a geodatabase in an Oracle or PostgreSQL database.
Install the ST_Geometry spatial data type in an Oracle or PostgreSQL database.
Create a SQLite database using the createSQLiteDatabase ArcPy function, and specify the ST_Geometry spatial data type.

In Oracle and PostgreSQL databases, the ST_Geometry type and its functions are created in the sde user's schema. In SQLite, the type and functions are stored in a library that you must load before you execute SQL against the SQLite database.

This section of the help provides a list and description of the functions available for use with the ST_Geometry spatial data type. The function topics are structured as follows:

Definition
A description of the function
Syntax
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.
Return type
The type of data that is returned when the function is issued
Example
Samples that use the specific function

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.

List of SQL functions

Click the links below to go to the functions you can use with the ST_Geometry type in Oracle, PostgreSQL, and SQLite.

When using ST_Geometry functions in 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. For PostgreSQL, the qualification is optional, but it is a good practice to include the qualifier. Do not include the qualification when using the functions with SQLite, as there is no sde schema in SQLite databases.

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

ST_Geometry SQL functions can be grouped based on their use.

Constructor functions

Constructor functions take one type of geometry or a text description of geometry and create a geometry. The following table lists the constructor functions and indicates which ST_Geometry implementations support each one.

Constructor functions

Function	Oracle	PostgreSQL	SQLite
ST_Curve	X		X
ST_GeomCollection	X	X
ST_GeomCollFromShape		X
ST_GeomCollFromWKB		X
ST_Geometry	X	X	X
ST_GeomFromShape		X
ST_GeomFromText	X		X
ST_GeomFromWKB
ST_LineFromShape		X
ST_LineFromText	X		X
ST_LineFromWKB	X	X	X
ST_LineString	X	X	X
ST_MLineFromShape		X
ST_MLineFromText	X		X
ST_MLineFromWKB	X	X	X
ST_MPointFromShape		X
ST_MPointFromText	X		X
ST_MPointFromWKB	X	X	X
ST_MPolyFromText	X		X
ST_MPolyFromWKB	X	X	X
ST_MultiCurve	X
ST_MultiLineString	X	X	X
ST_MultiPoint	X	X	X
ST_MultiPolygon	X	X	X
ST_MultiSurface	X
ST_Point	X	X	X
ST_PointFromShape		X
ST_PointFromText	X		X
ST_PointFromWKB	X	X	X
ST_PolyFromShape		X
ST_PolyFromText	X		X
ST_PolyFromWKB	X	X	X
ST_Polygon	X	X	X
ST_Surface	X		X

Accessor functions

There are a number of functions that take a geometry or geometries as input and return specific information about them.

Some of these functions 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 apply to all implementations except where noted otherwise.

Accessor functions

ST_Entity (Oracle and SQLite only)

ST_GeometryType

ST_GeoSize (PostgreSQL only)

ST_Is3d (Oracle and SQLite only)

ST_IsClosed

ST_IsEmpty

ST_IsMeasured (Oracle and SQLite only)

Relational functions

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

These functions apply to all implementations except where noted otherwise.

Relational functions

ST_Contains

ST_Crosses

ST_Disjoint

ST_EnvIntersects (Oracle and SQLite only)

ST_Equals

ST_Intersects

ST_OrderingEquals (Oracle and PostgreSQL only

ST_Overlaps

ST_Relate

ST_Touches