SQLを使って位置情報から距離計算をする

はじめに

マッハバイトでバックエンドを担当している @ayumu838 です。

今回は前回のような技術投資の話ではなく実務で使おうとしている話になります。

マッハバイトでは、求人ページに勤務地の最寄り駅に関する情報を掲載しています。
求人掲載の際に最寄駅は明示的に記載していただけることが多いのですが、一番近い最寄駅以外は意外と記載していただけないことがあります。
そこで、最寄駅から近い駅を自動抽出したいと考えたのですが、このようなニーズはマッハバイトに限ったものではないと思ったので、記事としてまとめてみました。

前提条件

実現するにあたり、以下を条件としました。

社内で使用している管理画面で使うだけなので、極力外部サービスに依存する箇所を減らす(≒専用のサービスを使わない)
どこが近いかが分かれば良いので極端に高い精度は求めない
- たとえ、数十メートルずれていても駅同士の近さの関係にはほぼ影響ない

また、実装環境として、DBMS は MariaDB を、Webアプリケーションのフレームワークは Ruby on Rails を使っています。

実現方法

データベース内には駅情報として以下のテーブルがあります。
※記事用にカラムのリネームや抜粋をしています。

CREATE TABLE `stations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `lat` double DEFAULT NULL,
  `lng` double DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

その場合、データは以下の通りになります。

id	name	lat	lng	address
1	東京	35.681391	139.766103	東京都千代田区
2	新橋	35.666195	139.758587	東京都港区

実DBではもう少し複雑なので、今後取り回しが良くなるようにviewにします。
また、今後処理しやすいように、緯度経度情報を ST_GeometryFromText を使って Geometry Types に変換しています。

CREATE ALGORITHM=UNDEFINED DEFINER=`write`@`%` SQL SECURITY DEFINER VIEW `geo_stations`
AS SELECT
  `stations`.`id`,
  `stations`.`name`,
  `stations`.`address`,
  ST_GeometryFromText(concat('POINT(',`stations`.`lat`,' ',`stations`.`lng`,')')) AS `point`
FROM `stations`
WHERE `lat` IS NOT NULL OR `lng` IS NOT NULL;

lat, lng が NULL になっている場合があったので、どちらかが NULL のデータは view 作成時に除外しています。

距離の計算には ST_DISTANCE を利用します。
こちらは平面上の二点間の距離を求めることができる関数です。
残念ながら、マッハバイトで使っているDBのバージョンにはなかったのですが、ST_DISTANCE_SPHERE を使うと球体上の距離を求めることになるので、さらに精度が良くなります。

距離の長さを比較するだけであれば、 ST_DISTANCE の結果だけでも大丈夫なのですが、もっと見やすいようにメートル単位で出力をします。
その場合、 ST_DISTANCE に地球楕円体の長半径における1度あたりの距離である 111319.49 を乗じます。

ある駅から近い駅を出力することを想定し、SQL にすると以下のようなクエリになります。

SELECT
  id,
  name,
  address,
  ST_Distance(
    (SELECT point FROM geo_stations WHERE id = 1), point
  ) * 111319.49 as distance
FROM geo_stations
ORDER BY distance

次に、この SQL を Ruby on Rails 経由で実行することを考えてみます。

直接SQLを作り ActiveRecord::Base.connection.execute で実行しても特に問題はありませんが、
その場合計算結果の取り回しが良くないのでActiveRecordを利用して計算します。

求めたい駅の緯度経度を出力します。
そのままではバイナリデータとして出力されるので、 ST_AsText で変換します。

target_point = GeoStation.where(name: name).pluck('ST_AsText(point)').first

これに対して、近い順に10駅ほど表示してみます。

GeoStation.all
  .select(:id, :name, :address,
          "ST_Distance(
            ST_GeometryFromText('#{target_point}'), point
          ) * 111319.49 as distance")
  .order('distance')
  .where.not(name: name)
  .limit(10)

同じ駅は(当たり前ですが)0メートルとなるので結果から除外しています。

例として東京駅を指定してみると以下の通りになりました。

[
  #<GeoStation:0x007f9f58e5a610 id: 3, name: "二重橋前", address: "東京都千代田区">,
  #<GeoStation:0x007f9f58e5a4d0 id: 4, name: "大手町", address: "東京都千代田区">,
  #<GeoStation:0x007f9f58e5a390 id: 5, name: "京橋", address: "東京都中央区">,
  #<GeoStation:0x007f9f58e5a250 id: 6, name: "有楽町", address: "東京都千代田区">,
  #<GeoStation:0x007f9f58e5a110 id: 7, name: "銀座一丁目", address: "東京都中央区">,
  #<GeoStation:0x007f9f58e59fd0 id: 8, name: "日本橋", address: "東京都中央区">,
  #<GeoStation:0x007f9f58e59e90 id: 9, name: "宝町", address: "東京都中央区">,
  #<GeoStation:0x007f9f58e59d50 id: 10, name: "日比谷", address: "東京都千代田区">,
  #<GeoStation:0x007f9f58e59c10 id: 11, name: "三越前", address: "東京都中央区">,
  #<GeoStation:0x007f9f58e59ad0 id: 12, name: "銀座", address: "東京都中央区">
]

また distance はカラムではないので結果には表示されていませんが、出力すると以下のようになります。

results.map{ |r| [r.name, r.distance] }
=> [["二重橋前", 463.90218763018777],
 ["大手町", 575.9569505567738],
 ["京橋", 674.8454667333606],
 ["有楽町", 709.994180022267],
 ["銀座一丁目", 790.7841106737169],
 ["日本橋", 828.7475333962705],
 ["宝町", 912.8591590908778],
 ["日比谷", 1004.6797666640091],
 ["三越前", 1048.5278493420508],
 ["銀座", 1073.3453165303602]]

良さそうな感じですね。

応用例

DB上には駅の緯度経度情報しかないので、任意の場所を基準地点として使いたいなら別の方法で算出してあげる必要があります。

たとえば、geocoder を使えば簡単に緯度経度情報を取得できます。

具体的にコードにすると以下のようになります。

# paramに調べたいものを入れる
# 例: 東京タワー,  東京都千代田区1丁目
latlon = Geocoder.coordinates(param)

# 緯度経度は以下のようにすれば上記の処理に入ります
target_point = "POINT(#{latlon[0]} #{latlon[1]})"

せっかくなので、上記の処理を別メソッドに切り出して、最終的に以下のようになります。

def search_with_param(param, limit = 10)
  latlon = Geocoder.coordinates(param)
  return if latlon.blank?

  target_point = "POINT(#{latlon[0]} #{latlon[1]})"

  calculate_distance(target_point).limit(limit)
end

def search_with_name(name, limit = 10)
  target_point = GeoStation.where(name: name).pluck('ST_AsText(point)').first

  calculate_distance(target_point)
    .where.not(name: name)
    .limit(limit)
end

private

def calculate_distance(target_point)
  GeoStation.all.select(:id, :name, :address,
             "ST_Distance(
               ST_GeometryFromText('#{target_point}'), point
             ) * 111319.49 as distance")
     .order('distance')
end