例: 地理空間クエリ - Amazon Athena

例: 地理空間クエリ

このトピックの例では、GitHub で利用可能なサンプルデータから 2 つのテーブルを作成し、そのデータに基づいてテーブルをクエリします。サンプルデータは、あくまで例に過ぎず、正確であることは保証されていません。以下のファイルにあります。

  • earthquakes.csv – カリフォルニアで発生した地震をリスト化します。この earthquakes テーブルの例では、このデータのフィールドを使用しています。

  • california-counties.jsonESRI 準拠の GeoJSON 形式でカリフォルニア州の郡データをリスト化します。データには AREAPERIMETERSTATECOUNTY、および NAME など多くのフィールドが含まれていますが 、サンプルの counties テーブルでは Name (文字列) と BoundaryShape (バイナリ) の 2 つしか使用していません。

    注記

    Athena は、com.esri.json.hadoop.EnclosedJsonInputFormat を使用して 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://my-query-log/csv/';

以下のコード例は、counties という名前のテーブルを作成します。

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/';

以下のサンプルクエリは、CROSS JOIN 関数を counties および earthquake テーブルで使用します。この例では、境界に ST_POINT で指定される地震位置が含まれる郡のクエリに ST_CONTAINS を使用します。クエリはこれらの郡を名前でグループ化し、発生数順に並べて、それらを降順で返します。このクエリは、Athena エンジンバージョン 2 を使用します。

注記

Athena エンジンバージョン 2 では、ST_CONTAINS のような関数が VARBINARY 型を入力としてサポートしなくなっています。このため、この例では ST_GeomFromLegacyBinary(varbinary) 関数を使用して boundaryshape バイナリ値をジオメトリに変換します。Athena エンジンバージョン 1 を使用している場合は、この変換を行う必要はありません。詳細については、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 | +------------------------+

その他のリソース

地理空間クエリのその他の例については、以下のブログ記事を参照してください。