BigQueryGISでOpenStreetMapのデータを触ってみる

## はじめに
**⚠️以下の内容をお手元で試すと、BigQueryで数百円~の課金が発生します⚠️**

## OpenStreetMapとは

https://www.openstreetmap.org/

有志で作られてるオープンな地図みたいです。wikiみたいに誰でも編集追加できるようです。
地図情報は大きく三つのオブジェクトで構成されています

| オブジェクト | 表すもの | 役割                     | サンプル |
| -------- | ------ | ------------------------ | ------ |
| node     | 点 　　 | 建物、ラベル、交差点など       |[東京駅](https://www.openstreetmap.org/node/2149761647)|
| way      | 線(面)  | 建物、道、路線、境界、川などの一部|[東京スカイツリー](https://www.openstreetmap.org/way/288269147)|
| relation | 関係性  | 一つのnodeやwayで表せないもの |[皇居](https://www.openstreetmap.org/relation/7676266)|

これらのオブジェクトにtagをつけることで役割を明確にしています。

このデータがBigQueryの公開データセットにて公開されています[^1]ので、それを触っていきます。

## BigQueryGIS

BigQuery上では地理データ(GEOGRAPHY型)を扱うことができます。
これにより、「ある点とある点がどれくらい離れているのか？」や「ある点から300m以内の特定の条件を満たす地点はいくつあるのか」などSQLで記述することができます。

https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions?hl=JA
こちらにある様々な関数がSQLで使えます。

## 新宿区だけのデータに絞る

BigQueryに格納されているデータは全世界のデータになっており、毎回フルアクセスするとBQの料金高くなってしまいます。
ですので新宿区だけに絞ってみましょう。

OSMで検索した結果、relationの[ID1758858](https://www.openstreetmap.org/relation/1758858)が新宿区の境界を表していることがわかりました。
ということで、これの内側のnode,way,relationだけが欲しいです。
BigQueryの公開データセットは[こちら](https://console.cloud.google.com/bigquery?p=bigquery-public-data&d=geo_openstreetmap&page=dataset)です。
こちらの[planet_nodes](https://console.cloud.google.com/bigquery?p=bigquery-public-data&d=geo_openstreetmap&t=planet_nodes&page=table)、[planet_ways](https://console.cloud.google.com/bigquery?p=bigquery-public-data&d=geo_openstreetmap&t=planet_ways&page=table)、[planet_relations](https://console.cloud.google.com/bigquery?p=bigquery-public-data&d=geo_openstreetmap&t=planet_relations&page=table)がnode, way, relationに対応しています[^2]。

とりあえず、お遊び用のデータセットを作っておきます。`osm_test`

一旦nodeだけで絞ってみましょう。

```SQL
CREATE TABLE osm_test.nodes AS
WITH area AS (
  SELECT geometry AS shinjuku
  FROM `bigquery-public-data.geo_openstreetmap.planet_relations`
  WHERE id = 1758858
)
SELECT id, all_tags, latitude, longitude
FROM `bigquery-public-data.geo_openstreetmap.planet_nodes`
CROSS JOIN area
WHERE ST_WITHIN(geometry, shinjuku)
```
**297GB=約$1.5**

これで、`osm_test`データセットに新宿区だけに絞った`nodes`テーブルが作成されます。
planet_nodesテーブルのgeometryカラムはなぜかnullが多いのでlatitude,longitudeをselectしています。
基本的にGISの絞り込みはCROSS JOINで結合し、WHERE句で絞り込む感じで書きます。
今回の場合は新宿のエリア内のnodeだけが欲しいのでST_WITHIN関数を使用しました。

# コンビニを抽出

コンビニを示すタグのkeyとvalueの組み合わせがなんなのかOSMのwikiで調べると、
https://wiki.openstreetmap.org/wiki/JA:Tag:shop%3Dconvenience
のページが見つかります。
どうやら、keyがshopでvalueがconvenienceのものを見つければいいようです。

`all_tags`から特定のtagを抽出するためにヘルパー関数を作っておくと便利

```sql
CREATE FUNCTION osm_test.get(
  key STRING, tags ARRAY<STRUCT<k STRING, v STRING>>
) AS ((
  SELECT v
  FROM UNNEST(tags)
  WHERE key = k
));
```

keyがshopでvalueがconvenienceを抽出するだけ！

```sql
SELECT
  id,
  osm_test.get('name', all_tags) AS name,
  ST_GEOGPOINT(longitude, latitude) AS point,
FROM osm_test.nodes
WHERE osm_test.get('shop', all_tags) = "convenience"
```

このままじゃよくわからないので、

[BigQuery Geo Viz](https://cloud.google.com/bigquery/docs/gis-visualize)を使って表示してみましょう。
![shijuku_convenience.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/287793/3dc631d6-b719-048c-67f1-b0128eea56af.png)
確かに新宿区内のコンビニが取得できている気がします。



[^1]: 謎の更新頻度をほこっている
[^2]: 他にも色々テーブルがあるようですがよくわかっていません。わかる人教えて。