« Type Conversion Functions Conditional Functions And Expressions »

› › ›

Geo Functions

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 »