ArcGIS Desktop

  • ArcGIS Pro
  • ArcMap

  • My Profile
  • Help
  • Sign Out
ArcGIS Desktop

ArcGIS Online

The mapping platform for your organization

ArcGIS Desktop

A complete professional GIS

ArcGIS Enterprise

GIS in your enterprise

ArcGIS for Developers

Tools to build location-aware apps

ArcGIS Solutions

Free template maps and apps for your industry

ArcGIS Marketplace

Get apps and data for your organization

  • Documentation
  • Support
Esri
  • Sign In
user
  • My Profile
  • Sign Out

ArcMap

  • Home
  • Get Started
  • Map
  • Analyze
  • Manage Data
  • Tools
  • Extensions

SQL functions used with ST_Geometry

  • List of SQL functions

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

FunctionOraclePostgreSQLSQLite

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

Related topics

  • Constructor functions for ST_Geometry
  • Spatial accessor functions
  • Spatial relationships
  • Spatial operations

ArcGIS Desktop

  • Home
  • Documentation
  • Support

ArcGIS Platform

  • ArcGIS Online
  • ArcGIS Desktop
  • ArcGIS Enterprise
  • ArcGIS for Developers
  • ArcGIS Solutions
  • ArcGIS Marketplace

About Esri

  • About Us
  • Careers
  • Esri Blog
  • User Conference
  • Developer Summit
Esri
Tell us what you think.
Copyright © 2020 Esri. | Privacy | Legal