如何从sql数据库中找到最近的位置?

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最近的地方。

7 个解决方案

#1

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.

下面是SQL语句，它将找到距离37，- 122坐标25英里范围内最近的20个位置。它根据行的纬度/经度和目标纬度/经度计算距离，然后只要求距离值小于25的行，按距离对整个查询进行排序，并将结果限制为20个。用公里代替英里来搜索，用6371代替3959。

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.

你可以在这里找到细节。

#2

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;

选择id (3959 * acos(cos(radians(37))) * cos(radians(lat))) * cos(radians(lng) - radians(-122) + sin(radians(37))) * sin(radians(radians(lat))))，作为距离小于25阶的标记的距离，限值为0,20;

This is the Best Query

这是最好的查询

#3

To find the nearby location , you can use the Geocoder Class.Since you have the Geopoints(latitude and longitude), Reverse geocoding can be used. Reverse Geocoding is the process of transforming a (latitude, longitude) coordinate into a (partial) address. Check out this for more information.

要找到附近的位置，可以使用Geocoder类。由于您有地理位置(纬度和经度)，可以使用反向地理编码。反向地理编码是将(纬度，经度)坐标转换为(部分)地址的过程。更多信息请查看这里。

#4

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

#5

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'];

#6

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的顺序，然后简单地去掉结果的平方根。

#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;

where [starlat] and [startlng] is the position where to start measuring the distance and 25 is the distance in kms.

其中[starlat]和[startlng]是开始测量距离的位置，25是kms的距离。

It is advised to make stored procedure to use the query because it would be checking a lots of rows to find the result.

建议让存储过程使用查询，因为它将检查大量行以找到结果。

#1