緯度経度から1/8（6次）メッシュを求めるSQL

これは何？

DBに緯度(latitude)、経度(longitude)が格納されているが、そこから地域メッシュを作成したい時に利用します。

どんな時に使うの？

統計などで利用する際に、地域メッシュなど大きな枠でまとめたい場合がしばしばあります。
その際、ユーザーは取得したデータを色々な媒体で利用するので、サーバーで算出、ブラウザのJavascriptで計算、Excelなどで算出など色々な所に計算式が散ってしまい管理できないので、DB側でどうにかしたくて作りました。

DBに負荷を掛けるなと怒られそうですが、折角作ったので困ってる時にでもどうぞ。
もっと効率的なクエリにできそうですが、面倒なので気合いでゴリ押ししました。
MySQLなどもこれから修正しながら使えるかと思います。
修正点、ご意見などあればコメントください。

[2022/12/23] BigQuery版も用意しました。

SQL

Presto

CASE
  -- 日本の座標の場合のみメッシュ変換をかける
  WHEN 20.3 <= TRY_CAST(latitude AS DOUBLE) AND TRY_CAST(latitude AS DOUBLE) <= 45.6 AND 122 <= TRY_CAST(longitude AS DOUBLE) AND TRY_CAST(longitude AS DOUBLE) <= 154 THEN
    CONCAT(
      -- 1次メッシュ
      TRY_CAST(TRY_CAST(FLOOR(TRY_CAST(latitude AS DOUBLE)*1.5) AS BIGINT) AS VARCHAR),
      TRY_CAST(TRY_CAST(FLOOR(TRY_CAST(longitude AS DOUBLE))-100 AS BIGINT) AS VARCHAR),
      -- 2次メッシュ
      TRY_CAST(TRY_CAST(FLOOR(MOD(TRY_CAST(latitude AS DOUBLE)*60,40)/5) AS BIGINT) AS VARCHAR),
      TRY_CAST(TRY_CAST(FLOOR(MOD(TRY_CAST(longitude AS DOUBLE)-100,1)*60/7.5) AS BIGINT) AS VARCHAR),
      -- 3次メッシュ
      TRY_CAST(TRY_CAST(FLOOR(MOD(MOD(TRY_CAST(latitude AS DOUBLE)*60,40),5)*60/30) AS BIGINT) AS VARCHAR),
      TRY_CAST(TRY_CAST(FLOOR(MOD(MOD((TRY_CAST(longitude AS DOUBLE)-100),1)*60,7.5)/0.75) AS BIGINT) AS VARCHAR),
      -- 1/2次メッシュ
      TRY_CAST(TRY_CAST(FLOOR(MOD(MOD(MOD(TRY_CAST(latitude AS DOUBLE)*60,40),5),0.5)/0.25)*2+FLOOR(MOD(MOD(MOD((TRY_CAST(longitude AS DOUBLE)-100),1)*60,7.5),0.75)/0.375)+1 AS BIGINT) AS VARCHAR),
      -- 1/4次メッシュ
      TRY_CAST(TRY_CAST(FLOOR(MOD(MOD(MOD(MOD(TRY_CAST(latitude AS DOUBLE)*60,40),5),0.5),0.25)/0.125)*2+FLOOR(MOD(MOD(MOD(MOD((TRY_CAST(longitude AS DOUBLE)-100),1)*60,7.5),0.75),0.375)/0.1875)+1 AS BIGINT) AS VARCHAR),
      -- 1/8次メッシュ
      TRY_CAST(TRY_CAST(FLOOR(MOD(MOD(MOD(MOD(MOD(TRY_CAST(latitude AS DOUBLE)*60,40),5),0.5),0.25),0.125)/0.0625)*2+FLOOR(MOD(MOD(MOD(MOD(MOD((TRY_CAST(longitude AS DOUBLE)-100),1)*60,7.5),0.75),0.375),0.1875)/0.09375)+1 AS BIGINT) AS VARCHAR)
    )
  -- 日本以外はNULL
  ELSE null
END

BigQuery

CASE
  -- 日本の座標の場合のみメッシュ変換をかける
  WHEN 20.3 <= SAFE_CAST(latitude AS FLOAT64) AND SAFE_CAST(latitude AS FLOAT64) <= 45.6 AND 122 <= SAFE_CAST(longitude AS FLOAT64) AND SAFE_CAST(longitude AS FLOAT64) <= 154 THEN

  CONCAT(
    -- 1次メッシュ
    SAFE_CAST(SAFE_CAST(FLOOR(latitude*1.5) AS INT64) AS STRING), -- 2桁
    SAFE_CAST(SAFE_CAST(FLOOR(longitude)-100 AS INT64) AS STRING), -- 2桁
    -- 2次メッシュ
    SAFE_CAST(SAFE_CAST(FLOOR(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC), 40.0)*0.2) AS INT64) AS STRING),-- 1桁
    SAFE_CAST(SAFE_CAST(FLOOR(MOD(SAFE_CAST(longitude-100 AS NUMERIC), 1.0)*8.0) AS INT64) as STRING), -- 1桁
    -- 3次メッシュ
    SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5.0)*2) AS INT64) AS STRING), -- 1桁
    SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(SAFE_CAST(longitude AS NUMERIC),1.0)*60,7.5) / 0.75) AS INT64) AS STRING), -- 1桁
    -- 1/2次メッシュ
    SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5.0),0.5)*4)*2+FLOOR(MOD(MOD(MOD(SAFE_CAST((longitude-100) AS NUMERIC),1.0)*60,7.5),0.75)* 2.666666666666)+1 AS INT64) AS STRING), -- 1桁
    -- 1/4次メッシュ
    SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5),0.5),0.25)*8)*2+FLOOR(MOD(MOD(MOD(MOD(SAFE_CAST((longitude-100) AS NUMERIC),1)*60,7.5),0.75),0.375)*5.33333333333)+1 AS INT64) AS STRING), -- 1桁。JS版の振る舞いと合わせるため
    -- 1/8次メッシュ
    SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(MOD(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5.0),0.5),0.25),0.125)*16)*2+FLOOR(MOD(MOD(MOD(MOD(MOD(SAFE_CAST((longitude-100) AS NUMERIC),1)*60,7.5),0.75),0.375),0.1875)* 10.666666666)+1 AS INT64) AS STRING)
  )
  -- 日本以外はNULL
  ELSE null
END