MySQL8.0.19で加わった VALUES を試してみる

MySQL 8.0.19。MySQL 8.0 の「メンテナンスリリース」です。
8.0.19より前のMySQLには、「標準SQLのひとつであるVALUES文が実装されていない」という重大な不具合が含まれていたため、バグ修正として本リリースに含まれたようです(真に受ける人がいると困るので、無粋ながら説明しておくと、これ、思いっきり「新機能」ですからね! )

 まだあまりよくわかっていないのですが、個人的にはこれは、「テンポラリテーブルを作らなくても、複数の行のデータを作れる」というものなのかなと理解しています。いまのところ。
 これまでは、クエリの中で何かの当て込み用に複数行のデータが欲しかった場合には

SELECT 13, "name1"
UNION
SELECT 17, "name2"
UNION
SELECT 19, "name3"

 のようにする方法はありました。全然スマートじゃないので、あんまり使うもんじゃないなというのが私の感想(でも、ここぞという時に役に立ったテクニックではあります)。

 VALUESは、 これを

VALUES 
    ROW(13, "name1"),
    ROW(17, "name2"),
    ROW(19, "name3")

 のように書けるもの。


 ということで、以前、緯度や経度1度あたりの長さを求める日記を書いたのですが、それを書き直してみたいと思います。今回は1度ではなく1秒の距離を求めてみることにします。

経度1秒の長さ

SELECT  ido, 
    ST_Distance( 
       ST_GeomFromText(CONCAT("POINT(", ido, " ", 135, ")"), 6668), 
       ST_GeomFromText(CONCAT("POINT(", ido, " ", 135+(1/60/60), ")"), 6668)
  ) dist
FROM (VALUES ROW(10),ROW(20),ROW(30),ROW(40),ROW(50),ROW(60),ROW(70),ROW(80),ROW(89),ROW(35),ROW(0)) as t(ido) 
ORDER BY ido;

結果:

+-----+--------------------+
| ido | dist               |
+-----+--------------------+
|   0 | 30.922293347363215 |
|  10 |  30.45543707038192 |
|  20 | 29.068816191795264 |
|  30 | 26.801938223437794 |
|  35 | 25.357920979131784 |
|  40 | 23.720694278317538 |
|  50 |  19.91554019501125 |
|  60 | 15.500025396953356 |
|  70 | 10.607016183528014 |
|  80 |  5.387103340492138 |
|  89 | 0.5478026839370822 |
+-----+--------------------+
11 rows in set (0.00 sec)

 赤道付近で約30m、北緯35度付近で 25m強となっていることが見て取れます。
 テキストエディタでINSERT文を作ったり、作業のためだけにテーブルを作ったりする方法よりもずっとスマートですね。 ST_GeomFromText()に渡すWKTを、CONCATで作っているところがちょっと格好悪いけど。

緯度1秒の長さ

 同様に、緯度1秒の長さを求めてみます。

SELECT 
  ido, 
  ST_Distance( 
     ST_GeomFromText(CONCAT("POINT(", ido,         " 135)"), 6668), 
     ST_GeomFromText(CONCAT("POINT(", ido+1/60/60, " 135)"), 6668)
  ) dist
FROM (VALUES ROW(10),ROW(20),ROW(30),ROW(40),ROW(50),ROW(60),ROW(70),ROW(80),ROW(89),ROW(35),ROW(0)) as t(ido) 
ORDER BY ido;
+-----+--------------------+
| ido | dist               |
+-----+--------------------+
|   0 |  30.71497472832411 |
|  10 | 30.724353692261502 |
|  20 |  30.75135882757604 |
|  30 | 30.792732916306704 |
|  35 | 30.817301218397915 |
|  40 | 30.843485630117357 |
|  50 | 30.897495447658002 |
|  60 | 30.948247983120382 |
|  70 | 30.989621736109257 |
|  80 | 31.016626415774226 |
|  89 |  31.02591015584767 |
+-----+--------------------+
11 rows in set (0.00 sec)

 以前の日記で書いたとおり、極付近に近づくほど、緯度1秒の長さが長くなっていることが見て取れます。

2桁の数字を作る

 確かセルコさんあたりが本に書いていた気がするのですが、クエリの中で2桁の整数が欲しくなったときのちょっとしたテクニック。 0~9の数字10件だけを登録しておいたテーブルを使う例だったと記憶していますが、VALUES文を使うと、それを、テーブルを使わずにできるようになります。

WITH t AS (
 SELECT * FROM (VALUES ROW(1),ROW(2),ROW(3),ROW(4),ROW(5),ROW(6),ROW(7),ROW(8),ROW(9),ROW(0)) AS t(n)
)
SELECT CONCAT(t1.n, t2.n) num
  FROM t t1, t t2
 ORDER BY num;
+------+
| num  |
+------+
| 00   |
| 01   |
| 02   |
:  :   :
| 98   |
| 99   |
+------+

 今まで使ってきたSQLの感覚に馴染むように、上のように書いてみましたが、実は VALUES 文は「文」ですから、これ自体が値を返すのです。
たとえば、冒頭で UNION との比較で紹介した例の実行結果は、こう。

mysql> VALUES 
    ->     ROW(13, "name1"),
    ->     ROW(17, "name2"),
    ->     ROW(19, "name3")
    -> ;
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|       13 | name1    |
|       17 | name2    |
|       19 | name3    |
+----------+----------+
3 rows in set (0.00 sec)

 ということで、先ほどのCTEの中身から、余計な SELECT 文を取り去ってしまいましょう。ほら、こんなふうに書けるんです。

WITH t(n) AS (
 VALUES ROW(1),ROW(2),ROW(3),ROW(4),ROW(5),ROW(6),ROW(7),ROW(8),ROW(9),ROW(0)
)
SELECT CONCAT(t1.n, t2.n) num
  FROM t t1, t t2
 ORDER BY num;

感想

 「テンポラリのテーブルを作らずに行を生やす」ということに漠然と憧れがあったのですが、まさか本当に実装されるとは思いませんでした。そもそもこれが標準SQLだというのも知りませんでした。 ただ、これはうまく使わないと、クエリが見えにくくなってしまうかもしれないなぁ、とも感じました。いくらでも「わかりにくいクエリ」を作ることができそうです。
 また、いちいち「ROW」と書かなきゃいけないのも、ちょっと面倒に感じました。
木村明治さんが2019年夏にRDBMSごとのSQLの比較を発表してくれていましたが、その資料の21ページ以降で VALUES 文が紹介されていました。 さすがです!!! しかも、アイデア自体はMySQL発祥(マルチプルインサート時のVALUES)らしい。へぇぇ。

www.slideshare.net

MySQLのSRID()でSRID変換する際にaxis-orderで悩んだ話

訳あって、MySQLで「GEOMETRY型のカラムに、いったん SRID=0で登録したあと、一気に正しいSRIDに変換する」ということをやろうとしたところ、思惑通りにいかず随分悩んだので、整理しておきます。

やろうとしたこととエラー発生

 ここではシンプルな例に置き換えた再現実験で紹介します。

 まず、GEOMETRY型を入れられるテーブルを作りデータを1件登録します。

CREATE TABLE g1 (g GEOMETRY);
INSERT INTO  g1 VALUES (ST_GeomFromText("POINT(35 135)"));

 SRIDを指定していないので、SRID=0で登録されています。axis-orderは lat-long です(というか、そうなっていることを期待して登録しました)。登録された内容を確認してみます。

mysql> SELECT st_astext(g), st_srid(g) from g1;
+---------------+------------+
| st_astext(g)  | st_srid(g) |
+---------------+------------+
| POINT(35 135) |          0 |
+---------------+------------+

 SRID=0 で、POINT(35 135) が期待通り登録されていることが確認できました。
では、このSELECT句にもうひとつ、SRIDを6668(JGD2011)に変換したものを取得するよう、加えてみます。

mysql> SELECT ST_AsText(g), ST_SRID(g), ST_AsText(ST_SRID(g, 6668)) FROM g1;
ERROR 3732 (22S03): A parameter of function st_srid contains a geometry with latitude 135.000000, which is out of range. It must be within [-90.000000, 90.000000].

 あら。変換できません。latitudeが 135だと言われています。これは何かaxis-orderについて、認識の相違がありそうです。文字通り、方向性の違い。バンド解散の危機です。

ST_SPATIAL_REFERENCE_SYSTEMSでの定義は?

 各SRIDごとの axis-order は INFORMATION_SCHEMA の ST_SPATIAL_REFERENCE_SYSTEMS に書かれていることがあります。ということで、とりあえず SRID=0 の axis-order がどうなっているかを確認してみることにします。

mysql> select * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID=0;                                             
+----------+--------+--------------+--------------------------+------------+-------------+
| SRS_NAME | SRS_ID | ORGANIZATION | ORGANIZATION_COORDSYS_ID | DEFINITION | DESCRIPTION |
+----------+--------+--------------+--------------------------+------------+-------------+
|          |      0 | NULL         |                     NULL |            | NULL        |
+----------+--------+--------------+--------------------------+------------+-------------+
1 row in set (0.03 sec)

 ・・・・残念。。。SRIDゼロは情報量ゼロ。

SRID=0は long-lat だと仮定して実験

 確たる情報にはたどり着けていませんが、私が latitudeだと思って登録した 135という値が、ST_SRID()関数では longitudeと解釈されたらしいことは間違いありません。latとlonを入れ替えれば動作するのではなかろうかと仮説を立てて、実験することにします。

mysql> DELETE FROM g1;
mysql> INSERT INTO  g1 VALUES (ST_GeomFromText("POINT(135 35)"));                                                               

 まぁ「丸くなった地球」を扱うために、WGS84とかJGD2011とかをずっと触ってきたMySQLユーザとしては、非常に気持ち悪いPOINTの記法ではありますが、我慢します。

先ほどエラーになったSQLを再度投げてみましょう。

mysql> select st_astext(g), st_srid(g), st_astext(st_srid(g, 6668)) from g1;                                                
+---------------+------------+-----------------------------+
| st_astext(g)  | st_srid(g) | st_astext(st_srid(g, 6668)) |
+---------------+------------+-----------------------------+
| POINT(135 35) |          0 | POINT(35 135)               |
+---------------+------------+-----------------------------+

 動きました! しかも、元のSRID=0の時の値が 135-35 だったのに、SRID変更*1したら 35-135 に変わったのも驚きです。

まとめ

 昨年にも、axis-order について内部の保持方法を含めて議論があったことを記憶していますが、当時の私の経験不足でついて行ききれなかった部分がありました。今回のこの事象が、その話かどうか再確認はしていないのですが、axis-order問題、こういうところでハマるんだなぁと体感できた、大変良い年末でした(笑)。

おまけ

 やりたかったのは、こういうことでした(もう少し複雑ですが本質的にはこんな感じ)。
一旦 SRID=0 で登録して(つまりSRID不明のまま処理を開始している)、登録が終わったあとで一気にSRIDを置き換える、と。
 今回のデータ例での実行結果を以下に紹介して本エントリを終わりにいたします。

mysql> UPDATE g1 SET g=ST_SRID(g, 6668);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select st_astext(g), st_srid(g) from g1;
+---------------+------------+
| st_astext(g)  | st_srid(g) |
+---------------+------------+
| POINT(35 135) |       6668 |
+---------------+------------+
1 row in set (0.00 sec)


 せっかくなので本日記を、今年は盛り上げきれなかった RDBMS-GIS アドベントカレンダの25日目ぶんとして登録しちゃうことにします:-) システム的に登録可能な時期でしたらいつでもみなさん、空いている日に追加エントリ登録しちゃってくださいませ。
qiita.com



追記: SRID=0については、@dupont-kedama さんの以下のエントリも参照ください。とくに、SRIDを指定しなくてSRID=0として扱われる場合と、SRID=0を指定した場合は違うんだぞ、という話が興味深かったです。
dupont.hatenablog.jp


追追記:
 SRID=0で lat-lon で登録した列に対して、ST_SRID()でのSRID変更相当の処理を行う方法を思いついた。

mysql> select st_astext(g), st_srid(g), ST_AsText(ST_GeomFromText(st_astext(g),6668))  from g1;
+---------------+------------+-----------------------------------------------+
| st_astext(g)  | st_srid(g) | ST_AsText(ST_GeomFromText(st_astext(g),6668)) |
+---------------+------------+-----------------------------------------------+
| POINT(35 135) |          0 | POINT(35 135)                                 |
+---------------+------------+-----------------------------------------------+

 うん、確かにできているけど、、、、格好悪い。。

*1:変換と変更という言葉を厳密に使い分けています。与えられた座標値の数字をそのままにSRIDだけ読み替えることをここでは「変更」と呼んでいます

MySQLのDROP DATABASEでWARNINGが表示されない事象

 DROP DATABASE IF EXISTS ... で存在しないデータベースをドロップしようとしたときに、WARNING が表示されない事象があったので紹介します。

IF EXISTS (テーブルの場合)

 MySQLDROP文には "IF EXISTS" というオプションがあり、たとえばテーブルの場合は、以下のように使います。

mysql> use test
mysql> DROP TABLE IF EXISTS mytable999;                                                                                             
Query OK, 0 rows affected, 1 warning (0.02 sec)

 mytable999 というテーブルは存在しませんが、IF EXISTS 句のおかげでエラーにはならず正常終了しています。
ワーニングがあるようなので見てみます。
 

mysql> SHOW WARNINGS;
+-------+------+---------------------------------+
| Level | Code | Message                         |
+-------+------+---------------------------------+
| Note  | 1051 | Unknown table 'test.mytable999' |
+-------+------+---------------------------------+

 そんなテーブルは存在しないよ、と言っています。
・・と、こんな感じの動作をします。

IF EXSISTS (データベースの場合)

 DROP DATABSE でも同じく IF EXISTS 句が使えるのでやってみます。

mysql> DROP DATABASE IF EXISTS test999;
Query OK, 0 rows affected, 1 warning (0.00 sec)

 test999というデータベースは存在しないので、ワーニングがでています。テーブルの時と同じように見てみましょう。

mysql> SHOW WARNINGS;                                                                                                            
Empty set (0.00 sec)

 なんと! ワーニングが表示されません!

WARNINGをすぐに表示するオプションなら表示される

 ということをtwitterに書いたら、すかさずとみたさんた実験してくれました。 mysqlコマンドラインクライアントは --show-warnings というオプションを付けて起動することで、warningが出た時にすぐにwarningメッセージを表示してくれる機能があります。(デフォルトではワーニングは「発生したという事実(と個数)」を表示するだけで、メッセージは表示されません)

 ワーニングが内部で発生していないというわけではないようです。ということは、内部的な後続処理でワーニングが消されてしまったという可能性が考えられます。

クエリが投げられていることを確認する

 STATUSの Questions で、これまでMySQLサーバに投げられたリクエストの数がわかるので、これを監視してみる方法があります。
まずテーブルのドロップで、動作を確認してみます。

mysql> SHOW STATUS LIKE '%Questions%';                                                                                           
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 42    |
+---------------+-------+

 42このクエリがこれまで投げられたということです。ここでテーブルをDROP して、再びQueriesを確認してみると、

mysql> DROP TABLE IF EXISTS mytable999;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SHOW STATUS LIKE '%Questions%';                                                                                           
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 44    |
+---------------+-------+

 44になりました。 DROP TABLE で1クエリ、SHOW STATUS 自身で1クエリ投げているので、2だけ増えるのは納得の結果です。


 では DROP DATABASE ではどうなるか。

mysql> SHOW STATUS LIKE '%Questions%';                                                                                           
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 51    |
+---------------+-------+

mysql> DROP DATABASE IF EXISTS test999;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW STATUS LIKE '%Questions%';                                                                                           
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 54    |
+---------------+-------+

 なんと、3増えました。仮説を裏付ける結果となりました。
そしてこの結果を、よりダイレクトにとみたさんが確認してくれました。私が今回さくっと試した環境は、事情があってパラメタをいじりたくなかったので試せなかったのですが、MySQLサーバオプションで一般ログを出力するようにすることで、どんなクエリが投げられたのかを確認することができます。

 ということで、DROP DATABASE のあとに 内部的に SELECT DATABASE() が投げられていることがわかりました。これにより発生したワーニングが消されてしまっているということですね。

さぁバグ報告だ! と思ったら・・・先人たちの報告

 調べてみると、すでにbugs.mysql.comへの報告が行われていました。2015年のことです。

MySQL Bugs: #79684: "drop database if exists" says "1 warning", but "show warnings" returns nothing

 同じ動作に気づいた人もいるようで、2017年、2018年にもそれぞれ同様の動作が報告されています(報告前に調べましょう)。

MySQL Bugs: #86989: Missing warning after DROP DATABASE IF EXISTS for a non-existent database
MySQL Bugs: #90058: Note for dropping a non-existent table not shown


 まぁ重要度が低いと考えられているのか、4年たっても直す気配がない様子ですね。 
こういうこともあるので、もしかしたら --show-warnings はデフォルトでオンにしておいてもいいのかなという気が、少ししてきました。これによってどれくらい、鬱陶しいメッセージが増えてしまうか、との兼ね合いなのでしょうけれど。


 なお、この実験をしている最中、どうも私が無意識に TABLE と DATABASE をよく打ち間違えていたようで、twitter上でも(おそらく間違えて打った結果を勘違いして)混乱する情報を出してしまって失礼しました。 「そんな結果にならないよ」と、すかさずツッコミを入れてくれたとみたさん、ありがとうございました!


おまけ

 この日記を書くために改めて実験していて、「そういやこのサーバ、Queriesがこんなに小さな値のわけがないんだけど・・・」と気づきました。
そうか、Queries は、GLOBALとSESSION で、それぞれ値を持っているのですね。 普段気にしないから認識の外にありましたが、副次的な学びでした(笑)。

mysql> SHOW GLOBAL STATUS LIKE 'Questions';                                                                                      
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 23324 |
+---------------+-------+

mysql> SHOW STATUS LIKE 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 46    |
+---------------+-------+


 

解決:高緯度ほど緯度1度が広くなる謎(追記あり)

数日前の日記の中で、「緯度1度あたりの長さは、緯度の高低に依らず一定と予想して、MySQLで計算してみたところ、差が出てびっくり」という実験結果を紹介しました。
http://sakaik.hateblo.jp/entry/20191202/mysql_gis_metre_per_degree

これは、地球が(そして今回計算に使用した JGD2011が)真球ではなく回転楕円体だからということに起因するものであると、すぐに想像できましたが、いやまて、よく見ると高緯度のほうが1度あたりの距離が長い。 

mysql> SELECT id, ST_Distance(pos1, pos2)  FROM g3 ORDER BY ID;
+------+-------------------------+
| id   | ST_Distance(pos1, pos2) |
+------+-------------------------+
|    0 |      110573.13812416371 |
|   10 |      110610.23595555034 |
|   20 |      110710.37167306663 |
|   30 |      110861.46743133431 |
|   35 |      110950.61420268985 |
|   40 |      111045.29885197058 |
|   50 |      111239.69315258414 |
|   60 |      111421.20351156592 |
|   70 |      111567.93710081652 |
|   80 |       111662.1956839133 |
|   90 |      111692.61028462648 |
+------+-------------------------+

 地球楕円体(赤道方向のほうが極方向よりも長い回転楕円体)は、赤道付近の半径が真球の場合に比べて伸びているのだから、普通に考えれば低緯度(赤道に近いほう)が1度あたりの距離は長くなるはず。 なのに、緯度0度よりも90度に近くなるに従って1度あたりの距離が長くなっているのは、直観に反する! というのが前回の日記での疑問でした。

なぜそう思ったのか

 イメージが掴みやすいように、扁平率を思いっきり大きくした以下のような楕円を地球だと考えてみます。この地球上に点Aと点Bがあるときを考えます。
f:id:sakaik:20191208215200p:plain

 以下のように、点A、点Bそれぞれに地球の中心から線をひっぱります。
f:id:sakaik:20191208215318p:plain

 このとき、これらの線が長軸となす角度を、緯度と考えました。
f:id:sakaik:20191208215502p:plain
 つまりこのとき、点A付近(低緯度)付近の1度と、点B付近(高緯度)付近の1度を比べると、明らかに点A付近のほうが長くなります。MySQLで計算した結果の値と逆の感覚です。「明らかに」の部分がピンと来ない人は、中心の角度を45度にした線を描いてみて、赤道~45度の弧の長さと、45度~90度の弧の長さを比較してみると良いでしょう。MySQLの結果の値自体が誤っているわけではないことは、他のソースで確認済なので、「明らかに」感じたほうが誤っているとしか考えようがありません。何がおかしいのでしょうか。


ほんとの緯度の定義

 実は「緯度」の定義が、上で書いたようなものとは異なっていることがわかりました。 これがわかったのは高校の教科書のおかげです。高校の教科書、すごい!
元はと言えば、先日のセミナーで「ベッセル楕円体って中学あたりで習ったよね」と発言してしまったことが、どうも庶民感覚(笑)とずれていたらしいと知ったことで、教科書を取り扱っている本屋さんに中高の地学の教科書を確認しに行ったのですが、まぁこの話自体は「高校で、地球の形について習う。回転楕円体(出版社によっては地球楕円体)という用語はあるが、GRS80については出て来ない」というのが結論でした。 これを確認している際、あるひとつの教科書(出版社名失念)で、緯度の定義についてさらりと触れられていたのです。教科書、すごい!

 まず、観測点Aにおける楕円体の接線を考えます。
 f:id:sakaik:20191208220416p:plain

 その接線に直交する線を引きます(=観測点における鉛直方向)
f:id:sakaik:20191208220923p:plain

 その線が長軸と交わるところの角度、これが「緯度」なのです。
f:id:sakaik:20191208220932p:plain
 考えてみれば人工衛星による観測のない時代、「真下」を知る根拠は重力なわけで、その「真下」は必ずしも地球中心を向いていない。なお、地球回転による遠心力もあるので、更にすこしずれるらしいですが、遠心力の影響は重力の力に比べてかなり小さいそうです。

 参考までに点Bにおける鉛直方向の図も示しておきます。
f:id:sakaik:20191208221424p:plain

 これらの図を見てみると、高緯度における1度のほうが距離が長くなるのも納得できますね。実際の地球は扁平率が 298分の1程度なのでその差は微々たるものですが、それでも緯度10度隔てると1度あたりの距離は100m(0.1%ほど)くらい差がでるようです。

そもそもの歴史的な話として

 そもそもが、高緯度における1度の長さを測ろうとしていた人たちがいて、どうも思っていたのよりも大きくなる。なんでだ?と疑問に思ったところから、地球が回転楕円体である(真球ではない)と気づいたということのようで、なるほど勉強になります。地球の形、奥が深い!

(2019/12/14追記) "ほんとの定義" などない!

 「ほんとの緯度の定義」と私が書いた点に対して、ご指摘をいただきました。

 JGD2011とかWGS84とかを見ている上では、緯度の定義は上に書いたとおりということで間違いはないのですが、これが「緯度の本当の定義」と呼ぶのは、言い過ぎだったようです。今回結論とした緯度の定義は「地理緯度」(地理座標系での緯度ってことですかね)であり、これとは別に、ちゃんと「地心緯度」というものもあるとのこと(この日記の冒頭で「こうだと思っていたんですが」と書いたほう)。
 ご指摘いただきありがとうございました。 不正確なことを書いちゃったけど、こうやって指摘いただけた時、あぁ書いてよかったなぁと思います。ひとつ賢くなった^^


参考:緯度 - Wikipedia

MySQL Technology cafe #6 でMySQLのGISの話をしてきました

 そんなわけで、12月5日に開催された Oracle Technology Cafe #6 にて、発表の機会をいただき、あんなことやこんなことを語ってきました。
 この2年間、色々なところでMySQLGISについてお話をしてきて、そろそろ「測地系というのが色々あるらしい」「緯度経度で表すらしい(地理座標系)」「内部バイナリと人間可読な記法の間で変換を明示する必要があるらしい」というあたりは浸透してきているかな、という感触を持っています。それほど難しいわけではないけれども、最初に考え方を理解するのに少しハードルがある部分なので、ここを乗り越えたらあとはみんなが盛り上げてくれるのを楽しく見守るようになれたらいいなぁと思っています。
 今回、これまでの総括的な意味合いも持ち、ゼロベースで構成を作り上げて発表をいたしました。今まで説明してこなかった最後の大ネタである投影座標系の説明も(それなりにたぶんしっかりと)することができて、「伝えることはすべて伝えたぞ!次はきみたちの番だ!」の気分です(笑)。
 発表資料はこちら。

www.slideshare.net

9系原点の場所を勘違い

 日本の平面直角座標系の9系(東京など関東付近)の原点位置を、セミナーの中で何度か「埼玉県の千葉県寄りのあたり」とお話をしましたが、これは誤りでした。自分の発表後にtwitterを見たら、指摘をいただいていました。千葉県民としては、これは不覚!


 1系から19系の各原点は36.0度とか、131.0度とか、129.5度のように切りの良い数字になるように設定されていることが多いのです。対象エリアの真ん中付近にあれば、物理的になにか設置するわけでもないので、ある程度自由に原点を決めることができるからです。ところが、この9系は北緯36度、東経139度50分というちょっと半端な場所が原点として定められています。これはどこかなーと目視で地図を確認した際、私、なぜか139度50分を 139.5度と勘違いして地図を見てしまったみたいなんですよね。
 ひとつ前の記事(http://sakaik.hateblo.jp/entry/20191202/mysql_gis_metre_per_degree)で調べたとおり、北緯35度付近では経度1度はおよそ91kmとなるので、139度50分(≒ 139.833度)と139.5度との差、0.333度 はおよそ30km。30km西側を見てしまったので埼玉県になってしまったのした。次のセミナーでも埼玉県と言い続けていた可能性も高く、ご指摘、感謝です!

「たのしかったです」

 小学生並みの感想ですが、今回本当に楽しかったです。オラクルさん主催の会なので、イベントの告知先も普段のユーザベースの勉強会とは異なるのか、いつもと少し客層の違うたくさんの人が来てくださいました。中にはGISにお詳しい方もいたり、逆にMySQL(をはじめとするRDBMS)でこんなデータを扱えるのかと感心してくださる方もいたり、とにかく「伝わった」感がビシバシ感じられるセミナーでした。楽しかった。
 理想では、もっとゆったりと、アルコール燃料を補給しながら進めたかったのですが、伝えたいことが多く、凄い勢いでまくしたててしまいました(笑)。まだ話し足りないw

GeoHashとかの解説資料

 私の過去の発表の中に、GeoHashや「標高」に関する説明があります。以下の資料が一番整理されているかなと思いますので、よかったらご覧ください。山﨑さんの発表の中で、GeoHash以外の位置の符号化手法について提案がありましたが、その中で「GeoHashは長方形なんですよ」と言っていた意味も、この資料でわかるかと思います(桁数が増えるごとに、タテ・ヨコに交互に長方形になる)

www.slideshare.net

GeoHashは SRID:4326にのみ対応

 山﨑さんの発表のなかで、ちらっと「WGS84にだけ対応しています」という話が出てきました。いくつかの関数でそうだった気がしますが、話題の(?) GeoHashもこれに該当します。少し解説してみますね。

 実験用のテーブルを作ってデータを投入、内容を確認します。

mysql> CREATE TABLE g4 (id INTEGER, g GEOMETRY SRID 6668);                                                                       
mysql> INSERT INTO g4 VALUES (1, ST_GeomFromText('POINT(35.678 136.984)', 6668));
mysql> INSERT INTO g4 VALUES (2, ST_GeomFromText('POINT(36.876 137.011)', 6668));

 内容を確認する際、GEOMETRY系の型はバイナリで格納されているため、テキスト(WKT)に変換する必要があります。ついでに、この位置情報がどの測地系(SRID)で格納されているかも確認してみましょう。ST_SRID()関数で見ることができます。

mysql> SELECT id, ST_AsText(g), ST_SRID(g) FROM g4;
+------+-----------------------+------------+
| id   | ST_AsText(g)          | ST_SRID(g) |
+------+-----------------------+------------+
|    1 | POINT(35.678 136.984) |       6668 |
|    2 | POINT(36.876 137.011) |       6668 |
+------+-----------------------+------------+

 投入したとおりの内容が得られますね(あたりまえ)。
では、もうひとつ取得カラムを追加して、GeoHashを得てみましょうか。ST_GeoHash() という関数があります。第1引数に GEOMETRY、第2引数に得たいハッシュの桁数を指定します。

mysql> SELECT id, ST_AsText(g), ST_SRID(g), ST_GeoHash(g, 6) FROM g4;
ERROR 3682 (22S00): Function st_geohash is only defined for SRID 0 and SRID 4326.

 
 おやおや。 st_geohash は SRID 0と4326 にだけ対応しているそうです。
丁寧に処理をしたければ、今回の JGD2011(6668) の緯度経度を、WGS84(4326)に変換してから ST_GeoHash()に与えるべきですが、幸いにも JGD2011と WGS84はほぼ一致します(数センチくらいの差、だったかな)。なので今回は、(本当は)JGD2011で表した緯度経度なのですが、強引にそのままエイヤとWGS84の緯度経度であるかのように読み替えてしまいましょう。先ほど使った ST_SRID() は、緯度経度の数字は変更せずに測地系だけを読み替える機能も持っています(互いの測地系によっては全然違う場所を指してしまうことになります。たとえば Tokyo 測地系で表された緯度経度をそのままWGS84として読むと、数百メートルずれた場所を指すことになります)。

 ST_SRIDの第2引数に WGS84(4326)を与えて変換し、ST_GeoHash() に喰わせた実行結果が、以下になります。

mysql> SELECT id, ST_AsText(g), ST_SRID(g), ST_GeoHash(ST_SRID(g, 4326), 6) FROM g4;
+------+-----------------------+------------+---------------------------------+
| id   | ST_AsText(g)          | ST_SRID(g) | ST_GeoHash(ST_SRID(g, 4326), 6) |
+------+-----------------------+------------+---------------------------------+
|    1 | POINT(35.678 136.984) |       6668 | xn36vn                          |
|    2 | POINT(36.876 137.011) |       6668 | xn93vc                          |
+------+-----------------------+------------+---------------------------------+

 めでたく GeoHash 値が得られました。
 

ラクルさんのMySQLイベントで発表しよう!

 今回オラクルさんのMySQLイベントに登壇したごほうびに、MySQLのイルカ(sakila)のぬいぐるみをいただきました。大きくて手触り良く、絶対これは良い品! さっそく我が家の先住イルたちに熱烈な歓迎を受けていました。

 

f:id:sakaik:20191207110709j:plain
我が家のイルカたちによる歓迎式典の様子

 オラクルの(いくつかの?)MySQLイベントでは、発表をした人にプレゼントしてくれるらしいので、イルカが欲しい人も、そうでない人も、「発表してみたいなー」という意思表示をしてみると良いかと思います。
 併せて、MySQLユーザ会もときどきセミナー形式のイベントや交流会形式のイベントなどを開催していまして、ユーザ会としても特に「若手」「初心者」「登壇未経験者」に発表の機会を持ってもらいたいと考えています。ちょっと不器用なユーザ会ですので、募集のアピールがうまく伝わらないこともあるかと思いますが、かように熱烈歓迎しておりますので、twitterハッシュタグ #mysql_jp などをウォッチしながら、チャンスだ!と思ったらぜひ手をあげてみていただけたらと思います。

f:id:sakaik:20191207110958j:plain
sakila

参考情報

 この日記は、RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2019 の5日目の記事として投稿しました。
RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2019 - Qiitaqiita.com

 今回のイベントのリンクはこちら(各資料へのリンクそしてtwitterまとめへのリンクなども貼られています)
oracle-code-tokyo-dev.connpass.com

経度一度はどれくらいの長さ?MySQLをつかって調べてみよう

この日記は、RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2019 の2日目の記事です。
( https://qiita.com/advent-calendar/2019/rdbms_gis )

経度一度ってどれくらい?

 赤道付近の一周の長さが だいたい 40,000km だというのはみんな知っていますよね。実際はもう少し長いのですが、とりあえず感覚的なものでいいです。で、経度というのは、緯度が上がっていくごとに円が小さくなりますから、1度の長さもどんどん短くなるはずです。どんな感じなのかな、とMySQLを使って見てみました。
 MySQL、まだまだ関数が少ないので(とくに集約系や分析的なもの)、関数が少ないうちに、少ないこれらの関数を遊び倒しましょう。できることが限られているというのは、アイデアを膨らませるという点でメリットでもあります!

経度1度を求める

 まず、テーブル g1 を作って適当にデータを入れます。ここでは、緯度0度から10度刻みで、緯度135度と136度の2つの位置情報をカラムに入れることにしました。測地系は、JGD2011を使います。

CREATE TABLE g1 (
    id integer, 
    pos1 GEOMETRY SRID 6668, 
    pos2 GEOMETRY SRID 6668
);

INSERT INTO g1 VALUES (0, ST_GeomFromText('POINT(0 135)', 6668), ST_GeomFromText('POINT(0 136)', 6668));
INSERT INTO g1 VALUES (10, ST_GeomFromText('POINT(10 135)', 6668), ST_GeomFromText('POINT(10 136)', 6668));
INSERT INTO g1 VALUES (20, ST_GeomFromText('POINT(20 135)', 6668), ST_GeomFromText('POINT(20 136)', 6668));
INSERT INTO g1 VALUES (30, ST_GeomFromText('POINT(30 135)', 6668), ST_GeomFromText('POINT(30 136)', 6668));
INSERT INTO g1 VALUES (35, ST_GeomFromText('POINT(35 135)', 6668), ST_GeomFromText('POINT(35 136)', 6668));
INSERT INTO g1 VALUES (40, ST_GeomFromText('POINT(40 135)', 6668), ST_GeomFromText('POINT(40 136)', 6668));
INSERT INTO g1 VALUES (50, ST_GeomFromText('POINT(50 135)', 6668), ST_GeomFromText('POINT(50 136)', 6668));
INSERT INTO g1 VALUES (60, ST_GeomFromText('POINT(60 135)', 6668), ST_GeomFromText('POINT(60 136)', 6668));
INSERT INTO g1 VALUES (70, ST_GeomFromText('POINT(70 135)', 6668), ST_GeomFromText('POINT(70 136)', 6668));
INSERT INTO g1 VALUES (80, ST_GeomFromText('POINT(80 135)', 6668), ST_GeomFromText('POINT(80 136)', 6668));
INSERT INTO g1 VALUES (90, ST_GeomFromText('POINT(90 135)', 6668), ST_GeomFromText('POINT(90 136)', 6668));

 このテーブルの各レコードの長さは、以下のSQLで求めることができます。

SELECT id, ST_Distance(pos1, pos2)  FROM g1 ORDER BY ID;
+------+-------------------------+
| id   | ST_Distance(pos1, pos2) |
+------+-------------------------+
|    0 |      111319.49079326246 |
|   10 |      109639.3390102644  |
|   20 |      104646.97563995633 |
|   30 |       96486.0081494232  |
|   35 |       91287.79089978167 |
|   40 |       85393.3619519223  |
|   50 |       71695.04015879167 |
|   60 |       55799.17666195067 |
|   70 |       38185.80088710373 |
|   80 |       19393.044953483724|
|   90 |                       0 |
+------+-------------------------+
11 rows in set (0.00 sec)

 赤道付近で、1度あたり111km、日本がある北緯35度付近では91km、70度で38km、80度で19kmと一気に狭くなっていくのがわかりますね。感覚とも一致します。

1度ではなく1分は?

 何十キロもの長さは日常でイメージしにくいので、もう少し小さい単位である「分」の単位で距離を求めてみましょう。1分は約 0.0167度 なので以下のようなデータを作ります。各緯度で、経度135度から135.0167度を表すデータ群です。

CREATE TABLE g2 (id integer, pos1 GEOMETRY SRID 6668, pos2 GEOMETRY SRID 6668);

INSERT INTO g2 VALUES (0, ST_GeomFromText('POINT(0 135)', 6668), ST_GeomFromText('POINT(0 135.0167)', 6668));
INSERT INTO g2 VALUES (10, ST_GeomFromText('POINT(10 135)', 6668), ST_GeomFromText('POINT(10 135.0167)', 6668));
INSERT INTO g2 VALUES (20, ST_GeomFromText('POINT(20 135)', 6668), ST_GeomFromText('POINT(20 135.0167)', 6668));
INSERT INTO g2 VALUES (30, ST_GeomFromText('POINT(30 135)', 6668), ST_GeomFromText('POINT(30 135.0167)', 6668));
INSERT INTO g2 VALUES (35, ST_GeomFromText('POINT(35 135)', 6668), ST_GeomFromText('POINT(35 135.0167)', 6668));
INSERT INTO g2 VALUES (40, ST_GeomFromText('POINT(40 135)', 6668), ST_GeomFromText('POINT(40 135.0167)', 6668));
INSERT INTO g2 VALUES (50, ST_GeomFromText('POINT(50 135)', 6668), ST_GeomFromText('POINT(50 135.0167)', 6668));
INSERT INTO g2 VALUES (60, ST_GeomFromText('POINT(60 135)', 6668), ST_GeomFromText('POINT(60 135.0167)', 6668));
INSERT INTO g2 VALUES (70, ST_GeomFromText('POINT(70 135)', 6668), ST_GeomFromText('POINT(70 135.0167)', 6668));
INSERT INTO g2 VALUES (80, ST_GeomFromText('POINT(80 135)', 6668), ST_GeomFromText('POINT(80 135.0167)', 6668));
INSERT INTO g2 VALUES (90, ST_GeomFromText('POINT(90 135)', 6668), ST_GeomFromText('POINT(90 135.0167)', 6668));
SELECT id, ST_Distance(pos1, pos2)  FROM g2 ORDER BY ID;
+------+-------------------------+
| id   | ST_Distance(pos1, pos2) |
+------+-------------------------+
|    0 |      1859.0354970687047 |
|   10 |      1830.9776594574585 |
|   20 |      1747.607087970476  |
|   30 |      1611.3214483744373 |
|   35 |      1524.512474129115  |
|   40 |      1426.0766207422205 |
|   50 |      1197.3160879967666 |
|   60 |       931.8551188371557 |
|   70 |       637.7100204965047 |
|   80 |       323.86784178140675|
|   90 |                       0 |
+------+-------------------------+

 赤道付近で1.9km、北緯35度付近で1.5kmです。念のため赤道付近の「分」の結果を60倍して答え合わせをしてみましょうか。

mysql> SELECT 1859.0354970687047 * 60  ;
+-----------------------+
| 1859.0354970687047*60 |
+-----------------------+
|  111542.1298241222820 |
+-----------------------+
1 row in set (0.00 sec)

 あれれ。本来の赤道付近の距離 111319.49079326246 よりも 少し大きくなっていまいましたね。考えてみたら、
「1度」を 0.0167 としましたが、これがちょっと切り上げが雑すぎたのかもしれません。0.016666666666667くらいでやるべきだったのでしょうか。まぁこれは感覚とも一致するのでよしとしましょう。
 というかそもそも、0.0166666....とか言っている時点で誤差許容なので、もうちょっとまともな計算をしましょうか。

経度1度、1分、1秒の長さを求める(別解)

 最初に「度」を求めたのですから、度を60で割って分を求めたり、度を60*60で割って秒を求めたりするほうが(先ほどみたいに途中で不正確な小数を持ち出すよりも)正確な値が求まります。ここでは、そのやり方で。最初に作った g1 テーブルを使います。

WITH cte_g1 AS (
  SELECT id, ST_Distance(pos1, pos2) dist FROM g1
)
SELECT id, round(dist,2) do, round(dist/60,2) fun, round(dist/60/60,2) byou 
  FROM cte_g1;
+------+-----------+---------+-------+
| id   | do        | fun     | byou  |
+------+-----------+---------+-------+
|    0 | 111319.49 | 1855.32 | 30.92 |
|   10 | 109639.34 | 1827.32 | 30.46 |
|   20 | 104646.98 | 1744.12 | 29.07 |
|   30 |  96486.01 | 1608.10 | 26.80 |
|   35 |  91287.79 | 1521.46 | 25.36 |
|   40 |  85393.36 | 1423.22 | 23.72 |
|   50 |  71695.04 | 1194.92 | 19.92 |
|   60 |  55799.18 |  929.99 | 15.50 |
|   70 |  38185.80 |  636.43 | 10.61 |
|   80 |  19393.04 |  323.22 |  5.39 |
|   90 |      0.00 |    0.00 |  0.00 |
+------+-----------+---------+-------+
11 rows in set (0.00 sec)

 うーん、CTE便利! MySQL 8.0 最高! まぁこの程度なら、FROMのサブクエリに書いてもいいんですけどね。
少数以下が長くあっても仕方がないので、ここでは小数第2位(センチメートル)で丸めて出力しました。
赤道付近での1秒は30.92m、北緯35度では 25.36m だとわかります。これくらいの長さだと、そのへんの通り道を眺めながら「あそこまでで1秒かー」とイメージが湧きやすいですね。

緯度はどうなるか

 ここまでやったらならば、一方の緯度はどうなるか確認してみたくなります。
緯度は経度みたいには細くなっていかないので、一定のままだろう、と予想して、テーブルg3とそのデータを作って試します。

CREATE TABLE g3 (id integer, pos1 GEOMETRY SRID 6668, pos2 GEOMETRY SRID 6668);

INSERT INTO g3 VALUES (0,  ST_GeomFromText('POINT(0  135)', 6668), ST_GeomFromText('POINT(1  135)', 6668));
INSERT INTO g3 VALUES (10, ST_GeomFromText('POINT(10 135)', 6668), ST_GeomFromText('POINT(11 135)', 6668));
INSERT INTO g3 VALUES (20, ST_GeomFromText('POINT(20 135)', 6668), ST_GeomFromText('POINT(21 135)', 6668));
INSERT INTO g3 VALUES (30, ST_GeomFromText('POINT(30 135)', 6668), ST_GeomFromText('POINT(31 135)', 6668));
INSERT INTO g3 VALUES (35, ST_GeomFromText('POINT(35 135)', 6668), ST_GeomFromText('POINT(36 135)', 6668));
INSERT INTO g3 VALUES (40, ST_GeomFromText('POINT(40 135)', 6668), ST_GeomFromText('POINT(41 135)', 6668));
INSERT INTO g3 VALUES (50, ST_GeomFromText('POINT(50 135)', 6668), ST_GeomFromText('POINT(51 135)', 6668));
INSERT INTO g3 VALUES (60, ST_GeomFromText('POINT(60 135)', 6668), ST_GeomFromText('POINT(61 135)', 6668));
INSERT INTO g3 VALUES (70, ST_GeomFromText('POINT(70 135)', 6668), ST_GeomFromText('POINT(71 135)', 6668));
INSERT INTO g3 VALUES (80, ST_GeomFromText('POINT(80 135)', 6668), ST_GeomFromText('POINT(81 135)', 6668));
INSERT INTO g3 VALUES (90, ST_GeomFromText('POINT(89 135)', 6668), ST_GeomFromText('POINT(90 135)', 6668));
mysql> SELECT id, ST_Distance(pos1, pos2)  FROM g3 ORDER BY ID;
+------+-------------------------+
| id   | ST_Distance(pos1, pos2) |
+------+-------------------------+
|    0 |      110573.13812416371 |
|   10 |      110610.23595555034 |
|   20 |      110710.37167306663 |
|   30 |      110861.46743133431 |
|   35 |      110950.61420268985 |
|   40 |      111045.29885197058 |
|   50 |      111239.69315258414 |
|   60 |      111421.20351156592 |
|   70 |      111567.93710081652 |
|   80 |       111662.1956839133 |
|   90 |      111692.61028462648 |
+------+-------------------------+

 あれれ。みんなだいたい111kmではありますが(赤道付近の経度1度ともほぼ合致していますね)、よく見ると少しずつ値が異なりますね。そうか、地球(JGD2011でのモデルであるGRS80)は真球ではなく回転楕円体だから、緯度があがるごとに少しずつ値が変化するわけですね。確かにそうだ。

 いや、でもよく見てみると、、、、緯度があがるほうが1度あたりの距離が長くなっていますね。イメージを真球から極半径を共有した回転楕円体へと拡張していくと、赤道付近のほうがぐーっと伸びているのだから、そちらのほうが1度あたりの距離が(真球と比べて)より長くなるようなイメージを持っていたのですが・・・・・かるく調べてみると値が間違っているわけではないようですが、感覚的に納得できません。。ここまでで今日の私はギブアップ。
 誰か、「なぜ極付近のほうが緯度1度の距離が長くなるのか」、わかりやすくおしえてください!

おまけ

 つくば市国土地理院内にある「地図と測量の科学館」には、地球のミニチュアモデルがあります。曲面に乗っかった日本の形とか、なかなか面白いものですよ。そしてその周りには、「1秒の距離をあるいてみよう」という看板と標石が設置されていて、実際にその場所での1秒(南北30.8m、東西25mくらい)を歩いて体験できるしかけがあります。なかなか面白いですよ。

f:id:sakaik:20181220115619j:plain

MyNA(日本MySQLユーザ会) 望年LT大会2019@赤坂 開催しました

 今年も、赤坂のワインバーnomunoを貸切利用させていただいての、日本MySQLユーザ会(MyNA)望年LT大会 というイベントを開催しました。
一応、いわゆる忘年会的な位置づけではあるのですが、前後の各種イベントとの兼ね合いを考え、今年はちょっと早めのこの時期の開催となりました。

mysql.connpass.com


 昨年の反省を踏まえ、参加申込を開催直前まで受け付けるようにしたり、学生さんに対する割引を思い切った設定にしたり、幹事想定最低人数を公開するなど、細かい工夫をしてみました。そのおかげもあってか、早い時期にたくさんの申込をいただき、また、昨年ゼロだった学生さん枠で2人の方が参加してくださったり、イベントとして一歩前進した気がします。ありがとうございました。
 
 ひとつ心残りと言えば、私自身がLTをできなかったこと。事前の用意をする時間が全然なくて、もしLTやってくれる人が少なかったら見に行ったあちこちの三角点の話でもしようかと思っていたけど、濃厚なMySQL話をたっぷり聞かせて貰って、まぁいいかという気分になりました(笑)。ただ、後ほど耳に入った話だと、「LT用意はしていたんだけど・・・・」とか「やろうかなと思っていたけど手を挙げられなくて」といった声があったそうで、幹事の至らなさ故、申し訳ございません。 「次だれやるー?」と手を挙げてもらう方式は結構勇気いりますよね。来年開催の場合は、事前に募集(もちろん当日飛び入りも歓迎)という形にすると、覚悟も決まって良い気もしてきました。まだまだ工夫ポイントがある感じで、イベント開催って奥深く楽しいです。

 ともかく、参加してくださった皆様、LTをしてくれたみんな、どうもありがとうございました。とても疲れたけど幹事もとても楽しかったです!
それから、今年も昨年に引き続き、ノーショウ(無連絡欠席)がゼロでした! ノーノーショウ! 幹事自腹切らずにすみました。ありがとうございます!
 そして、最後になりましたが、今年も本イベントを経済的にサポートしてくださった、SCSK様どうもありがとうございました!!


scsk-db.jp



f:id:sakaik:20191118175841j:plain
f:id:sakaik:20191118210042j:plain
f:id:sakaik:20191118184212j:plain





参考:昨年の開催
sakaik.hateblo.jp