A quick tour of SQL functions used with ST_Geometry
Structured Query Language (SQL) is a standardized language used to select and manipulate data stored in a database management system (DBMS). When you create a geodatabase or install the ST_Geometry type in Oracle or PostgreSQL, specific SQL functions and types are created in the sde user's schema. You can use SQL and these functions and types to query and edit data.
The function topics in this section of the help 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
What type of data is returned when the function is issued
-
Examples
Scenario 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 ST_Geometry in Oracle and PostgreSQL.
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.
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.
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) |
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.
ST_Entity (Oracle only) |
ST_GeoSize (PostgreSQL only) |
ST_Is3d (Oracle only) |
ST_IsMeasured (Oracle 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.
ST_EnvIntersects (Oracle only) |
Geometry functions
These functions take spatial data, perform analyses on it, and return new spatial data.
|
ST_Aggr_ConvexHull (Oracle only) |
|
ST_Aggr_Intersection (Oracle only) |
|
ST_Aggr_Union (Oracle only) |
|
ST_Equalsrs (PostgreSQL only) |