mysql 纬度/经度找到最接近的纬度/经度-复杂的SQL或复杂的计算



math coordinates (13)

以防万一你像我一样懒惰,这里有一个从SO和其他答案合并的解决方案。

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;

我有经度和纬度,我想从距离最近的纬度和经度的数据库中提取记录,如果距离超过指定的距离,则不检索它。

表结构:

id
latitude
longitude
place name
city
country
state
zip
sealevel

Answer #1

试试这个,它显示提供的坐标的最近点(在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/ : http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/


Answer #2

这里是我用PHP实现的完整解决方案。

此解决方案使用http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL介绍的Haversine公式。

应该指出的是,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"
);
?>

正如上面发布的“Geo-Distance-Search-with-MySQL”文章所建议的那样,使用MySQL存储过程可能会提高性能。

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


Answer #3

这个问题根本不是很难,但是如果你需要优化它,它会变得更加复杂。

我的意思是,你的数据库中有100个位置还是1亿个位置? 它有很大的不同。

如果位置数量很少,只需执行 - >将它们从SQL中移出并放入代码中

Select * from Location

一旦你把它们变成代码,用Haversine公式计算每个纬度/经度与你原来的距离并对其进行分类。


Answer #4

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

Answer #5

找到最接近我的用户:

以米为单位的距离

基于Vincenty的公式

我有用户表:

+----+-----------------------+---------+--------------+---------------+
| id | email                 | name    | location_lat | location_long |
+----+-----------------------+---------+--------------+---------------+
| 13 | [email protected] | Isaac   | 17.2675625   | -97.6802361   |
| 14 | [email protected]   | 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(米)


Answer #6

你正在寻找像沙丁胺配方这样的东西。 here也可以看到。

还有其他的,但这是最常被引用的。

如果您正在寻找更强大的功能,您可能需要查看数据库GIS功能。 他们能够做出一些很酷的事情,例如告诉你一个点(城市)是否出现在给定的多边形(地区,国家,大陆)内。


Answer #7
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]是开始测量距离的位置。


Answer #8

听起来像你应该只使用PostGIS,SpatialLite,SQLServer2008或Oracle Spatial。 他们都可以用空间SQL为你解答这个问题。



Answer #10

在极端情况下,这种方法失败了,但为了表现,我跳过了三角法并简单地计算了对角线的平方。



Answer #12

这听起来像你想做一个最近的邻居搜索与一些距离的约束。 就我所知,SQL不支持任何类似的东西,您需要使用其他数据结构,例如R-treekd-tree





computational-geometry