我有经纬度,我想从数据库中提取记录,它有最近的经纬度,如果这个距离比指定的长,就不检索它。
表结构:
id
latitude
longitude
place name
city
country
state
zip
sealevel
我有经纬度,我想从数据库中提取记录,它有最近的经纬度,如果这个距离比指定的长,就不检索它。
表结构:
id
latitude
longitude
place name
city
country
state
zip
sealevel
当前回答
试试这个,它显示最近的点提供的坐标(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/
其他回答
听起来就像你想在距离上做一个最近邻居搜索。据我所知,SQL不支持这样的任何东西,您需要使用另一种数据结构,如r树或kd树。
以防你像我一样懒,这里有一个解决方案,由这个和其他关于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;
简单的一个;)
SELECT * FROM `WAYPOINTS` W ORDER BY
ABS(ABS(W.`LATITUDE`-53.63) +
ABS(W.`LONGITUDE`-9.9)) ASC LIMIT 30;
把坐标换成你需要的坐标。这些值必须存储为double类型。这是一个工作中的MySQL 5。x的例子。
干杯
试试这个,它显示最近的点提供的坐标(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/
下面是我用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秒。