はじめに
⚠️以下の内容をお手元で試すと、BigQueryで数百円~の課金が発生します⚠️
OpenStreetMapとは
有志で作られてるオープンな地図みたいです。wikiみたいに誰でも編集追加できるようです。
地図情報は大きく三つのオブジェクトで構成されています
| オブジェクト | 表すもの | 役割 | サンプル |
|---|---|---|---|
| node | 点 | 建物、ラベル、交差点など | 東京駅 |
| way | 線(面) | 建物、道、路線、境界、川などの一部 | 東京スカイツリー |
| relation | 関係性 | 一つのnodeやwayで表せないもの | 皇居 |
これらのオブジェクトに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が新宿区の境界を表していることがわかりました。
ということで、これの内側のnode,way,relationだけが欲しいです。
BigQueryの公開データセットはこちらです。
こちらのplanet_nodes、planet_ways、planet_relationsがnode, way, relationに対応しています2。
とりあえず、お遊び用のデータセットを作っておきます。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を使って表示してみましょう。
確かに新宿区内のコンビニが取得できている気がします。