SQL functions used with ST_Geometry

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 Create spatial indexes on tables with an ST_Geometry column using SQL 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.

When you provide well-known text strings as input with an ST_Geometry SQL function, you can use scientific notation to specify very large or very small values. For example, if you specify coordinates using well-known text when constructing a new feature, and one of the coordinates is 0.000023500001816501026, you could type 2.3500001816501026e-005 instead.

Tip:

For spatial types in other databases—such as the PostGIS types, SQL Server spatial types, Oracle SDO_Geometry, or SAP HANA ST_Geometry—consult the documentation provided by the database management system vendor for information on the functions used by each of these.

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_GeomCollFromWKB		X
ST_Geometry	X	X	X
ST_GeomFromText	X		X
ST_GeomFromWKB
ST_LineFromText	X		X
ST_LineFromWKB	X	X	X
ST_LineString	X	X	X
ST_MLineFromText	X		X
ST_MLineFromWKB	X	X	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_PointFromText	X		X
ST_PointFromWKB	X	X	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 accessor 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_Area

ST_AsBinary

ST_AsText

ST_Centroid

ST_CoordDim

ST_Dimension

ST_EndPoint

ST_Entity

ST_GeomFromCollection (PostgreSQL only)

ST_GeometryType

ST_GeoSize (PostgreSQL only)

ST_Is3d (Oracle and SQLite only)

ST_IsClosed

ST_IsEmpty

ST_IsMeasured (Oracle and SQLite only)

ST_IsRing

ST_IsSimple

ST_Length

ST_M

ST_MaxM

ST_MaxX

ST_MaxY

ST_MaxZ

ST_MinM

ST_MinX

ST_MinY

ST_MinZ

ST_NumGeometries

ST_NumInteriorRing

ST_NumPoints

ST_Perimeter

ST_SRID

ST_StartPoint

ST_X

ST_Y

ST_Z

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_DWithin (Oracle and PostgreSQL only)

ST_EnvIntersects (Oracle and SQLite only)

ST_Equals

ST_Intersects

ST_OrderingEquals(Oracle and PostgreSQL only)

ST_Overlaps

ST_Relate

ST_Touches

ST_Within

Geometry functions

These functions take spatial data, perform analyses on it, and return new spatial data.

These functions apply to all implementations except where noted otherwise.

Geometry functions

ST_Aggr_ConvexHull (Oracle and SQLite only)

ST_Aggr_Intersection (Oracle and SQLite only)

ST_Aggr_Union (Oracle and SQLite only)

ST_Boundary

ST_Buffer

ST_ConvexHull

ST_Difference

ST_Distance

ST_Envelope

ST_Equalsrs (PostgreSQL only)

ST_ExteriorRing

ST_GeometryN

ST_InteriorRingN

ST_Intersection

ST_PointN

ST_PointOnSurface

ST_SymmetricDiff

ST_Transform

ST_Union