Accessor functions
Accessor functions are useful to obtain values in types varchar,
bigint, or double from different geometry
data types, where geometry is any of the geometry data types supported in
Athena: point, line, polygon,
multiline, and multipolygon. For example, you can obtain
an area of a polygon geometry data type, maximum and minimum X and Y values
for a specified geometry data type, obtain the length of a line, or receive
the number of points in a specified geometry data type.
geometry_invalid_reason(geometry)
Returns, in a varchar data type, the reason why the specified geometry is not valid or not simple. If the specified geometry is neither valid nor simple, returns the reason why it is not valid. If the specified geometry is valid and simple, returns null. Example:
SELECT geometry_invalid_reason(ST_Point(-158.54, 61.56))
great_circle_distance(latitude1, longitude1, latitude2,
longitude2)
Returns, as a double, the great-circle distance between two points on Earth's surface in kilometers. Example:
SELECT great_circle_distance(36.12, -86.67, 33.94, -118.40)
line_locate_point(lineString, point)
Returns a double between 0 and 1 that represents the location of the closest point on the specified line string to the specified point as a fraction of total 2d line length.
Returns null if the specified line string or point is empty or null. Example:
SELECT line_locate_point(ST_GeometryFromText('LINESTRING (0 0, 0 1)'), ST_Point(0, 0.2))
simplify_geometry(geometry, double)
Uses the Ramer-douglas-peucker algorithm
SELECT simplify_geometry(ST_GeometryFromText('POLYGON ((1 0, 2 1, 3 1, 3 1, 4 1, 1 0))'), 1.5)
ST_Area(geometry)
Takes as an input a geometry data type and returns an area in type
double. Example:
SELECT ST_Area(ST_Polygon('polygon((1 1, 4 1, 4 4, 1 4))'))
ST_Centroid(geometry)
Takes as an input a geometry data type
polygon, and returns a point geometry data type that is
the center of the polygon's envelope. Examples:
SELECT ST_Centroid(ST_GeometryFromText('polygon ((0 0, 3 6, 6 0, 0 0))'))
SELECT ST_AsText(ST_Centroid(ST_Envelope(ST_GeometryFromText('POINT (53 27)'))))
ST_ConvexHull(geometry)
Returns a geometry data type that is the smallest convex geometry that encloses all geometries in the specified input. Example:
SELECT ST_ConvexHull(ST_Point(-158.54, 61.56))
ST_CoordDim(geometry)
Takes as input one of the supported geometry
data types, and returns the count of coordinate components in the type
tinyint. Example:
SELECT ST_CoordDim(ST_Point(1.5,2.5))
ST_Dimension(geometry)
Takes as an input one of the supported geometry data types, and returns the spatial dimension of a geometry in
type tinyint. Example:
SELECT ST_Dimension(ST_Polygon('polygon((1 1, 4 1, 4 4, 1 4))'))
ST_Distance(geometry, geometry)
Returns, based on spatial ref, a double containing the two-dimensional minimum Cartesian distance between two geometries in projected units. Starting in Athena engine version 2, returns null if one of the inputs is an empty geometry. Example:
SELECT ST_Distance(ST_Point(0.0,0.0), ST_Point(3.0,4.0))
ST_Distance(sphericalGeography, sphericalGeography)
Returns, as a double, the great-circle distance between two spherical geography points in meters. Example:
SELECT ST_Distance(to_spherical_geography(ST_Point(61.56, -86.67)),to_spherical_geography(ST_Point(61.56, -86.68)))
ST_EndPoint(geometry)
Returns the last point of a line geometry data type in a
point geometry data type. Example:
SELECT ST_EndPoint(ST_Line('linestring(0 2, 2 2)'))
ST_Geometries(geometry)
Returns an array of geometries in the specified collection. If the specified geometry is not a multi-geometry, returns a one-element array. If the specified geometry is empty, returns null.
For example, given a MultiLineString object,
ST_Geometries creates an array of LineString objects.
Given a GeometryCollection object, ST_Geometries returns
an un-flattened array of its constituents. Example:
SELECT ST_Geometries(GEOMETRYCOLLECTION(MULTIPOINT(0 0, 1 1), GEOMETRYCOLLECTION(MULTILINESTRING((2 2, 3 3)))))
Result:
array[MULTIPOINT(0 0, 1 1),GEOMETRYCOLLECTION(MULTILINESTRING((2 2, 3 3)))]
ST_GeometryN(geometry, index)
Returns, as a geometry data type, the geometry element at a specified integer
index. Indices start at 1. If the specified geometry is a collection of geometries
(for example, a GEOMETRYCOLLECTION or MULTI* object),
returns the geometry at the specified index. If the specified index is less than 1
or greater than the total number of elements in the collection, returns null. To
find the total number of elements, use ST_NumGeometries. Singular geometries (for example,
POINT, LINESTRING, or POLYGON), are
treated as collections of one element. Empty geometries are treated as empty
collections. Example:
SELECT ST_GeometryN(ST_Point(-158.54, 61.56),1)
ST_GeometryType(geometry)
Returns, as a varchar, the type of the geometry. Example:
SELECT ST_GeometryType(ST_Point(-158.54, 61.56))
ST_InteriorRingN(geometry, index)
Returns the interior ring element at the specified index (indices start at 1). If the given index is less than 1 or greater than the total number of interior rings in the specified geometry, returns null. Throws an error if the specified geometry is not a polygon. To find the total number of elements, use ST_NumInteriorRing. Example:
SELECT ST_InteriorRingN(st_polygon('polygon ((0 0, 1 0, 1 1, 0 1, 0 0))'),1)
ST_InteriorRings(geometry)
Returns a geometry array of all interior rings found in the specified geometry, or an empty array if the polygon has no interior rings. If the specified geometry is empty, returns null. If the specified geometry is not a polygon, throws an error. Example:
SELECT ST_InteriorRings(st_polygon('polygon ((0 0, 1 0, 1 1, 0 1, 0 0))'))
ST_IsClosed(geometry)
Takes as an input only line and multiline
geometry data types. Returns
TRUE (type boolean) if and only if the line is closed.
Example:
SELECT ST_IsClosed(ST_Line('linestring(0 2, 2 2)'))
ST_IsEmpty(geometry)
Takes as an input only line and multiline
geometry data types. Returns
TRUE (type boolean) if and only if the specified
geometry is empty, in other words, when the line start and end values
coincide. Example:
SELECT ST_IsEmpty(ST_Point(1.5, 2.5))
ST_IsRing(geometry)
Returns TRUE (type boolean) if and only if the
line type is closed and simple. Example:
SELECT ST_IsRing(ST_Line('linestring(0 2, 2 2)'))
ST_IsSimple(geometry)
Returns true if the specified geometry has no anomalous geometric points (for example, self intersection or self tangency). To determine why the geometry is not simple, use geometry_invalid_reason(). Example:
SELECT ST_IsSimple(ST_LineString(array[ST_Point(1,2), ST_Point(3,4)]))
ST_IsValid(geometry)
Returns true if and only if the specified geometry is well formed. To determine why the geometry is not well formed, use geometry_invalid_reason(). Example:
SELECT ST_IsValid(ST_Point(61.56, -86.68))
ST_Length(geometry)
Returns the length of line in type double.
Example:
SELECT ST_Length(ST_Line('linestring(0 2, 2 2)'))
ST_NumGeometries(geometry)
Returns, as an integer, the number of geometries in the collection. If the
geometry is a collection of geometries (for example, a
GEOMETRYCOLLECTION or MULTI* object), returns the
number of geometries. Single geometries return 1; empty geometries return 0. An
empty geometry in a GEOMETRYCOLLECTION object counts as one geometry.
For example, the following example evaluates to 1:
ST_NumGeometries(ST_GeometryFromText('GEOMETRYCOLLECTION(MULTIPOINT EMPTY)'))
ST_NumInteriorRing(geometry)
Returns the number of interior rings in the polygon geometry in type
bigint. Example:
SELECT ST_NumInteriorRing(ST_Polygon('polygon ((0 0, 8 0, 0 8, 0 0), (1 1, 1 5, 5 1, 1 1))'))
ST_NumPoints(geometry)
Returns the number of points in the geometry in type bigint.
Example:
SELECT ST_NumPoints(ST_Point(1.5, 2.5))
ST_PointN(lineString,
index)
Returns, as a point geometry data type, the vertex of the specified line string at the specified integer index. Indices start at 1. If the given index is less than 1 or greater than the total number of elements in the collection, returns null. To find the total number of elements, use ST_NumPoints. Example:
SELECT ST_PointN(ST_LineString(array[ST_Point(1,2), ST_Point(3,4)]),1)
ST_Points(geometry)
Returns an array of points from the specified line string geometry object. Example:
SELECT ST_Points(ST_LineString(array[ST_Point(1,2), ST_Point(3,4)]))
ST_StartPoint(geometry)
Returns the first point of a line geometry data type in a
point geometry data type. Example:
SELECT ST_StartPoint(ST_Line('linestring(0 2, 2 2)'))
ST_X(point)
Returns the X coordinate of a point in type double. Example:
SELECT ST_X(ST_Point(1.5, 2.5))
ST_XMax(geometry)
Returns the maximum X coordinate of a geometry in type double.
Example:
SELECT ST_XMax(ST_Line('linestring(0 2, 2 2)'))
ST_XMin(geometry)
Returns the minimum X coordinate of a geometry in type double.
Example:
SELECT ST_XMin(ST_Line('linestring(0 2, 2 2)'))
ST_Y(point)
Returns the Y coordinate of a point in type double. Example:
SELECT ST_Y(ST_Point(1.5, 2.5))
ST_YMax(geometry)
Returns the maximum Y coordinate of a geometry in type double.
Example:
SELECT ST_YMax(ST_Line('linestring(0 2, 2 2)'))
ST_YMin(geometry)
Returns the minimum Y coordinate of a geometry in type double.
Example:
SELECT ST_YMin(ST_Line('linestring(0 2, 2 2)'))
