BigQueryでGIS関数を使う時は下準備が大事 #GoogleCloud

はじめに

LBMA Japan Advent Calendar 2021 の記事です。

こんにちは、unerryでプロダクトマネージャをしている鶴見です。

2021年は、モビリティ、スマートシティ、ポイ活など、位置情報マーケティング・サービスの領域が飛躍的に拡大しました。
領域の拡大に伴って位置情報データのボリュームも飛躍的に増加し、データが増えたことによってまた新しい領域が拡がるという、非常に良い循環が業界全体に生まれた素晴らしい1年だったと思います。

一方、データが増えたことにより集計や分析の処理に時間がかかるようになってしまったため、常に時間と戦い続けた年でもありました。
今日はそんな時間との戦いの中で生まれたBigQueryで位置情報データを集計する際の効率化に関するちょっとしたTIPSをご紹介いたします。

一般的なGIS関数の使い方

ケース

日本全国のPOI情報が入っているマスタがあり、各POIの中心点から任意の半径を指定し、周囲の位置情報ログを一括で抽出するケースを想定します。
マスタは下記のようなイメージになります。radiusはデータを取得したい半径（メートル）です。

シンプルにクエリを書いてみる

中心点＋半径でデータを抽出する場合は、GIS関数のST_DWITHINを使います。
user_id、time_stamp、latitude、longitudeのある位置情報のログテーブルがあると想定し、シンプルにクエリを書いてみると以下のような感じになります。
※addressは不要なのでSELECTから落としました。

WITH
mst AS (
  SELECT
    place_name,
    place_latitude,
    place_longitude,
    radius
  FROM
    poi_mst),
log AS (
  SELECT
    user_id,
    time_stamp,
    latitude,
    longitude
  FROM
    log_table)

  SELECT DISTINCT
    user_id,
    time_stamp
    latitude,
    longitude,
    place_name
  FROM
    mst JOIN log
  ON
    ST_DWITHIN(ST_GEOGPOINT(place_longitude, place_latitude),ST_GEOGPOINT(longitude, latitude),radius)

結果

サンプルで作成した5万のPOI数、2,000万レコードのログを対象に実行してみたところ、30分経過してもクエリが完了しなかったので敢え無く断念。
これだと日が暮れてしまうので次はちょっとした工夫をしてみます。

緯度経度に対し数値処理を入れてみる

アプローチ方法

先ほどのクエリだと、5万のPOIそれぞれに対して2,000万レコードのログが半径の中におさまっているかどうかを判定してしまっているせいで処理に時間がかかったようです。
この問題を解決するために緯度経度を数値処理してGIS関数で判定するログを絞り込んでみます。

アレンジしたクエリを書いてみる

各POI中心点の緯度経度から、取得したい半径の分だけそれぞれプラスマイナスをした緯度経度の数値をカラムに追加します。
lat_min,lat_max,lon_min,lon_maxが該当のカラムで、「30.8184 * 0.000277778」と「25.2450 * 0.000277778」はそれぞれメートルを緯度経度に変換するときの係数です。
イメージとしては、円に外接する正方形の各頂点の緯度経度を取得する感じですね（実際は10メートルほどバッファを持たせているので外接はしていません）。

JOINする際に緯度経度をlat_min,lat_max,lon_min,lon_maxで数値として絞り込む処理をした後にGIS関数を使って円の形でログを抽出します。

WITH
mst AS (
  SELECT
    place_name,
    place_latitude,
    place_longitude,
    radius,
    place_latitude  - ((radius + 10) / 30.8184 * 0.000277778) AS lat_min,
    place_latitude  + ((radius + 10) / 30.8184 * 0.000277778) AS lat_max,
    place_longitude - ((radius + 10) / 25.2450 * 0.000277778) AS lon_min,
    place_longitude + ((radius + 10) / 25.2450 * 0.000277778) AS lon_max,
  FROM
    poi_mst),
log AS (
  SELECT
    user_id,
    time_stamp,
    latitude,
    longitude
  FROM
    log_table)

  SELECT DISTINCT
    user_id,
    time_stamp
    latitude,
    longitude,
    place_name
  FROM
    mst JOIN log ON latitude BETWEEN lat_min AND lat_max AND longitude BETWEEN lon_min AND lon_max
  WHERE
    ST_DWITHIN(ST_GEOGPOINT(place_longitude, place_latitude),ST_GEOGPOINT(longitude, latitude),radius)

結果

5分ほどで集計結果が出力されました。これぐらいの時間でクエリが回ってくれればサクサクと分析が進められそうです！

最後に

今回は、緯度経度を数値処理してGIS関数の集計を効率化するやり方をご紹介しました。
この記事では中心点＋半径でデータを取得する方法で書きましたが、ポリゴンによる取得においてもポリゴンデータの各頂点の緯度経度の最小値、最大値を取得することで同様の処理が可能です。

また、数値処理以外にもgeohashを使ったりすることによってスキャン量を抑えたり処理を高速化したりすることもできます。GIS関数は奥が深いですね。