BigQueryに聞く天気予報

 2023.12.04 XIMIX 中根

gbq_weather_000日本情報通信の中根です。

本記事はNI+C TeamGCP Advent Calendar 2023 4日目の記事となります。
若干タイトル詐欺ですがBigQueryくんに天気を聞いてみました。

目標

目標はシンプルです。
BigQueryくんだけを使って天候情報を導き出すことです。

準備

なにはともあれデータです。
今回利用するのは以下のBigQuery Public Dataset です。

はい、すでにこの時点で気づくかと思いますがMLによる予測ではないです。
予測されたデータを使います。

Step.1 予測する地点のデータを用意する

まずは天気を確認する範囲として47都道府県とします。
政府統計ポータルサイトのe-Statから地図データは取得出来るのでこちらを使うことが多いのですが今回はBigQuery縛り(?)なのでOpenStreetMapより抽出します。
OpenStreetMapは自由に利用、編集ができるフリーの地理データベースです。
コミュニティ運営されておりユーザーベースのWikiもあります。
 
BigQueryのパブリックデータとして昔からありますが今回は47都道府県を以下のSQLで抽出します。
 
CREATE OR REPLACE TABLE blog.osm_prefectures AS
SELECT
*
FROM `bigquery-public-data.geo_openstreetmap.planet_features_multipolygons`
WHERE
("is_in:country", "Japan") IN (SELECT (KEY, value) FROM UNNEST(all_tags))
AND ("admin_level", "4") IN (SELECT (KEY, value) FROM UNNEST(all_tags))
OpenStreatMapのデータはそこそこ大きいので事前に必要な範囲のみを抽出しましょう。
上記のクエリで日本の都道府県のポリゴンを抽出した結果14MBぐらいになります。

Step.2 天気のデータを抽出し予測地点に掛け合わせる

天気のデータはNOAAのデータを使用します。
予報日であるcreation_timeがパーティションカラムなので必ず指定しましょう。
指定しないと数百TBのクエリを実行してしまう可能性があります。

-- 予報日付
DECLARE weather_date DATE DEFAULT "2023-12-01";

WITH osm_prefectures AS(SELECT * FROM `<PROJECT_ID>.blog.osm_prefectures` )
, join_noaa AS(
  SELECT
    osm.geometry
    ,  (SELECT value FROM UNNEST(osm.all_tags) WHERE key = 'name') as prefecture
    , noaa.*
    FROM 
    osm_prefectures osm
    ,`bigquery-public-data.noaa_global_forecast_system.NOAA_GFS0P25` noaa 
    WHERE
        noaa.creation_time = weather_date
        AND ST_COVEREDBY(noaa.geography, osm.geometry)
), calc_precipitation AS(
  SELECT
    ANY_VALUE(geometry) as geometry
    , prefecture
    , DATE(time) as date
    , hours
    , AVG(temperature_2m_above_ground) avg_temperature
    , AVG(total_precipitation_surface) / 6 as avg_precipitation -- 降水量は6時間毎の累計のため1時間あたりに直すために6で除算
    , AVG(total_cloud_cover_entire_atmosphere) avg_cloud_cover
  FROM 
    join_noaa
    JOIN UNNEST(forecast) AS forecast
  WHERE DATE(time)  = weather_date AND hours = 12
  GROUP BY 2,3,4
)
SELECT
  *
  , CASE 
      WHEN avg_precipitation > 1 THEN "雨"
      WHEN avg_cloud_cover <= 10 THEN "快晴"
      WHEN avg_cloud_cover > 10 AND avg_cloud_cover <= 80 THEN "晴れ"
      ELSE "曇り"
    END as weather
FROM calc_precipitation
ORDER BY 2, 3
  

上記クエリでは該当都道府県に含まれる予報データ(降水量と雲の割合)から天気を決定します。

気をつける点としては降水量であるtotal_precipitation_surfaceは6時間毎の累計降水量となります。
WINDOW関数を使って1時間あたりの降水量に直すと良いですが今回は(アドベントカレンダーの締め切り的に)6~12時の累計値をシンプルに6で割るだけにします。

Step.3 データの可視化

BigQueryからGISデータを可視化する場合はBigQuery Geo Vizがあります。
「データを探索」からアクセス出来るツールでおなじみかと思います。

bq_weather_001

BigQuery Geo Vizでそこそこのデータ量を処理しようとするとなかなか重くなりがちなのでノートブックを使うのも良いでしょう。
Colab Enterpriseも登場したのでBigQueryのWeb UI上で表示することが可能となります。

まとめ

タイトル詐欺でしたがBigQueryとパブリックデータだけで天気がわかりました。
今回は47都道府県の天気としていましたが実際のビジネスでは店舗や配送経路など様々なデータに掛け合わせることが出来るでしょう。

可視化についてはBigQuery Geo Vizはコンソールからシームレスに移動できるため、まずは可視化をというレベルに最適です。
パフォーマンスや独自の可視化を行いたい場合はColab Enterpriseをはじめとしたノートブックが選択肢となるでしょう。
BigQueryStudioにより一貫したある操作が可能になりましたのでぜひ利用してみてはいかがでしょうか。

Google Cloud、Google Workspace に関するご相談はXIMIXへ!

Google Cloud、Google Workspaceに関する お問い合わせはこちら

執筆者紹介

Yohei Nakane
XIMIX 中根
2012 年日本情報通信株式会社へ入社。 入社後はインフラ エンジニアとして活躍。 2018 年より Google Cloud を担当。 アプリ開発からデータ基盤、クラウド全体のアーキテクトと幅広く担当。 Google Cloud Top Engineer 2023 を受賞。

BigQueryに聞く天気予報

BACK TO LIST