elasticsearch/docs/reference/sql/functions/geo.asciidoc

[role="xpack"]
[testenv="basic"]
[[sql-functions-geo]]
=== Geo Functions

beta[]

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

==== Limitations

Both <<geo-point, `geo_point`>> and <<geo-shape, `geo_shape`>> types are represented in SQL as geometry and can be used
interchangeably with the following exceptions:

* `geo_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

[[sql-functions-geo-st-as-wkt]]
===== `ST_AsWKT`

.Synopsis:
[source, sql]
--------------------------------------------------
ST_AsWKT(
    geometry <1>
)
--------------------------------------------------

*Input*:

<1> geometry

*Output*: string

.Description:

Returns the WKT representation of the `geometry`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/geo.csv-spec[aswkt]
--------------------------------------------------


[[sql-functions-geo-st-wkt-to-sql]]
===== `ST_WKTToSQL`

.Synopsis:
[source, sql]
--------------------------------------------------
ST_WKTToSQL(
    string <1>
)
--------------------------------------------------

*Input*:

<1> string WKT representation of geometry

*Output*: geometry

.Description:

Returns the geometry from WKT representation.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/geo.csv-spec[aswkt]
--------------------------------------------------

==== Geometry Properties

[[sql-functions-geo-st-geometrytype]]
===== `ST_GeometryType`

.Synopsis:
[source, sql]
--------------------------------------------------
ST_GeometryType(
    geometry <1>
)
--------------------------------------------------

*Input*:

<1> geometry

*Output*: string

.Description:

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

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/geo.csv-spec[geometrytype]
--------------------------------------------------

[[sql-functions-geo-st-x]]
===== `ST_X`

.Synopsis:
[source, sql]
--------------------------------------------------
ST_X(
    geometry <1>
)
--------------------------------------------------

*Input*:

<1> geometry

*Output*: double

.Description:

Returns the longitude of the first point in the geometry.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/geo.csv-spec[x]
--------------------------------------------------

[[sql-functions-geo-st-y]]
===== `ST_Y`

.Synopsis:
[source, sql]
--------------------------------------------------
ST_Y(
    geometry <1>
)
--------------------------------------------------

*Input*:

<1> geometry

*Output*: double

.Description:

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

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/geo.csv-spec[y]
--------------------------------------------------

[[sql-functions-geo-st-z]]
===== `ST_Z`

.Synopsis:
[source, sql]
--------------------------------------------------
ST_Z(
    geometry <1>
)
--------------------------------------------------

*Input*:

<1> geometry

*Output*: double

.Description:

Returns the altitude of the first point in the geometry.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/geo.csv-spec[z]
--------------------------------------------------

[[sql-functions-geo-st-distance]]
===== `ST_Distance`

.Synopsis:
[source, sql]
--------------------------------------------------
ST_Distance(
    geometry, <1>
    geometry  <2>
)
--------------------------------------------------

*Input*:

<1> source geometry
<2> target geometry

*Output*: Double

.Description:

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

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/geo.csv-spec[distance]
--------------------------------------------------