2022-12-06

MySQL8.0で北極点と南極点2点間に線を引いてみた

GIS MySQL

本エントリの概要
前提
極点の表し方
2点間で線を引く
線はどこにひかれたのか
ちょっといたずら
まとめ

本エントリの概要

この記事は RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2022 の6日目として作成しています。
ふと思いついてやってみたけどよくわからないことになったメモです。

前提

手元にあったMySQL8.0.17で検証しています。
SRIDは4326を使います。
SRID=4326の場合、AXIS["Lat",NORTH],AXIS["Lon",EAST]の順番なので、緯度→経度の順番で書きます。
今回はざっくりと算出したいので細かい精度はこだわりません。

極点の表し方

まずは2点の場所について考えます。

北極点WKT：POINT(90 0)
南極点WKT：POINT(-90 0)

極点に経度はないので仮に0とします。

SELECT ST_Distance_Sphere(
    ST_GeomFromText('POINT(90 0)',4326),
    ST_GeomFromText('POINT(-90 0)',4326)
);

結果：20015114.352233686 (単位はm(メートル)です)
地球一周が4万km弱なのでだいたいあってますね。
ちなみに経度は-180～180の間で設定できますので、こんなことをいろいろ試してみましたが、距離を測るといずれも同じ結果でした。

SELECT ST_Distance_Sphere(
    ST_GeomFromText('POINT(90 180)',4326),
    ST_GeomFromText('POINT(-90 0)',4326)
);
SELECT ST_Distance_Sphere(
    ST_GeomFromText('POINT(90 0)',4326),
    ST_GeomFromText('POINT(-90 180)',4326)
);

ここまでは特に問題ありません。

2点間で線を引く

では、北極点と南極点を結ぶ線を描いてみます。
WNTはLineString(90 0,-90 0)とします。
この線の長さを測ってみます。

SELECT ST_Length(ST_GeomFromText('LineString(90 0,-90 0)',4326));

結果：20003917.356955886
ヨシ、約2万kmです。
2点間距離の結果と少し違いますが、まぁそういうものだろうということで差は一旦忘れます。

線はどこにひかれたのか

「さて、この線が地球上のどこにひかれているのでしょうか。」（これが本エントリの主題です）
北極点と南極点の最短ルートであること、いずれかの緯線に沿っていることは確信するところですが、経度はどこを通ってもここでの計算上は同じになるはずです。
「とりあえずまぁ本初子午線を通ってるんじゃまいか」という仮説を立ててST_Contains()で緯度経度0 0を通っているか確かめます。

SELECT ST_Contains(
    ST_GeomFromText('LineString(90 0,-90 0)',4326),
    ST_GeomFromText('Point(0 0)',4326)
);

結果：1 （引数1に引数2が含まれている場合1、含まれない場合0）
ヨシ。やっぱり。
念のためほかの経度を通っていないかも確認します。

SELECT
ST_Contains(
    ST_GeomFromText('LineString(90 0,-90 0)',4326),
    ST_GeomFromText('Point(0 0)',4326)
) AS Lon0,
ST_Contains(
    ST_GeomFromText('LineString(90 0,-90 0)',4326),
    ST_GeomFromText('Point(0 90)',4326)
) AS Lon90,
ST_Contains(
    ST_GeomFromText('LineString(90 0,-90 0)',4326),
    ST_GeomFromText('Point(0 180)',4326)
) AS Lon180,
ST_Contains(
    ST_GeomFromText('LineString(90 0,-90 0)',4326),
    ST_GeomFromText('Point(0 -90)',4326)
) AS Lonm90;

結果：Lon0のみ1、それ以外は0
ヨシ。やっぱり経度0のみです。

さて、さっき「経度は-180～180の間で設定できます」って試したのをこちらでもやってみます。

SELECT ST_Length(ST_GeomFromText('LineString(90 180,-90 180)',4326));

結果：20003917.356955886
同じ長さです。
この場合も経度0を通っているのか確認してみます。

SELECT ST_Contains(
    ST_GeomFromText('LineString(90 180,-90 180)',4326),
    ST_GeomFromText('Point(0 0)',4326)
);

結果：0
あれ、通っていません。
ということは経度180を指定しているのが効いているのでしょうか。

SELECT ST_Contains(
    ST_GeomFromText('LineString(90 180,-90 180)',4326),
    ST_GeomFromText('Point(0 180)',4326)
);

結果：1

そのようです。
念のためほかの経度も確認します。

SELECT
ST_Contains(
    ST_GeomFromText('LineString(90 180,-90 180)',4326),
    ST_GeomFromText('Point(0 0)',4326)
) AS Lon0,
ST_Contains(
    ST_GeomFromText('LineString(90 180,-90 180)',4326),
    ST_GeomFromText('Point(0 90)',4326)
) AS Lon90,
ST_Contains(
    ST_GeomFromText('LineString(90 180,-90 180)',4326),
    ST_GeomFromText('Point(0 180)',4326)
) AS Lon180,
ST_Contains(
    ST_GeomFromText('LineString(90 180,-90 180)',4326),
    ST_GeomFromText('Point(0 -90)',4326)
) AS Lonm90;

結果：Lon180のみ1、それ以外は0
問題なさそうです。

ちょっといたずら

北極点と南極点で違う経度を指定して、まずは距離を測ります。

SELECT ST_Length(ST_GeomFromText('LineString(90 0,-90 180)',4326));

結果：20003917.35695591
今度は距離が目視できないレベルで変化しました。
が、約2万kmであることは変わらないです。

さてこれはどこを通っているのでしょうか。

SELECT
ST_Contains(
    ST_GeomFromText('LineString(90 0,-90 180)',4326),
    ST_GeomFromText('Point(0 0)',4326)
) AS Lon0,
ST_Contains(
    ST_GeomFromText('LineString(90 0,-90 180)',4326),
    ST_GeomFromText('Point(0 90)',4326)
) AS Lon90,
ST_Contains(
    ST_GeomFromText('LineString(90 0,-90 180)',4326),
    ST_GeomFromText('Point(0 180)',4326)
) AS Lon180,
ST_Contains(
    ST_GeomFromText('LineString(90 0,-90 180)',4326),
    ST_GeomFromText('Point(0 -90)',4326)
) AS Lonm90;

結果：Lon0とLon180が1、それ以外は0
え？あれ？いったいなにが起こっているの。
経度0と180を緯度を10度ずつずらしながら検査してゆきます。

SET @line1 = ST_GeomFromText('LineString(90 0,-90 180)',4326);
SELECT
ST_Contains(@line1, ST_GeomFromText('Point(80 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(70 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(60 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(50 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(40 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(30 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(20 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(10 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(0 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-10 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-20 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-30 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-40 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-50 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-60 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-70 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-80 0)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(80 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(70 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(60 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(50 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(40 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(30 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(20 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(10 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(0 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-10 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-20 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-30 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-40 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-50 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-60 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-70 180)',4326)) AND
ST_Contains(@line1, ST_GeomFromText('Point(-80 180)',4326));

結果：1
一つでも0が混ざれば結果は0になるはずですので、すべて通っていることになります。
これはつまり「経度0と経度180の両方に線があるので地球1周分のはずが、長さは約2万kmである」ということになりますね。
なぞです。

まとめ

極点を扱う場合は経度に指定する数値を何かに固定して運用するのがよいのかもしれません。
これを解析するだけの能力は私にはないようですので、「変なことをするのが悪い」ということにしたいと思います。
手元にMySQL以外のGIS環境がないので他のシステムでどうなるのかはわかりません。

2019-12-17

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

MySQL GIS

本エントリの概要
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.

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

2019-12-16

MySQLは5.7までGROUP BYにASCとかDESCとかをつけられていた話

MySQL

本エントリの概要

MySQLは5.7までGROUP BYにASCとかDESCとかをつけられていたことに気づいたことと、他のRDBMSがどうかを調べてみたので書きます

2019-12-15

MySQL5.5からMySQL8.0にマイグレーションしたゆるい話

MySQL

本エントリの概要
前提と環境条件
背景
要件
移行手順詳細
- 既存の構成
- 1. スキーマ定義の取得
- 2. 5.6のインストール
- 3. 5.7のインストール
- 4. 8.0のインストール
- 5. 中間インスタンスへのデータ投入
- 6. 中間インスタンスのデータ同期
- 7. 本番およびスタンバイに8.0をインストール
- 8. 本番およびスタンバイにデータ複製
- 9. 本番およびスタンバイの準備完了
- 10. 切替作業
- 11. 後始末
感想
参考) 非サポートな操作(のひとつ）

本エントリの概要

この記事は MySQL Advent Calendar 2019 - Qiita の15日目です。
タイトルの通り、職場のMySQL5.5を8.0にマイグレーションした話を書きます。

2019-02-28

「プログラマのためのSQL第4版」のサンプルコードをMySQLで動くようにしてみた(38.6 曜日)

プログラマのためのSQL MySQL

本エントリの概要
第38章 38.6.1 曜日によるソート P733
- ポイント
- 結果
検証環境

f:id:dupont_kedama:20190228135448p:plain

本エントリの概要

「プログラマのためのSQL第4版」の読書会に参加させてもらってるのですが、たまには予習をしようと思い付きでやってみました。
突発的なやつなので継続はしないつもりです。
本エントリでは動かすことが目的なので内容の理解は後回しです。

2019-02-28

「プログラマのためのSQL第4版」のサンプルコードをMySQLで動くようにしてみた(38.5 その他の時間関数)

MySQL プログラマのためのSQL

本エントリの概要
第38章 38.5 その他の時間関数 P730
- ポイント
- 検証
- 結果
検証環境
PostgreSQLのDOW

f:id:dupont_kedama:20190228135448p:plain

本エントリの概要

「プログラマのためのSQL第4版」の読書会に参加させてもらってるのですが、たまには予習をしようと思い付きでやってみました。
突発的なやつなので継続はしないつもりです。
本エントリでは動かすことが目的なので内容の理解は後回しです。

2019-02-28

「プログラマのためのSQL第4版」のサンプルコードをMySQLで動くようにしてみた(38.4 ユリウス通日)

MySQL プログラマのためのSQL

本エントリの概要
第38章 38.4 ユリウス通日 P728
- ポイント
- 検証
- 結果
第38章 38.4 ユリウス通日 P729
- ポイント
- 検証
- 結果
検証環境
人物

f:id:dupont_kedama:20190228135448p:plain

本エントリの概要

「プログラマのためのSQL第4版」の読書会に参加させてもらってるのですが、たまには予習をしようと思い付きでやってみました。
突発的なやつなので継続はしないつもりです。
本エントリでは動かすことが目的なので内容の理解は後回しです。

untitled .engineer

技術系のブログ（仮）

MySQL8.0で北極点と南極点2点間に線を引いてみた

本エントリの概要

前提

極点の表し方

2点間で線を引く

線はどこにひかれたのか

ちょっといたずら

まとめ

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

本エントリの概要

SRIDを指定した場合

SRIDを指定しない場合

まとめと個人的見解

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

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

MySQLは5.7までGROUP BYにASCとかDESCとかをつけられていた話

本エントリの概要

MySQL5.5からMySQL8.0にマイグレーションしたゆるい話

本エントリの概要

「プログラマのためのSQL第4版」のサンプルコードをMySQLで動くようにしてみた(38.6 曜日)

本エントリの概要

「プログラマのためのSQL第4版」のサンプルコードをMySQLで動くようにしてみた(38.5 その他の時間関数)

本エントリの概要

「プログラマのためのSQL第4版」のサンプルコードをMySQLで動くようにしてみた(38.4 ユリウス通日)

本エントリの概要