はじめに

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

OpenStreetMapとは

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

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

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

BigQueryGIS

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

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

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

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

OSMで検索した結果、relationのID1758858が新宿区の境界を表していることがわかりました。
ということで、これの内側のnode,way,relationだけが欲しいです。
BigQueryの公開データセットはこちらです。
こちらのplanet_nodes、planet_ways、planet_relationsがnode, way, relationに対応しています²。

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

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

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を抽出するためにヘルパー関数を作っておくと便利

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を抽出するだけ！

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を使って表示してみましょう。

確かに新宿区内のコンビニが取得できている気がします。