Mysql – How to find nearest location using latitude and longitude from SQL database

MySQL

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.

Best Answer

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.

Related Solutions

Mysql – Find nearest latitude/longitude with an SQL query

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.

Php – Problem Storing Latitude and Longitude values in MySQL database

You need to use decimal if you don't want the numbers to be approximated.

Fixed-Point (Exact-Value) Types

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data.

And now the "here you go" answer:

Use DECIMAL(10,7). Where 10 is the total number of digits in the number and 7 is the number of digits after the .. (This means that before the dot will be 3 digits.)

Adjust these numbers as needed. Also please take a look at the manual entry I linked earlier in the answer.

Best Answer

Related Solutions

Mysql – Find nearest latitude/longitude with an SQL query

Php – Problem Storing Latitude and Longitude values in MySQL database

Related Topic