Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
47
Help us understand the problem. What are the problem?

More than 3 years have passed since last update.

posted at

updated at

Organization

[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;
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
47
Help us understand the problem. What are the problem?