【MySQL】空間情報(Geometry)について

概要
内容

概要

MySQL5.7のInnoDBでは、空間インデックスがサボートされました。
空間インデックスとは、空間データ型を高速に検索するために用いられます。
空間と言っても2次元までです。

内容

MySQL5.7からInnoDBでもGeometry型のカラムでインデックスを作成できるようになったので、ご紹介します。

テーブル作成

CREATE TABLE `test`.`test` (
  `name` VARCHAR(30) NOT NULL, 
  `location` GEOMETRY NOT NULL
) ENGINE = InnoDB;

5.7からInnoDBでもGeometry型のカラムでインデックスを作れるようになりました。

ALTER TABLE `test`.`test` ADD SPATIAL `location` (`location`);

テーブルインサート

東京駅の緯度(35.681298)経度(139.766247)を格納してみます。

MySQL5.7まではGeomFromText関数が使われていましたが、MySQL8.0以降はGeomFromText関数は非推奨となっているため、ST_GeomFromTextを使うことになります。
レンタルサーバでは、MySQL5.7.xとなっていますので、GeomFromTextを使います。

INSERT INTO `test` (`name`, `location`) 
VALUES ('東京駅', GeomFromText('POINT(139.766247 35.681298)'));

データを表示します。X関数で経度を、Y関数で緯度を取得できます。

mysql> SELECT X(location), Y(location)  FROM `test`;
+-------------+-------------+
| X(location) | Y(location) |
+-------------+-------------+
|  139.766247 |   35.681298 |
+-------------+-------------+
1 row in set, 2 warnings (0.00 sec)

近い順でレコード取得

品川駅から近い順でソートしてみます。

mysql> SELECT name, 
    ->        GLength(GeomFromText(
    ->            CONCAT('LineString(139.73876 35.628471, ', 
    ->                   X(location), 
    ->                   ' ', 
    ->                   Y(location), 
    ->                   ')'
    ->                  )
    ->        )) as distance
    -> FROM test
    -> ORDER BY distance;
+-----------+----------------------+
| name      | distance             |
+-----------+----------------------+
| 品川駅    |                    0 |
| 東京駅    | 0.059550206531958666 |
| 上野駅    |  0.09188110750855313 |
| 札幌駅    |    7.611854583584342 |
+-----------+----------------------+
4 rows in set, 4 warnings (0.00 sec)

上記クエリでは以下のことを行っています。