MySQL 使用 SQL 查询查找最近的纬度/经度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2234204/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find nearest latitude/longitude with an SQL query
提问by Basit
I have latitude and longitude and I want to pull the record from the database, which has nearest latitude and longitude by the distance, if that distance gets longer than specified one, then don't retrieve it.
我有纬度和经度，我想从数据库中提取记录，该数据库具有距离最近的纬度和经度，如果该距离长于指定的距离，则不要检索它。
Table structure:
表结构：
id
latitude
longitude
place name
city
country
state
zip
sealevel
回答by Kaletha
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;
where [starlat]and [startlng]is the position where to start measuring the distance.
其中[starlat]和[startlng]是开始测量距离的位置。
回答by Sviatoslav Oleksiv
Google's solution:
谷歌的解决方案：
Creating the Table
创建表
When you create the MySQL table, you want to pay particular attention to the lat and lng attributes. With the current zoom capabilities of Google Maps, you should only need 6 digits of precision after the decimal. To keep the storage space required for your table at a minimum, you can specify that the lat and lng attributes are floats of size (10,6). That will let the fields store 6 digits after the decimal, plus up to 4 digits before the decimal, e.g. -123.456789 degrees. Your table should also have an id attribute to serve as the primary key.
创建 MySQL 表时，要特别注意 lat 和 lng 属性。使用 Google 地图当前的缩放功能，您应该只需要小数点后 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 ;
Populating the Table
填充表格
After creating the table, it's time to populate it with data. The sample data provided below is for about 180 pizzarias scattered across the United States. In phpMyAdmin, you can use the IMPORT tab to import various file formats, including CSV (comma-separated values). Microsoft Excel and Google Spreadsheets both export to CSV format, so you can easily transfer data from spreadsheets to MySQL tables through exporting/importing CSV files.
创建表后，是时候用数据填充它了。下面提供的样本数据适用于分散在美国各地的大约 180 家比萨饼。在 phpMyAdmin 中，您可以使用 IMPORT 选项卡导入各种文件格式，包括 CSV（逗号分隔值）。Microsoft Excel 和 Google 电子表格都导出为 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');
Finding Locations with MySQL
使用 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.
要在给定纬度/经度的特定半径距离内查找标记表中的位置，您可以使用基于 Haversine 公式的 SELECT 语句。Haversine 公式通常用于计算球体上两对坐标之间的大圆距离。维基百科给出了深入的数学解释，并且在 Movable Type 的站点上对与编程相关的公式进行了很好的讨论。
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.
这是将查找距离 37, -122 坐标 25 英里半径内最近的 20 个位置的 SQL 语句。它根据该行的纬度/经度和目标纬度/经度计算距离，然后仅询问距离值小于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;
This one is to find latitudes and longitudes in a distance less than 28 miles.
这是在小于 28 英里的距离内查找纬度和经度。
Another one is to find them in a distance between 28 and 29 miles:
另一种方法是在 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
https://developers.google.com/maps/articles/phpsqlsearch_v3#creating-the-map
回答by circuitry
Here is my full solution implemented in PHP.
这是我用 PHP 实现的完整解决方案。
This solution uses the Haversine formula as presented in http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL.
此解决方案使用半正弦公式，如http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL 中所示。
It should be noted that the Haversine formula experiences weaknesses around the poles. This answershows how to implement the vincenty Great Circle Distance formulato get around this, however I chose to just use Haversine because it's good enough for my purposes.
应该注意的是，Haversine 公式在极点周围存在弱点。这个答案显示了如何实现vincenty Great Circle Distance 公式来解决这个问题，但是我选择只使用Haversine，因为它足以满足我的目的。
I'm storing latitude as DECIMAL(10,8) and longitude as DECIMAL(11,8). Hopefully this helps!
我将纬度存储为 DECIMAL(10,8)，经度存储为 DECIMAL(11,8)。希望这会有所帮助！
showClosest.php
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
./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
./assets/db/dbSettings.php
<?php
$dbInfo = array(
'host' => "localhost",
'user' => "root",
'pass' => "password"
);
?>
It may be possible to increase performance by using a MySQL stored procedure as suggested by the "Geo-Distance-Search-with-MySQL" article posted above.
按照上面发布的“Geo-Distance-Search-with-MySQL”文章的建议，可以通过使用 MySQL 存储过程来提高性能。
I have a database of ~17,000 places and the query execution time is 0.054 seconds.
我有一个约 17,000 个位置的数据库，查询执行时间为 0.054 秒。
回答by Evan
Just in case you are lazy like me, here's a solution amalgamated from this and other answers on SO.
以防万一你像我一样懒惰，这里有一个从这个和其他关于 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;
回答by Nicholas
Easy one ;)
简单的一个;)
SELECT * FROM `WAYPOINTS` W ORDER BY
ABS(ABS(W.`LATITUDE`-53.63) +
ABS(W.`LONGITUDE`-9.9)) ASC LIMIT 30;
Just replace the coordinates with your required ones. The values have to be stored as double. This ist a working MySQL 5.x example.
只需用您需要的坐标替换坐标即可。这些值必须存储为双精度值。这是一个有效的 MySQL 5.x 示例。
Cheers
干杯
回答by Koobz
You're looking for things like the haversine formula. See hereas well.
There's other ones but this is the most commonly cited.
还有其他的，但这是最常被引用的。
If you're looking for something even more robust, you might want to look at your databases GIS capabilities. They're capable of some cool things like telling you whether a point (City) appears within a given polygon (Region, Country, Continent).
如果您正在寻找更强大的功能，您可能需要查看您的数据库 GIS 功能。他们能够做一些很酷的事情，比如告诉你一个点（城市）是否出现在给定的多边形（地区、国家、大陆）内。
回答by smartmouse
Try this, it show the nearest points to provided coordinates (within 50 km). It works perfectly:
试试这个，它会显示距离提供的坐标最近的点（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
Just change <table_name>. <userLat>and <userLon>
只是改变<table_name>。<userLat>和<userLon>
You can read more about this solution here: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/
您可以在此处阅读有关此解决方案的更多信息：http: //www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/
回答by JuanManuelFigueroa
Check this code based on the article Geo-Distance-Search-with-MySQL:
根据文章Geo-Distance-Search-with-MySQL检查此代码：
Example: find the 10 nearest hotels to my current location in a 10 miles radius:
示例：在 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.
回答by Hardip
simpledb.execSQL("CREATE TABLE IF NOT EXISTS " + tablename + "(id INTEGER PRIMARY KEY AUTOINCREMENT,lat double,lng double,address varchar)");
simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2891001','70.780154','craftbox');");
simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2901396','70.7782428','kotecha');");//22.2904718 //70.7783906
simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2863155','70.772108','kkv Hall');");
simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.275993','70.778076','nana mava');");
simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2667148','70.7609386','Govani boys hostal');");
double curentlat=22.2667258; //22.2677258
double curentlong=70.76096826;//70.76096826
double curentlat1=curentlat+0.0010000;
double curentlat2=curentlat-0.0010000;
double curentlong1=curentlong+0.0010000;
double curentlong2=curentlong-0.0010000;
try{
Cursor c=simpledb.rawQuery("select * from '"+tablename+"' where (lat BETWEEN '"+curentlat2+"' and '"+curentlat1+"') or (lng BETWEEN '"+curentlong2+"' and '"+curentlong1+"')",null);
Log.d("SQL ", c.toString());
if(c.getCount()>0)
{
while (c.moveToNext())
{
double d=c.getDouble(1);
double d1=c.getDouble(2);
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
回答by Sherman
The original answers to the question are good, but newer versions of mysql (MySQL 5.7.6 on) support geo queries, so you can now use built in functionality rather than doing complex queries.
该问题的原始答案很好，但较新版本的 mysql（MySQL 5.7.6 上）支持地理查询，因此您现在可以使用内置功能而不是进行复杂的查询。
You can now do something like:
您现在可以执行以下操作：
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
The results are returned in metersso if you want KMinstead of miles use .0001instead of .000621371192
结果将返回，meters因此如果您想要KM而不是里程使用.0001而不是.000621371192
