第5回　位置情報を保存しよう（前編）

今回から2回に分けて、位置情報をDatastoreに格納する方法をいくつか紹介します[1]⁠。

数値型で保存する

緯度経度の情報をデータベースへ格納するときに、もっとも簡単な方法が数値型として保存する方法です。緯度経度がとりうる値の範囲は、以下の通りですので、システムに必要な小数点以下の数字を考慮して型を決めましょう。

緯度	-90～90
経度	-180～180

はてなフォトライフでは、写真に緯度経度のメタ情報を設定することができますが、高精度な緯度経度情報は必要ないので、型を以下のように指定しています。

latitude     decimal(7,4)
longitude  decimal(7,4)

decimal(7,4)という指定は、10進数で7桁のデータで、小数点以下は4桁まで格納するというものです。

あるオブジェクトの緯度経度を保存し、表示するだけならこれだけで十分ですが、位置情報を中心に扱うサービスになると、格納したデータを緯度経度を指定して検索する必要が出てきます。

Geometry型で保存する

続いて、平面空間データ型であるGeometry型で保存する方法を紹介します。

Geometry型とは、ちょっと耳慣れませんが、データベースに位置情報を特殊な形式で保存することで、2点間の距離を求めたり、検索の条件に位置を指定して、その位置から距離が近い順のデータを取得したり、地図上の2点が示す矩形エリアの中のデータを取得したりと、数値型に比べて複雑な処理がデータベース側でできます。

例えば、spot_idと、緯度経度を保存するテーブルは以下のように定義できます。

CREATE TABLE spot (
 spot_id INT NOT NULL,
 latlon GEOMETRY NOT NULL,
 PRIMARY KEY (spot_id),
 SPATIAL KEY (latlon)
) Engine=InnoDB;

CREATE文の最後にEngine=MyISAMと指定されているのは、このGeometry型に定義されているSPATIAL型のINDEXがMyISAMでしか利用できないからです[2]⁠。

SPATIAL INDEXは空間データのIndexで、先に例で示した、ある地点の近くのデータを取得するというSQLを書くことができます。

Geometry型へのデータの挿入

Geometry型のカラムにデータを挿入する場合、以下のようなSQLを発行します。

INSERT INTO spot (spot_id , latlon ) VALUES (1, GeomFromText('POINT(137.10 35.20)'));

GeomFromTextは、MySQLに定義されている関数で、文字列表現からGeometry型のデータを生成します。POINT(経度緯度)と、経度の方が先に来ていることに注意しましょう。また、経度と緯度の間は,(カンマ)ではなくスペースであることにも注意してください。

Geometry型のデータの取得

上記SQL文を実行してデータを作成したあと、テーブルをselectしてみましょう。

> select * from spot2;
+---------+---------------------------+
| spot_id | latlon                    |
+---------+---------------------------+
|       1 |        33333#a@??????A@ |
+---------+---------------------------+
1 row in set (0.00 sec)

latlon型のところが文字化して表示されました。Geometry型から、必要な情報を取り出すときは、別の関数を使う必要があります。

select spot_id, X(latlon), Y(latlon), ASTEXT(latlon) from spot;
+---------+-----------+-----------+-------------------+
| spot_id | X(latlon) | Y(latlon) | ASTEXT(latlon)    |
+---------+-----------+-----------+-------------------+
|       1 |     137.1 |      35.2 | POINT(137.1 35.2) |
+---------+-----------+-----------+-------------------+
1 row in set (0.00 sec)

X、Y、ASTEXTがそれぞれ関数になっており、Xは経度を、Yは緯度を、ASTEXTは文字列表現を返します。

矩形エリアのスポットを取得する

それでは、(35.00 , 135.00) と (36.00 , 138.00)の2点で表現される矩形エリアの中にあるスポットを取得してみましょう。

> select spot_id, ASTEXT(latlon) from spot where MBRContains(GeomFromText('LINESTRING(138.00 36.00, 135.00 35.00)'), latlon);
+---------+-------------------+
| spot_id | ASTEXT(latlon)    |
+---------+-------------------+
|       1 | POINT(137.1 35.2) |
+---------+-------------------+
1 row in set (0.00 sec)

where句のところに、なにやら複雑な式が出てきましたが、内容をひとつずつ解析していきましょう。

LINESTRING(138.00 36.00, 135.00 35.00): これは、「⁠(35.00 , 135.00) と (36.00 , 138.00)の2点で表現される直線」の文字列表現にになります。先程のPOINT(経度緯度)同様、矩形のエリアは、LINESTRING(地点Aの経度地点Aの緯度, 地点Bの経度地点Bの緯度)という文字列で表現されます。
GeomFromText(A): LINESTRINGの文字列表現を、実際のGeometry型に変換しています。
MBRContains(A B): この関数は、Aの最小外接矩形に、Bの最小外接矩形が含まれているかどうかを判定します。先のSQLではAの部分にLINESTRINGが、Bの部分にはgeometry型のlatlonカラムの値が入るので、Aで指定された直線が最小外接する矩形エリア内に入っているスポットで検索していることになります。

このように、矩形エリアを示す4点ではなく、2点を与えることで、矩形エリア内に存在するデータを検索することができます。

Geometry型にPOINT以外のデータを格納する

カンが良い方なら気づかれたかもしれませんが、Geometry型には、POINT形式だけでなく、LINESTRING形式や、多角形のエリアを表現するPOLYGON形式のデータも格納することができます。以下のようなgeoテーブルを定義してみましょう。

CREATE TABLE geo (
 geo_id INT NOT NULL,
 name VARCHAR(255) NOT NULL,
 geo GEOMETRY NOT NULL,
 PRIMARY KEY (geo_id),
 SPATIAL KEY (geo)
)ENGINE=MyISAM;

ここに、以下のようなSQLを発行します。

INSERT INTO geo (geo_id , name, geo) values (1, 'はてな京都本社', geomFromText('POINT(135.761919 35.011141)'));
INSERT INTO geo (geo_id , name , geo) values (2, '地下鉄烏丸御池', geomFromText('POINT(135.759666 35.010745)'));
INSERT INTO geo (geo_id , name , geo) values (3, '東洞院通り上る', geomFromText('LINESTRING(135.761050 35.012165,135.761050 35.011040)'));
INSERT INTO geo (geo_id , name , geo) values (4, 'ハートンホテル', polygonFromText('POLYGON((135.760497 35.012033,135.760497 35.011655, 135.760970 35.011655, 135.760970 35.012033,135.760497 35.012033))'));

これで4つのGeoデータが生成されました。1つ目と2つ目は、緯度経度のみのPOINTデータ、3つ目は2点を指定したLINESTRINGデータ、4つ目は4点からなるPOLYGONデータになります。

それぞれ地図上に記述すると以下のようになります。

これらのGeoデータを対象に、地点A(35.011769,135.760009)と地点B(35.010851, 135.762439)に最小外接する矩形エリアに存在するものを検索してみます。

> select geo_id, name, ASTEXT(geo) from geo where MBRContains(GeomFromText('LineString(135.760009 35.011769, 135.762439 35.010851)'), geo);
+--------+-----------------------+-----------------------------+
| geo_id | name                  | ASTEXT(geo)                 |
+--------+-----------------------+-----------------------------+
|      1 | はてな京都本社 | POINT(135.761919 35.011141) |
+--------+-----------------------+-----------------------------+
1 row in set (0.01 sec)

結果は、geo_idが1の「はてな京都本社」のみが得られました。この検索を先程の地図上に描いてみると、以下のようになります。

紫の部分に最小外接するものが対象になるため、「⁠ハートンホテル」や「東洞院通り上る」といったGeoデータは、紫のエリアに交わってはいますが、完全に含まれているわけではないので、検索結果から外れています。

完全に含まれていなくても、交わっていれば良いという場合は、MBRContains関数の代わりに、MBRIntersects関数を利用します。

> select geo_id, name,  ASTEXT(geo) from geo where MBRIntersects(GeomFromText('LineString(135.760009 35.011769, 135.762439 35.010851)'), geo);
+--------+-----------------------+-------------------------------------------------------------------------------------------------------------------+
| geo_id | name                  | ASTEXT(geo)                                                                                                       |
+--------+-----------------------+-------------------------------------------------------------------------------------------------------------------+
|      1 | はてな京都本社 | POINT(135.761919 35.011141)                                                                                       |
|      3 | 東洞院通り上る | LINESTRING(135.76105 35.012165,135.76105 35.01104)                                                                |
|      4 | ハートンホテル | POLYGON((135.760497 35.012033,135.760497 35.011655,135.76097 35.011655,135.76097 35.012033,135.760497 35.012033)) |
+--------+-----------------------+-------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

もちろん、検索するエリアを以下のように広げても、そこに含まれるGeoデータを検索することができます。

> select geo_id, ASTEXT(geo) from geo where MBRContains(GeomFromText('LineString(135.760009 35.012279, 135.762439 35.010851)'), geo);
+--------+-------------------------------------------------------------------------------------------------------------------+
| geo_id | ASTEXT(geo)                                                                                                       |
+--------+-------------------------------------------------------------------------------------------------------------------+
|      1 | POINT(135.761919 35.011141)                                                                                       |
|      3 | LINESTRING(135.76105 35.012165,135.76105 35.01104)                                                                |
|      4 | POLYGON((135.760497 35.012033,135.760497 35.011655,135.76097 35.011655,135.76097 35.012033,135.760497 35.012033)) |
+--------+-------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

本連載の第2回で、緯度経度による位置情報の表現の限界について触れましたが、このようにLINESTRINGやPOLYGONを用いて、位置情報を表現することも可能です。POLYGONを利用すると、「⁠琵琶湖」というような、非常に大きな範囲のエリアを登録することができますが、データの内容は緯度経度の集合になっているので、緯度経度だけに場合に比べてデータを作成する労力が大きくなってしまいます。

Geometry型は、緯度経度情報を扱うのに便利ですが、そこに格納するデータは、自分が作成するアプリケーションの内容をよく吟味して、POINTだけにするのか、あるいはPOLYGONも格納するのかを検討しましょう。

> また、現在のところInnoDBではSPATIAL INDEXが作成できない点も忘れないようにしましょう。

次回予告

今回は、緯度経度を数値型や平面空間データ型でDBへ保存し、検索する方法を解説しました。

次回は、引き続き「位置情報を保存する」というテーマで、文字列情報として緯度経度を格納するGeohashについて解説したいと思います。