MySQL8 の GIS 関数を使ってみる

MySQL では geometry 型という緯度経度をテーブルで扱うことができます。
これと組み合わせて MySQL8 から本格導入となった GIS 関数を使うと何ができるのか調べていきたいと思います。

GIS 関数とは

GIS とは地理情報システム Geographic Information System の略で、地理的位置を手がかりに位置に関する情報を持ったデータ（空間データ）を総合的に管理・加工し、視覚的に表示し高度な分析や迅速な判断を可能にする技術のことらしいです。
GIS のツールの中には QGIS や ArcGIS などがあります。

この GIS ツールなどで使われる 2 点間の距離を求めるなど位置情報に関する操作を SQL ベースで利用できるようになるというイメージです。

今まで MySQL を使ってサービスで位置情報を扱うには緯度経度を float 型で保持し対象のレコードを絞ってからアプリケーションコードで位置情報に関する処理を行うしかありませんでした。
GIS 関数の対応によって絞り込みや集約を SQL レベルでできるようになることが期待できます。

MySQL8 の環境構築

ローカルには MySQL 5.7 をインストール済みで他のプロジェクトでも使うため Docker で構築していきます。

docker-compose.yml

version: "3"
services:
  db:
    image: mysql:8.0
    volumes:
      - db-store:/var/lib/mysql
      - ./logs:/var/log/mysql
      - ./docker/mysql/my.cnf:/etc/mysql/conf.d/my.cnf
    environment:
      - MYSQL_DATABASE=geometry
      - MYSQL_USER=root
      - MYSQL_PASSWORD=password
      - MYSQL_ROOT_PASSWORD=password
      - TZ="Asia/Tokyo"
    ports:
      - 13306:3306
volumes:
  db-store:

これで完成。

データの用意

geometry というデータベースが作られるのでここにテーブルを作成します。

CREATE TABLE mc (
  `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(64) NOT NULL,
  `address` VARCHAR(128) NOT NULL, 
  `geohash` VARCHAR(32) NOT NULL
) ENGINE = InnoDB

geohash に位置情報が積めます。
Geohash とは緯度経度をとある文字列に置き換えたものです。
Geohash にすることによってデータ上文字列として扱うことができます。

次にデータを Insert します。

INSERT INTO mc (name, address, geohash)
    VALUES ('西町店', '北海道札幌市西区西町北２－１－６', ST_GeoHash(141.292534094, 43.0773043276, 12)),
    ('白石南郷通店', '北海道札幌市白石区南郷通２丁目南１０－１５', ST_GeoHash(141.400104165, 43.0446331934, 12)),
...

データにはマクドナルドの店舗情報をハックして取得した位置情報を使います。
ST_GEOHASH という GIS 用の関数が用意されており、経度、緯度の順番で引数に与えると geohash に変換してくれます。
変換した値をテーブルに追加していきます。

これで用意も完了。

GIS 関数を体験

一番遠い店舗順に取得

東京駅から一番遠いマクドナルドの店舗を算出します。

SELECT name, address,
st_distance(ST_GeomFromText('Point(139.764936 35.6812362)'),
  ST_GeomFromText(CONCAT('Point(', ST_LongFromGeoHash(geohash), ' ', ST_latFromGeoHash(geohash), ')'))
    ) as distance
    FROM mc
    ORDER BY distance desc
    LIMIT 1;

ここで大切なのが ST_LongFromGeoHash と ST_latFromGeoHash である。
名前の通りこれらの関数を使うと geohash から緯度経度へのデコードができます。
POINT() というのは後続の GIS 上のデータフォーマットで後続の関数の引数として使用されます。
この POINT からジオメトリオブジェクトに変換するのが ST_GeomFromText で、そのオブジェクト間の距離を算出するのが ST_DISTAMCE 関数です。

ちなみに指定している緯度経度は東京駅のもので一番通りマクドナルドは石垣サンエー店でした。

半径 1 km にある店舗

参考 qiita.com

SELECT
  id, name, address,
  ST_LENGTH(ST_GEOMFROMTEXT(
      CONCAT('LINESTRING(139.764936 35.6812362, ', ST_LongFromGeoHash(geohash),' ', ST_latFromGeoHash(geohash), ')')
  )) AS distance
FROM mc
group BY id
having distance <= 0.0089831601679492 -- 1 km という意味らしい
;

LINESTRING は先程の POINT と同じく GIS 上のデータ・フォーマットで ST_GEOMFROMTEXT でジオメトリオブジェクトに変換し、ST_LENGTH で長さを求めます。
これが 1 km 未満の店舗を表示するという簡単な SQL。

ちなみに結果は以外で 4 店舗しかありませんでした。

f:id:h-piiice16:20200315205252p:plain

まとめ

MySQL で位置情報を扱ってみましたが、実践的な用途にも使えそうです。
ただ、関数によってはインデックスが無効化されたりするものがありパフォーマンスはあまり良くなさそうです。
PostGIS という PostgreSQL ベースの GIS プラグインがあるようでそちらのほうが集約関数に富んでるらしく実用的かもしれません。

既存のサービスでは MySQL を使ったものも多いため色々と改善してほしいものです。 GIS 関数に関しては開発中らしく、これからが楽しみです。