根据文章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.

听起来就像你想在距离上做一个最近邻居搜索。据我所知，SQL不支持这样的任何东西，您需要使用另一种数据结构，如r树或kd树。

MS SQL版本在这里:

        DECLARE @SLAT AS FLOAT
        DECLARE @SLON AS FLOAT

        SET @SLAT = 38.150785
        SET @SLON = 27.360249

        SELECT TOP 10 [LATITUDE], [LONGITUDE], SQRT(
            POWER(69.1 * ([LATITUDE] - @SLAT), 2) +
            POWER(69.1 * (@SLON - [LONGITUDE]) * COS([LATITUDE] / 57.3), 2)) AS distance
        FROM [TABLE] ORDER BY 3

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

该解决方案使用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);
?>

/资产/ 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;
    }
}
?>

资产/ db - dbSettings。php

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

根据上面“使用MySQL进行地理距离搜索”文章的建议，可以通过使用MySQL存储过程来提高性能。

我有一个约17,000个位置的数据库，查询执行时间为0.054秒。

Mysql查询搜索坐标的距离限制和条件

 SELECT id, ( 3959 * acos( cos( radians('28.5850154') ) * cos( radians(latitude) ) * cos( radians( longitude ) - radians('77.07207489999999') ) + sin( radians('28.5850154') ) * sin( radians( latitude ) ) ) ) AS distance FROM `vendors` HAVING distance < 5;

这个问题最初的答案是好的，但是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

结果以米为单位返回。因此，如果你想计算KM，只需使用。001而不是。000621371192(这是英里)。

MySql文档在这里