示例：地理空间查询 - Amazon Athena
其他资源

示例：地理空间查询

本主题中的示例从 GitHub 上提供的示例数据创建两个表，并根据数据查询这两个表。以下文件中的示例数据仅用于说明目的，并不能保证准确性：

  • earthquakes.csv – 列出了加利福尼亚发生的地震。示例 earthquakes 表使用此数据中的字段。

  • california-counties.json – 使用符合 ESRI 标准的 GeoJSON 格式列出加利福尼亚州的县级数据。此数据包含多个字段，例如 AREA、PERIMETER、STATE、COUNTY 和 NAME，但示例 counties 表仅使用两个字段：Name（字符串）和 BoundaryShape（二进制）。

    注意

    Athena 使用 com.esri.json.hadoop.EnclosedEsriJsonInputFormat 将 JSON 数据转换为地理空间二进制格式。

以下代码示例创建一个名为 earthquakes 的表：

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://amzn-s3-demo-bucket/my-query-log/csv/';

以下代码示例创建一个名为 counties 的表：

CREATE external TABLE IF NOT EXISTS counties
 (
 Name string,
 BoundaryShape binary
 )
ROW FORMAT SERDE 'com.esri.hadoop.hive.serde.EsriJsonSerDe'
STORED AS INPUTFORMAT 'com.esri.json.hadoop.EnclosedEsriJsonInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://amzn-s3-demo-bucket/my-query-log/json/';

以下示例查询在 counties 和 earthquake 表中使用 CROSS JOIN 函数。此示例使用 ST_CONTAINS 来查询边界包含地震位置的县，这些位置由 ST_POINT 指定。然后，查询会按名称对这些县进行分组，然后按计数对其进行排序，并以降序返回它们。

注意

从 Athena 引擎版本 2 开始，ST_CONTAINS 等函数不再支持将 VARBINARY 类型作为输入。因此，该示例使用 ST_GeomFromLegacyBinary(varbinary) 函数来将 boundaryshape 二进制值转换为几何体。有关更多信息，请参阅 Athena 引擎版本 2 参考中的 地理空间函数的更改。

SELECT counties.name,
        COUNT(*) cnt
FROM counties
CROSS JOIN earthquakes
WHERE ST_CONTAINS (ST_GeomFromLegacyBinary(counties.boundaryshape), ST_POINT(earthquakes.longitude, earthquakes.latitude))
GROUP BY  counties.name
ORDER BY  cnt DESC

此查询返回：

+------------------------+
| name             | cnt |
+------------------------+
| Kern             | 36  |
+------------------------+
| San Bernardino   | 35  |
+------------------------+
| Imperial         | 28  |
+------------------------+
| Inyo             | 20  |
+------------------------+
| Los Angeles      | 18  |
+------------------------+
| Riverside        | 14  |
+------------------------+
| Monterey         | 14  |
+------------------------+
| Santa Clara      | 12  |
+------------------------+
| San Benito       | 11  |
+------------------------+
| Fresno           | 11  |
+------------------------+
| San Diego        | 7   |
+------------------------+
| Santa Cruz       | 5   |
+------------------------+
| Ventura          | 3   |
+------------------------+
| San Luis Obispo  | 3   |
+------------------------+
| Orange           | 2   |
+------------------------+
| San Mateo        | 1   |
+------------------------+

其他资源

有关地理空间查询的更多示例，请参阅以下博客文章：