This documentation contains work-in-progress information for future Elastic Stack and Cloud releases. Use the version selector to view supported release docs. It also contains some Elastic Cloud serverless information. Check out our serverless docs for more details.
« Type Conversion Functions Conditional Functions And Expressions »
Elastic Docs ›Elasticsearch Guide [master] ›SQL ›Functions and Operators

Geo Functions

edit

Geo Functions

edit

This functionality is in beta and is subject to change. The design and code is less mature than official GA features and is being provided as-is with no warranties. Beta features are not subject to the support SLA of official GA features.

The geo functions work with geometries stored in geo_point, geo_shape and shape fields, or returned by other geo functions.

Limitations

edit

geo_point, geo_shape and shape and types are represented in SQL as geometry and can be used interchangeably with the following exceptions:

  • geo_shape and shape fields don’t have doc values, therefore these fields cannot be used for filtering, grouping or sorting.
  • geo_points fields are indexed and have doc values by default, however only latitude and longitude are stored and indexed with some loss of precision from the original values (4.190951585769653E-8 for the latitude and 8.381903171539307E-8 for longitude). The altitude component is accepted but not stored in doc values nor indexed. Therefore calling ST_Z function in the filtering, grouping or sorting will return null.

Geometry Conversion

edit

ST_AsWKT

edit

Synopsis:

ST_AsWKT(
    geometry 
)

Input:

geometry. If null, the function returns null.

Output: string

Description: Returns the WKT representation of the geometry.

SELECT city, ST_AsWKT(location) location FROM "geo" WHERE city = 'Amsterdam';

     city:s    |     location:s
Amsterdam      |POINT (4.850312 52.347557)

ST_WKTToSQL

edit

Synopsis:

ST_WKTToSQL(
    string 
)

Input:

string WKT representation of geometry. If null, the function returns null.

Output: geometry

Description: Returns the geometry from WKT representation.

SELECT CAST(ST_WKTToSQL('POINT (10 20)') AS STRING) location;

   location:s
POINT (10.0 20.0)

Geometry Properties

edit

ST_GeometryType

edit

Synopsis:

ST_GeometryType(
    geometry 
)

Input:

geometry. If null, the function returns null.

Output: string

Description: Returns the type of the geometry such as POINT, MULTIPOINT, LINESTRING, MULTILINESTRING, POLYGON, MULTIPOLYGON, GEOMETRYCOLLECTION, ENVELOPE or CIRCLE.

SELECT ST_GeometryType(ST_WKTToSQL('POINT (10 20)')) type;

      type:s
POINT

ST_X

edit

Synopsis:

ST_X(
    geometry 
)

Input:

geometry. If null, the function returns null.

Output: double

Description: Returns the longitude of the first point in the geometry.

SELECT ST_X(ST_WKTToSQL('POINT (10 20)')) x;

      x:d
10.0

ST_Y

edit

Synopsis:

ST_Y(
    geometry 
)

Input:

geometry. If null, the function returns null.

Output: double

Description: Returns the latitude of the first point in the geometry.

SELECT ST_Y(ST_WKTToSQL('POINT (10 20)')) y;

      y:d
20.0

ST_Z

edit

Synopsis:

ST_Z(
    geometry 
)

Input:

geometry. If null, the function returns null.

Output: double

Description: Returns the altitude of the first point in the geometry.

SELECT ST_Z(ST_WKTToSQL('POINT (10 20 30)')) z;

      z:d
30.0

ST_Distance

edit

Synopsis:

ST_Distance(
    geometry, 
    geometry  
)

Input:

source geometry. If null, the function returns null.

target geometry. If null, the function returns null.

Output: Double

Description: Returns the distance between geometries in meters. Both geometries have to be points.

SELECT ST_Distance(ST_WKTToSQL('POINT (10 20)'), ST_WKTToSQL('POINT (20 30)')) distance;

   distance:d
1499101.2889383635
« Type Conversion Functions Conditional Functions And Expressions »