26
21

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

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

Last updated at Posted at 2016-10-07

#祝★気象データが入りました!
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をがんばってみたりするかもです。

26
21
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
26
21

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?