Geometry型で緯度・経度を管理

2022/11/12

緯度・経度を管理できるGeometry型について動作確認します。MySQL5.7からInnoDBでもGeometry型のカラムでインデックスを作成できるようになったので、利用機会が増えるかと思います。

テーブル作成

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)を格納してみます。

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)

近い順でレコード取得

2点間の距離が近い順にデータを取得します。

前準備としてレコードを挿入します。

INSERT INTO `test` (`name`, `location`) 
VALUES ('札幌駅', GeomFromText('POINT(141.350857 43.067656)')),
       ('上野駅', GeomFromText('POINT(139.776381 35.712297)')),
       ('品川駅', GeomFromText('POINT(139.73876 35.628471)'));

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

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)

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