🌏

[MySQL]空間データに入門してみた

2024/06/04に公開

MySQL

直近の業務で空間データを使用する機会があったのですが、初めて触れる領域のため整理しておきたいと思い記事にしました

本記事は、MySQLで空間データを取り扱う上で最低限知っておくべきことと、実際のデータを使用して簡単な空間分析をしてみるまでの入門的な内容になっています

対象読者

空間データ周りに苦手意識を持っている
空間データについて触れたことはないが興味はある
空間データを取り扱う上で押さえておくべきことを知りたい

環境

macOS Sonoma 14.3.1
MySQL 8.0.30

空間データ型を知る

MySQLでは空間データ型が用意されています。こちらはOGC^[1]が策定したOpenGIS^[2]で定義されているジオメトリモデルに基づいています

空間データ型はGeometryを基本クラスとし、その配下に様々なクラスが用意されています。主要なものとしてPoint, LineString, Polygonがあります

Pointクラス

単一の地点を表現するジオメトリになります
バス停や、記事を読んでいるあなたの地点を表現できます

LineStringクラス

複数の点を持ち、点と点の間を直線で結んだジオメトリになります
河川や路線、道路を表すことができます
Curveクラスの子クラス^[3]

Polygonクラス

複数の点を持ち、点と点の間を結び面を表現するジオメトリになります
森林や都道府県行政区域を表現することができます
Surfaceの子クラス^[4]

空間データ形式を意識する

クエリで実際に空間データを扱う際は、データの形式を意識する必要があります
MySQLではOGCが定めた以下の標準空間データ形式をサポートしています

WKT(Well-known text)
WKB(Well-known binary)

MySQLで使用するデータ形式は、上記の標準形式とは異なる「内部ジオメトリ記憶形式」となります。そのため、それぞれの形式で保存したり取り出す際には、形式を変換する変換関数を使用します
ここでは、WKTを例にとり、実際にどのように扱うのか見ていきましょう。

WKT形式で空間データを使用する

WKTでは、以下のような形式でジオメトリを表現できます

データ型	WKT形式
Point	POINT (30 10)
LineString	LINESTRING (30 10, 10 30, 40 40)
Polygon	POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))

WKT形式から、ST_X関数を使用してPOINTのX座標を取得してみます

試しに直接WKT形式を引数に、ST_X関数を実行してみます

mysql> SELECT ST_X('POINT(15 20)');
ERROR 3548 (SR001): There's no spatial reference system with SRID 1313427280.

エラーになります。MySQLで取り扱うためには形式の変換が必要です。ST_GeomFromText関数を使用して変換してみましょう

mysql> SELECT ST_GeomFromText('POINT(15 20)');
+------------------------------------------------------------------+
| ST_GeomFromText('POINT(15 20)')                                  |
+------------------------------------------------------------------+
| 0x0000000001010000000000000000002E400000000000003440             |
+------------------------------------------------------------------+

内部ジオメトリ記憶形式に変換できていることが確認できました。では、ST_X関数でX座標を取得します

mysql> SELECT ST_X(ST_GeomFromText('POINT(15 20)'));
+---------------------------------------+
| ST_X(ST_GeomFromText('POINT(15 20)')) |
+---------------------------------------+
|                                    15 |
+---------------------------------------+

X座標を取得することができました

反対に今度は内部ジオメトリ記憶形式から WKT形式に変換してみます。ST_AsTextを使用します

SELECT ST_AsText(ST_GeomFromText('POINT(15 20)'));
+--------------------------------------------+
| ST_AsText(ST_GeomFromText('POINT(15 20)')) |
+--------------------------------------------+
| POINT(15 20)                               |
+--------------------------------------------+

WKT形式で取得できていますね

これまで取り上げたように、空間データを取り扱うには常に形式の変換を意識する必要があります
ここでは省略しますが、WKBも専用の関数を用いて同様に形式の変換が可能です

また標準空間データ形式以外にも、MySQLではGeoJSONやGeohashといった形式もサポートしています。例えばGeoJSON形式で空間データを取得するには以下のようなクエリになります

SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(15 20)'),8);
+-------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(15 20)'),8) |
+-------------------------------------------------+
| {"type": "Point", "coordinates": [15.0, 20.0]}  |
+-------------------------------------------------+

千代田区内のJR総武線の駅名を取得する

空間データの取り扱いについて概要はここまで理解できたと思いますので、ここでは少しだけ実践的な内容にチャレンジしてみます
東京都千代田区内に存在するJR総武線の駅名を、空間関数を使用して取得してみたいと思います
流れとしては以下の手順になります

テーブルを作成
千代田区と各駅のジオメトリを列に持つレコードを作成
空間関数を使用して、千代田区内の駅名を取得

それではやっていきましょう

テーブルを作成

GEOMETRY型を列にもつテーブルを作成します。駅名も取得したいので、名前も列に加えます

CREATE TABLE geom ( name VARCHAR(20) NOT NULL, g GEOMETRY NOT NULL);
Query OK, 0 rows affected (0.01 sec)

各駅と千代田区のジオメトリを列に持つデータを作成

千代田区のレコードを作成します。国土交通省が出している令和5年の行政区域データをもとにQGISを使用してGeoJSONを取得します。(QGISの取り扱いは本記事のスコープ外になるので、割愛します)
得られたGeoJSONから、変換関数を使用しデータを作成します

INSERT INTO geom VALUES ("東京都千代田区", ST_GeomFromGeoJSON(
    '{ "type": "MultiPolygon", "coordinates": [ [ [ [ 139.772865837288691, 35.703702135445383 ] ... ] ] ] }'
));

次に各駅のデータを作成します
JR総武線の座標ですが、こちらのサイトにて駅の座標が載っていたので、こちらのデータを元にWKT形式で作成します

INSERT INTO geom VALUES ("千葉", ST_GeomFromText('POINT(35.612858 140.11434)', 4326));
INSERT INTO geom VALUES ("西千葉", ST_GeomFromText('POINT(35.622581 140.103279)', 4326));
INSERT INTO geom VALUES ("稲毛", ST_GeomFromText('POINT(35.637425 140.092404)', 4326));
...

ST_GeomFromTextの第二引数にある4326という数値はSRIDと言って、ざっくり取り上げると測地系と座標系という概念の組み合わせのコードになります

詳細はここでは割愛しますが、「位置」を示すためには、測地系と座標系によって表現方法や値が異なり、身近な緯度・経度は座標系の中の地理座標系にあたります

今回は4326(世界測地系(WGS84)+ 地理座標系)を使用します

空間関数を使用して、千代田区内の駅名を取得

データは整ったので、千代田区内にある総武線の駅名を取得してみましょう。ST_Within関数を使用します

SELECT name
　　　　FROM geom
　　WHERE ST_Within(g, (select g from geom where name='東京都千代田区'));

+--------------+
| name         |
+--------------+
| 秋葉原       |
| 御茶ノ水     |
| 水道橋       |
| 飯田橋       |
| 市ケ谷       |
| 四ツ谷       |
+--------------+
6 rows in set (0.04 sec)

取得することができました！

終わりに

当初、業務で空間データ周りのSQLを見たときには、やたらST_と名のつく関数が多く出てきて困惑しましたが、整理してみるとデータ形式の変換のために必要だと分かり、それ以降は以前よりも難しく感じなくなりました。
とはいえ、この記事をまとめる中で、測地系や座標系についての理解がまだ不十分であることに気づいたので、今後これらの点についても整理していきたいと思います。

記事を通して空間データを使用するハードルが少しでも下がったなら嬉しいです。最後までお読みいただき、ありがとうございました！

参考

脚注

業界主導で地理情報の標準化に取り組んでいる国際的な非政府・非営利の標準化団体1 ↩︎
地理情報データの標準化された仕様のこと2 ↩︎
Curveクラスはインスタンス化ができないので、データを投入する際には使用できません3 ↩︎
SurfaceクラスもCurveクラスと同様、インスタンス化できません4 ↩︎