BigQueryで気象データを使ってみる

祝★気象データが入りました！

BigQueryのPublic-dataに気象データが入るようになりました。
http://googlecloudplatform-japan.blogspot.jp/2016/10/ghcn-bigquery.html
これはアメリカのGHCNってやつらしいです。詳しくはブログを見てください。

※2016/10/07現在
2016/09/28までのデータが入ったというだけで、毎日更新されるわけではなさそうです。
こういうのって自動的にGoogle側で入れてくれると便利だよなぁと思ったり。
甘えるなって話しですかねぇ。(´・ω・`)

※2016/10/11
このデータですが、TMAX（最高気温）、TMIN（最低気温）がちょいちょい抜けていることがわかりました。

じゃ、使ってみましょう。

日本の観測ポイントを知る

観測ポイントは [bigquery-public-data:ghcn_d.ghcnd_stations] っていうテーブルに入っています。約10万の観測ポイントがあり、そこから自分が知りたいポイントを指定します。
それをさすがに目で探すのは大変です。ブログにはCloudDataLabで地図上にプロットして、そこから探せば？的なことを書いていますが、CloudDataLabを使うにはちょっとハードルがあるかと。
そこで、データを見てみたところ緯度と経度も入っており、コレを使ってみればよいのでは？と思いました。

国土地理院で公開されている地図を見たところ緯度30°-45°で経度130°-150°で北海道～九州まで、緯度25°-30°で経度125°-130°で沖縄をだいたいカバーできることがわかります。
じゃ、これをSQLにしてみましょう。

station.sql

SELECT
 *
FROM
(SELECT
  *
FROM
  [bigquery-public-data:ghcn_d.ghcnd_stations]
WHERE
 latitude between 30 and 45 and
 longitude between 130 and 150),
(SELECT
  *
FROM
  [bigquery-public-data:ghcn_d.ghcnd_stations]
WHERE
 latitude between 25 and 30 and
 longitude between 125 and 130)
ORDER BY
 2,3

結果はこんな感じになります。

ちなみにもっと絞りたい！って言う人は都道府県別の緯度経度も記載があります。

※あ、日本だけで抜き出すのであればこっちでも出来た

station_ja.sql

SELECT
  *
FROM
  [bigquery-public-data:ghcn_d.ghcnd_stations]
WHERE
  left(id,2) = 'JA'

気象データとJOINしてみる。

では、2016年の気象データが入っている[bigquery-public-data:ghcn_d.ghcnd_2016]から先ほど抽出した日本だけを抜き出してみましょう。
JOINするのはidというカラム同士で行います。elementに情報の区分が入っており、代表的なものはPRCP（降雨量）、SNOW（降雪量）、SNWD（積雪量）、TMAX（最高気温）、TMIN（最低気温）だそうです。自分的にはTAVG（平均気温）も使えると思います。
で、各elementに対してvalueが入っています。

※注：PRCPなのにSNWDがあったりします。うーん。。。

今回は大雑把ではありますが、PRCPだけ見てみましょう。

prcp.sql

SELECT
  W.date date,
  S.name name,
  W.element element,
  W.value value
FROM
[bigquery-public-data:ghcn_d.ghcnd_2016] W
JOIN
[bigquery-public-data:ghcn_d.ghcnd_stations] S
ON
W.id = S.id
WHERE
  left(W.id,2) = 'JA' and
  W.element = 'PRCP'
order by
  date

そして、これを今回はViewに保存してみてDataStudioで可視化してみます。
Filter control（観測地点）にnameを設定し、data rangeをつけて折れ線グラフを作ってみました。

せっかく緯度と経度があるのでmapにしてみようかと思いましたが、もう眠いので勘弁してください（笑）

取り急ぎ使ってみたという感じなので、改版したり、mapをがんばってみたりするかもです。