Zenn
🌏

【MySQL】Geometry型(平面空間データ型)を使ってみた

2020/10/30に公開
MySQL
DB
geometry
座標
tech

はじめに

普段から業務でDBを用いる機会が多いが、Geometry型というあまり聞きなれない型に触れる機会があった。
調べてみると平面座標(緯度・経度)を管理するための型であった。
過去に住所だったり建物の位置情報を管理するテーブルを作成して緯度経度の情報を操作することはあったが、その際は以下のようなテーブルを作成した。

(例)建物マスタ

論理名 物理名 型
ID id int
建物名 building_name varchar(50)
郵便番号 post_code varchar(8)
住所 address varchar(100)
緯度 latitude double
経度 longitude double
… … …

このテーブルのように浮動小数点を扱える型でも緯度経度は管理でき、Geometry型を使って位置情報を管理する必要はない。
しかし、システムの要件によってはGeometry型の方がよい場合もあるため、使用用途を紹介しようかと思います。

メリット・デメリット

※使ってみての感想

メリット

地点間の距離を求めることができる
これに尽きると思います。ただ距離を求めるだけではなく、「求めた距離内の〇〇を抽出」「自分の位置から最短の距離にある〇〇を探す」など応用が可能である。

デメリット

他の型と違って特殊な使い方になる
他の型のように単純なINSERTやSELECTは使用できないので、MySQL上で実行できる形式にSQLを作成する必要がある。
MySQL上で直接実行する場合は問題ないかもしれないが実際に運用するシステムでそんなことをするはずはない。
何らかの言語やフレームワークを使用して画面等を生成し、イベントが発生した際に登録や抽出を行うはずである。
Geometry型を使う際はフレームワークの規則に則った方法でうまく使えないかもしれない。

使い方

CREATE

先ほどの建物マスタでGeometry型を使った場合以下のようになる。

論理名 物理名 型
ID id int
建物名 building_name varchar(50)
郵便番号 post_code varchar(8)
住所 address varchar(100)
座標 location geometry
… … …
CREATE文
CREATE TABLE `building` (
  `id` INT AUTO_INCREMENT comment 'ID',
  `building_name` VARCHAR(50) NOT NULL comment '建物名', 
  `post_code` VARCHAR(8) NOT NULL comment '郵便番号', 
  `address` VARCHAR(8) NOT NULL comment '住所', 
  `location` GEOMETRY NOT NULL comment '座標',
  …,
  PRIMARY KEY (id),
  INDEX building_name_idx(building_name)
)
ENGINE = InnoDB,
default charset=utf8mb4
comment='建物マスタ';

INSERT

INSERT文
INSERT INTO building (building_name, post_code, address, location, …) VALUES
("国会議事堂", "100-0014", "東京都千代田区永田町１丁目７−１", ST_GEOMFROMTEXT('POINT(139.744972 35.675889)', 4326), …),
("東京都庁", "163-8001", "東京都新宿区西新宿２丁目８−１", ST_GEOMFROMTEXT('POINT(139.691694 35.6895)', 4326), …),

Geometry型のカラムに座標を登録する際は
ST_GEOMFROMTEXT('POINT(経度 緯度)', 4326)
と記載する必要がある。

※ 4326はSRID（空間参照系識別コード）のこと。
　 緯度経度の座標で表すときの座標の基点になるコードらしい。

SELECT

座標を取得

SELECT文
SELECT 
 building_name AS 建物名, 
 ST_AsText(location) AS 緯度経度 
FROM 
 building

取得結果↓

建物名 緯度経度
国会議事堂 POINT(139.744972 35.675889)
東京都庁 POINT(139.691694 35.6895)

緯度経度をテキスト形式で取得することができる。

緯度経度を取得

SELECT文
SELECT 
 building_name AS 建物名, 
 X(location) AS 経度,
 Y(location) AS 緯度
FROM 
 building

取得結果↓

建物名 経度 緯度
国会議事堂 139.744972 35.675889
東京都庁 139.691694 35.6895

緯度と経度を別々に取得して別カラムとすることができる。
※X：経度、Y：緯度　←注意

様々な値を取得

比較対象：東京駅⇒POINT(139.767165 35.681300)

距離(m)：ST_Distance_Sphere関数

SELECT文
SELECT 
 building_name AS 建物名, 
 ST_Distance_Sphere(
     GeomFromTextPOINT(139.767165 35.681300)),
     GeomFromText(ASTEXT(location))
 ) AS 距離
FROM 
 building

取得結果↓

建物名 距離
国会議事堂 東京駅～国会議事堂までの距離
東京都庁 東京駅～東京都庁までの距離

度：GLENGTH関数

SELECT文
SELECT 
 SELECT 
 building_name AS 建物名, 
 GLENGTH(GEOMFROMTEXT(
       CONCAT('LINESTRING(141.346797 43.064281, ',
           X( location ),' ',Y( location ),
       ')'
   ))
 ) AS 度 
FROM 
 building

※LINESTRING：座標と座標を引数に渡すことで線としてデータ化
　CONCAT：文字列を結合する関数

取得結果↓

建物名 度
国会議事堂 東京駅～国会議事堂までの度
東京都庁 東京駅～東京都庁までの度

※1度 = 約112.12kmとなるから取得した度に112.12をかけるとkmが算出できる。

まとめ

関数は他にも用意されているようなのでもしGeometry型を使用する場合、システムの要件としてどのような関数を使用して何を求めるかを設計の段階で決めるのが良いでしょう。

Discussion