MySQLのgeometry型で○km以内の場所を取得してみました

これはGaiax Engineers' Advent Calendar 2015の8日目の記事です。

Gaiaxで主にPerlを書いてます@mitaniです。
最近はTABICAという「地域の暮らしを旅する」をコンセプトにした、地元の人が開催する旅(ツアー)の予約サービスの開発を行っています。

TABICA(たびか)へのレコメンド機能追加

今回、特定の場所(過去の予約や、閲覧している旅など)から近くの旅をレコメンドする機能を実装する事になりました。

以下は、レコメンド機能の実装の際にやってみたまとめになります。
MySQLのgeometry型については、エンジニアネタとしては使い古された感じもありますが大目に見てもらえると幸いです。

既存のデータ

TABICAではGoogleマップを利用して旅の開催場所を表示するために、MySQL(5.6系)で各旅の位置(緯度経度)をgeometry型で保持しています。

以下のようなテーブルに

CREATE TABLE `spot` (
  `id` bigint(16) unsigned NOT NULL,
  `latlng` geometry NOT NULL,
  PRIMARY KEY (`htm_id`),
  SPATIAL KEY `latlng` (`latlng`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

以下のようなデータが入っています。

mysql> SELECT id,ASTEXT(latlng) FROM spot;
+----+---------------------------------------------+
| id | ASTEXT(latlng)                              |
+----+---------------------------------------------+
|  1 | POINT(139.7454299032688 35.65858405958573)  |
|  2 | POINT(139.8106947541237 35.71006652116823)  |
+----+---------------------------------------------+

idには各旅固有のidが、
latlngには該当の旅の場所の緯度経度が入っています。

ASTEXT
geometry型をそのまま取得するとバイナリが取得されて人には読めないので、文字列で結果を取得します。

POINT
座標から特定の場所(点)を表すデータ型。

近い順に旅を取得

上記のテーブルからまずは 特定の旅(id=1) の場所から近い順に旅のidを取得しようと思います。

SELECT
  s2.id,
  GLENGTH(GEOMFROMTEXT(
      CONCAT('LINESTRING(',
          X( s1.latlng ),' ',Y( s1.latlng ), ',',
          X( s2.latlng ),' ',Y( s2.latlng ),
      ')'
  ))
) AS distance
FROM spot AS s1
  INNER JOIN spot AS s2
WHERE s1.id = 1 AND s1.id != s2.id
ORDER BY distance;

上記で、特定の旅(id=1) から近い順に旅を取得する事ができました。
id=1の緯度経度から、id!=1の緯度経度まで線を引いて、線の長さが短い順に並べるという具合です。

GLENGTH
LINESTRINGの長さを取得。

LINESTRING
POINTとPOINTを繋いだ線を表すデータ型。

X
経度

Y
緯度

GEOMFROMTEXT
geometry型への変換。

CONCAT関数
これは説明不要だと思いますが、文字列結合です。

近い順？？

上記のSQLでは、近い順に旅を取得することができましたが、このままでは1000kmも2000kmも離れてる旅がレコメンドされる事になってしまい、とても近いとは言えません。
今回は歩ける距離ということで、1km以内を近い旅としました。

そこで問題となるのが、GLENGTHで取得できる距離の単位が度数なこと。
まずは1kmが何度なのかを知る為に地球の外周が何kmなのかを把握する必要があります。

Google先生曰く、地球の外周は約40075kmとの事なので、
40075km / 360度 ≒ 111.3194km
1度は、約111.3194kmのようです。
(およその数字にしているのは、地球が完全な球体ではない為です)

1 / 111.3194 ≒ 0.0089831601679492
1kmは、約0.0089831601679492度ですね。
(すでに誤差があるので、もう1km≒0.009度にしてしまってもいいかもしれませんが)

先ほどのSQLに条件を追加して、特定の旅(id=1) から約1km以内の旅を近い順に取得してみます。

SELECT
  s2.id,
  GLENGTH(GEOMFROMTEXT(CONCAT(
      'LineString(',
        X( s1.latlng ),' ',Y( s1.latlng ), ',',
        X( s2.latlng ),' ',Y( s2.latlng ),
      ')'
  )))
  AS distance
FROM spot AS s1
  INNER JOIN spot s2
WHERE s1.id = 1 AND s1.id != s2.id
GROUP BY s2.id HAVING distance <= 0.0089831601679492
ORDER BY distance

上記のSQLでとある旅(id=1) から約1km以内の旅を近い順に取得することができました。
これで、条件が何km以内であっても取得できそうです。

明日(9日目)の記事は？

明日はGaiaxの図書委員長こと @kyrieleison さんです。
当記事より100倍は役立つと思いますのでお楽しみに。