使用SQL查询查找最近的经纬度

Question

我有纬度和经度，想要从数据库中取出最靠近这个坐标的记录，如果距离超过了指定的距离，则不检索它。

表结构：

id
latitude
longitude
place name
city
country
state
zip
sealevel

- Basit

---

这有点类似于“接近搜索”问题的重复。 - Darius Bacon

这里有一组由Alexander Rubin制作的幻灯片，关于使用MySQL进行地理（邻近）搜索（PDF链接）。 - Martijn Pieters

注意这里的答案。大多数不能使用任何索引，因此在处理大型数据集时性能较差。有些受到非球形距离计算的限制，因此在许多全局应用中无法使用。 - Rick James

更多讨论，包括关于扩展、精度和5种竞争技术的讨论，请参见：http://mysql.rjweb.org/doc.php/find_nearest_in_mysql - Rick James

如果查询中包含 HAVING distance < ...，那么该查询很可能会检查每一行并计算每一行的距离。（速度慢且不可扩展。） - Rick James

Answer 1

SELECT latitude, longitude, SQRT(
    POW(69.1 * (latitude - [startlat]), 2) +
    POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM TableName HAVING distance < 25 ORDER BY distance;

其中[starlat]和[startlng]是开始测量距离的位置。

- Kaletha

---

性能建议：最好不要对距离变量进行平方根运算，而是将“25”测试值进行平方...如果需要显示距离，则稍后对已通过的结果进行平方根运算。 - sradforth

要将距离单位从英里转换为米，应提出什么样的查询？ - httpete

这个“25”是什么尺寸？ - Steffan Donal

这里需要澄清一下，69.1是将英里转换为纬度度数的转换因子。57.3大约相当于180/pi，这是从度数到弧度的转换，用于余弦函数。25是搜索半径，单位是英里。当使用十进制度和英里时，请使用此公式。 - John Vance

另外，它没有考虑地球的曲率。对于较短的搜索半径来说，这不是问题。否则，Evan和Igor的答案更为完整。 - John Vance

Answer 2

谷歌的解决方案：

创建表格

在创建MySQL表格时，您需要特别注意lat和lng属性。随着Google Maps当前的缩放能力，您只需要小数点后6位的精度。为了使您的表格所需的存储空间最小化，您可以指定lat和lng属性为大小为（10,6）的浮点数。这将允许字段存储小数点后6位数字，以及小数点前最多4位数字，例如-123.456789度。您的表格还应该有一个id属性作为主键。

CREATE TABLE `markers` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 60 ) NOT NULL ,
  `address` VARCHAR( 80 ) NOT NULL ,
  `lat` FLOAT( 10, 6 ) NOT NULL ,
  `lng` FLOAT( 10, 6 ) NOT NULL
) ENGINE = MYISAM ;

填充表格

创建表格后，就可以开始填充数据了。下面提供的示例数据是美国境内分布在约180个披萨店的数据。在phpMyAdmin中，您可以使用导入选项卡来导入各种文件格式，包括CSV（逗号分隔值）。Microsoft Excel和Google Spreadsheets都支持导出为CSV格式，因此您可以通过导出/导入CSV文件轻松地将数据从电子表格传输到MySQL表格。

INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Frankie Johnnie & Luigo Too','939 W El Camino Real, Mountain View, CA','37.386339','-122.085823');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Amici\'s East Coast Pizzeria','790 Castro St, Mountain View, CA','37.38714','-122.083235');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Kapp\'s Pizza Bar & Grill','191 Castro St, Mountain View, CA','37.393885','-122.078916');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Round Table Pizza: Mountain View','570 N Shoreline Blvd, Mountain View, CA','37.402653','-122.079354');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Tony & Alba\'s Pizza & Pasta','619 Escuela Ave, Mountain View, CA','37.394011','-122.095528');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Oregano\'s Wood-Fired Pizza','4546 El Camino Real, Los Altos, CA','37.401724','-122.114646');

使用MySQL查找位置

要查找标记表中与给定纬度/经度的特定半径距离内的位置，可以使用基于Haversine公式的SELECT语句。 Haversine公式通常用于计算球面上两个坐标对之间的大圆距离。维基百科提供了深入的数学解释，Movable Type网站上对公式与编程的关系进行了很好的讨论。

以下是SQL语句，它将查找在25英里半径内的最接近37,-122坐标的20个位置。它根据该行和目标纬度/经度的纬度/经度计算距离，然后仅请求距离值小于25的行，按距离排序整个查询，并将其限制为20个结果。要按公里而不是英里搜索，请将3959替换为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 < 28 
ORDER BY distance LIMIT 0, 20;

这是一个用于查找距离小于28英里的纬度和经度的函数。

另一个函数则用于查找距离在28到29英里之间的纬度和经度：

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 < 29 and distance > 28 
ORDER BY distance LIMIT 0, 20;

https://developers.google.com/maps/articles/phpsqlsearch_v3#creating-the-map

- Sviatoslav Oleksiv

---

查询半径为25英里内的位置，应该是HAVING distance < 25吗？ - Vitalii Elenhaupt

你确定 @vidurpunj 大于 25 吗？ - Amranur Rahman

为什么我们需要将 id 设为主键？我们不能使用纬度和经度作为我们的主键吗？ - Devashish Prasad

"大小为（10,6）的浮点数...将让字段在小数点后存储6个数字，加上小数点前最多4个数字" - 这是不正确的。相反，FLOAT 仅限于约7个有效数字。这对大多数用途已足够。放弃 (10,6)；它在8.0.17中已被弃用，并将在以后被删除。 - Rick James

到目前为止，这是最快的查询。它比https://dev59.com/ZXE95IYBdhLWcg3wm_Mu#5548877快7~8%。 - Mahmoud Kassem

Answer 3

这个问题的原始回答很好，但是较新版本的mysql（MySQL 5.7.6及以上）支持地理查询，因此您现在可以使用内置功能而不是进行复杂的查询。

现在您可以执行以下操作：

select *, ST_Distance_Sphere( point ('input_longitude', 'input_latitude'), 
                              point(longitude, latitude)) * .000621371192 
          as `distance_in_miles` 
  from `TableName`
having `distance_in_miles` <= 'input_max_distance'
 order by `distance_in_miles` asc

结果以米为单位返回。如果您想使用千米，请使用.001而不是.000621371192（适用于英里）。

这里是Mysql文档链接

- Sherman

---

如果可能，请在答案中添加mysql版本。如果可以，请在回答中提供MySQL的版本号。 - Parixit

“ST_Distance_Sphere” 在我的主机安装中不存在（mysql Ver 15.1 Distrib 10.2.23-MariaDB）。我在某个地方读到要用“ST_Distance”代替，但距离相差很远。 - ashleedawg

@ashleedawg - 从版本来看，我认为你正在使用MariaDB，这是mysql的一个分支。从这个对话来看，似乎MariaDB还没有实现ST_Distance_Sphere。 - Sherman

这个答案应该排名靠前，因为它是唯一使用“新”功能并且产生更好查询结果的答案。虽然说实话，我对于默认使用英制单位的回答数量感到困惑；至少这个答案说明了如何在两种单位之间切换。 - theberzi

非常好！我已经编写了类似的代码多次，但这一次是完美的！感谢您！ 我还有一个问题，我有三个位置表格。您有任何想法如何在所有表格上运行此选择？ - Sascha Grindau

Answer 4

以下是我用 PHP 实现的完整解决方案。

该解决方案使用了 Haversine 公式，如 http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL 所述。

需要注意的是，Haversine 公式在极地周围存在一些弱点。 这个答案 展示了如何实现 Vincenty 大圆距离公式 来避免这个问题，但是我选择只使用 Haversine 公式，因为对我的目的来说已经足够好了。

我将纬度存储为 DECIMAL(10,8)，经度存储为 DECIMAL(11,8)。希望这可以帮到你！

showClosest.php

<?PHP
/**
 * Use the Haversine Formula to display the 100 closest matches to $origLat, $origLon
 * Only search the MySQL table $tableName for matches within a 10 mile ($dist) radius.
 */
include("./assets/db/db.php"); // Include database connection function
$db = new database(); // Initiate a new MySQL connection
$tableName = "db.table";
$origLat = 42.1365;
$origLon = -71.7559;
$dist = 10; // This is the maximum distance (in miles) away from $origLat, $origLon in which to search
$query = "SELECT name, latitude, longitude, 3956 * 2 * 
          ASIN(SQRT( POWER(SIN(($origLat - latitude)*pi()/180/2),2)
          +COS($origLat*pi()/180 )*COS(latitude*pi()/180)
          *POWER(SIN(($origLon-longitude)*pi()/180/2),2))) 
          as distance FROM $tableName WHERE 
          longitude between ($origLon-$dist/cos(radians($origLat))*69) 
          and ($origLon+$dist/cos(radians($origLat))*69) 
          and latitude between ($origLat-($dist/69)) 
          and ($origLat+($dist/69)) 
          having distance < $dist ORDER BY distance limit 100"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
    echo $row['name']." > ".$row['distance']."<BR>";
}
mysql_close($db);
?>

./assets/db/db.php

<?PHP
/**
 * Class to initiate a new MySQL connection based on $dbInfo settings found in dbSettings.php
 *
 * @example $db = new database(); // Initiate a new database connection
 * @example mysql_close($db); // close the connection
 */
class database{
    protected $databaseLink;
    function __construct(){
        include "dbSettings.php";
        $this->database = $dbInfo['host'];
        $this->mysql_user = $dbInfo['user'];
        $this->mysql_pass = $dbInfo['pass'];
        $this->openConnection();
        return $this->get_link();
    }
    function openConnection(){
    $this->databaseLink = mysql_connect($this->database, $this->mysql_user, $this->mysql_pass);
    }

    function get_link(){
    return $this->databaseLink;
    }
}
?>

./assets/db/dbSettings.php

<?php
$dbInfo = array(
    'host'      => "localhost",
    'user'      => "root",
    'pass'      => "password"
);
?>

如上文“使用MySQL存储过程实现地理距离搜索”的建议，可能可以通过使用MySQL存储过程来提高性能。

我有一个包含约17,000个地点的数据库，查询执行时间为0.054秒。

- circuitry

---

警告。这是一个很好的解决方案，但它有一个漏洞。所有的 abs 应该被移除。在将角度转换为弧度时，没有必要取绝对值，即使你这样做了，也只是对其中一个纬度进行操作。请编辑它以修复此漏洞。 - Chango

对于想以米为单位的人： 将3956英里转换为千米：地球半径； 将69英里转换为千米：大约1纬度长度（km）； 并输入距离（以千米为单位）。 - Chango

应该能够将“3956”替换为“6371”，以便结果以公里为单位显示，因为这是地球的半径（以公里为单位）。 - rkeet

并将“69”替换为“111,044736”（在上述评论中忘记了）。 - rkeet

这对我有用，我尝试了很多解决方案，但这个解决方案拯救了我的一天。谢谢。 - Sasindu Jayampathi

Answer 5

如果你像我一样有点懒，这里有一个解决方案，是从stackoverflow和其他回答中汇总得来的。

set @orig_lat=37.46; 
set @orig_long=-122.25; 
set @bounding_distance=1;

SELECT
*
,((ACOS(SIN(@orig_lat * PI() / 180) * SIN(`lat` * PI() / 180) + COS(@orig_lat * PI() / 180) * COS(`lat` * PI() / 180) * COS((@orig_long - `long`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` 
FROM `cities` 
WHERE
(
  `lat` BETWEEN (@orig_lat - @bounding_distance) AND (@orig_lat + @bounding_distance)
  AND `long` BETWEEN (@orig_long - @bounding_distance) AND (@orig_long + @bounding_distance)
)
ORDER BY `distance` ASC
limit 25;

- Evan

---

bounding_distance 究竟代表什么？这个值是否限制结果在一英里范围内？因此，在这种情况下，它将返回1英里范围内的结果？ - james

@bounding_distance 这里以度数为单位，用于通过限制有效搜索区域来加快计算速度。例如，如果您知道用户在某个城市中，而且您知道该城市内有几个点，则可以将绑定距离安全地设置为几度。 - Evan

这是使用哪个地理距离公式？ - bbodenmiller

“Bounding box”被用于WHERE中，以便使用INDEX(lat, lng), INDEX(lng, lat)，但这是次优的，因为需要通过除以COS(@orig_lat)来调整@bounding_distance。 - Rick James

Answer 6

简单易懂的问题 ;)

SELECT * FROM `WAYPOINTS` W ORDER BY
ABS(ABS(W.`LATITUDE`-53.63) +
ABS(W.`LONGITUDE`-9.9)) ASC LIMIT 30;

只需用您需要的坐标替换原始坐标即可。这些值必须作为双精度浮点数存储。这是一个可用的 MySQL 5.x 示例。

干杯

- Nicholas

---

不知道为什么会有点赞，OP想要按照一定的距离限制和排序，而不是限制30并按dx+dy排序。 - okm

这对我很有帮助。虽然不是原帖作者想要的，但却符合我的需求，感谢您的回答！ :) - Webmaster G

最外层的 ABS() 已足够。 - dzona

Answer 7

试一下这个，它可以显示距离提供的坐标最近的点（在50公里范围内）。它完美地运作：

SELECT m.name,
    m.lat, m.lon,
    p.distance_unit
             * DEGREES(ACOS(COS(RADIANS(p.latpoint))
             * COS(RADIANS(m.lat))
             * COS(RADIANS(p.longpoint) - RADIANS(m.lon))
             + SIN(RADIANS(p.latpoint))
             * SIN(RADIANS(m.lat)))) AS distance_in_km
FROM <table_name> AS m
JOIN (
      SELECT <userLat> AS latpoint, <userLon> AS longpoint,
             50.0 AS radius, 111.045 AS distance_unit
     ) AS p ON 1=1
WHERE m.lat
BETWEEN p.latpoint  - (p.radius / p.distance_unit)
    AND p.latpoint  + (p.radius / p.distance_unit)
    AND m.lon BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
    AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
ORDER BY distance_in_km

只需更改<table_name>，<userLat>和<userLon>即可。

您可以在此处阅读有关此解决方案的更多信息：http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

- smartmouse

Answer 8

您正在寻找类似哈弗辛公式的内容。此外，也可以看看这里。

还有其他方法，但这是最常引用的一种。

如果您正在寻找更强大的东西，您可能需要查看数据库的GIS功能。它们能够做一些很酷的事情，比如告诉您一个点（城市）是否出现在给定的多边形（区域、国家、大陆）中。

- Koobz

---

它确实是最常被引用的，但许多文章在谈到使用其他方法进行计算时都参考了旧的计算硬件，详见 http://www.movable-type.co.uk/scripts/latlong.html#cosine-law。 - Arjan

Answer 9

请参考基于Geo-Distance-Search-with-MySQL的以下代码：

示例：查找半径为10英里内距离我的当前位置最近的10家酒店：

#Please notice that (lat,lng) values mustn't be negatives to perform all calculations

set @my_lat=34.6087674878572; 
set @my_lng=58.3783670308302;
set @dist=10; #10 miles radius

SELECT dest.id, dest.lat, dest.lng,  3956 * 2 * ASIN(SQRT(POWER(SIN((@my_lat -abs(dest.lat)) * pi()/180 / 2),2) + COS(@my_lat * pi()/180 ) * COS(abs(dest.lat) *  pi()/180) * POWER(SIN((@my_lng - abs(dest.lng)) *  pi()/180 / 2), 2))
) as distance
FROM hotel as dest
having distance < @dist
ORDER BY distance limit 10;

#Also notice that distance are expressed in terms of radius.

- JuanManuelFigueroa

Answer 10

查找距离我最近的用户：

距离以米为单位

基于Vincenty公式

我有一个用户表：

+----+-----------------------+---------+--------------+---------------+
| id | email                 | name    | location_lat | location_long |
+----+-----------------------+---------+--------------+---------------+
| 13 | [e-mail] | Isaac   | 17.2675625   | -97.6802361   |
| 14 | [e-mail]   | Monse   | 19.392702    | -99.172596    |
+----+-----------------------+---------+--------------+---------------+

SQL：

-- my location:  lat   19.391124   -99.165660
SELECT 
(ATAN(
    SQRT(
        POW(COS(RADIANS(users.location_lat)) * SIN(RADIANS(users.location_long) - RADIANS(-99.165660)), 2) +
        POW(COS(RADIANS(19.391124)) * SIN(RADIANS(users.location_lat)) - 
       SIN(RADIANS(19.391124)) * cos(RADIANS(users.location_lat)) * cos(RADIANS(users.location_long) - RADIANS(-99.165660)), 2)
    )
    ,
    SIN(RADIANS(19.391124)) * 
    SIN(RADIANS(users.location_lat)) + 
    COS(RADIANS(19.391124)) * 
    COS(RADIANS(users.location_lat)) * 
    COS(RADIANS(users.location_long) - RADIANS(-99.165660))
 ) * 6371000) as distance,
users.id
FROM users
ORDER BY distance ASC

地球半径：6371000米

- Isaac Limón