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
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.
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 |
|---|---|---|---|
| X | X | ||
| X | X | ||
| X | |||
| X | X | X | |
| X | X | ||
| X | X | ||
| X | X | X | |
| X | X | X | |
| X | X | ||
| X | X | X | |
| X | X | ||
| X | X | X | |
| X | X | ||
| X | X | X | |
| X | |||
| X | X | X | |
| X | X | X | |
| X | X | X | |
| X | |||
| X | X | X | |
| X | X | ||
| X | X | X | |
| X | X | ||
| X | X | X | |
| X | X | X | |
| 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_Entity (Oracle and SQLite only) |
| ST_GeoSize (PostgreSQL only) |
| ST_Is3d (Oracle and SQLite only) |
| 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_EnvIntersects (Oracle and SQLite only) |
| ST_OrderingEquals(Oracle and PostgreSQL only |
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_Equalsrs (PostgreSQL only) |