MySQLを使って指定した緯度経度から半径XXm以内の地点を検索する

mysql_geometry

MySQLに緯度経度を登録しておいて、特定の地点から近いレコードを引っ張ってくるという事をやってみました。

今回、参考URLは以下の2つ

データベース内の緯度・経度を利用して半径500m以内を検索する方法
MySQL空間テーブルの作り方

今回の目標

0:DBにテーブル作成
1:緯度経度情報をもった地点をMySQLに登録する
2:ある地点 lat, lng から500m はなれた地点の緯度経度2点を計算する
3:ある地点 lat, lng から半径XXm以内のレコードをMySQLから検索

なんで、検索の前に2地点の計算をしなくてはならないかというと
MySQLで半径Xmの円内の検索をしたくても、そもそも関数がないようにみえる
http://qiita.com/kochizufan/items/a68b30ba74849483f75c
結局矩形の検索をした後に円と円に内接する長方形の間の地点を求めるのだとか。

1.MySQLにテーブル作成

locationsテーブルを作成する事として、DBに保存する各レコードのカラムは、
・ID
・名称
・住所
・緯度経度
として、スキーマは以下のようにした。

create table locations (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name        VARCHAR(255) NOT NULL,
  address     VARCHAR(255) NOT NULL,
  location GEOMETRY NOT NULL,
  PRIMARY KEY(id),
  SPATIAL KEY location (location)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

緯度経度はGEOMETRY型という型で保存するようです。
関係ないんですが、当初DBのmy.cnfでCharsetを指定していなかったからか、文字化けしまくったので途中で気づいてutg8指定を追加した。

2.データをINSERTする

さっそく作ったテーブルにInsertしていくんですが、緯度経度はそのまま入れてはダメで、GeomFromText()にPOINT関数を使って渡してあげるそうです。
ちなみに自分用のメモも兼ねているので、Rubyの中のMySQL2インスタンスで使ってるソースをそのまま貼っておきます。

INSERT INTO locations ( name_h, address, location)
  VALUES ('#{name}', '#{address}', 
     GeomFromText('POINT(#{lat_w.to_s} #{lng_w.to_s})'))")

コードの中で緯度・経度をFloatとして扱っていたのでStringに変換してから突っ込んでたりします。

3.500m以内の場所を検索する

まず、矩形の範囲を作るため2地点の緯度経度を求めておく
それぞれを、latA, lngA と latB, lngB とすると。。

PointA
latA = lat + (500 ÷ 30.8184 × 0.000277778)
lngA = lng + (500 ÷ 25.2450 × 0.000277778)

PointB はマイナスはAの計算で単純にマイナスにするだけ
latB = lat – (500 ÷ 30.8184 × 0.000277778)
lngB = lng – (500 ÷ 25.2450 × 0.000277778)

検索クエリ

下記のサンプルでは東京駅前の国際フォーラムあたりの緯度経度(35.677885 ,139.764553) から500m以内のLocationレコードをひっぱります。
※PointAとPoingBは事前に計算済みのTEI

SELECT name,
       X(location) AS lat,
       Y(location) AS lng,
       GLength(
         GeomFromText(
           CONCAT('LineString(35.677885 139.764553,' 
                  , X(location), ' ', Y(location),'
                  )'))) AS distance
FROM locations
WHERE MBRWithin(location,
      GeomFromText('LineString(latA lngA, latB lngB)'));

ちなみに、ここで返ってくるdistance(Glength)の単位がわからなかったのですが、度(1/360)でした。
What is the unit in returned by MySQL GLength method?
地球の半径から1度あたりの距離(Km)を求めてかければOKだそうですね。
こちらの方は緯度経度関係なく、円の弧として計算すればよさそう。 ただ、ちょっとめんどくさいのでまた今後。

レコードの中の緯度経度とクエリの緯度経度の間の距離を求める

緯度経度同士の距離計算はGLength関数を使うようですが、間にLineString、CONCAT、GeomFromTextがダブルでラッピングされていて、パット見よくわからない系のクエリになっています。
自分用に各関数のリファレンス・マニュアルのリンクを貼って終了。

LineString
CONCAT
GeomFromText

終わり
 

2016-05-05 | Posted in 技術的なポエムNo Comments » 


関連記事

Comment





Comment



*