読者です 読者をやめる 読者になる 読者になる

Yamotty Blog

プロダクトマネージャーの雑記

ホワイトクリスマスのために東京都民はどこへ行けばよいか - BigQueryに訊く

BigQueryにはGoogle が用意した一般公開のデータセットが置かれており、クエリを叩くことで自由に分析をすることが可能だ。1TBのサインアップボーナスで練習したり遊ぶにはもってこいの教材である。

f:id:yamo3:20161127232358p:plain

そこで今回は過去の気象統計データ『GSOD』から、ホワイトクリスマスを過ごすため、東京都民は12/24にどこへ行けばよいかというイシューついて調べてみようと思う。

0 / 準備 - 気象統計データを使う

  • BigQuery内の一般公開データセットの中に、NOAA Global Surface Summary of the Day Weatherというデータセットがある。
  • 詳しくはBigQueryのdocumentを参照。National Oceanic and Atmospheric Administrationが提供している気象データをGoogle がBigQueryで利用できるようにぶち込んでくれたものですね。

この公開データセットは米国海洋大気庁(NOAA)によって作成されたもので、空軍戦闘気候学センターから取得された全世界のデータを含んでいます。このデータセットは 1929~2016 年の間に 9,000 か所以上の観測地点から収集された GSOD データを網羅しています。

  • このデータセットの構成はgsod2015のような、その年の全観測地点における気象データが入ったテーブルと、stationという観測地点の基本データが入ったマスターテーブルの2種類がある。

それでは早速見ていこう。

1 / 日本国内に観測地点はいくつあるか?

  • まず今回の対象となる日本国内の観測地点について調べる。
  • 市区町村の数と、観測地点の数をカウントする。
SELECT
  country,
  EXACT_COUNT_DISTINCT(name) as CntState,
  COUNT(name) as CntUsaf
FROM
  [bigquery-public-data:noaa_gsod.stations] b
WHERE country = "JA"
  GROUP BY
  1

結果 : 364のエリアに428の観測地点🤔

f:id:yamo3:20161127141947p:plain

この364のエリアに対して、『12/24にはどこへ向かえば雪が降って着やすいのか?』よいかを算出することになる。

補足 : 東京都の観測地点は何個あるか?

  • 緯度(latitude)・経度(longitude)を区切ると都道府県周辺の観測地点数を割り出すことができる。
  • 東京都の経緯度はここで調べられる。ただし東京都は国土の南に幾つかの離島を持ち、それらが緯度と経度を南西に大きく押し下げている。
  • そこで擬似的に経度の範囲を奥多摩町役場(139.0547)から葛飾区役所(139.5050)まで、緯度の範囲を埼玉県草加市(35.8106)から神奈川県川崎市役所(35.3151)までとする。
SELECT
  country,
  EXACT_COUNT_DISTINCT(name) AS CntState,
  COUNT (name) Cnt
FROM
  [bigquery-public-data:noaa_gsod.stations] b
WHERE
  country = "JA" AND lat BETWEEN 35.3151
  AND 35.8106
  AND lon BETWEEN 139.0547
  AND 139.5050
GROUP BY
  1

結果 : 8のエリアに10箇所の観測地点🤔

f:id:yamo3:20161127142050p:plain

本土の東京都(近郊)には観測地点が意外と少ない。

2 / 12月24日に降雪した日にちが最も多い観測地点はどこか

  • 日本の全観測地点を対象に12/24の、降雪日/全観測日(便宜のため降雪率と呼ぶ)を算出する。データの重みを考慮するため、観測年数も付ける。
    • 降雪日の算出にはbigquery-public-data:noaa_gsodの中にあるsnow_ice_pelletsを利用。
SELECT
  b.name,
  ROUND(COUNT(CASE WHEN a.snow_ice_pellets ="1" THEN a.year END)/COUNT(a.year),3) as rate,
  ROUND(COUNT(a.year)/EXACT_COUNT_DISTINCT(a.stn),0) as sumpleYears,
  FROM (
  SELECT
    stn,
    wban,
    year,
    snow_ice_pellets
  FROM
    TABLE_QUERY([bigquery-public-data:noaa_gsod], 'table_id CONTAINS "gsod"')
  WHERE
    mo = "12"
    AND da = "24") a
JOIN (
  SELECT
    usaf,
    wban,
    name,
  FROM
    [bigquery-public-data:noaa_gsod.stations]
  WHERE
    country = "JA") b
ON
  a.stn=b.usaf
  AND a.wban=b.wban
GROUP BY
  1
ORDER BY
  2 DESC

結果 : 上位は北海道・東北エリア(当然)🤔

  • 以下は結果のうち降雪率TOP30をリスティングしたもの。当然の結果に笑
観測地点名 降雪率 観測年数
OMINATO 100% 1
RUMOI 92.7% 55
WAKKANAI 90.2% 46
ASAHIKAWA 88.5% 43
KITAMIESASHI 88.2% 34
KUTCHAN 88% 25
SUTTSU 87.3% 55
OMU 86.1% 36
RISHIRI 84.2% 19
SAPPORO 83% 53
CHITOSE 82.1% 28
HABORO 80.6% 36
NEW CHITOSE 79.2% 53
AOMORI 76.9% 39
IWAMIZAWA 75.7% 37
OMINATO (JASDF) 75.6% 41
MISAWA AB 73.4% 64
ABASHIRI 72.7% 55
MOMBETSU 72% 25
REBUN ISLAND 71.4% 7
OTARU 70.3% 37
FUKAURA 69% 42
MORIOKA 68.9% 61
AKITA 68.4% 49
HAKODATE 67% 53
NEMURO 65.5% 55
ESASHI 64.8% 54
MURORAN/YAKUMO 63.6% 55
MEMANBETSU 62.8% 43
OKUSHIRI 60.9% 23

3 / 東京エリアの降雪率は?

補足で使用した経緯度のフィルタを使い、東京エリアの降雪率を算出する。

SELECT
  b.name,
  ROUND(COUNT(CASE WHEN a.snow_ice_pellets ="1" THEN a.year END)/COUNT(a.year),3) as rate,
  ROUND(COUNT(a.year)/EXACT_COUNT_DISTINCT(a.stn),0) as sumpleYears,
  FROM (
  SELECT
    stn,
    wban,
    year,
    snow_ice_pellets
  FROM
    TABLE_QUERY([bigquery-public-data:noaa_gsod], 'table_id CONTAINS "gsod"')
  WHERE
    mo = "12"
    AND da = "24") a
JOIN (
  SELECT
    usaf,
    wban,
    name,
  FROM
    [bigquery-public-data:noaa_gsod.stations]
  WHERE
    country = "JA" AND lat BETWEEN 35.3151
  AND 35.8106
  AND lon BETWEEN 139.0547
  AND 139.5050) b
ON
  a.stn=b.usaf
  AND a.wban=b.wban
GROUP BY
  1
ORDER BY
  2 DESC

結果 : 東京でのホワイトクリスマス率は3%🤔

東京圏でホワイトクリスマスを迎えられる可能性は3%...。衝撃的な結果。都市数が8→6へ減っているのはブランクのデータセットがちょいちょい紛れているからか。ホワイトクリスマスのためには東京脱出が必須

観測地点名 降雪率 観測年数
TACHIKAWA (JASDF) 3.1% 65
YOKOTA AB 2.9% 68
KASTNER AAF 0% 16
ATSUGI NAS 0% 52
ATSUGI US NAVAL AIR STATION 0% 11
FUSSA 0% 19

4 / 東京から50km圏内、どこへ向かえばよいか?

最後に東京から50kmへ拡張した際に、最も降雪率の高いエリアを割り出す。経緯度をいじることでザックリと算出する。 この記事によると、

日本における 1km あたりの緯度は、だいたい0.0090133729745762 度
日本における 1km あたりの経度は、だいたい0.010966404715491394 度

とのこと。そのため緯度経度を上下左右0.5( ≒ 0.01 ✕ 50 km)ずつ拡張し、以下の範囲で算出してみる。

  • 緯度 : 34.8151 ~ 36.3106
  • 経度 : 138.5547 ~ 140.0050
SELECT
  b.name,
  ROUND(COUNT(CASE WHEN a.snow_ice_pellets ="1" THEN a.year END)/COUNT(a.year),3) as rate,
  ROUND(COUNT(a.year)/EXACT_COUNT_DISTINCT(a.stn),0) as sumpleYears,
  FROM (
  SELECT
    stn,
    wban,
    year,
    snow_ice_pellets
  FROM
    TABLE_QUERY([bigquery-public-data:noaa_gsod], 'table_id CONTAINS "gsod"')
  WHERE
    mo = "12"
    AND da = "24") a
JOIN (
  SELECT
    usaf,
    wban,
    name,
  FROM
    [bigquery-public-data:noaa_gsod.stations]
  WHERE
    country = "JA" AND lat BETWEEN 34.8151
  AND 36.3106
  AND lon BETWEEN 138.5547
  AND 140.0050) b
ON
  a.stn=b.usaf
  AND a.wban=b.wban
GROUP BY
  1
ORDER BY
  2 DESC

結果 : クリスマスは富士山へ😇😇😇

  • 統計的には14%の確率でホワイトクリスマスとなっております。12/24は富士山へGO!!💨💨
    • 注 : 閉鎖期間中なので登山不可の見込み
  • 真面目な結果としては「横須賀市」「河口湖」あたりが統計的には東京都内より2倍ほどホワイトクリスマス率が高い。このあたりを狙ってみるのは良いのではないか。
観測地点名 降雪率 観測年数
FUJISAN 14% 43
FUJI (JASDF) 13.3% 30
YOKOSUKA NAVAL STATION 6.9% 29
KAWAGUCHIKO 5.6% 36
TACHIKAWA (JASDF) 3.1% 65
YOKOTA AB 2.9% 68
CHICHIBU 2.7% 37
TATEYAMA 2.3% 43
TOKYO INTL 1.6% 63
IRUMA 1.4% 35

まとめ

  • 東京都民が手軽にホワイトクリスマスを狙おうと思った場合、富士山へ急行するか、横須賀へ向かえという結論となった。
  • なお私の実家のある青森では過去39年間で76.9%のホワイトクリスマスという驚異的な結果を叩き出しており、正直個人的にホワイトクリスマスなんて当たり前だったというドヤを記しておこう。
  • 青森は新幹線で3.5時間で着くし、温泉も食べ物も豊富。12/24、都民は富士山より青森へ行こう🚅💨(PR)