訳あって、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だけ読み替えることをここでは「変更」と呼んでいます
