UGA Boxxx

つぶやきの延長のつもりで、知ったこと思ったこと書いてます

【BigQuery】SQLで求める角度の平均

BigQuery

BigQueryに保存された緯度経度データから、それらの平均の緯度経度を求めたい

SQLで求めるが、緯度経度は角度であるため、単純なAVGをつかってはダメ

uga-box.hatenablog.com

そのため、以下の式で求められる角度をSQLで算出する必要がある

$\displaystyle X = \sum_{}cos\theta$

$\displaystyle Y = \sum_{}sin\theta$

$\displaystyle \bar{\theta} = tan^{-1}\frac{Y}{X}$

BigQueryには数学関数が用意されている

cloud.google.com

そのためSUM, COS, SINはもちろん、アークタンジェント ATAN2もあるため、それを利用すればよさそう

ただし、 ATAN2の引数はラディアンであるため、緯度経度をラディアン変換する必要があるが、BigQueryにπが用意されていない

これをどうするか

ユーザ定義の関数を使う

BigQueryではSQL 式または JavaScript コードを使用してユーザー定義関数（UDF）を作成することができる

cloud.google.com

これを利用して、角度からラディアンに変換する関数を定義すればよさそう

CREATE TEMP FUNCTION RADIANS(x BIGDECIMAL) AS (
  ACOS(-1) * x / 180
);

SELECT RADIANS(37);  -- returns 0.6457718232379019

これでBigQueryに保存された緯度経度の平均を求めることができそう

参考

stackoverflow.com