支持的地理空间函数列表 - Amazon Athena
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

如果我们为英文版本指南提供翻译,那么如果存在任何冲突,将以英文版本指南为准。在提供翻译时使用机器翻译。

支持的地理空间函数列表

Athena 中的地理空间函数具有以下特征:

  • 这些函数遵循空间查询的一般原则。

  • 这些函数是作为一个使用 ESRI Java Geometry Library 的 Presto 插件实现的。此库具有 Apache 2 许可证。

  • 这些函数依赖于 ESRI Geometry API

  • 并非 ESRI 支持的所有函数都在 Athena 中可用。本主题仅列出 Athena 中支持的 ESRI 地理空间函数。

Athena 支持四种类型的地理空间函数:

开始前的准备工作

创建两个表 earthquakescounties,如下所示:

CREATE external TABLE earthquakes ( earthquake_date STRING, latitude DOUBLE, longitude DOUBLE, depth DOUBLE, magnitude DOUBLE, magtype string, mbstations string, gap string, distance string, rms string, source string, eventid string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE LOCATION 's3://my-query-log/csv'
CREATE external TABLE IF NOT EXISTS counties ( Name string, BoundaryShape binary ) ROW FORMAT SERDE 'com.esri.hadoop.hive.serde.JsonSerde' STORED AS INPUTFORMAT 'com.esri.json.hadoop.EnclosedJsonInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://my-query-log/json'

后面的一些示例基于这些表,并依赖于在 Amazon S3 位置存储的两个示例文件。这些文件不包括在 Athena 中,仅用于说明目的:

  • 一个 earthquakes.csv 文件,其中列出了加利福尼亚发生的地震。此文件包含与表 earthquakes 中的字段对应的字段。

  • 一个 california-counties.json 文件,其中列出了采用 ESRI 兼容格式的 JSON 编码县数据,而且包括许多字段,例如 AREAPERIMETERSTATECOUNTYNAMEcounties 表基于此文件,并且只有两个字段:Name (字符串) 和 BoundaryShape (二进制)。

构造函数

使用构造函数可获取 pointlinepolygon geometry 数据类型的二进制表示。您也可以使用这些函数来将二进制数据转换为文本数据,以及获取以已知文本 (WKT) 格式表示的 geometry 数据的二进制值。

ST_POINT(double, double)

返回 point geometry 数据类型的二进制表示。

要获取 point geometry 数据类型,请使用 Athena 中的 ST_POINT 函数。对于此函数的输入数据值,请使用几何值,例如墨卡托方位法 (UTM) 笛卡尔坐标系中的值,或者采用十进制度的地图单位(经度和纬度)。经度和纬度值使用世界大地测量系统,也称为 WGS 1984 或 EPSG:4326。WGS 1984 是全球定位系统 (GPS) 使用的坐标系。

例如,在以下表示法中,使用经度和纬度指定地图坐标,值 .072284 是缓冲距离,使用十进制度的角度单位指定。

ST_BUFFER(ST_POINT(-74.006801, 40.705220), .072284)

语法:

SELECT ST_POINT(longitude, latitude) FROM earthquakes LIMIT 1;

示例.此示例使用 earthquakes.csv 中的特定经度和纬度坐标:

SELECT ST_POINT(61.56, -158.54) FROM earthquakes LIMIT 1;

它返回 geometry 数据类型 point 的以下二进制表示:

00 00 00 00 01 01 00 00 00 48 e1 7a 14 ae c7 4e 40 e1 7a 14 ae 47 d1 63 c0

下一个示例使用特定经度和纬度坐标:

SELECT ST_POINT(-74.006801, 40.705220);

它返回 geometry 数据类型 point 的以下二进制表示:

00 00 00 00 01 01 00 00 00 20 25 76 6d 6f 80 52 c0 18 3e 22 a6 44 5a 44 40

在以下示例中,我们使用 ST_GEOMETRY_TO_TEXT 函数从 WKT 获取二进制值:

SELECT ST_GEOMETRY_TO_TEXT(ST_POINT(-74.006801, 40.705220)) AS WKT;

此查询返回 point geometry 类型的 WKT 表示:1 POINT (-74.006801 40.70522)

ST_LINE(varchar)

返回一个 line 数据类型的值,它是该 geometry 数据类型 line 的二进制表示。例如:

SELECT ST_Line('linestring(1 1, 2 2, 3 3)')

ST_POLYGON(varchar)

返回一个 polygon 数据类型的值,它是该 geometry 数据类型 polygon 的二进制表示。例如:

SELECT ST_POLYGON('polygon ((1 1, 1 4, 4 4, 4 1))')

ST_GEOMETRY_TO_TEXT (varbinary)

将每个指定的 geometry 数据类型转换为文本。返回一个 geometry 数据类型的值,它是该 geometry 数据类型的 WKT 表示。示例:

SELECT ST_GEOMETRY_TO_TEXT(ST_POINT(61.56, -158.54))

ST_GEOMETRY_FROM_TEXT (varchar)

将文本转换为 geometry 数据类型。返回一个 geometry 数据类型的值,它是该 geometry 数据类型的二进制表示。示例:

SELECT ST_GEOMETRY_FROM_TEXT(ST_GEOMETRY_TO_TEXT(ST_Point(1, 2)))
注意

如果您正在使用 AmazonAthenaPreviewFunctionality 工作组,请使用语法 ST_GEOMETRYFROMTEXT (varchar)

地理空间关系函数

以下函数表示您指定为输入的两个不同几何体之间的关系。它们返回类型为 boolean 的结果。指定几何体对的顺序很重要:第一个几何体值称为左几何体,第二个几何体值称为右几何体。

这些函数返回:

  • 当且仅当满足了函数所描述的关系时为 TRUE

  • 当且仅当不满足函数所描述的关系时为 FALSE

ST_CONTAINS (geometry, geometry)

当且仅当左几何体包含右几何体时返回 TRUE。示例:

SELECT ST_CONTAINS('POLYGON((0 2,1 1,0 -1,0 2))', 'POLYGON((-1 3,2 1,0 -3,-1 3))')
SELECT ST_CONTAINS('POLYGON((0 2,1 1,0 -1,0 2))', ST_Point(0, 0));
SELECT ST_CONTAINS(ST_GEOMETRY_FROM_TEXT('POLYGON((0 2,1 1,0 -1,0 2))'), ST_GEOMETRY_FROM_TEXT('POLYGON((-1 3,2 1,0 -3,-1 3))'))

ST_CROSSES (geometry, geometry)

当且仅当左几何体穿过右几何体时返回 TRUE。示例:

SELECT ST_CROSSES(ST_LINE('linestring(1 1, 2 2 )'), ST_LINE('linestring(0 1, 2 2)'))

ST_DISJOINT (geometry, geometry)

当且仅当左几何体和右几何体的交集为空时返回 TRUE。示例:

SELECT ST_DISJOINT(ST_LINE('linestring(0 0, 0 1)'), ST_LINE('linestring(1 1, 1 0)'))

ST_EQUALS (geometry, geometry)

当且仅当左几何体等于右几何体时返回 TRUE。示例:

SELECT ST_EQUALS(ST_LINE('linestring( 0 0, 1 1)'), ST_LINE('linestring(1 3, 2 2)'))

ST_INTERSECTS (geometry, geometry)

当且仅当左几何体与右几何体相交时返回 TRUE。示例:

SELECT ST_INTERSECTS(ST_LINE('linestring(8 7, 7 8)'), ST_POLYGON('polygon((1 1, 4 1, 4 4, 1 4))'))

ST_OVERLAPS (geometry, geometry)

当且仅当左几何体与右几何体重叠时返回 TRUE。示例:

SELECT ST_OVERLAPS(ST_POLYGON('polygon((2 0, 2 1, 3 1))'), ST_POLYGON('polygon((1 1, 1 4, 4 4, 4 1))'))

ST_RELATE (geometry, geometry)

当且仅当左几何体与右几何体具有指定的尺寸扩展九交集模型 (DE-9IM) 关系时返回 TRUE。有关更多信息,请参阅维基百科主题 DE-9IM。示例:

SELECT ST_RELATE(ST_LINE('linestring(0 0, 3 3)'), ST_LINE('linestring(1 1, 4 4)'), 'T********')

ST_TOUCHES (geometry, geometry)

当且仅当左几何体与右几何体接触时返回 TRUE

示例:

SELECT ST_TOUCHES(ST_POINT(8, 8), ST_POLYGON('polygon((1 1, 1 4, 4 4, 4 1))'))

ST_WITHIN (geometry, geometry)

当且仅当左几何体位于右几何体内时返回 TRUE

示例:

SELECT ST_WITHIN(ST_POINT(8, 8), ST_POLYGON('polygon((1 1, 1 4, 4 4, 4 1))'))

操作函数

使用操作函数可对 geometry 数据类型值执行操作。例如,您可以获取单个 geometry 数据类型的边界;两个 geometry 数据类型之间的交集;左、右几何体之间的差异 (其中每个几何体都具有相同的 geometry 数据类型);或围绕特定 geometry 数据类型的外部缓冲区或环。

所有操作函数都采用一个 geometry 数据类型作为输入,并返回其二进制表示。

ST_BOUNDARY (geometry)

采用一个 geometry 数据类型作为输入,并返回该 boundary geometry 数据类型的二进制表示。

示例:

SELECT ST_BOUNDARY(ST_LINE('linestring(0 1, 1 0)')))
SELECT ST_BOUNDARY(ST_POLYGON('polygon((1 1, 1 4, 4 4, 4 1))'))

ST_BUFFER (geometry, double)

将 geometry 数据类型(例如点、线、多边形、复线和多个多边形)之一作为输入,并将距离作为类型 double。返回按指定距离(或半径)缓冲的 geometry 数据类型的二进制表示。示例:

SELECT ST_BUFFER(ST_Point(1, 2), 2.0)

在以下示例中,使用经度和纬度指定地图坐标,值 .072284 是缓冲距离,使用十进制度的角度单位指定。

ST_BUFFER(ST_POINT(-74.006801, 40.705220), .072284)

ST_DIFFERENCE (geometry, geometry)

返回左几何体和右几何体之间的差异的二进制表示。示例:

SELECT ST_GEOMETRY_TO_TEXT(ST_DIFFERENCE(ST_POLYGON('polygon((0 0, 0 10, 10 10, 10 0))'), ST_POLYGON('polygon((0 0, 0 5, 5 5, 5 0))')))

ST_ENVELOPE (geometry)

获取作为输入 linepolygonmultilinemultipolygon geometry 数据类型。不支持 point geometry 数据类型。返回一个信封的二进制表示,其中信封是一个围绕指定 geometry 数据类型的矩形。示例:

SELECT ST_ENVELOPE(ST_LINE('linestring(0 1, 1 0)'))
SELECT ST_ENVELOPE(ST_POLYGON('polygon((1 1, 1 4, 4 4, 4 1))'))

ST_EXTERIOR_RING (geometry)

返回输入类型 polygon 的外部环的二进制表示。示例:

SELECT ST_EXTERIOR_RING(ST_POLYGON(1,1, 1,4, 4,1))
SELECT ST_EXTERIOR_RING(ST_POLYGON('polygon ((0 0, 8 0, 0 8, 0 0), (1 1, 1 5, 5 1, 1 1))'))

ST_INTERSECTION (geometry, geometry)

返回左几何体和右几何体的交集的二进制表示。示例:

SELECT ST_INTERSECTION(ST_POINT(1,1), ST_POINT(1,1))
SELECT ST_INTERSECTION(ST_LINE('linestring(0 1, 1 0)'), ST_POLYGON('polygon((1 1, 1 4, 4 4, 4 1))'))
SELECT ST_GEOMETRY_TO_TEXT(ST_INTERSECTION(ST_POLYGON('polygon((2 0, 2 3, 3 0))'), ST_POLYGON('polygon((1 1, 4 1, 4 4, 1 4))')))

ST_SYMMETRIC_DIFFERENCE (geometry, geometry)

返回左几何体和右几何体之间的几何对称差异的二进制表示。示例:

SELECT ST_GEOMETRY_TO_TEXT(ST_SYMMETRIC_DIFFERENCE(ST_LINE('linestring(0 2, 2 2)'), ST_LINE('linestring(1 2, 3 2)')))

访问器函数

访问器函数可用于从不同 geometry 数据类型中获取类型 varcharbigintdouble 的值,其中 geometry 是 Athena 中支持的任何 geometry 数据类型:pointlinepolygonmultilinemultipolygon。例如,您可以获取 polygon geometry 数据类型的面积、指定 geometry 数据类型的最大和最小 x 和 y 值,获取 line 的长度,或接收指定 geometry 数据类型中的点数。

ST_AREA (geometry)

采用 geometry 数据类型 polygon 作为输入,并返回类型为 double 的面积。示例:

SELECT ST_AREA(ST_POLYGON('polygon((1 1, 4 1, 4 4, 1 4))'))

ST_CENTROID (geometry)

采用 geometry 数据类型 polygon 作为输入,并返回作为多边形信封中心的 point(类型为 varchar)。示例:

SELECT ST_CENTROID(ST_GEOMETRY_FROM_TEXT('polygon ((0 0, 3 6, 6 0, 0 0))'))
SELECT ST_GEOMETRY_TO_TEXT(ST_CENTROID(ST_ENVELOPE(ST_GEOMETRY_FROM_TEXT('POINT (53 27)'))))

ST_COORDINATE_DIMENSION (geometry)

采用支持的 geometry 数据类型作为输入,并返回坐标分量的计数(类型为 bigint)。例如:

SELECT ST_COORDINATE_DIMENSION(ST_POINT(1.5,2.5))

ST_DIMENSION (geometry)

采用一个支持的 geometry 数据类型作为输入,并返回一个几何体的空间维度 (类型为 bigint)。例如:

SELECT ST_DIMENSION(ST_POLYGON('polygon((1 1, 4 1, 4 4, 1 4))'))

ST_DISTANCE (geometry, geometry)

根据空间参考,以投影单位返回两个几何体之间的二维最小笛卡尔距离。示例:

SELECT ST_DISTANCE(ST_POINT(0.0,0.0), ST_POINT(3.0,4.0))

ST_IS_CLOSED (geometry)

获取作为仅用于输入的 linemultiline geometry 数据类型。当且仅当线条闭合时返回 TRUE (类型 boolean)。示例:

SELECT ST_IS_CLOSED(ST_LINE('linestring(0 2, 2 2)'))

ST_IS_EMPTY (geometry)

获取作为仅用于输入的 linemultiline geometry 数据类型。当且仅当指定的几何体为空时,返回 TRUE(类型 boolean),换而言之,在 line 开始值和结束值均位于内部时。例如:

SELECT ST_IS_EMPTY(ST_POINT(1.5, 2.5))

ST_IS_RING (geometry)

当且仅当 line 类型闭合且简单时返回 TRUE (类型 boolean)。示例:

SELECT ST_IS_RING(ST_LINE('linestring(0 2, 2 2)'))

ST_LENGTH (geometry)

返回 line 的长度 (类型为 double)。示例:

SELECT ST_LENGTH(ST_LINE('linestring(0 2, 2 2)'))

ST_MAX_X (geometry)

返回几何体的最大 X 坐标 (类型为 double)。示例:

SELECT ST_MAX_X(ST_LINE('linestring(0 2, 2 2)'))

ST_MAX_Y (geometry)

返回几何体的最大 Y 坐标 (类型为 double)。示例:

SELECT ST_MAX_Y(ST_LINE('linestring(0 2, 2 2)'))

ST_MIN_X (geometry)

返回几何体的最小 X 坐标 (类型为 double)。示例:

SELECT ST_MIN_X(ST_LINE('linestring(0 2, 2 2)'))

ST_MIN_Y (geometry)

返回几何体的最小 Y 坐标 (类型为 double)。示例:

SELECT ST_MAX_Y(ST_LINE('linestring(0 2, 2 2)'))

ST_START_POINT (geometry)

返回 line geometry 数据类型的第一个点 (类型为 point)。示例:

SELECT ST_START_POINT(ST_LINE('linestring(0 2, 2 2)'))

ST_END_POINT (geometry)

返回 line geometry 数据类型的最后一个点 (类型为 point)。示例:

SELECT ST_END_POINT(ST_LINE('linestring(0 2, 2 2)'))

ST_X (point)

返回点的 X 坐标 (类型为 double)。示例:

SELECT ST_X(ST_POINT(1.5, 2.5))

ST_Y (point)

返回点的 Y 坐标 (类型为 double)。示例:

SELECT ST_Y(ST_POINT(1.5, 2.5))

ST_POINT_NUMBER (geometry)

返回几何体中的点数 (类型为 bigint)。示例:

SELECT ST_POINT_NUMBER(ST_POINT(1.5, 2.5))

ST_INTERIOR_RING_NUMBER (geometry)

返回 polygon 几何体中的内部环数 (类型为 bigint)。示例:

SELECT ST_INTERIOR_RING_NUMBER(ST_POLYGON('polygon ((0 0, 8 0, 0 8, 0 0), (1 1, 1 5, 5 1, 1 1))'))