如何从sql数据库中使用纬度和经度查找最近的位置？

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

Finding locations nearby with 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.

Table Structure :

id,name,address,lat,lng

NOTE - Here latitude = 37 & longitude = -122. So you just pass your own.

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.

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.

The SQL have a problem. In table like:

`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:

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

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

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.

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

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.

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.

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