GoogleCloudPlatform
GoogleBigQuery
bigquery

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

More than 1 year has passed since last update.


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

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を使うにはちょっとハードルがあるかと。

そこで、データを見てみたところ緯度経度も入っており、コレを使ってみればよいのでは?と思いました。

000087768.jpg

国土地理院で公開されている地図を見たところ緯度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_result.jpg

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

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


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をつけて折れ線グラフを作ってみました。

data_studio.jpg

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

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