[MySQL]指定した緯度経度を中心に指定半径内のスポットデータを近い順に取得する（geometry型不使用編）

スポット情報を保存するこんなテーブルがあったとして、

CREATE TABLE `spots` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `latitude` decimal(8,6) DEFAULT NULL,
  `longitude` decimal(9,6) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

緯度 35.6804067、経度 139.7550152 を中心に半径3km以内のスポットを近い順に取得したい場合、下記のSQLで取得ができる。

SELECT
  id, name, latitude, longitude,
  (
    6371 * acos( -- kmの場合は6371、mileの場合は3959
      cos(radians(35.6804067))
      * cos(radians(latitude))
      * cos(radians(longitude) - radians(139.7550152))
      + sin(radians(35.6804067))
      * sin(radians(latitude))
    )
  ) AS distance
FROM
  spots
HAVING
  distance <= 3 -- 半径3km以内
ORDER BY
  distance -- 近い順(ASC)
LIMIT 10 -- 最大10件
;

取得データサンプル

id	name	latitude	longitude	distance
51	スポット51	35.676988	139.758881	0.5161685844029122
49	スポット49	35.679841	139.765092	0.9123285501120572
139	スポット139	35.672128	139.758912	0.9855475862507722
112	スポット112	35.681382	139.766084	1.0056121098808728
115	スポット115	35.681788	139.766761	1.0719528578851056
94	スポット94	35.671470	139.761512	1.1540565221794934
22	スポット22	35.680924	139.768938	1.2588424754888945
21	スポット21	35.670642	139.762923	1.2996690197721117
23	スポット23	35.669531	139.760409	1.3037774084414326
118	スポット118	35.670436	139.763142	1.3296837880068584

参考: https://developers.google.com/maps/articles/phpsqlsearch_v3

Finding Locations with MySQL

To find locations in your markers table that are within a certain radius distance of a given latitude/longitude, you can use a SELECT statement based on the Haversine formula. The Haversine formula is used generally for computing great-circle distances between two pairs of coordinates on a sphere. An in-depth mathemetical explanation is given by Wikipedia and a good discussion of the formula as it relates to programming is on Movable Type's site.

Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;