データマネジメントチームの森 達也です。
本記事では、位置情報データをSQLで処理する方法についてご紹介いたします。
位置情報データとは
位置情報とは、人や車両など、動く物体の位置を測位した情報のことを指します。ご契約企業様の中には、スマートフォンのアプリや、機器に搭載されたセンサから取得した位置情報ログを活用されているケースがあります。一般的な位置情報データには、測位間隔ごとに緯度、経度、測位日時、個体の識別子（ID）などが含まれています。
緯度（latitude）は -90 〜 90、経度（longitude）は-180 〜 180の最大・最小値を持ちます。また小数点以下の桁数によって位置の精度が決まります。測位そのものの精度（誤差）とは別に、活用用途に必要な桁数が存在するか、最初に確認が必要です。
なおTreasure Data CDPのネイティブなデータ型としては、Geometry型をサポートしていないため、緯度、経度はdouble型として個別カラムに格納するのが一般的です。
また測位対象が多くある場合や、測位間隔が短い場合は、ログ全体が膨大なデータ量となることがあります。本日は取り上げませんが、処理時間を短くするために、必要な範囲で精度を落としたり、レコードの間引きをすることもあります。
位置情報の処理
それでは実際にSQLで緯度、経度を処理する方法をご紹介いたします。Presto/HiveではGeometry関数が利用可能です。なお各バージョンによって利用可能な関数が異なるので注意が必要です。執筆時点では、下記ドキュメントに記載されている関数が利用可能です。
Presto: https://trino.io/docs/317/functions/geospatial.html
Hive: http://hivemall.apache.org/userguide/geospatial/latlon.html
距離の算出方法
ここではPrestoでの距離算出の例をご紹介します。
2点間の距離を算出するにはST_Distanceを使用します。デフォルトだとデカルト距離を返すため、to_spherical_geography()を入れる事でメートル単位の結果を得ることができます。例えば店舗-自宅の距離、移動距離の算出など、最も使用頻度の高いGeometry関数の一つです。
ST_Distance( to_spherical_geography(ST_Point(lon1, lat1)), to_spherical_geography(ST_Point(lon2, lat2)) )
位置情報の連続したログであれば、レコード間の距離を予めカラムに作成しておくことで、柔軟に距離の集計ができます。その際は下記例のように一つ前のレコードの緯度、経度を前処理しておくことで距離算出の記述がシンプルになります。
LAG(lat) OVER(PARTITION BY id ORDER BY created_time) AS lat_prev LAG(lon) OVER(PARTITION BY id ORDER BY created_time) AS lon_prev
位置情報への意味づけ
位置情報は単体ではログに過ぎず、そこから気づきを得るには、位置や移動への意味付けが必要になります。例えば、下記のような軸をログから抽出する事ができれば、分析の幅はぐっと広がるはずです。
-
意味付けの例
- Where:
・近所、 遠方
・都道府県、市区町村、都市部 / 地方部
・店舗付近
・施設名、道路名 - When:
・平日 / 休日
・時間帯（朝昼夜）
・移動回数、頻度 - What:
・日常行動（通勤など）、非日常行動（遠方への旅行など）
上記は位置情報のみから抽出できるものもあれば、リバースジオコーディングなど外部データの参照が必要なものもあります。
自宅圏の推定方法
ここでは位置情報のログから、自宅圏を推定する方法をご紹介します。自宅圏が分かれば、それを起点に近所 / 遠方、日常 / 非日常など多くの軸を導出する事が可能です。属性データとして住所を取得している場合もありますが、一部（字や番地など）が欠損していたり、更新頻度が低く転居している場合に有効です。
基本的なアプローチとしては、出現頻度の高い位置を自宅圏と見なす、というものになります。例外もあるかもしれませんが、最も多くの時間を過ごす場所は自宅である、という仮定を置いています。
ここでの処理はシンプルで、緯度、経度の組み合わせを集計して、最もレコード数の多いものを採用するという方法になります。その際は緯度、経度の精度にもよりますが、細か過ぎる場合は予め小数点以下を丸める事で、簡易的にメッシュ（正方形に分割したもの）を作成できます。もちろん予め標準地域メッシュなどを作成しておいても問題ありません。
具体的には下記の様なSQLで抽出が可能です。
SELECT
id
, MAX_BY(lat, num) AS home_area_lat
, MAX_BY(lon, num) AS home_area_lon
FROM
(
SELECT
id
, ROUND(lat, 3) AS lat -- 約111m四方のメッシュを作成
, ROUND(lon, 3) AS lon -- 約111m四方のメッシュを作成
, COUNT(1) AS num
FROM
location_data_log
GROUP BY
1, 2, 3
)
GROUP BY
1
終わりに
位置情報は顧客のリアルな行動ログであるので、十分な前処理をした上で意味付けを行わないと、解釈する事が難しいデータです。本日は位置情報データの基本処理をご紹介しましたが、Geometry関数が他にも多数用意されている事からも分かるように、位置情報データ処理はとても奥が深い分野です。また機会があれば、ぜひ他の処理方法をご紹介できればと思います。最後までお読みいただき、ありがとうございました。
