LoginSignup
25

More than 5 years have passed since last update.

mysqlの空間インデックス(spatial index)で思うような結果が出なかったら読んで下さい。

Posted at

結論から書きます。

例えばある緯度経度が含まれる市町村の名前を探すアプリケーションをmysqlで実現したい場合は、、、
1.検索にはmysql5.6を使って下さい。
2.mysql5.6以前のバージョンでデータを入力しても、それはそのまま使えます。
という事を言いたいだけです。検証に結構時間がかかったので、共有しておきます。

問題点

緯度経度情報から市町村を取得する必要があって、色々調べていたら、次のページをみつけました。
http://gihyo.jp/dev/feature/01/location-based-services/0005?page=2

mysqlで緯度経度をポリゴンの形式で保持することが出来ます。
また、任意の緯度経度を含んだデータを引っ張ってくる事も可能です。
例えばこんなテーブルを作成して、、、

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

こんな感じで登録して、、、

INSERT INTO geo (name,geo) values ('ハートンホテル', polygonFromText('POLYGON((135.760497 35.012033,135.760497 35.011655, 135.760970 35.011655, 135.760970 35.012033,135.760497 35.012033))'));

こんな風に検索しようという話です。

mysql> select count(*) from geo where MBRIntersects(GeomFromText('POINT(140.174560546875 35.64306822520527)'), geo);

上記の参考資料くらいのデータ量だと特に問題もなくハッピーなのですが、例えば日本の市町村データをテーブルに入れたりすると複数のエリアが検索されてしまいます(情報の集め方は後述します)。

mysql> select count(*) from geo where MBRIntersects(GeomFromText('POINT(140.174560546875 35.64306822520527)'), geo);
+----------+
| count(*) |
+----------+
|   3      |
+----------+

、、、こんな感じ。google mapに領域を書いて確認してみましたが、複数のエリアが被っているという事はありませんでした。

色々ぐぐってみると、mysql5.6より前は、polygonの検索にpointは対応してないよ、、、的な事が出てきたので、強引に次のようにしてみました。

mysql> select count(*) geo city where MBRIntersects(GeomFromText('LINESTRING(140.174560546875 35.64306822520527, 140.174560546876 35.64306822520528)'), geo);
+----------+
| count(*) |
+----------+
|   3      |
+----------+

が、結果は同じです。

対策

そこからまたひたすらググってみて、次の記事を見つけました。
http://www.percona.com/blog/2013/10/21/using-the-new-spatial-functions-in-mysql-5-6-for-geo-enabled-applications/

で、ここで結論なのですが、mysql5.6にしないと、思ったような結果は出ないみたいです。
Mysql5.6だと次のように検索出来ます。

SELECT count(*) FROM geo WHERE st_contains(geo, point(140.174560546875,35.64306822520527));
+----------+
| count(*) |
+----------+
|   1      |
+----------+

なお、データ自体はMysql5.5で入力したものです。単純に計算アルゴリズムだけの問題のようです。

追記

都道府県の領域情報の収集は、このデータは次の記事を参考にさせて頂きました。
http://qiita.com/hamichamp/items/ac9e80f1078febb9f1b9

参考資料ではMongoDBを使っています。これも試しましたが、Mysqlのような問題はありませんでした。
ちなみにdebian/ubuntuユーザーの方向けですが、apt-getを単純にしても、(現時点では)mysql5.6はインストールできません。
ただ、oracleのサイトにapt-getでmysql5.6をインストールできるようにするdebファイルがありますので、それをダウンロードすると幸せになるかもしれません。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
25