Presto（AWS Athena）で動作する緯度経度からメッシュコードの算出SQL

はじめに

AWSのAthenaで緯度経度から３次メッシュコードを算出する必要性があったので、その際の成果を共有します。

実装

Athenaでは、基本的にはユーザ定義関数（UDF）は利用できないとFAQにあったので、インラインで計算する方法を考えます。

Q: Athena はユーザー定義関数 (UDF) をサポートしていますか?

現在、Athena ではカスタム UDF をサポートしていません。カスタム UDF のサ>ポートが必要な場合は、お客様の要件についての詳細を [e-mail] に E メールでお問い合わせください。

必要な演算

メッシュコードの算出には、四則演算に加えて下記のような演算が必要です。Athenaのリファレンスでは数学関数についてはPrestoのサイトにリンクされています。

• 剰余 ： %演算子 か MOD関数
• 少数の切り捨て ： FLOOR関数またはTRUNCATE関数
• 型変換 ： CAST関数

クエリ

lat(緯度）, lon（経度）

athena_lonlat2meshcode.sql

WITH  base AS 
(SELECT  
 139.73729637 AS lon -- 経度
 ,
 35.7177361 AS lat -- 緯度
)
SELECT 
  CAST(  
   floor(lat*3/2)*1000000
  +floor(lon-100)*10000+floor((lat*60%40)/5)*1000
  +floor(((lon-100)%1)*60/7.5)*100
  +floor(((lat*60%40)%5)*2)*10
  +floor((((lon-100)%1)*60%7.5)*60/45) 
    AS integer ) AS meshcode3
FROM base ;

-- 結果 53397317 

簡単ではありますが、prestoでも動くメッシュコード計算を記載しました。標準的なSQLに近いものなので、PostgreSQLなど広く利用可能であると思われます。

また、AWS AthenaはGIS関数（ST_Intersectsなど）が利用可能ですので、ビッグデータのGIS処理を検討する方は一度検討してみてはいかがでしょう。