「緯度」「経度」をMySQLに数値で登録しておきたいときの対処法

第12回の今回は、「緯度」「経度」の数値をMySQLに登録する方法について解説します。

6:30

はじめに

これまで、MySQLで点、線、面の情報を扱う方法を解説してきました。地理情報専用のデータ型であるジオメトリー型に登録する際に、WKTでPOINT、LINESTRING、POLYGONのキーワードを使うのでしたね。

点のデータは専用型に入れる必要がある?

線や面のデータを地理情報専用の型を使わないで格納するのはちょっと大変そうですが、点のデータについては、わざわざ専用のPOINT型を使わなくても、緯度と経度の値をそれぞれそのまま数値として格納すれば良いのではないか、という発想もあると思います。個人的には特に理由がない限り、専用の型を使っておけば良いと考えています。その理由として、

明らかにおかしな値を登録した場合(緯度で90度、経度で180度を超えるような値)にエラーで弾いてくれる
測地系の情報を一緒に管理できる
2点間の距離やポリゴン等との包含関係の算出などで、地理情報型になっていればそのまま演算に使える

といったものがあります。

一方で、元のCSVデータなどで緯度と経度の数値が入っていてそのまま登録したかったり、上で挙げたような利点は特に活用する必要がないといった場合など、緯度カラムと経度カラムで値を管理したいことも理解できます。今回はそのようなデータの扱い方について紹介します。

データの用意

緯度と経度をそれぞれDOUBLE型の数値として持つテーブルを作成します。今回のテーブルではGEOMETRY型は使用しません。「lat」が緯度(「latitude」の略)、「lon」が経度(「longitude」の略)です。

CREATE TABLE g12(id integer, name varchar(30), lat double, lon double);

2つほど点の位置情報を登録してみましょう。サンプルデータは北緯35度ちょうど東経135度ちょうどにある「日本のへそ」という場所と、北緯35.694572度、東経139.760397度にあるインプレスの場所です。

INSERT INTO g12 VALUES (1, '日本のへそ', 35, 135);INSERT INTO g12 VALUES (2, 'インプレス', 35.694572, 139.760397);

登録された情報を確認すると以下のようになります。文字列や数値からなる、よく見るテーブルデータですね。

mysql> SELECT * FROM g12;+------+-----------------+-----------+------------+| id   | name            | lat       | lon        |+------+-----------------+-----------+------------+|    1 | 日本のへそ      |        35 |        135 ||    2 | インプレス      | 35.694572 | 139.760397 |+------+-----------------+-----------+------------+2 rows in set (0.000 sec)

地理情報型への変換

これらのデータを地理情報の関数で使用したい場合は、地理情報型に変換する必要があります。基本的には前回までに紹介した「ST_GeomFromText()関数にWKTを与える」という考え方です。WKTの部分をlat列、lon列の値から作り込むことになります。

mysql> SELECT id, name, lat, lon, ST_GeomFromText(CONCAT('POINT(',lat,' ',lon,')'),6668) pos FROM g12;+------+-----------------+-----------+------------+------------------------------------------------------+| id   | name            | lat       | lon        | pos                                                  |+------+-----------------+-----------+------------+------------------------------------------------------+|    1 | 日本のへそ      |        35 |        135 | 0x0C1A000001010000000000000000E060400000000000804140 ||    2 | インプレス      | 35.694572 | 139.760397 | 0x0C1A0000010100000040DF162C55786140D15B3CBCE7D84140 |+------+-----------------+-----------+------------+------------------------------------------------------+2 rows in set (0.000 sec)

今回の例では、それぞれの点データに対するデータベースはSRIDの情報を持っていないので、SQL文を書く人が「知っている」ことが前提となる点に気をつけてください(これが私が地理情報専用型の使用を勧める最大の理由です)。より安全で確実なデータ管理の観点からはlat、lonとは別にsrid列を作って全ての列にSRIDの値を登録するのもありかもしれませんし、このシステム全体で使うSRIDの情報を管理するためだけのテーブルを1つ作るのも方法かもしれません。

例で示したSQLは複雑に見えますが、ST_GeomFromText()関数の引数としてCONCATで作ったWKT(POINT)文字列とSRIDの数値を渡しているだけであることが分かると思います。

POINT関数を使う場合は

MySQLのマニュアルを見た人の中には、POINT()という関数を目ざとく見つけた人がいるかもしれません。今回の格納データで使用できるのではないでしょうか。見てみましょう。

mysql> SELECT id, name, lat, lon, POINT(lat,lon) pos FROM g12;+------+-----------------+-----------+------------+------------------------------------------------------+| id   | name            | lat       | lon        | pos                                                  |+------+-----------------+-----------+------------+------------------------------------------------------+|    1 | 日本のへそ      |        35 |        135 | 0x00000000010100000000000000008041400000000000E06040 ||    2 | インプレス      | 35.694572 | 139.760397 | 0x000000000101000000D15B3CBCE7D8414040DF162C55786140 |+------+-----------------+-----------+------------+------------------------------------------------------+2 rows in set (0.000 sec)

先ほどのCONCATを使った例に比べ、SQLは非常にシンプルになっています。しかし、登録されたデータをよく見比べてみると、先ほどとは異なっていることに気がつきます。主な相違点としては

SRID部分(最初の4バイト)がゼロになっている
緯度と経度が逆の順で登録されている

という点が挙げられます。測地系の情報というのは「これはJGD2011(SRID:6668)で表された座標です」といったことを表すものですから、その値がゼロであることは単なる平面座標(デカルト座標)上での座標を示したことになり、正しい地球上での位置を指していません。これでは使えません。

そこでSRIDをセットする関数の登場です。「ST_SRID」という関数を使います。

SELECT id, name, lat, lon, ST_SRID(POINT(lon,lat),6668) pos FROM g12;mysql> SELECT id, name, lat, lon, ST_SRID(POINT(lon,lat),6668) pos FROM g12;+------+-----------------+-----------+------------+------------------------------------------------------+| id   | name            | lat       | lon        | pos                                                  |+------+-----------------+-----------+------------+------------------------------------------------------+|    1 | 日本のへそ      |        35 |        135 | 0x0C1A000001010000000000000000E060400000000000804140 ||    2 | インプレス      | 35.694572 | 139.760397 | 0x0C1A0000010100000040DF162C55786140D15B3CBCE7D84140 |+------+-----------------+-----------+------------+------------------------------------------------------+2 rows in set (0.000 sec)

先ほどのPOINT()関数の結果にST_SRID()関数で測地系を与えることで、正しくJGD2011(SRID:6668)のデータになったことが確認できました(ST_GeomFromText()のときの結果と見比べてみてください)。

このように、POINT関数とST_SRID関数を組み合わせることでST_GeomFromText関数のときと同じようなことができます。しかし、例えばPOINT関数だけを使用してSRIDの設定を忘れてしまう「うっかり事故」を起こしてしまったり、緯度と経度の指定方法が他と逆順だったりなど、事故につながる可能性が大きいため、地理情報を扱う際には基本的にST_が先頭に付く関数を使用することを心がけるよう、お勧めします。

【コラム】大津駅の北緯35度モニュメント

本文で取り上げた「日本のへそ」(北緯35度・東経135度)にあるモニュメントは第5回で紹介しましたが、これ以外にも緯度や経度のキリ番のモニュメントは様々な場所にあります。今回紹介するのは、滋賀県大津駅のホームにある北緯35度線を示すモニュメントです。