MySQL8.0でSRIDを指定しないでGEOMETRY列作った場合の挙動

本エントリの概要
SRIDを指定した場合
SRIDを指定しない場合
まとめと個人的見解
(おまけ)「SRID=0」を明示してテーブル定義した場合
(おまけ)存在しないSRIDの扱い

本エントリの概要

この記事は RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2019 の6日目として後追いで作成しています。
Oracle Technology Cafe #6 の際に「MySQL8.0でSRIDを指定しないでGEOMETRY列を作った場合の挙動」をsakaikさんに聞かれ、記憶があいまいなまま答えてしまったので改めて調べた内容を書きます。（結果的にはだいたいあってたんですが）
以下はMySQL8.0.17で検証しています。

SRIDを指定した場合

そもそもSRIDを指定して列を作った場合は、そのSRIDを指定して行を挿入しないとエラーになります。これはsakaikさんも記事を書かれています通りです。

sakaik.hateblo.jp

SRIDを指定しない場合

ではSRIDを指定しないで列を作るとどうなるでしょうか。

mysql> CREATE TABLE geom1 (
    ->   `id` int unsigned NOT NULL,
    ->   `geom` GEOMETRY NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.05 sec)

はい作れます。

では複数のSRIDを指定してレコードを登録してみます。

mysql> INSERT INTO geom1 VALUES
    -> (1, ST_GeomFromText('POINT(1 1)', 4612)),
    -> (2, ST_GeomFromText('POINT(2 2)', 4612)),
    -> (3, ST_GeomFromText('POINT(1 1)', 4326)),
    -> (4, ST_GeomFromText('POINT(2 2)', 4326)),
    -> (5, ST_GeomFromText('POINT(1 1)')),
    -> (6, ST_GeomFromText('POINT(2 2)'));
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

すんなり6行入りました。

値を確認してみましょう。

mysql> SELECT
    -> id,ST_SRID(geom)
    -> FROM geom1;
+----+---------------+
| id | ST_SRID(geom) |
+----+---------------+
|  1 |          4612 |
|  2 |          4612 |
|  3 |          4326 |
|  4 |          4326 |
|  5 |             0 |
|  6 |             0 |
+----+---------------+
6 rows in set (0.00 sec)

それぞれのSRIDは保持されています。また、SRIDを指定しなかった挿入ではSRIDが0になることもわかります。

ではレコード同士を比較してみましょう。今回は2つの距離を測る「ST_Dintance()」を使って2点間の距離を測ります。

まずは同じSRID同士id=1,2の比較です。

mysql> SELECT ST_Distance(
    -> (SELECT geom FROM geom1 WHERE id=1),
    -> (SELECT geom FROM geom1 WHERE id=2)
    -> ) AS DIST FROM DUAL;
+--------------------+
| DIST               |
+--------------------+
| 156874.38594646144 |
+--------------------+
1 row in set (0.00 sec)

問題ありません。（単位はメートル）

同じように3,4 5,6を測ってみます。

mysql> SELECT ST_Distance(
    -> (SELECT geom FROM geom1 WHERE id=3),
    -> (SELECT geom FROM geom1 WHERE id=4)
    -> ) AS DIST FROM DUAL;
+-------------------+
| DIST              |
+-------------------+
| 156874.3859490455 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT ST_Distance(
    -> (SELECT geom FROM geom1 WHERE id=5),
    -> (SELECT geom FROM geom1 WHERE id=6)
    -> ) AS DIST FROM DUAL;
+--------------------+
| DIST               |
+--------------------+
| 1.4142135623730951 |
+--------------------+
1 row in set (0.00 sec)

5,6はルート2そのままなので単純な平面の距離を出していることがわかります。

では違うSRID同士で距離を測ろうとするとどうなるでしょうか。

id=2,3の比較

mysql> SELECT ST_Distance(
    -> (SELECT geom FROM geom1 WHERE id=2),
    -> (SELECT geom FROM geom1 WHERE id=3)
    -> ) AS DIST FROM DUAL;
ERROR 3033 (HY000): Binary geometry function st_distance given two geometries of different srids: 4612 and 4326, which should have been identical.

エラーです。「違うSRIDが引数に与えられてるよ」的なことを言われています。まぁ当然と言えば当然ですね。

念のためid=4,5の比較も見てみましたが、ほぼ同様です。

mysql> SELECT ST_Distance(
    -> (SELECT geom FROM geom1 WHERE id=4),
    -> (SELECT geom FROM geom1 WHERE id=5)
    -> ) AS DIST FROM DUAL;
ERROR 3033 (HY000): Binary geometry function st_distance given two geometries of different srids: 4326 and 0, which should have been identical.

そもそもテーブルに入れずに比較した場合も同じ結果でした。

mysql> SELECT ST_Distance(
    -> ST_GeomFromText('POINT(1 1)', 4612),
    -> ST_GeomFromText('POINT(1 1)', 4326)
    -> ) FROM DUAL;
ERROR 3033 (HY000): Binary geometry function st_distance given two geometries of different srids: 4612 and 4326, which should have been identical.

まとめと個人的見解

テーブル定義でSRIDを指定しないとどんなSRIDのレコードも受け入れる
違うSRIDの値同士を比較しようとするとエラー3033になる
プロダクトで使う場合はSRIDをしっかりと定義しておくのがドメイン設計として正しいでしょう

(おまけ)「SRID=0」を明示してテーブル定義した場合

テーブル定義でもデータ挿入時もSRIDを指定しなかった場合、「SRID=0」の値になることがわかりました。
ではテーブル定義時に「SRID=0」を指定した場合の扱いはどうなるでしょうか。試してみます。

mysql> CREATE TABLE geom0 (
    ->   `id` int unsigned NOT NULL,
    ->   `geom` GEOMETRY NOT NULL SRID 0,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.05 sec)

はい、作成は可能でした。

値を入れてみましょう。まずはSRID=0を指定して挿入

mysql> INSERT INTO geom0 VALUES (1, ST_GeomFromText('POINT(1 1)',0));
Query OK, 1 row affected (0.01 sec)

これは想定通りです。ではSRID指定なしではどうなるでしょうか。

mysql> INSERT INTO geom0 VALUES (2, ST_GeomFromText('POINT(1 1)'));
Query OK, 1 row affected (0.02 sec)

問題ありませんね。関数の引数を省略した場合のデフォルト値が0となっていて、その値をチェックしているのではないかと思われます。では違うSRIDで入れてみます。

mysql> INSERT INTO geom0 VALUES (3, ST_GeomFromText('POINT(1 1)',4612));
ERROR 3643 (HY000): The SRID of the geometry does not match the SRID of the column 'geom'. The SRID of the geometry is 4612, but the SRID of the column is 0. Consider changing the SRID of the geometry or the SRID property of the column.

ですよね。つまり、「SRIDを指定しないで列を作った場合」と「SRID=0を指定して列を作った場合」は違うということになります。

(おまけ)存在しないSRIDの扱い

MySQLに定義されていないSRIDを使おうとするとどうなるでしょう。ここでは9999で試してみます。

テーブル定義しようとすると

mysql> CREATE TABLE geom9 (
    ->   `id` int unsigned NOT NULL,
    ->   `geom` GEOMETRY NOT NULL SRID 9999,
    ->   PRIMARY KEY (`id`)
    -> );
ERROR 3548 (SR001): There's no spatial reference system with SRID 9999.

選択しようとすると

mysql> SELECT ST_GeomFromText('POINT(1 1)', 9999);
ERROR 3548 (SR001): There's no spatial reference system with SRID 9999.

同じエラーで門前払いでした。

untitled .engineer

技術系のブログ（仮）