243 inari このページをアンテナに追加 RSSフィード

2009-02-12

MySQLで指定した緯度経度から半径nメートル内検索っぽいのを実現するSQL

MySQL4.1以降で空間情報を扱う機能が使えるけど、指定した緯度経度から半径nメートル以内の検索が出来ないのが難点です。

かなり大雑把なやり方で半径nメートル検索のやり方を考えてみました。

まず最初に緯度経度の情報を持ったテーブルを作成。

CREATE TABLE `geotable` (
`id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR(255) NOT NULL,
`geom` POINT NOT NULL,
SPATIAL INDEX(geom)
) ENGINE = MYISAM ;

続いてデータを挿入。

INSERT INTO `geotable` (`name`, `geom`) VALUES
('上野駅', GeomFromText('POINT(139.777254 35.713768)')),
('西郷隆盛像', GeomFromText('POINT(139.774029 35.711846)')),
('上野の森美術館', GeomFromText('POINT(139.774744 35.712737)')),
('不忍池弁財天', GeomFromText('POINT(139.770872 35.712351)')),
('野口英世博士像', GeomFromText('POINT(139.775696 35.716293)')),
('国立西洋美術館', GeomFromText('POINT(139.775803 35.71542)')),
('国立科学博物館', GeomFromText('POINT(139.776544 35.716319)')),
('東京都美術館', GeomFromText('POINT(139.772776 35.717186)')),
('東京国立博物館', GeomFromText('POINT(139.776462 35.718883)')),
('花やしき', GeomFromText('POINT(139.794547 35.71528)')),
('雷門', GeomFromText('POINT(139.792692 35.710635)'));

これで準備完了。

例として上野駅から300m内にある場所を検索してみる。

MySQLではA点からB点の緯度経度を元に範囲検索ができるので、上野駅から300m離れた緯度経度が必要になるため、300mが何度になるかを計算する。

まずは1度当たりの距離を計算。

地球の円周が約4万kmなので、これを360で割ると約111kmとなる。

1度=111kmと単位が大きすぎるので、秒単位に変換する。

1度=3600秒なので、1秒当たりの距離は111kmを3600で割ると約30mとなる。*1

DBに入ってるのは度単位なので、1秒を度単位に変換すると約0.000277778なので、300m離れると0.00277778度変わるはず。

上野駅の緯度が35.713768度、経度が139.777254度なのでそれぞれプラスマイナス0.00277778度すると300mくらい離れた地点の緯度経度が計算できる。

あとはこれを使って検索するだけ。

SELECT name, Y(geom), X(geom) FROM geotable
WHERE MBRContains(GeomFromText
('LineString(139.7800318 35.71654578, 139.7744762 35.71099022)'),geom);
--実行結果:
上野駅 		35.713768 	139.777254
上野の森美術館 	35.712737 	139.774744
野口英世博士像 	35.716293 	139.775696
国立西洋美術館 	35.71542 	139.775803
国立科学博物館 	35.716319 	139.776544

参考記事:

Google MapsとMySQLの連携

MySQL4.1以降での空間情報の扱い方

*1:実際は緯度経度によって1秒当たりの距離は違うけど

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証