#mysql

２次元の空間座標データをMySQLで扱うベストプラクティス

公開日：2023.07.25 更新日：2023.11.29

こんにちは、株式会社Gizumoでメンターをしている阿部と申します！
2019年に株式会社Gizumoに入社後、開発現場での経験を経て現在は社内でエンジニアを育成するメンターをさせていただいております。
現場では主にMySQLでのパフォーマンス改善を主体としたタスクを経験しており、データベースが大好きになりました！

今回は、私が大好きなMySQLのコンテンツの中でマイナーな「MySQLのデータ型」をご紹介いたします。

前提・対象とする読者
今回やりたいこと
問題点と解決方法
- 解決策としてのgeometry型
テーブル作成
- デメリット
データのインサートからデータの取得
距離の測定

前提・対象とする読者

この記事は以下を前提として読み進めてください。

MySQL準拠のSQLで記述

また、対象とする読者は以下のような方を想定しています。

MySQLなどのDBMSの基本操作が可能な方
テーブル設計に対して一定の知見がある方・興味がある方

今回やりたいこと

複数の座標情報を保持・座標間の距離を計算するような地図アプリを例に取っていきたいと思います。

この地図アプリでは、現在地と特定の施設との距離を明示するようなことができます。ではそのような時に、どのようなテーブル構成にすべきでしょうか。

まずは最も単純なテーブル構成をご紹介いたします。

施設テーブル

id(int)	user_id(int)	name(varchar)	x座標(float)	y座標(float)
1	1	東京駅	139.767288	35.6811339
2	1	東京ディズニーリゾート	139.8804447	35.6329597

このように、x座標とy座標を数値データとして保持するのが最も単純な形になるかと思います。

では、このようなテーブル構成の地図アプリケーションにおいて「現在地から近い施設情報を取得する」という機能があったとしたら、どのように実現すればよいでしょうか？現在地から500メートル以内の地点情報を取得する例を見てみましょう。

SELECT
  *
FROM
  施設テーブル
WHERE
  SQRT( // 平方根
    (現在地x座標 - x座標カラム) * 111,000 ** 2 // 111,000は経度1度当たりのメートル距離
    +
    (現在地y座標 - y座標カラム) * 91,000 ** 2 // 91,000は緯度1度当たりのメートル距離
  ) < 500;

上記のSQLでは、２地点間の座標からピタゴラスの定理を利用して距離を求めております。

経度１度あたりをメートル距離に直したり、２地点間の距離を求めるために複雑な計算式を全レコード分行う必要性が出てきます。
登録されている施設数にもよりますが、レコードが増えれば増えるほど計算量が増大していき、 結果としてパフォーマンスの悪化 につながります。

地図アプリのようなリアルタイム性を求められるアプリケーションでは、現在地が更新される度に莫大な計算量が発生し、地図の更新が遅れてしまうような事象が発生してしまうため、パフォーマンスの悪化は致命的になり得ます。

問題点と解決方法

今回問題点は下記の2点です。

クエリの複雑化
クエリのパフォーマンス悪化

こういったパフォーマンスの問題が発生した場合、多くのDBMSではインデックスの付与などでの対応が常套手段ではあるのですが、集計値（特定のカラム同士の足し算引き算した後の値）に対する検索は、ほとんどの場合通常のB-TREEインデックスの効果がないのです。
よって、インデックスの付与などではこのアプリケーションのパフォーマンス改善に大きな効果は見込めないことになります。

解決策としてのgeometry型

MySQLには、こういった２次元座標データを扱うためのgeometry型というデータ型が実装されています。

MySQL 空間拡張により、地理的特性の生成、ストレージ、および分析が可能になります。
・空間値を表すデータ型
・空間値を操作する関数
・空間カラムへのアクセス時間を改善するための空間インデックス設定

引用：空間データ型

このgeometry型を利用することで、二次元座標の操作を容易にすると同時にパフォーマンス向上が見込めます。

テーブル作成

では早速、先ほど作成した施設テーブルとは別にgeometry型を利用した施設テーブルを再作成してみましょう。

CREATE TABLE 施設テーブル (
    id INT PRIMARY KEY,
    user_id INT,
    name VARCHAR(255) NOT NULL,
    location GEOMETRY NOT NULL // 今回追加されたgeometry型
);

ALTER TABLE 施設テーブル ADD SPATIAL location (location); // 空間座標インデックスの付与

基本的にgeometry型は、2値を保有し続けるので、一般的なB-TREEインデックスの強みがほとんど活きません。そのため、R-TREEインデックスという空間座標インデックスをパフォーマンス向上のために使用しています。

ここを細かく解説するとそれ一本で記事が書けてしまうぐらいの文量になってしまうので、ざっくりと下記の図のように、範囲ごとに値を保持してくれるイメージを持っていただければ大丈夫です。

デメリット

今回紹介するgeometry型ですが、実際に使ってみて個人的に感じたデメリットを紹介します。

バックエンド言語のフレームワークによっては対応していない
- 例えばPHPのフレームワークであるLaravelの場合、代表的なORMであるEloquentにおいて、geometry型のデータから簡単に距離を取得できるようなメソッドが存在せず、rawなどを用いて素のSQLを書き込む必要がある。
R-TREEインデックスが思ったより容量を食う
- 空間情報を取り扱う機構上致し方ないが、B-TREEインデックスより1.5倍くらいの容量になります。データ数によっては別の問題が発生する可能性があります。

上記のようなデメリットが存在するため、既存のシステムに組み込む場合は十分に検討した上で追加してください。

データのインサートからデータの取得

INSERT INTO
  施設テーブル
  (user_id, name, location) 
VALUES 
  (1, '東京駅', ST_GeomFromText('POINT(139.767288 35.6811339)'));

カラムの実態はBINARY型なので、適切な形の文字列を入れてあげれば、データの挿入はできますが、基本的にはMySQLの組み込み関数のST_GeomFromTextを利用します。

mysql> SELECT X(location), Y(location)  FROM 施設テーブル;
+-------------+-------------+
| X(location) | Y(location) |
+-------------+-------------+
| 139.767288 |   38.375023 |
+-------------+-------------+

取得時にはX、Y関数を噛ませてあげることでX座標Y座標個別に取得することも可能になっております。

距離の測定

SELECT
    name,
    ST_Distance_Sphere(
        location,
        ST_GeomFromText('POINT (現在地座標)')
    ) AS distance
FROM 施設テーブル
ORDER BY distance;

+-----------+--------------------+
| name      | distance           |
+-----------+--------------------+
| 現在地     |  0                 |
| 東京駅     |  404379.84663      |
+-----------+--------------------+

ST_Distance_Sphereという組み込み関数を使用することで、2地点間の距離をメートル距離で返してくれます。

しかも、計算時にR-TREEインデックス使用してくれるため、先ほど例示した２地点間の距離を計測するSQLよりも格段に早い処理を期待できます。さらに、組み込み関数で計算式が簡略されるため、SQLの可読性向上にもつながります。

SELECT
  *
FROM
  施設テーブル
WHERE
  SQRT( // 平方根
    (現在地x座標 - x座標カラム) * 111,000 ** 2 // 111,000は経度1度当たりのメートル距離
    +
    (現在地y座標 - y座標カラム) * 91,000 ** 2 // 91,000は緯度1度当たりのメートル距離
  ) < 500;

↓

SELECT
  *
FROM
  施設テーブル
WHERE
  ST_Distance_Sphere(
    location,
    ST_GeomFromText('POINT (現在地座標)')
  ) < 500;

これぐらい、SQLを簡略化することができた上に、距離計算の速さも向上するので、地図データなどの管理においてはベストプラクティスになりそうです。
ということで、空間座標を扱う上で、おすすめデータ型geometry型の紹介でした。