MySQLには位置情報を扱う機能があるが、コツが必要なようなのでメモ

テーブルを作成

テーブルを作成する際に注意するべきなのは以下の2点。

• 緯度・経度を保存するフィールドのデータ型は、 “geometry” とする。
• インデックスに SPATIAL を使用する。

自分の環境の、phpMyAdmin では SPATIAL インデックスが作成できなかったので、以下のようなSQLを発行した。

ALTER TABLE geom ADD SPATIAL INDEX(geo);

テーブルのスキーマは以下のような感じ。

CREATE TABLE `points` (
  `loc` varchar(20) NOT NULL,
  `point` varchar(50) NOT NULL,
  `geo` geometry NOT NULL,
  PRIMARY KEY  (`loc`),
  UNIQUE KEY `point` (`point`),
  SPATIAL KEY `geo` (`geo`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

実際のSQL

以下のようなSQLで、指定した緯度・経度に最も近い１件を取り出すことができる。

SELECT
    loc,
    GLength(
        GeomFromText(
            CONCAT(
                'LineString(135.8 33.48,',
                X(geo),
                ' ',
                Y(geo),
                ')'
            )
        )
    ) AS len
FROM points
WHERE
    MBRContains(
        GeomFromText(
            Concat(
                'LineString(',
                135.8 + 1,
                ' ',
                33.48 + 1,
                ',',
                135.8 - 1,
                ' ',
                33.48 - 1,
                ')'
            )
        ),
        geo
    )
ORDER BY len limit 0,1

ここで注意！

単純に最も近い1件を取り出すだけならWHERE句は必要ないが、MySQLでは空間インデックス（SPATIAL）をORDER BYで使用することはできなようだ。（たぶん。。。）

そのため、WHERE句で対象となるデータを減らした上で、ORDER BYしたらパフォーマンスが向上したので、やむなくWHERE句を挿入した。

WHERE句内で使用している “+ 1″ とか “- 1″ のあたりは指定した緯度経度を基準にした約100kmに相当する矩形をつくるという意味であるが、対象となるデータの件数や密度によって、増やしたり減らしたりする必要がある。

これに関しては、以下のサイトで詳しく検証されている。

MySQL TIPS 3 空間情報(geometry)を使って経度・緯度の検索を高速化する – イノベートな非日常

が、explain の結果にfilesort とあるので、ORDER BYではやはりインデックスが使用されていないことに注意する必要がある。

というわけで。。。

MySQLで位置情報を取り扱う際にORDER BYを使用するには注意が必要。

Google MAPを使用する等ユーザーインターフェースで工夫して逃げる方が得策かもしれない。