「標準的なバスフォーマット(GTFS-JP)」勉強会#1 参画

 「標準的なバスフォーマット/GTFS 勉強会」(https://gtfs-jp20190420study.peatix.com/)に参加してきました。GTFSは、バスなどの交通機関の停車場や時刻などの記述方法を定めたもので、このフォーマットで作成したファイルを Google駅すぱあと や 乗換案内 などの会社さんに提供することで活用されます。分かりやすいところでは、Googleの経路検索などでの活用で、例えば遅延情報や臨時便/迂回運行などの情報をGoogleにアップロードすることで、即座に検索結果に反映されるというフットワークの軽い情報提供が可能になります(事前の契約は必要)。
 バス事業者とCP(Contents provider)の間でクローズドにやりとりされるだけでなく、同じデータをオープンデータとして公開しているバス事業者さんも、まもなく100社にならんとする勢いで、岡山をはじめ全国に広がっているとの印象を受けています。

 「なんで私がバスデータの勉強会に!?」と自分でも不思議に思うのですが、話せば長く、書いても長いのですが、かいつまむと、MySQL 8.0 で位置情報について興味を持った→ジオ系(位置情報、地図、測量等)のイベントに参加してみた→バスロケのお話を聞いて面白そうだと思った→バスロケの勉強会に参加してみた(今年2月)→今回 という、面白い具合にご縁が繋がっての、今回の参加となった次第。


 前回参加したイベントは、どちらかというと実際にデータを提供するバス事業者さんが中心といった雰囲気のものでしたが、今回はIT系なトラックも設けるとのことで、色々聞かせてもらうのを楽しみに、参加を申込みました。
 ・・・・が、直前になって「LT枠に、まだアキがあるけど、何かやる?」とお誘いをいただき、この分野まったくの初心者であるにも関わらず、お話をさせていただきました。しかも、結構余裕がありそうだったので2枠やらせてもらうことになったり、その後結構埋まってきた様子だったり、しかも私の話、ぜんぜん5分に収まらなかったり、なんかすいません。(2本目、15分くらい喋ってましたよね(^^;))
 結構怖かったし、至らぬ部分も多くあった発表でしたが、聞いてくださった皆さんの反応も良く、色々教えていただいたりもして、とても良い経験をさせていただきました。ありがとうございました。私のトークについては、後述します。


 午後の実習コースでは、postgreSQLに入れたバスデータをQGISから参照して、あれこれ見栄えや表示条件を変更したりする方法を体験しました。この1年くらい、QGISを触りたいのにインストールすらしていない「やるやる詐欺」状態となっていましたが、こうして缶詰にされて、ようやく初インストール(笑)。数日前にメールで環境準備について連絡が来ていたのを私が見落としていて、あわててインストール(前夜にサイト上の案内は発見していたので、ソフトのダウンロードだけは済ませていたのは、我ながらナイスリカバリ)。
 今回触ったのは、おそらく機能の極極ほんの一部だと思いますが、点や線の集合であるDBデータを、手軽に視覚化できることに感心しました。これはすごい。
 人それぞれの環境の差や、ちょっとした操作ミスまたは抜けなどに起因すると思われるトラブルも結構多く、講師/運営 の皆さまとも大変そうでしたが、ひとつひとつフォローされていて、

 GTFS-JP v2 で加わった、バス運行リアルタイム情報(GTFS-RT)についても沢山の話を聞くことができ、いまひとつピンと来ていなかったGTFS-RTが少し身近になった気がしました。-RTは 独立したフォーマットだと思っていたのが私の勘違いで、GTFS-JP のデータがあってこその -RTである(具体的には、-RTからは -JPのほうで定義されているIDを参照するよとうこと)ことが、納得ポイントでした。

 そのまま会場で行われた懇親会でも、色々な方にバス運行を取り巻くお話から、データ整備の実情から、様々な武勇伝から、たくさんの話を聞かせていただいて、たいへん充実した一日でした。どうもありがとうございました!


 以下、私のトークについて:

「バス停の位置情報も格納できる!」MySQL 8.0 の紹介

www.slideshare.net

 GTFSには、バス停や経路をあらわすための位置情報(緯度、経度)が含まれています。しかも必須項目。こういった項目を RDBMSに格納するには、まぁ緯度と経度を別々のカラムにそれぞれ数字で持たせておいても良いのでしょうが、それだと活用がしにくい(距離計算とかたいへん)ので、普通は 「緯度+経度」として扱える専用の型を持ったカラムに格納したくなるものです。 こういった場合に、業界標準としては PostgreSQLPostGIS を追加して使用するのが一般的でした。 MySQLでは測地系を考慮した位置情報をこれまで扱えなかったからです。 しかし、2018年4月にリリースされた MySQL 8.0 では、ついに「ちゃんと丸い地球(つまりは測地系)に対応した」空間情報を扱えるようになりました。業界の方でもまだ知らないかたが多いので、その紹介と、今回はバリバリの業界人でない方も多いと伺っていたため、そもそもDBMSという役割を持ったソフトウェアがあるんですよ、という話も含めてみました。
 「MySQLで位置情報」という話に、ほぅ、と反応してくれる人がいて、「オモテからは見えないけど、データベースというソフトが裏側で頑張っているんですよ」の話に、へぇぇという顔でメモを取る方も多くいて、レベル設定としては良い感じにハマったのではないかな、と少し気分が良かったです。 面白かったのが、前者の話に反応する人は前のほうの席に、後者の話に反応したのが後ろ三分の一くらいに多かったのが印象的でした(笑)。
 まだまだ GIS 関係の、とくに集約や分析系の関数が不足しているMySQLですが、ぜひ今後の選択肢のひとつとしてお試しいただければと思います。

「なんじゃこりゃぁ」DB屋が見たGTFS-JPファーストインプレッション

www.slideshare.net
 GTFSのフォーマットを、DBのテーブルだと思って眺めた時に感じた違和感(カルチャーショック)を語らせていただきました。仕様書を眺めていて「なんじゃこりゃ」と思っていても、特に今まで誰と議論する機会もなく、そんな状態での発表でしたが、初々しく受け取っていただけたでしょうか。さすが何十ヶ月もこのフォーマットに触れてきた皆さん、私が感じた違和感は既にお考えでしたし、私が感じた問題は既に解決済でしたし、たいへん勉強になりました。
 これまで、なんとなく仕様書を眺めていて、なんとなく「へんなの」と思っていたものを、発表の機会をいただけたことで改めて真剣に読み込み、真剣に考えることができました。少しステップアップできた気がします。まだ理解不足なところが多いですが(料金関係は手つかず。TripとRouteの違いがよく分からない等)、少しずつ学んでいけたらと思います。




参考リンク:
www.mlit.go.jp


イベント中に紹介された書籍(同人誌):
 関係者の皆さんで作成し、先週の「技術書展」でも飛ぶように売れたという本。GTFSに興味を持ったなら必読!だと思う(たぶん)。私もこれから買うところなので(笑)。
booth.pm

f:id:sakaik:20190420094135j:plain

OSC award を受賞しました

これまでのOSC開催に多大な貢献をしたので、このたび「第6回 OSC アワード」を受賞いたしました。ありがとうございます。

www.ospn.jp

それはなに?

 日本各地で開催されている「オープンソースカンファレンス(OSC)」に【多大な貢献】をした人に贈られる賞です。

公式サイトの案内より:

オープンソースカンファレンスは2004年9月の開催以来、全国各地で開催を重ね、通算160回以上を開催して参りました。
そして、2014年2月の第100回開催を記念して、これまでのOSC開催に多大な貢献をされた方を表彰する「OSCアワード」を設立し、過去に5回の表彰をしています。

どんな貢献をしたの?

 困ったことに、「貢献をしたぞ」と思えるような自覚症状が、ありません。勝手に、各地で開催されるOSCへと参加していただけです。モットーは「自分が楽しむ」であり、先日、OSCの主催者から「坂井さんが各地のOSCに参加するモチベーションって、なに?」と聞かれたときにも、「おいしいもん食べたいしー」「いろんな人に会えるしー」挙げ句には「OSCどーでもよくて、その前後がたのしー」なんて言い出す始末です。 
 とは言え、数は力で、結果としてOSCの盛りあがりの一部を構成していた面はあったのかもしれませんので、今回のご評価、ありがたく頂戴することにいたしました。

受賞理由(公式)

◆受賞者:日本MySQLユーザ会 坂井 恵
受賞理由:日本MySQLユーザ会として、全国各地のOSCに出展し、MySQLの認知・普及に務めるだけでなく、他のプロダクトとの交流を積極的に行い、オープンソースコミュニティの活性化に寄与しているため。

 とのことです。日本MySQLユーザ会(MyNA)として各地のOSCでブースを出展し、セミナーを開催してきたことを中心に評価をいただきました。
嬉しかったのは、「だけでなく」以降の部分です。

 コミュニティ活動というのは、ついウチにウチにと閉じこもりがちな面があります。そのほうが活動を続けていくうちに「おうち感」もできて来て気楽に感じるからです。多くの話が「承前」で通じる。そんな空間はとても居心地が良いのですが、一方でそういったコミュニティは、コミュニティとして成長しないと感じています。特に、前提をまだ共有できていない新しい人への対応は面倒くさく感じるものになるでしょうから、そういった空気を感じ取って、新しい人は入ってこない状態となります。入りたくても、定着しない。DBコミュニティ全体として、ただでさえ新しい人が増えにくい傾向がある中で、そうならないためには、前提条件を共有できていない人たちとの交流を普段から行っていることも一助となると考えています。

 また、あるターゲットを理解しようと思ったら、そのターゲット以外のことを知ることがとても有効です。外の世界を知ることで、そのターゲットの優れている点、不足している点、異常/特徴のある点などが初めて分かります。MySQLを知りたければMySQL以外を知れ、です。MySQLを隠すならDBMSの中に隠せ、です(ちょっと違う)。

 そんな考えから、「中に閉じないコミュニティ」は常に意識し、大切にしていた部分ではあるので、その点をしっかり見ていただいて評価をいただけたことを、本当に嬉しく思いました。

いっぱい行ったって、いったいどれくらい行ったぃ?

 2004年の東京(大久保)で開催された第1回の「オープンソースカンファレンス(OSC)2004」から、160回近くのOSCが開催されてきました。その中で、自分が確実に参加したと判明しているものだけを数えたところ、60近くありました。これが多いのか少ないのかは判断つきませんが、感覚的には「比較的多めのグループに入るんじゃないかなぁ」という気はします。
 初めての遠征(東京以外の地域のOSCへの参加)は、2007年に福岡の第1回が開催された時でした。このときの懇親会やOSC本編の中で、沢山の地元の方と仲良くなったのは勿論ですが、予想外の効果として他の地域から来た人たちとも「遠くからわざわざ来た奇特な仲間」という意識なのか、親密さが増したことがありました。これに味をしめたのでしょうね。年々、行く先が増えていきました。 行った先で会った地元の方が、今度は別の地域のOSCに参加してくれるようになるシーンにも何度も出会い、嬉しかったものです。こんな「横の交流」に魅力を感じるところも、私らしいな、と、受賞理由をいただきながら思いました。

余談: SD誌 最多出場?

 ブラタモリのテーマ風に言えば「OSCの楽しみは ゼンゴ にあり!?」。
現地には前日入りすることが多いので、当然「前夜祭」があります。公にはない場合もありますが、その場合は遠方から参加する人どうしで連絡を取り合って、勝手に前夜祭をやります。これがまた楽しいんだ。わざわざ遠方から参加するような人たちですから、経験も話題もたくさん持っていて、色んな話を聞かせてもらえるし、地元のおいしいお酒も飲めるし、比較的少人数なので、ガッツリ交流もできます。 あまりに前夜祭が楽しいので、前夜祭終わりかけの頃になると「いや~今回も、いいOSCだった~!!」と口に出すのが恒例のようになってしまったくらいです。 (そして「まだ終わってない!っていうか始まってないから!」とツッコんでもらうまでがお約束。 でも最近は、ちょっと言い過ぎているかなと思うところもあり、少しだけ、自粛していますw)

 前夜祭や、当日懇親会後の二次会など、大概は、主催の宮原さん*1 とも一緒に行動することが多いのですが、宮原さんの選店眼にはいつもうならされます。私なら絶対に選べないようなお店をぴたっと見つけてくる。本当に素敵なお店ばかり。
 宮原さんは Software Design 誌に「オープンソース放浪記」という連載をしていて、そこでは、OSCやその他のイベントで訪問した各地の、観光や飲食を中心に紹介しています。呑んだお店での自撮り写真を掲載するので、一緒に行動している私も写真に写り込んだりすることが多くあり、宮原さんからも「SD誌最多登場」のお墨付きをいただいてしまいました(ちゃんと数えてはいませんが、確かにダントツで多い気はします(笑))。たいへん光栄なことです:-)
 

今後

 色々と少し増やしすぎたとの感もあり、少し活動の種類を落ち着かせる予定です。実は昨年末に周りの人たちにも「少し減らす予定」と伝えたところだったので、今回の受賞の連絡をいただいたときには「いままでご苦労さん、の、卒業記念の意味合いもあるのかな」などと頭をよぎったのですが、何のことはない「もっとやれ」でした(笑)。

 とは言え、時間の面、経済的な面での負担も感じるようになってきたことと、概ね代わり映えしない事も増えてきたので、活動のフレームを少し変えていこうと考えています。

 OSCについては、最近は「とにかく行く!」をデフォルトとして行動して来た面があるので、今後はもう少し目的意識を持っての参加をするつもりです。
2019年は以下の条件に合致する場合に参加を検討する感じにする予定です。

  • どうしても伝えたい内容がある場合(2018年の GIS入門的な)
  • どうしても会いたい人がいる場合(そのタイミング、その場所でないとならない場合)
  • 前後の日程で、お仕事に関係する話がその地域である場合(お引き合いお待ちしています)
  • 前後にとっても「寄り道」したい場所がある場合
  • 北海道


 OSC以外の活動については、MySQLユーザ会自体のイベントに充てられる時間をもう少し増やしたいなと考えています。私が魅力を感じているのは、交流を中心としたもので、つまり、参加者が一度も口を開かないで帰るようなものではなく、おしゃべりして帰ってもらえるようなものなので、そういうのをやりたいですね。昨年末に赤坂で開催したような「呑み交流会」も、またやりたいです(費用面でドキドキするのですが、前回はドタキャン/無断キャンが1人もなく、大変助かりました!ありがとうございます!今後の開催でも宜しくお願いします!)。
 あと、今回受賞理由にもなっている「横のつながり」は、やっぱり楽しいので、「RDBMS的な会食」も具体的に検討をはじめています。開催の折にはご参加/ご協力よろしくお願いします。

まとめ

 思うままに書き綴ったので、全然まとまっていませんが、根底にある思いを個条書きにすると:

  • 技術イベント(特にここではOSC)、楽しいよ。参加したことがない方はぜひ覗いてごらん
  • 既にイベントに参加している方は、他のコミュニティ、他の地域などを覗いてごらん
  • MySQLユーザの皆さんは、OSCブース番、やってみませんか。更にはMySQLユーザ会の枠でセミナー担当してみませんか(twitter @sakaik まで連絡ください。一応条件としては「MySQLをそこそこ好きであること」「(セミナーの場合は)MySQLの魅力を伝えたいと思っていること」くらいです)
  • 宮原さんいつもおいしい料理をありがとう!


 そんなわけで、ちょっと違うことをやりたいなと暖めている事もいくつかあり、今後とも引き続き、よろしくおつきあいの程をお願い申し上げます。
ありがとうございました。
f:id:sakaik:20190224145243j:plain

*1:そういや宮原さんを語るときの肩書きがいつも迷います。「主催者」「事務局トップ」「発起人」など時に応じて色々ありますが、まぁ親しみを込めて「ボス」といったあたりでしょうか

OSC2019-Tokyo/Spring 参画

オープンソースカンファレンス2019東京・春(OSC2019-Tokyo/Spring)に参加してきました。
www.ospn.jp

 OSC東京の会場は、千葉県に住む私にとっては非常に遠いので、参加には毎度なかなか後ろ向きなのですが、理由あって今回も参加することと相成りました。いつも通り、日本MySQLユーザ会(MyNA)としてブース出展とセミナーを1枠開催。

f:id:sakaik:20190223160143j:plain
写真はyokuさん



 セミナーでは、@yoku0825 さんと一緒に、みんなの前で「雑談」をしてきました。

オープンソースカンファレンス2019 Tokyo/Spring - イベント案内 | 2019-02-23 (土): MySQL 〜 yoku0825 と sakaik が語る最新情報と周辺情報〜

 参加者からのツッコミや情報提供などをいただきながら、台本なしのトークを繰り広げる趣向です。 yokuさんと一緒に好き勝手雑談をしたので、私自身は最高に楽しかったです。 話題の背景が分からずに置いていかれる人が多くならないよう、適宜補足説明を行う等の工夫はしたつもりですが、配慮の行き届かないところがあったら、すいません。
 楽しかったので、またやりたい。yokuさん、ありがとうございました!
 みんなにも、もっと良心を持ってほしい(内輪ネタw)。



 ブースのほうは、今回は「ユーザ会の中の人」がたくさん来てくれたので、私はほとんどブースに居ずに、他のブースをぶらぶら。久々の方から2週間ぶりの方まで、多くの人とお話できて、良かったです。随分とラクをさせてもらいました。いつもこうだと良いなぁ。

 その後は、私にしては珍しく閉会式まで参加してから、帰宅。
 



 

OSC2019-Hamanako参画

オープンソース浜名湖(OSC2019-Hamanako)に参画してきました。


f:id:sakaik:20190210112037j:plain

www.ospn.jp

 いつもどおり、日本MySQLユーザ会(MyNA)として、セミナーを1枠もらってのお話と、ブースの出展とで。
申し込みが遅かったので、セミナー枠は埋まっているかな、と思っていたところ、朝イチの枠が空いているとのことで僭越ながらオープニングの枠を務めさせて頂きました。

 OSC浜名湖セミナーは、ワントラックで、来場者全員が(同部屋に併設のブースを見ることもできますが)座ったままですべてのセミナーを聞けるスタイルです。なので、まぁMySQLに特に興味があるわけじゃない人も多いだろうな、と、「データベースってなに?」という層を今回はターゲットとしてみました。 まぁレベル設定はなかなか難しいですね。。分からなくてもいいから最先端の話を聞いてみたいという人も少なくないようで、まだまだ私にとっても、開催スタイルによる話題やレベル感の選定は悩みだらけです。

 浜松は、測量で使用する「三角点」という面からは、日本で2番目の基線が設定された場所でもあり、LTなどではそれをネタに2年連続、お話をさせていただきました。何人かの方には面白がっていただいたり、実際に三角点に興味を持って頂いたり、ささやかな広がりを感じることができました。

 お会いしたかった方とたくさんお話できたり、音楽関係のお話をいっぱい教えてもらえたり、充実のOSC2019-Hamanakoでした。
ランチは、いつもどおり、うなぎ。

f:id:sakaik:20190210124852j:plain
写真はうなぎ


LT発表資料:

www.slideshare.net

MySQLのSpatialでのPOLYGONの同一性

boiledorange73さんのこの記事を見て、むくむくと疑問が湧き上がったわけですよ。本題とは全然関係ないところで、単に「右回り左回り」というキーワードだけに反応して、妄想全開モードになっただけなのですが。
qiita.com

MySQLは 記述方法の違うポリゴンを同一と見なしてくれるのか

 点A、点B、点C、点D からなるポリゴンって、A-B-C-D-A とも、B-C-D-A-B とも、B-A-D-C-B ともあらわすことができますよね。感覚的にはこれらはすべて同じと見なして欲しいな、と思うのですが、実際にはどうなのだろう。というのが今回の疑問です。
 以降、だらだらと例示が続くので結論を先に書いちゃうと、ちゃんと同じと見なしてくれます。

同一性の確認方法

 MySQLには、ST_Equals() という同一性判定の関数があります。
POINT を例に見ると、以下のように、同一の点であれば「1」が、異なる点であれば「0」が返ってきます。

mysql> SELECT ST_Equals( ST_GeomFromText('POINT(1 1)'), 
    ->                   ST_GeomFromText('POINT(1 1)') ) res;
+------+
| res  |
+------+
|    1 |
+------+

mysql> SELECT ST_Equals( ST_GeomFromText('POINT(1 1)'), 
    ->                   ST_GeomFromText('POINT(1 2)') ) res;
+------+
| res  |
+------+
|    0 |
+------+

LINESTRINGの場合は

 同様に、LINESTRING の場合を見てみます。点Aから点BへのLINESTRINGは、A-B とも B-A とも表せますが、これらを同じ物と見なしてほしな、という実験です。

mysql> SELECT ST_Equals( ST_GeomFromText('LINESTRING(1 1, 1 2)'), 
    ->                   ST_GeomFromText('LINESTRING(1 1, 1 2)') ) res;
+------+
| res  |
+------+
|    1 |
+------+

mysql> SELECT ST_Equals( ST_GeomFromText('LINESTRING(1 1, 1 2)'), 
    ->                   ST_GeomFromText('LINESTRING(1 2, 1 1)') ) res;
+------+
| res  |
+------+
|    1 |
+------+

 はい。「1」が返ってきたので、MySQLはこれら2つの線を同じ物と認めてくれたことがわかりました。
なんかユルユルに、なんでも「1」を返してるんじゃないの?という疑問を消し去るために、明らかに違う2つの線で試してみます。

mysql> SELECT ST_Equals( ST_GeomFromText('LINESTRING(1 1, 1 2)'), 
    ->                   ST_GeomFromText('LINESTRING(1 1, 2 1)') ) res;
+------+
| res  |
+------+
|    0 |
+------+

 ええ、ちゃんと「違う線である」と判断してくれました。

いよいよ本丸のポリゴン

 ちょっとわかりにくいのですが、A(1,1)、B(1,2)、C(2,2)、D(2,1) としたときに、
最初のは、A-B-C-D-A どうしを比べたもの。
2番目のは、A-B-C-D-A と、B-C-D-A-B を比べたもの(記述上の起点が異なる)。
3番目のは、A-B-C-D-A と、D-C-B-A-D を比べたもの(記述上の起点が異なり更に逆回転)。
いずれも「1」(同一)と判定されました。

mysql> SELECT ST_Equals( ST_GeomFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))'), 
    ->                   ST_GeomFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))') ) res;
+------+
| res  |
+------+
|    1 |
+------+

mysql> SELECT ST_Equals( ST_GeomFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))'), 
    ->                   ST_GeomFromText('POLYGON((1 2, 2 2, 2 1, 1 1, 1 2))') ) res;
+------+
| res  |
+------+
|    1 |
+------+

mysql> SELECT ST_Equals( ST_GeomFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))'), 
    ->                   ST_GeomFromText('POLYGON((2 1, 2 2, 1 2, 1 1, 2 1))') ) res;
+------+
| res  |
+------+
|    1 |
+------+

まとめ

 非常にシンプルなサンプルですが、線の順序やポリゴンの回転方向などの記述方法の如何に依らず、MySQL の ST_Equals() は正しくその形の同一性を見て判断してくれることがわかりました。めでたしめでたし。

JGD2011からのST_Transform()、JGD2011へのST_Transform()

この日記は RDBMS-GIS Advent Calendar20日目の枠です。


 一昨日に書いた MySQL 8.0.13 の ST_Transform()を試す - sakaikの日々雑感~(T)編 に対する返歌を 有意識者の boiledorange73 さんにいただいた(JGD2011の座標系にtowgs84が無いとかそもそもtowgs84って何やねん - Qiita)ので、それに対する恋文返しです。実際には、boiledorange73 さんへのお返事になっているというよりも、boiledorange73 さんにいただいた恋文をこっそりアレンジして他の人に送りつけているような感じと言ったほうが近いかもしれません。

TOWGS84 の 7つの数字の意味

 前の日記で、

(7個ある数字の意味については今後調べていきたいと思います)

 と書きましたが、 boiledorange73 さんが解説してくださいました。ありがとうございます。

 towgs84 = [t_x, t_y, t_z, d, r_x, r_y, r_z]

 で、tが各方向への並行移動量、d が縮尺に関する値、rが回転に関する値であるということです。

SRID:4301(Tokyo)で 、 TOWGS84[-147,506,687,0,0,0,0] と定義されていたのは、最初の3つのみに値が指定されていて残りの4つはゼロであることから、回転や縮尺の変更は伴わずに単純な並行移動のみで変換ができるということをあらわしています。

x,y,z 方向への平行移動の値は、こんなものか?の確認

 この定義によると、x, y, z 方向への並行移動はそれぞれ、-147, 506, 687 とされています。同じ緯度経度の数字とした場合、その指し示す位置は、Tokyo測地系からJGD2000 に向かっておおよそ東南東の方向に約600mほどズレることは、既に知識として持っているので*1、感覚的にこれに合致するかを確認することにします。
 x, y, z と示されていますが、x=-147, y=687 であることから、xが南北方向(北方向をプラス)、yが東西方向(東方向をプラス) に関わる値であると推測できます。
 では、z の 687 というのはどうでしょうか。これも、Tokyo測地系で使用しているベッセル楕円体と、JGD2000が採用しているGRS80 とを比べることで分かります。両者の極半径の差は 674m、赤道半径の差は 740m ですので、まぁなんとなく、論外な値でもなさそうだなと見えます。それっぽいので、終了としたいと思います。(緯度経度の話の中には z軸は出てこない)

行列はキライ

 私は行列は嫌いです。ラーメンは大好きですが、行列してまで食べません。
ということで参照先の紹介だけ。
boiledorange73さんの式の中で出てきた 回転の計算式の R_x, R_y, R_z はそれぞれ、x, y, z方向に関係する 3x3 の行列です。これについては、boiledorange73さんも紹介していた書籍
世界測地系と座標変換
の第2章に詳しく出ているので、興味のある方は参考になさってください。この本、非常に軽妙に、知りたい情報を書いてくれているのですが、私のようなこの分野の素人にとっては、もう少し丁寧に説明して欲しいところもあるというのが率直な感想ですが、3ヶ月前に意味もわからなかった部分が後に少し理解が進んでいることが把握できるなど、自分の成長を感じられる部分もあるのが面白いです。

拡大縮小のパラメタは?

 boiledorange73さんの示してくれた変換式をよく見ると、拡大縮小のパラメタ D を用いる項が目に付きます。「倍率」と示されていますが、倍率であるなら、変化なしの場合に 1 であるべきで、今回のような 0 という設定値には違和感があります。調べてみました。
 正確には、このパラメタ D は、「スケールファクタ(スケール補正量)」と言って、補正なしならゼロ、補正ありなら プラスいくつ、またはマイナスいくつ という値を指定するようです。なお、値の単位は  10^{-8} です。
 

結局 JGD2011 からの/への 座標変換はどうしたらいいの

 JGD2011 と JGD2000 は、地理座標系に於いては同一(=7つの変換パラメタがすべてゼロ)と考えて良いので、boiledorange73さんのエントリでも最後にちらっと書かれていたように、JGD2000を経由して(JGD2011なんだけどJGD2000の値だと見なして)変換処理を行っても問題なさそうです。

一昨日の私のエントリのデータを使って試してみます。

mysql> select ST_AsText(g), ST_SRID(g), ST_AsText( ST_Transform(g, 4301) ) FROM g1  WHERE ST_SRID(g)=6668;
ERROR 3743 (22S00): Transformation from SRID 6668 is not supported. The spatial reference system has no TOWGS84 clause.

 この行のgの値は (towgs84がない)SRID:6668(JGD2011)であるためにエラーになったので、 (変換ではなくそのままの値でSRIDだけ変更する関数である)ST_SRID() を g に噛ますことで、JGD2000ということにして、変換を実施できます。

mysql> select ST_AsText(g), ST_SRID(g), ST_AsText( ST_Transform( ST_SRID(g, 4612) , 4301) ) FROM g1  WHERE ST_SRID(g)=6668;
+---------------+------------+----------------------------------------------------+
| ST_AsText(g)  | ST_SRID(g) | ST_AsText( ST_Transform( ST_SRID(g,4612) , 4301) ) |
+---------------+------------+----------------------------------------------------+
| POINT(35 135) |       6668 | POINT(34.996751668769726 135.00278101474854)       |
+---------------+------------+----------------------------------------------------+


 逆に、Tokyo測地系で与えられた緯度経度を、JGD2011 へと変換したい場合の例は、以下。そのまま 4301(Tokyo)を 6668(JGD2011)へ変換するとエラーになるので、一旦 4612(JGD2000)へと変換し、結果値を 6668(JGD2011)と見なす指示を行うことで、TokyoからJGD2011への変換を実現可能です。

mysql> select ST_AsText(g), ST_SRID(g), ST_AsText( ST_Transform(g, 6668) ) FROM g1  WHERE ST_SRID(g) = 4301;
ERROR 3744 (22S00): Transformation to SRID 6668 is not supported. The spatial reference system has no TOWGS84 clause.

mysql> select ST_AsText(g), ST_SRID(g), ST_AsText(ST_SRID( ST_Transform(g, 4612), 6668) ) FROM g1  WHERE ST_SRID(g) = 4301;
+---------------+------------+---------------------------------------------------+
| ST_AsText(g)  | ST_SRID(g) | ST_AsText(ST_SRID( ST_Transform(g, 4612), 6668) ) |
+---------------+------------+---------------------------------------------------+
| POINT(35 135) |       4301 | POINT(35.003247866817325 134.99721914450956)      |
+---------------+------------+---------------------------------------------------+

 

ST_SRID() 関数って?

 ST_SRID() の2種類の使い方を、何の説明のなく出したので、混乱した人もいるかもしれません。
ST_SRID() 関数は、引数がひとつ(GEOMETRY型)の場合には、それがあらわされているSRIDの数字を返し、第2引数に数字(SRID)を与えた時には、そのSRIDへと変換した GEOMETRY を返す、という挙動をします。
 単に情報を取得したい場合と、変換したい場合とで同じ関数を使うので、慣れるまではちょっとややこしいと私は感じました。

MySQL :: MySQL 8.0 Reference Manual :: 12.16.7.1 General Geometry Property Functions

ということで

 アドヴェントカレンダー用エントリにする程でもなかったかもしれませんが、本日が終わりそうなタイミング(というかこれを書いているまさに今、本日が終わって新しい本日になってしまった)で20日目の枠が空いたままだったので、アドヴェント参加作品(笑)とさせていただきました。
 
f:id:sakaik:20181220121034j:plain
写真は地球。奥のほうにあるのが月。右奥は、日本近郊を切り出した曲面。日本列島が球面の上に乗っていることを体感できます@地図と測量の科学館@つくば

*1:追記:600mではなくて400mでした。指摘くださった ozo360さん、ありがとうございます。600mも差が出る理由はモデルとしている楕円体の違い(ベッセル楕円体とGRS80)で変換するからかなと想像しましたが、今の私にはまだ理解できず、そのうち出直してきます

MySQL 8.0.13 の ST_Transform()を試す

この日記は RDBMS-GIS Advent Calendar の 18日目の枠です。

MySQLの残念な ST_Transform()

 ST_Transform() という関数があります。測地系を変換できる機能です。Tokyo測地系(別名日本測地系)で記述されている緯度経度を、JGD2000/2011 に変換したり、あるいは地理座標系の JGD2011の緯度経度を JGD2011の平面直角座標系の9系の座標*1に変換できたりするもの、だと思います。
 思います、と書いたのは、MySQLでは後者について、バージョン 8.0.13 の時点ではまだ対応してないからです。そもそも、この ST_Transform()関数、8.0.13が出る前から MySQLのリファレンスマニュアルには記載されていて、でも実際には実装すらされていなかったので私は「蕎麦屋の出前詐欺*2」と呼んでいたものです。このたび限定的な機能ながら、 8.0.13 で実装されましたが、地理座標系どうしの変換の一部にのみ対応しているのが、現在の状況です。

 本エントリでは、MySQL 8.0.13 で、ST_Transform() 関数を試してみた結果を紹介します。

データの用意

 様々な地理座標系であらわされた「北緯35度、東経135度」のデータを登録しておきます。

CREATE TABLE g1 (g GEOMETRY);
INSERT INTO g1 VALUES ( ST_GeomFromText('POINT(35 135)', 6668));
INSERT INTO g1 VALUES ( ST_GeomFromText('POINT(35 135)', 4612));
INSERT INTO g1 VALUES ( ST_GeomFromText('POINT(35 135)', 4326));
INSERT INTO g1 VALUES ( ST_GeomFromText('POINT(35 135)', 4301));

mysql> SELECT ST_AsText(g), ST_SRID(g) FROM g1;
+---------------+------------+
| ST_AsText(g)  | ST_SRID(g) |
+---------------+------------+
| POINT(35 135) |       6668 | ← JGD2011
| POINT(35 135) |       4612 | ← JGD2000
| POINT(35 135) |       4326 | ← WGS84
| POINT(35 135) |       4301 | ← Tokyo
+---------------+------------+

 いうまでもないことですが、これらの「35度135度」の点は、兵庫県内の、それぞれ異なる場所を指し示しています(実際には Tokyo 測地系のみが大きく離れていて、あとの3つはほぼ同じ場所を指している)

とりあえず実行してみる

 登録した4件のデータに対して、ST_Transform() 関数を噛ませてみましょう。
まず、SRID:6668(JGD2011)への変換を試みます。

mysql> SELECT ST_AsText(  ST_Transform(g, 6668)  ), ST_SRID(g) FROM g1;
ERROR 3744 (22S00): Transformation to SRID 6668 is not supported. The spatial reference system has no TOWGS84 clause.

 あれれ。「6668への変換はサポートされてないよ」だそうです。
うむ。では、SRID:4326(WGS84)への変換を試みてみましょう。

mysql> SELECT ST_AsText(  ST_Transform(g, 4326)  ), ST_SRID(g) FROM g1;
ERROR 3743 (22S00): Transformation from SRID 6668 is not supported. The spatial reference system has no TOWGS84 clause.

 少しエラーコードとエラーメッセージの内容が変わりました。「6668からの変換はサポートしてないよ」だそうです。

ということで、6668はダメらしいと分かったので、6668以外のものを 4326への変換を試みることにします。残念な6668。

mysql> SELECT ST_AsText(  ST_Transform(g, 4326)  ), ST_SRID(g) org_srid FROM g1 WHERE ST_SRID(g)<>6668;
+---------------------------------------------+----------+
| ST_AsText( ST_Transform(g, 4326) )          | org_srid |
+---------------------------------------------+----------+
| POINT(35 135)                               |     4612 |
| POINT(35 135)                               |     4326 |
| POINT(35.00324786593045 134.99721914450956) |     4301 |
+---------------------------------------------+----------+

 無事実行できて、結果が返ってきました。JGD2000からWGS84への変換は値に変化がありませんが、TokyoからWGS84への変換は、何やら変化があった感じです。この結果は、『Tokyo測地系が「東経135度」と思っている場所は、WGS84に言わせると「東経134.997度」』ということを表しています。(経度だけに着目して語ると)Tokyo測地系の東経135度は明石市役所付近を通り、WGS84やJGD20xxの135度はその東側約400m(天文台の130mくらい西)を通っているという知識を適用すると、これは感覚的には納得できる結果です。

 特に意味はないけど、調子に乗って SRID:4269 にも変換してみちゃう。

mysql> SELECT ST_AsText(  ST_Transform(g, 4269)  ), ST_SRID(g) org_srid FROM g1 WHERE ST_SRID(g)<>6668;
+---------------------------------------------+----------+
| ST_AsText( ST_Transform(g, 4269) )          | org_srid |
+---------------------------------------------+----------+
| POINT(35.00000738369947 135.00001549175067) |     4612 |
| POINT(35.00000738458632 135.00001549175067) |     4326 |
| POINT(35.00325525051159 134.99723463674064) |     4301 |
+---------------------------------------------+----------+

 SRID:4269 というのは、NAD83 です。

mysql> SELECT SRS_NAME, SRS_ID FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID=4269;
+----------+--------+
| SRS_NAME | SRS_ID |
+----------+--------+
| NAD83    |   4269 |
+----------+--------+

なぜ JGD2011 は変換できないのか

 我々がよく使うことになりそうな JGD2011 が ST_Transform() に対応していないというのは、どういうことでしょうか。先ほどのエラーメッセージをよく見ると、「The spatial reference system has no TOWGS84 clause.」と言っています。どうやら、JGD2011には "TOWGS84" 句が記述されていないようです。
 JGD2011の definition を見てみましょう*3(見やすいように整形済)

GEOGCS[
  "JGD2011",
   DATUM["Japanese Geodetic Datum 2011",
         SPHEROID["GRS 1980", 
                  6378137,
                  298.257222101,
                  AUTHORITY["EPSG","7019"]],
         AUTHORITY["EPSG","1128"]],
   PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
   UNIT["degree",0.017453292519943278, AUTHORITY["EPSG","9122"]],
   AXIS["Lat",NORTH],
   AXIS["Lon",EAST],
   AUTHORITY["EPSG","6668"]]

 確かに、TOWGS84 なんて記述はない。。
JGD2000を見てみると、

GEOGCS[
  "JGD2000",
  DATUM["Japanese Geodetic Datum 2000",
        SPHEROID["GRS 1980",
                 6378137,
                 298.257222101,
                 AUTHORITY["EPSG","7019"]],
        TOWGS84[0,0,0,0,0,0,0],
        AUTHORITY["EPSG","6612"]],
  PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
  UNIT["degree",0.017453292519943278, AUTHORITY["EPSG","9122"]],
  AXIS["Lat",NORTH],
  AXIS["Lon",EAST],
  AUTHORITY["EPSG","4612"]]

 ある!!ある!!! TOWGS84 が!!!
 内容はオールゼロですが、これが、先ほどの WGS84 への変換実験で、緯度経度に変化がなかった理由ですね。

ちなみに、SRID:4301(Tokyo)では、

TOWGS84[-147,506,687,0,0,0,0]

となっており、TokyoからWGS84への変換では、結構変化しそうな感じが伝わってきます(7個ある数字の意味については今後調べていきたいと思います)。

なぜ JGD2011には TOWGS84 の記述がないのか

 知りません。わかりません。
ST_SPATIAL_REFERENCE_SYSTEMS の値が、どこか別のところで定められた定義をそのまま持ってきているのであれば、そのおおもとがどうなっているかを辿っていく必要がありそうですし、MySQL独自に定義しているものであれば、JGD2011に TOWGS84を(オールゼロで)記述してもらうようリクエストを出せばよさそうです。
 有識者からの情報に期待したいと思います :-)

TOWGS84 が記述されている座標系

mysql> SELECT SRS_NAME, SRS_ID FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE definition LIKE 'GEOGCS%' AND definition LIKE '%TOWGS84%' ORDER BY SRS_NAME;
+-------------------------------+--------+
| SRS_NAME                      | SRS_ID |
+-------------------------------+--------+
| Abidjan 1987                  |   4143 |
| Accra                         |   4168 |
:
| Yoff                          |   4310 |
| Zanderij                      |   4311 |
+-------------------------------+--------+
323 rows in set (0.09 sec)

 地理座標系(GEOGCS)は、MySQL 8.0.13 では、479 個が定義されているので、 323/479 に TOWGS84 が記述されているということになります。

今後の期待

 JGD2011にも TO_WGS84 が記述されて、地理座標系どうしの変換仲間にいれてもらうことも勿論ですが、それ以上に 地理座標系と平面直角座標系との変換に期待したいところです。最近のMySQLのリリーススパンは結構安定してきて、その流れに従えば 次回は 2019年1月後半に MySQL 8.0.14 が出ます。ST_Transform() がどの程度進化するのか、どんなGIS関数が追加されるのか、楽しみです。機能が非常に限定された ST_Transform()関数。いま理解しておくと、MySQLの進化とともにあなたも成長します。たぶん。 いま始めれば、あなたも ST_Transform()関数のプロフェッショナル(笑)。ぜひお試しください。


f:id:sakaik:20181218000408p:plain
※写真は「さまざまな135度」

*1:ちなみに緯度経度ではなくメートルで表します

*2:「いまでました!」

*3:select definition FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID=4612; (または6668)で取得できます