範例：地理空間查詢 - Amazon Athena
其他資源

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

範例：地理空間查詢

本主題中的範例會根據可用的範例資料建立兩個資料表， GitHub 並根據資料查詢資料表。範例資料位於下列檔案中，僅供說明之用且不保證準確無誤：

  • earthquakes.csv – 列出在加州發生的地震。範例 earthquakes 資料表使用此資料的欄位。

  • california-counties.json— 以ESRI符合標準的 Geo JSON 格式列出加州州的縣資料。該資料包含許多欄位，例如 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   |
+------------------------+

其他資源

如需地理空間查詢的其他範例，請參閱下列部落格文章：