【BigQuery】都内の各地点のUUを前後の期間で比較するデータを集計してケプラーで描画する

ケプラーを利用すると緯度経度のデータからグラフィカルなマップグラフを作成できるとを以前記事に書きました（【kepler.gl】コロナ対策による人流の変化も地図上に可視化（各種メディアで報道））。今回、ケプラーのH3というグラフ形式を使って、東京都内各地点内の異なる期間のUUの比率をマップグラフで描画するため必要なデータを集計すためのクエリを紹介します。また、集計データのケプラーでの描画については、下記のサイトが分かりやすいのでご参考にして下さい。

◇クエリ例

WITH
  t1 AS (
  SELECT
    UDF.geoToH3(latitude,
      longitude,
      8) AS geoH3_1,
    COUNT(DISTINCT uid) AS CntUU_1,
  FROM
    data
  WHERE
    AND TIMESTAMP(DATETIME(TIMESTAMP(detected_time),
        'Asia/Tokyo')) >= TIMESTAMP('2020-02-02 00:00:00')
    AND TIMESTAMP(DATETIME(TIMESTAMP(detected_time),
        'Asia/Tokyo')) < TIMESTAMP('2020-02-03 00:00:00')
    AND latitude BETWEEN 23
    AND 46
    AND longitude BETWEEN 123
    AND 148
    --
    --東京都内
    AND ST_COVERS(ST_GEOGFROMTEXT('POLYGON((139.868043 35.628718,139.887956 35.653272,139.885209 35.680048,139.911302 35.685068,139.918855 35.70347,139.896195 35.728001,139.899629 35.744164,139.879716 35.761996,139.878343 35.77481,139.86255 35.79542,139.771226 35.810456,139.732774 35.778152,139.650376 35.799875,139.616731 35.763667,139.54326 35.752523,139.421723 35.765896,139.371598 35.764782,139.277528 35.834953,139.170411 35.843153,139.016603 35.891563,138.955491 35.853728,139.044755 35.717259,139.219163 35.645868,139.411424 35.582232,139.454015 35.541781,139.471867 35.519988,139.482167 35.534517,139.459508 35.571946,139.476674 35.595958,139.443028 35.606008,139.453328 35.6328,139.492467 35.619963,139.528859 35.652889,139.658635 35.598192,139.715627 35.553513,139.750645 35.548765,139.796651 35.524738,139.809354 35.51384,139.868043 35.628718))'),
      ST_GEOGPOINT(longitude,
        latitude))
  GROUP BY
    geoH3_1 ),
  t2 AS (
  SELECT
    UDF.geoToH3(latitude,
      longitude,
      8) AS geoH3_2,
    COUNT(DISTINCT uid) AS CntUU_2,
  FROM
    data
  WHERE
    AND TIMESTAMP(DATETIME(TIMESTAMP(detected_time),
        'Asia/Tokyo')) >= TIMESTAMP('2020-04-19 00:00:00')
    AND TIMESTAMP(DATETIME(TIMESTAMP(detected_time),
        'Asia/Tokyo')) < TIMESTAMP('2020-04-20 00:00:00')
    AND latitude BETWEEN 23
    AND 46
    AND longitude BETWEEN 123
    AND 148
    --
    --東京都内
    AND ST_COVERS(ST_GEOGFROMTEXT('POLYGON((139.868043 35.628718,139.887956 35.653272,139.885209 35.680048,139.911302 35.685068,139.918855 35.70347,139.896195 35.728001,139.899629 35.744164,139.879716 35.761996,139.878343 35.77481,139.86255 35.79542,139.771226 35.810456,139.732774 35.778152,139.650376 35.799875,139.616731 35.763667,139.54326 35.752523,139.421723 35.765896,139.371598 35.764782,139.277528 35.834953,139.170411 35.843153,139.016603 35.891563,138.955491 35.853728,139.044755 35.717259,139.219163 35.645868,139.411424 35.582232,139.454015 35.541781,139.471867 35.519988,139.482167 35.534517,139.459508 35.571946,139.476674 35.595958,139.443028 35.606008,139.453328 35.6328,139.492467 35.619963,139.528859 35.652889,139.658635 35.598192,139.715627 35.553513,139.750645 35.548765,139.796651 35.524738,139.809354 35.51384,139.868043 35.628718))'),
      ST_GEOGPOINT(longitude,
        latitude))
  GROUP BY
    geoH3_2 ),
  join_log AS (
  SELECT
    geoH3_1,
    MAX(CntUU_1) AS CntUU_1,
    MAX(CntUU_2) AS CntUU_2,
  FROM
    t1
  LEFT JOIN
    t2
  ON
    t1.geoH3_1 =t2.geoH3_2
  WHERE
    geoH3_1 IS NOT NULL
  GROUP BY
    geoH3_1 )
SELECT
  *,
  CntUU_2 / CntUU_1 AS rate
FROM
  join_log
ORDER BY
  CntUU_1 DESC

◇参考サイト

• 【kepler.gl】コロナ対策による人流の変化も地図上に可視化（各種メディアで報道）
• BigQuery・Kepler.gl・Uber H3による空間情報分析の始め方
• BigQuery GISを用いた位置情報データ分析の入門
• Table of Cell Areas for H3 Resolutions

【kepler.gl】コロナ対策による人流の変化も地図上に可視化（各種メディアで報道）

kepler.glのサイト画面

kepler.glを使ってコロナ対策の効果を分析したところ、テレビ、新聞、ネットのメディアから問い合わせや報道依頼が殺到。今も、土日返上で都内や全国の人流変化を分析しています。この記事では人流変化の可視化に便利なkepler.glについて簡単に紹介します。

◇kepler.glとは何か？

緯度経度などの空間データを可視化できるブラウザベースの地理情報システムです。開発はUber社がしており誰でもアカウント不要で利用することができます。緯度経度などを含むcsvファイルを用意するだけで空間データをマップ上に可視化できます。

◇kepler.glの良いところ

• 緯度経度などを含むデータを用意するだけでとても質の良い図を描ける
• プログラミング不要
• 時間データがあれば動的な表現ができる
• ローカルのデータを読み込むためサイトにデータをアップする必要がない
• tableauとかにも利用できる
• 緯度経度だとログ数ベースだがH3というデータ形式のファイルを用意すれば範囲内のUUをマップグラフで表現できる（【BigQuery】都内の各地点のUUを前後の期間で比較するデータを集計してH3でケプラーで描画する）

◇注意点

• 密度や3D図での濃さや高さは、そのデータでの最大値をmaxとして表現してるっぽいので、異なるデータを比較する際は、予めそれぞれのデータに最大値となるログを挿入しておく必要がある。ただ、動画として表現するときは、時間単位に調整用のデータを差し込まないと調整データがない部分の高さが調整されない。その場合は、1分ごとに任意地点のデータを差し込むなどの方法がある（【BigQuery】1分毎に1レコード生成するクエリ）。

◇ケプラーのサイト

• https://kepler.gl/

◇ケプラーでコロナ対策の人流効果を可視化した例（各種メディア報道）

覚えている限りですが下記のメディアで取り上げて頂きました。

• 【日経クロステック】「外出自粛」で渋谷の人出はどれだけ減ったか、データが示す新型コロナ対策の効果
• 【日経新聞】「外出自粛」で激変　行動データが示す渋谷の人出
• 【時事通信社】【コロナ影響調査】東京都知事による先週末（3/28,29）の「外出自粛」「隣接４県からの移動自粛要請」の効果は？
• 【シブヤ経済新聞社】外出自粛中の渋谷の人出、半減以下に　リアル行動データで実態調査結果
• 【ITmedia】渋谷の人出が半減　外出自粛要請の効果、行動データで明らかに
• 朝日新聞社（朝刊：4月4日）
• ワイドスクランブル（放映日：4月3日）
• サンデーステーション（放映日：4月5日）
• サンデーLIVE（放映日：4月5日、4月12日）
• とくダネ（放映日：4月6日、4月13日）
• スッキリ！（放映日：4月6日）
• 真相報道バンキシャ！（放映日：4月12日）
• など

◇参考サイト

• Kepler.GLでまったくコードを使わずに全国の地価データを可視化する
• kepler.glを使ってみました
• 巨大な地理空間データをサクッと可視化できる、Uber社製オープンソースwebGIS「Kepler.gl」
• kepler.glで（バッド）オープンデータを可視化する
• BigQuery・Kepler.gl・Uber H3による空間情報分析の始め方
• 【前編】非エンジニアだけど人工衛星データを可視化して分析してみた
• 強化された拡張機能のセキュリティが公開されました。詳細情報 Tableau ダッシュボードの拡張

【BigQuery】複雑な緯度経度の範囲を指定する方法

方法は？

WHERE文に下記の命令を記載するだけ。緯度経度は10進数。最後に、最初に指定した「経度1 緯度1」を入力することで範囲が閉じられる。

ST_COVERS(ST_GEOGFROMTEXT('POLYGON((経度1 緯度1,経度2 緯度2,～,経度1 緯度1))'),
ST_GEOGPOINT(経度のカラム名,緯度のカラム名))

例

SELECT
  time,latitude,longitude
FROM
  data
WHERE
  --error回避のため日本の範囲を指定
  latitude BETWEEN 23
  AND 46
  AND longitude BETWEEN 123
  AND 148
  --複雑な範囲を指定 
  AND ST_COVERS(ST_GEOGFROMTEXT('POLYGON((139.598915 35.687744,
    139.598909 35.687584,139.598915 35.687744))'),
    ST_GEOGPOINT(longitude,
      latitude))

エラー回避（ST_GeogPoint failed: Latitude must be between -90 and 90 degrees.）のために日本の範囲を先に指定している。

参考サイト

• 標準 SQL の地理関数