Help us understand the problem. What is going on with this article?

SQLで緯度経度からメッシュコードを計算する

メッシュコードについて

メッシュとは緯度経度に基づいて、地域を格子状に分割したもので、
メッシュコードとは、この地域メッシュに割り当てられたユニークな識別番号になります。
測地系は平成14年4月1日以前は日本測地系でしたが、それ以降は世界測地系に準じているようです。
この記事では、私がよく使うPostgreSQLとAWS Athena(Presto SQL)で緯度経度からメッシュコードを計算するクエリを紹介いたします。

計算方法の参考文献:http://www.stat.go.jp/data/mesh/pdf/gaiyo1.pdf#page=7

クエリ

紹介するクエリは以下のSQLで動作することを確認しております。

  • PostgreSQL
  • PrestoSQL(AWS Athena)

今回はlon, latがともに世界測地系であることを前提としています。

1次メッシュ

  • 辺の長さ:約80km
  • 経度差:1度
  • 緯度差:40分
WITH t AS (
  SELECT
  139.71475 AS lon,
  35.70078 AS lat
)
SELECT
    CONCAT(
      CAST(CAST(floor(lat*60/40) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor(lon-100) AS INTEGER) AS VARCHAR)
    ) AS mcode
FROM t
> 53394

2次メッシュ

  • 辺の長さ:約10km
  • 経度差:7分30秒
  • 緯度差:5分
WITH t AS (
  SELECT
  139.71475 AS lon,
  35.70078 AS lat
)
SELECT
    CONCAT(
      CAST(CAST(floor(lat*60/40) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor(lon-100) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor((lat*60)%40/5) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor((lon-floor(lon))*60/7.5) AS INTEGER) AS VARCHAR)
    ) AS mcode
FROM t
> 533945

3次メッシュ

  • 辺の長さ:約1km
  • 経度差:45秒
  • 緯度差:30秒
WITH t AS (
  SELECT
  139.71475 AS lon,
  35.70078 AS lat
)
SELECT
    CONCAT(
      CAST(CAST(floor(lat*60/40) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor(lon-100) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor((lat*60)%40/5) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor((lon-floor(lon))*60/7.5) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor(((lat*60)%40)%5*60/30) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor(((lon-floor(lon))*60)%7.5*60/45) AS INTEGER) AS VARCHAR)
    ) AS mcode
FROM t
> 53394547

2分の1地域メッシュ

  • 辺の長さ:約500m
  • 経度差:22.5秒
  • 緯度差:15秒
WITH t AS (
  SELECT
  139.71475 AS lon,
  35.70078 AS lat
)
SELECT
    CONCAT(
      CAST(CAST(floor(lat*60/40) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor(lon-100) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor((lat*60)%40/5) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor((lon-floor(lon))*60/7.5) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor(((lat*60)%40)%5*60/30) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor(((lon-floor(lon))*60)%7.5*60/45) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor(((lat*60)%40)%5*60%30/15)*2 + floor(((lon-floor(lon))*60)%7.5*60%45/22.5)+1 AS INTEGER) AS VARCHAR)
    ) AS mcode
FROM t
> 533945471

4分の1地域メッシュ

  • 辺の長さ:約250m
  • 経度差:11.25秒
  • 緯度差:7.5秒
WITH t AS (
  SELECT
  139.71475 AS lon,
  35.70078 AS lat
)
SELECT
    CONCAT(
      CAST(CAST(floor(lat*60/40) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor(lon-100) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor((lat*60)%40/5) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor((lon-floor(lon))*60/7.5) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor(((lat*60)%40)%5*60/30) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor(((lon-floor(lon))*60)%7.5*60/45) AS INTEGER) AS VARCHAR),
      CAST(CAST(floor(((lat*60)%40)%5*60%30/15)*2 + floor(((lon-floor(lon))*60)%7.5*60%45/22.5)+1 AS INTEGER) AS VARCHAR),
      CAST(CAST(floor(((lat*60)%40)%5*60%30%15/7.5)*2 + floor(((lon-floor(lon))*60)%7.5*60%45%22.5/11.25)+1 AS INTEGER) AS VARCHAR)
    ) AS mcode
FROM t
> 5339454711

プログラミングのように変数にできれば、もっと綺麗に計算できるのですがクエリだとどうしても力技になってしまいます。
しかし、テーブルにメッシュコードのカラムを付与することができれば簡単にメッシュごとの統計処理を行えるようになります。
是非ご検討ください。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away