php 如何使用sql数据库中的纬度和经度找到最近的位置？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11112926/
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
How to find nearest location using latitude and longitude from sql database?
提问by Krishna Karki
I want to find a nearest location from following database table
我想从以下数据库表中找到最近的位置
Address Latitude longitude
Kathmandu 44600, Nepal 27.7 85.33333330000005
Los, Antoniterstra?e 37.09024 -95.71289100000001
Sydney NSW, Australia 49.7480755 8.111794700000019
goa india 15.2993265 74.12399600000003
I have fetched this all data from google maps. Here i have to find nearest location from a place. Suppose i am at place Surkhet its latitude is 28.6 and longitude is 81.6, how can i find nearest place from the place Surkhet.
我已经从谷歌地图中获取了所有数据。在这里，我必须找到离某个地方最近的位置。假设我在 Surkhet 地方，它的纬度是 28.6，经度是 81.6，我怎样才能找到离 Surkhet 地方最近的地方。
采纳答案by Krishna Karki
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 < 25 ORDER BY distance LIMIT 0 , 20;
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 < 25 ORDER BY distance LIMIT 0 , 20;
This is the Best Query
这是最好的查询
回答by Scorpion
Finding locations nearby with MySQL
使用 MySQL 查找附近的位置
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。
Table Structure :
表结构：
id,name,address,lat,lng
NOTE - Here latitude = 37 & longitude = -122. So you just pass your own.
注意 - 这里纬度 = 37 & 经度 = -122。所以你只需通过你自己的。
SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) *
cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) *
sin( radians( lat ) ) ) ) AS distance FROM your_table_name HAVING
distance < 25 ORDER BY distance LIMIT 0 , 20;
You can find details here.
您可以在此处找到详细信息。
回答by Ankit Saxena
To find the nearby location , you can use the GeocoderClass.Since you have the Geopoints(latitude and longitude), Reverse geocoding can be used. Reverse Geocodingis the process of transforming a (latitude, longitude) coordinate into a (partial) address. Check out thisfor more information.
为了找到附近的地点，您可以使用地理编码Class.Since你有Geopoints（经度和纬度），可使用反向地理编码。 反向地理编码是将（纬度、经度）坐标转换为（部分）地址的过程。查看此了解更多信息。
回答by user2142645
The SQL have a problem. In table like:
SQL有问题。在表中：
`ubicacion` (
`id_ubicacion` INT(10) NOT NULL AUTO_INCREMENT ,
`latitud` DOUBLE NOT NULL ,
`longitud` DOUBLE NOT NULL ,
PRIMARY KEY (`id_ubicacion`) )
The id_ubicacion change when use:
使用时 id_ubicacion 的变化：
SELECT `id_ubicacion` , ( 3959 * ACOS( COS( RADIANS( 9.053933 ) ) * COS( RADIANS( latitud ) ) * COS( RADIANS( longitud ) - RADIANS( - 79.421215 ) ) + SIN( RADIANS( 9.053933 ) ) * SIN( RADIANS( latitud ) ) ) ) AS distance
FROM ubicacion
HAVING distance <25
ORDER BY distance
LIMIT 0 , 20
回答by Nilesh Prajapati
sesach : ( 3959 * acos( cos( radians('.$_REQUEST['latitude'].') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('.$_REQUEST['longitude'].') ) + sin( radians('.$_REQUEST['latitude'].') ) * sin( radians( latitude ) ) ) ) <='.$_REQUEST['mile'];
回答by Jesper Kleis
When the method should perform it is nicer to first filter on latitude and longitude, and then calculate the squared distance approximative. For Nordic countries, it will be about 0.3 percent off within 1000 km's.
当该方法应该执行时，最好先过滤纬度和经度，然后近似计算平方距离。对于北欧国家，在 1000 公里范围内将享受约 0.3% 的折扣。
So instead of calculatinG the distance as:
因此，而不是将距离计算为：
dist_Sphere = r_earth * acos ( sin (lat1) * sin (lat2) + cos(lat1)*cos(lat2)*cos(lon 2 - lon 1)
one can calculate the approximate value (assume that lat = lat 1 is close to lat 2) as
可以计算近似值（假设 lat = lat 1 接近 lat 2）为
const cLat2 = cos lat ^ 2
const r_earth2 = r_earth ^ 2
dist_App ^2 = r_earth2 * ((lat 2 - lat 1) ^2 + clat2 *(lon 2 - lon 1) ^2)
Order by Dist_App 2, and then simply take the square root off the result.
按 Dist_App 2 排序，然后简单地从结果中取平方根。
回答by Himanshu Upadhyay
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 and 25 is the distance in kms.
其中[starlat]和[startlng]是开始测量距离的位置，25 是以公里为单位的距离。
It is advised to make stored procedure to use the query because it would be checking a lots of rows to find the result.
建议使用存储过程来使用查询，因为它会检查很多行才能找到结果。
