Overture MapsをSnowflakeで使ってみた（Snowflake Marketplace）

## Overture Mapsの一般提供版がリリースされました
Amazon, Meta, Microsoft、tomtom, esriなどが協力し整備しているオープンな地図データである「Overture Maps」が2024年7月24日にGA版としてリリースされました！

https://overturemaps.org/overture-maps-foundation-releases-general-availability-of-its-open-maps-datasets/

昨年のβ版データ公開時点ではダウンロードするにはS3経由・Azure経由でダウンロードする必要がありましたが、今は[Pythonのコマンドラインツール](https://github.com/OvertureMaps/overturemaps-py)が公開されており、サクッと利用しやすくなっています。

https://docs.overturemaps.org/getting-data/


## SnowflakeマーケットプレイスにOverture Mapsあります！
Pythonのコマンドラインツールは使いやすいのですが、元のデータセットが巨大なため、もっと利用しやすい方法は無いかなと思っていたところ、SnowflakeのマーケットプレイスにCARTOがデータセット一式を公開しているとの記事を見つけました！BigQueryでも利用できるみたいです。
（2024年5月2日から公開されているみたいです）

https://carto.com/blog/overture-maps-data-now-on-the-cloud-use-it-with-carto

マーケットプレイスで公開されているということは、利用登録してあとはクエリするだけでOKということです！便利！

Snowsightにログインして、`データ製品` → `Marketplace` → `overture`で検索
すれば6つのデータセットがヒットします。
今回は`Places`と`Buildings`を使ってみます。

![snowflake_marketplace_carto_overturemaps.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2930990/8e6305c5-068f-76af-0ea9-49d171bfbd80.png)

`Places`はPOIのデータセットです。
施設や場所の名前、カテゴリー、住所、ジオメトリ（ポイント）などが格納されています。

`Buildings`は建物ポリゴンのデータセットです。
建物名称や階数などが付与されているフィーチャーもあるようです。

それぞれのデータの詳細説明はこちらです。

https://docs.overturemaps.org/guides/


## クエリしてQGISで表示してみた
### Placesデータセット

`addresses`カラムと`geometry`カラムのそれぞれを利用して、日本国内の場所情報を取得してみましょう。

データ量は以下のとおりとなっていました。
```sql
select count(id)
from overture_maps__places.carto.place
;
/*
COUNT(ID)
52849527
*
```

#### `addresses`カラムでクエリする
まずは`addresses`カラムを利用してみます。
以下のようにクエリして、場所名、カテゴリ、住所関連情報、wktを出力しました。
カラムによってはJSON形式になっているので展開が必要です。

`addresses`の`country`キーに国情報があるので、それを利用しました。

```sql
select
    id,
    parse_json(names):primary as name,
    parse_json(categories):main as main_category,
    parse_json(addresses):list[0].element.country as country,
    parse_json(addresses):list[0].element.locality as locality,
    parse_json(addresses):list[0].element.postcode as postcode,
	parse_json(addresses):list[0].element.freeform as freeform,
    st_aswkt(geometry) as wkt
from
    OVERTURE_MAPS__PLACES.CARTO.PLACE
where
    parse_json(addresses):list[0].element.country = 'JP'
limit 1000
;
```

上記を可視化するとこんな分布になっています！
何故か国外のポイントになっている施設が多数存在します😅

![jp_place.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2930990/4b408b6c-6643-d2cb-8b89-61984be0fbec.png)
※背景地図：国土地理院 地理院地図（以降、同様）

国外にプロットされたデータについて詳しくチェックすると、`geometry`以外の情報は合っていそうなものが多数でした。データセット作成時の不具合？

（※マーケットプレイス上のデータだけの現象なのか、もとのデータも同様なのかを[Overture Maps Explorer](https://explore.overturemaps.org)で簡易的に確認しましたが、同様の結果でした。）

#### `geometry`でクエリする
次に、`addresses`の情報ではなく、`geometry`をもとに抽出してみました。
東京都内のデータを抽出したいので、こちらもSnowflake Marketplaceに公開されているtruestar様の[JAPANESE PREFECTURE DATA](https://podb.truestar.co.jp/archives/pref-data)を利用しました。

```sql
-- 東京都離島を除くポリゴンを利用
select
    a.id,
    parse_json(names):primary as name,
    parse_json(categories):main as main_category,
    parse_json(addresses):list[0].element.country as country,
    parse_json(addresses):list[0].element.locality as locality,
    parse_json(addresses):list[0].element.postcode as postcode,
	parse_json(addresses):list[0].element.freeform as freeform,
    st_aswkt(geometry) as wkt
from
    OVERTURE_MAPS__PLACES.CARTO.PLACE a
	join PREPPER_OPEN_DATA_BANK__JAPANESE_PREFECTURE_DATA.E_PODB.E_PR_FD20 b
	on st_within(a.geometry, b.polygon_jp_pref_3_light) -- 東京都ポリゴン内のポイントだけを抽出
where
	b.pref_name = '東京都'
limit 1000
;
```

結果はこんな感じです！
お店から観光スポット、史跡など、幅広くカバーされています。

![スクリーンショット 2024-08-26 15.52.21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2930990/a89be296-6113-0f0d-5507-087af98f3fec.png)


### Buildingsデータセット
次に、`buildings`で試してみましょう！
件数はこんな感じです。（Placesと二桁違う）
```sql
select count(id)
from overture_maps__buildings.carto.building
;
/*
COUNT(ID)
2354376929
 */
 ```

`Places`のときと同様に東京都内のデータを抽出してみましょう！
と思い下記のクエリを実行したのですが、全然完了しません...。

```sql
select
    a.id,
    st_aswkt(geometry) as wkt
from
    overture_maps__buildings.carto.building a
	join PREPPER_OPEN_DATA_BANK__JAPANESE_PREFECTURE_DATA.E_PODB.E_PR_FD20 b
	on st_within(a.geometry, b.polygon_jp_pref_3_light)
    and b.pref_name = '東京都'
limit 10
;
```

<br>

ポリゴン同士の計算は重いのかもしれないので、`bbox`カラムの数字を利用して抽出を試してみましょう！
ということで下記のクエリを試しましたが、こちらでも全然クエリが完了せず...。
bboxカラムの値をパースしてから評価しているので、むしろ重い処理になってそうですね。

```sql
with target_pref as (
    select
        st_xmax(polygon_jp_pref_3_light) as xmax,
        st_xmin(polygon_jp_pref_3_light) as xmin,
        st_ymax(polygon_jp_pref_3_light) as ymax,
        st_ymin(polygon_jp_pref_3_light) as ymin
    from
        PREPPER_OPEN_DATA_BANK__JAPANESE_PREFECTURE_DATA.E_PODB.E_PR_FD20
    where
        pref_name = '東京都'
)
select
    id,
    st_aswkt(geometry) as wkt
from
    overture_maps__buildings.carto.building
where
    parse_json(bbox):xmax <= (select xmax from target_pref)
    and parse_json(bbox):xmin >= (select xmin from target_pref)
    and parse_json(bbox):ymax <= (select ymax from target_pref)
    and parse_json(bbox):ymin >=  (select ymin from target_pref)
limit 10
;
```

悔しいのでマーケットプレイスからのクエリは諦めて、Pythonのコマンドラインツールを利用して、渋谷区内の建物ポリゴンをダウンロードしてみます。
※ローカルにダウンロードするので、データサイズを気にして対象範囲を渋谷区に狭めています。

以下のクエリで渋谷区のbboxの値を取得して、コマンドラインツール用のコマンドを生成します。
```sql
select
    concat(
        'overturemaps download --bbox=',
        st_xmin(polygon_jp_city_light),
        ',',
        st_ymin(polygon_jp_city_light),
        ',',
        st_xmax(polygon_jp_city_light),
        ',',
        st_ymax(polygon_jp_city_light),
        ' -f geojson --type=building -o shibuya_building.geojson'
    ) as command
from
    PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.E_PODB.E_CI_FD20
where
    pref_name = '東京都'
    and city_name = '渋谷区'
;
```

実行します。
```python
overturemaps download --bbox=139.661756,35.641658,139.72356,35.691991 -f geojson --type=building -o shibuya_building.geojson
```

取得できました！（約8万件）
そこまで時間かからずにダウンロード完了しました。
![builsings_shibuya.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2930990/ca260386-dd41-4a7f-66f3-e103f160a67e.png)

## おわりに
`Places`のデータセットからは施設や場所の情報が簡単に取得できるので活用の機会が多そうですね！
`Buildings`は必要なエリアに絞った形での取得はクエリに時間がかかりすぎて取得できずでした…。（Pythonのコマンドラインツールは早かった。）
Snowflake上での利用頻度が高いのであれば、bboxカラムをパースしたマテリアライズド・ビューを作っておくとかでしょうか？要検討したいです！