GIS（地理空間情報システム）をORMから上手く扱うために：MySQLの場合

お仕事で、GISデータのインデックスを用いた処理を実装する必要があったので、記事としてメモを残しておきます。

設例と前提

RDBMSはMySQLを使う。
- これは業務上の制約
- この記事で述べている内容は、検証はしていないけれども、ほぼそのまま PostgreSQL & PostGIS にも適用可能であるはず。知らんけど。
あるテーブルに、緯度・経度を含むデータを格納する。
- このテーブルには数十万件以上の行が入る予定。100万件超えてきたら古いデータを消すはず😅
- 業務上、指定した地点の「近く」を示すデータを検索する必要がある。件数から考えて、spatial index(空間インデックス)を用いた探索処理が必須。
テーブルデータの読み書きは、Webアプリケーション・フレームワークのORMから実施する必要がある。

実装上の課題

spatial index を使いたいので、テーブル上にはDouble型2つではなくて、Point型（中身は緯度と経度とSRID*1）の列が必要である。
Point型のデータをWebアプリから読み書きするには、面倒くさいコーディング or 外部ライブラリの導入が必要である。
- もちろん、合理的な労力の範囲内なら、外部ライブラリは少ない方が「優れた」アプリケーションであるという前提

もしも外部ライブラリを導入する場合

ここでは動作確認してないけども…

例えばRailsアプリ(ActiveRecord) + PostGIS であれば、 rgeo ライブラリのデータをそのまま扱うことができそう。実装例は後述の参考文献に。
例えばLaravelアプリ(Eloquent) + PostGIS であれば、 laravel-geo で上手く扱うことができそう。実装例は後述の参考文献に。

もしも面倒くさいコーディングをする場合その１：バイナリ処理を頑張る

データベース上のデータをアプリケーションで読みこむ際、SELECT文を発行した後に得られるバイナリをparseすれば良い、という発想です。
CakePHPの実装例を調べてる途中で見つけて、ビックリしました😇
実装例は参考文献の中に。

書き込みも同様…のはずです…知らんけど。

※もちろん、この方法はお仕事では採用したくないです😇

もしも面倒くさいコーディングをする場合その２：SQLの関数を駆使しまくる

読み込み : ST_X(), ST_Y(), ST_Longitude(), ST_Latitude() などの関数を駆使すれば Point 型のデータを読み込むことができます。
書き込み : ST_GeomFromText() という関数を使えば、文字列データを空間データに変換してくれるので、これを駆使すれば、アプリケーション側から空間データを書き込むことができます。

いずれも、SQLを書くのは面倒ですね😇

本命：generated column を使った方法

本件の設例においては、

ORMからの読み書きが、ORMの標準機能だけでできればハッピー
空間インデックスを使いたい

という2要件さえ満たせば良いので、こう考えました。MySQLの generated column の機能を使って Point 型のカラムのデータを用意してあげれば、これらの2要件を同時に満たせそう、と。
そうそう、 generated column で作ったカラムにもindex張れるんです👩‍🏫

ORMからのデータ投入は double 型のカラム2つに緯度経度を入れれば良いだけなので、簡単ですね。取得もdouble型を読むだけです。
spatial index を使ったクエリは、前述の「SQLの関数を駆使しまくる」に近い対応になりますが、、これくらいなら耐えられます！！😃

以下、実装例です。

テーブル定義

CREATE TABLE spatial_test (
    id int unsigned AUTO_INCREMENT PRIMARY KEY,
    longitude double NOT NULL COMMENT '経度',
    latitude double NOT NULL COMMENT '緯度',
    location point GENERATED ALWAYS AS (ST_SRID(Point(longitude, latitude), 4326)) STORED NOT NULL SRID 4326 COMMENT 'spatial point (materialized)'
);
CREATE SPATIAL INDEX spatial_test_location ON spatial_test(location);

テストデータ

INSERT INTO spatial_test(longitude, latitude)
VALUES
(135.11111111, 36.11111111),
(135.22222222, 36.22222222),
(135.33333333, 36.33333333),
(135.44444444, 36.44444444),
(135.55555555, 36.55555555),
(135.66666666, 36.66666666);

EXPLAIN

FORCE INDEXしてますが、これはデータの件数が少ないから、です。件数が増えたらFORCEを使わなくてもインデックスを使ってくれるのではないかと…（たぶん）（MySQL 8.0.29では、FORCE使わないとindex使わないというバグがあったらしい。後述の参考文献を参照）

EXPLAIN SELECT * FROM spatial_test FORCE INDEX (spatial_test_location)
WHERE MBRContains(
  ST_SRID(
    ST_Buffer(
      point(135.2, 36.2),
      0.03,
      ST_Buffer_Strategy('point_circle', 4)
    ), 4326),
  location
);


-- 結果
+----+-------------+--------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | spatial_test | NULL       | range | spatial_test_location | spatial_test_location | 34      | NULL |    1 |   100.00 | Using where |
+----+-------------+--------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

-- warningは、目を瞑ってね😉

実際のクエリ実行

SELECT * FROM spatial_test FORCE INDEX (spatial_test_location)
WHERE MBRContains(
  ST_SRID(
    ST_Buffer(
      point(135.2, 36.2),
      0.03,
      ST_Buffer_Strategy('point_circle', 4)
    ), 4326),
  location
);

-- 結果
+----+--------------+-------------+------------------------------------------------------+
| id | longitude    | latitude    | location                                             |
+----+--------------+-------------+------------------------------------------------------+
|  2 | 135.22222222 | 36.22222222 | 0xE6100000010100000006EBC5711CE7604019AC17C7711C4240 |
+----+--------------+-------------+------------------------------------------------------+
1 row in set (0.01 sec)

generated column 方式の注意点など

テーブル定義では、SRIDを省略することもできるが、テーブル定義のSIRDを省略すると、MySQLは空間インデックスを使ってくれなかった（そもそも possible keys に入ってこない）
generated column を virtual とするのか、それとも stored とするのか、悩むところ。indexを使ってもらうことが目的であり、virtualでも index の中では実態の値が保持されるため、virtual で事足りそうではある。でも、データの件数が不十分な場合（かつFORCEを使わない場合）にはindexを使わずに全行探索をすることもあるはず。その時に全行分の virtual の再計算をされるととても遅そう…だと思ったので、ここでは stored を選択した。もしかしたら、optimizerさんはそこも考慮してくれるかもしれないけど😉
間違えて generated column の値を INSERT / UPDATE 文などで書き込もうとすると、きちんとSQLのエラーになってくれる。安全！！👍 → ERROR 3105 (HY000): The value specified for generated column 'location' in table 'spatial_test' is not allowed. （このエラーメッセージはINSERTの場合。たぶんUPDATEも同様のはず）