Amazon AthenaでAmazon S3のデータを空間検索してQGISで可視化してみた

![img](https://day-journal.com/memo/images/try-089_00.png)

### Amazon AthenaでAmazon S3のデータを空間検索してQGISで可視化してみました :tada:  

<br>

詳細として下記について説明します。

- [事前準備](https://qiita.com/dayjournal/items/441f89794f6fb5656846#%E4%BA%8B%E5%89%8D%E6%BA%96%E5%82%99)
- [クエリ保存先を設定](https://qiita.com/dayjournal/items/441f89794f6fb5656846#%E3%82%AF%E3%82%A8%E3%83%AA%E4%BF%9D%E5%AD%98%E5%85%88%E3%82%92%E8%A8%AD%E5%AE%9A)
- [テーブル作成](https://qiita.com/dayjournal/items/441f89794f6fb5656846#%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E4%BD%9C%E6%88%90)
- [空間検索](https://qiita.com/dayjournal/items/441f89794f6fb5656846#%E7%A9%BA%E9%96%93%E6%A4%9C%E7%B4%A2)

<br>

## 事前準備

[Amazon Athena](https://aws.amazon.com/athena)で利用するGISデータを準備します。今回は事前に[QGIS](https://qgis.org)で4種類のサンプルデータを作成しました。  

ポイント・ライン・ポリゴンのGISデータをそれぞれCSV(TSV形式)で準備しました。
![img](https://day-journal.com/memo/images/try-089_01.png)

追加で100万ポイントのGISデータをCSV(TSV形式)で準備しました。
![img](https://day-journal.com/memo/images/try-089_02.png)

4種類のCSV(TSV形式)を任意の名称でS3に保存しました。
![img](https://day-journal.com/memo/images/try-089_03.png)

今回のサンプルデータをGitHubに登録したのでぜひご利用ください。  

https://github.com/dayjournal/data/tree/main/try-089

これで事前のGISデータの準備は完了です！

S3バケット名にgeojsonという名称が入っていますが今回はCSV(TSV形式)を利用します。元々はGeoJSON形式で手軽に利用しようとしたのですが、現状ではGeoJSON形式は非対応(Hive JSON SerDeという形式はあるみたいです)なので今回はCSV(TSV形式)の中にWKTを定義しています。

<br>

## クエリ保存先を設定

Amazon Athenaでクエリ保存先を設定する方法です。  

事前に任意の名称でクエリ保存先のS3バケットを準備します。
![img](https://day-journal.com/memo/images/amazon-athena-001_01.png)

AWSマネジメントコンソール → Athenaをクリック。
![img](https://day-journal.com/memo/images/amazon-athena-001_02.png)

「クエリエディタを詳しく確認する」をクリック。
![img](https://day-journal.com/memo/images/amazon-athena-001_03.png)

「設定を表示」をクリック。
![img](https://day-journal.com/memo/images/amazon-athena-001_04.png)

「管理」をクリック。
![img](https://day-journal.com/memo/images/amazon-athena-001_05.png)

クエリ保存先のS3バケットを指定 → 「保存」をクリック。
![img](https://day-journal.com/memo/images/amazon-athena-001_06.png)

クエリの保存先が設定される。
![img](https://day-journal.com/memo/images/amazon-athena-001_07.png)

これでクエリ保存先の設定は完了です！

<br>

## テーブル作成

Amazon Athenaでテーブルを作成する方法です。  

Athenaのエディタ → テーブルとビュー作成 → 「S3バケットデータ」をクリック。
![img](https://day-journal.com/memo/images/amazon-athena-002_02.png)

テーブル名・データベース選択・対象のS3バケット指定・データ形式・カラム設定。プレビュー確認 → 「テーブルを作成」をクリック。
![img](https://day-journal.com/memo/images/amazon-athena-002_03.png)

今回は4つの任意のテーブルを作成しました。対象のテーブル → 「テーブルをプレビュー」をクリック。
![img](https://day-journal.com/memo/images/amazon-athena-002_04.png)

取得したレコードが表示されます。
![img](https://day-journal.com/memo/images/amazon-athena-002_05.png)

これでテーブルの作成は完了です！

<br>

## 空間検索

最後に、Amazon Athenaで[空間検索](https://docs.aws.amazon.com/athena/latest/ug/geospatial-functions-list-v2.html)をする方法を紹介します。  

ポリゴンから重心のポイントを取得してみます。結果データをダウンロードします。

```sql
SELECT "geojson-database"."geojson-polygon-table"."name", 
ST_Centroid(ST_GeometryFromText("geojson-database"."geojson-polygon-table"."wkt")) 
FROM "geojson-database"."geojson-polygon-table";
```

![img](https://day-journal.com/memo/images/try-089_04.png)

ダウンロードしたデータをQGISで可視化すると処理結果データを表示確認できます。
![img](https://day-journal.com/memo/images/try-089_05.png)

<br>

ラインから始点のポイントを取得してみます。結果データをダウンロードします。

```sql
SELECT "geojson-database"."geojson-line-table"."name", 
ST_StartPoint(ST_GeometryFromText("geojson-database"."geojson-line-table"."wkt")) 
FROM "geojson-database"."geojson-line-table";
```

![img](https://day-journal.com/memo/images/try-089_06.png)

ダウンロードしたデータをQGISで可視化すると処理結果データを表示確認できます。
![img](https://day-journal.com/memo/images/try-089_07.png)

<br>

ポリゴン内に含まれるポイントのみを取得してみます。結果データをダウンロードします。

```sql
SELECT "geojson-database"."geojson-point-table"."name", "geojson-database"."geojson-point-table"."wkt" 
FROM "geojson-database"."geojson-point-table", "geojson-database"."geojson-polygon-table" 
WHERE ST_Within(ST_GeometryFromText("geojson-database"."geojson-point-table"."wkt"), ST_GeometryFromText("geojson-database"."geojson-polygon-table"."wkt"));
```

![img](https://day-journal.com/memo/images/try-089_08.png)

ダウンロードしたデータをQGISで可視化すると処理結果データを表示確認できます。
![img](https://day-journal.com/memo/images/try-089_09.png)

<br>

100万件のポリゴン内に含まれるポイントのみを取得してみます。大量GISデータの検索でも高速にレスポンスされました。結果データをダウンロードします。

```sql
SELECT "geojson-database"."geojson-randompoint-table"."name", "geojson-database"."geojson-randompoint-table"."wkt" 
FROM "geojson-database"."geojson-randompoint-table", "geojson-database"."geojson-polygon-table" 
WHERE ST_Within(ST_GeometryFromText("geojson-database"."geojson-randompoint-table"."wkt"), ST_GeometryFromText("geojson-database"."geojson-polygon-table"."wkt"));
```

![img](https://day-journal.com/memo/images/try-089_10.png)

ダウンロードしたデータをQGISで可視化すると処理結果データを表示確認できます。
![img](https://day-journal.com/memo/images/try-089_11.png)
![img](https://day-journal.com/memo/images/try-089_12.png)

Amazon Athenaを利用することでS3に登録したデータを空間検索することが可能になります！

<br>
<br>

Amazon AthenaとAmazon S3とQGISについて、他にも記事を書いています。よろしければぜひ :bow:
<a href="https://day-journal.com/memo/tags/Amazon-Athena" target="_blank">tags - Amazon Athena</a>
<a href="https://day-journal.com/memo/tags/Amazon-S3/" target="_blank">tags - Amazon S3</a>
<a href="https://day-journal.com/memo/tags/QGIS" target="_blank">tags - QGIS</a>

やってみたシリーズ :grinning:
<a href="https://day-journal.com/memo/tags/Try" target="_blank">tags - Try</a>

https://qiita.com/dayjournal/items/fa3aac5a24e9605f2958

https://qiita.com/dayjournal/items/e7eeeb8f527bbbbe42cf

<br>
<br>

<a href="https://dayjournal.booth.pm" target="_blank"><img src="https://day-journal.com/memo/images/etc/book.png" alt="book"></a>