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

Question

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.

have you looked at the *related* links on the left >>> some of them look on topic for you. — , Jun 20 '12 at 04:46
@Damien_The_Unbeliever um i live in the souther hemisphere, our left is your right :-) — , Jun 20 '12 at 19:56
I believe you need to do something like Geospatial search. Please see this paper http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL — Sandeep Nair, Jun 20 '12 at 04:47
Possible duplicate of [latitude/longitude find nearest latitude/longitude - complex sql or complex calculation](https://stackoverflow.com/questions/2234204/latitude-longitude-find-nearest-latitude-longitude-complex-sql-or-complex-calc) — StayOnTarget, Jul 31 '17 at 19:44

score 99 · Answer 1 · edited Jul 20 '14 at 00:56

99

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.

edited Jul 20 '14 at 00:56

Pang

9,564
146
81
122

answered Jun 20 '12 at 04:52

Scorpion

6,831
16
75
123

3

Also go through this one. It explains everything in nice way. https://developers.google.com/maps/articles/phpsqlsearch – Scorpion Jun 20 '12 at 04:54
Thank you so much for query. I have to find nearest location from Surkhet which latitude is 28.6 and longitude is 81.6. How to include this point on this query.. Please suggest me..:) – Krishna Karki Jun 20 '12 at 05:06
See the note. I edited my answer. You need to pass your lat & lng in place of 37 & -122, – Scorpion Jun 20 '12 at 05:15
3

Does this work for you?? And also keep in mind that i have used 3959, it will search by miles. If you want to search by km change it to 6371. – Scorpion Jun 20 '12 at 05:41
SELECT address, lng, SQRT( POW(69.1 * (lat -21.0486), 2) + POW(69.1 * ( 76.5344 - lng) * COS(lat/ 57.3), 2)) AS distance FROM partner_locations HAVING distance < 25000 ORDER BY distance I found this query.. Its working properly.. But i m not sure about unit of distance.. is it kilometer or miles... can you suggest me please – Krishna Karki Jun 20 '12 at 07:16
Sorry but don't have any idea about this query. Have you try the query which i give you? Try it once.. – Scorpion Jun 20 '12 at 08:25
@Scorpion i am facing a error in this query. will you please help? – Sandy Mar 10 '14 at 08:17
can you please tell us if this work fine for thousand records. Thanks – Muhammad Usama Mashkoor Aug 10 '17 at 20:15

score 30 · Accepted Answer · edited Sep 08 '20 at 11:27

30

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;

edited Sep 08 '20 at 11:27

Adriaan

17,741
7
42
75

answered Jul 25 '12 at 05:27

Krishna Karki

1,304
4
14
31

1

distance 25 here is in miles?? – Sripaul Dec 02 '12 at 17:37
13

Yes. 3959 - Miles, 6371 - Kilometers – Paulius Vindzigelskis Aug 06 '13 at 11:40
@Krishna Karki, will this return a lat and lon if I put it in function? – The_Martian Sep 07 '15 at 21:49
Distance from where? – MarceloBarbosa Feb 07 '16 at 21:44
can you please tell us if this work fine for thousand records. Thank – Muhammad Usama Mashkoor Aug 10 '17 at 20:15
@krishna karki please can you help me check this out https://stackoverflow.com/questions/51227693/how-can-i-query-database-based-on-the-longitude-and-latitude-stored-in-the-datab – Akoh Victor Gutz Jul 07 '18 at 23:53
@AkohVictorGutz Your question is under hold. I cannot answer there. – Krishna Karki Jul 09 '18 at 05:24
@krishna karki yes i was told i did not outline my need clearly, but basically what i needed is that i have to objects. object 1 = Jobs and object 2 = Professionals each having longitude and latitude stored in my database i want it in a way that when i search my database for jobs only the one close to my longitude and latitude come out for me. but the challenge is that i don't know how to measure the closeness or farness – Akoh Victor Gutz Jul 10 '18 at 05:02
@KrishnaKarki I want the travel time as well How can I do that. – Ahtesham Shah Apr 12 '19 at 13:09

score 3 · Answer 3 · answered Jun 20 '12 at 06:42

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.

score 2 · Answer 4 · answered Jul 14 '17 at 11:17

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.

score 0 · Answer 5 · answered Mar 07 '13 at 15:02

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

score 0 · Answer 6 · answered Sep 05 '13 at 10:04

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

score 0 · Answer 7 · answered Oct 07 '14 at 08:20

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.

score 0 · Answer 8 · edited Aug 31 '22 at 11:22

If you want to display all values with distance

replace lat long with double quote ( " " )

the query will display only 25km distance data for more increase value

check for tricks and tips:- multigrad.in

SELECT *, SQRT(
POW(69.1 * (lat - "YOUR LATITUDE VALUE"), 2) +
POW(69.1 * ("YOY LONGITUDE VALUE" - lon) * COS(lat / 57.3), 2)) AS distance FROM moms HAVING distance < 25 ORDER BY distance;

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

8 Answers8

Linked