CODEWITHSUNDEEP
mysqlsqlgoogle-maps
Marc
Marc

Reputation: 746

MySQL location search within radius for Google maps - why am I getting the wrong distances?

I am trying to query a MySQL table called 'stockists' to return any locations within a given radius. The table holds a latitude and longitude for each record, and I have tested each of these to check that the locations/co-ordinates are correct.

At the moment there are only 3 test locations in the table, all within a 20 mile radius of the search co-ordinates, the nearest being only 5 miles away, yet the distances returned by the query are all over 60 miles and not in the correct order.

The co-ordinates of my test search location are 51.690495, -3.780315.

After reading the following article: https://developers.google.com/maps/articles/phpsqlsearch_v3 I concluded that I needed to floor both co-ordinates to make my numbers match the example given (ie 51.690495, -3.780315 becomes 51 and -3) but I'm not sure if this was the correct thing to do.

My query is as follows:

SELECT id, company_name, floor(( 3959 * acos( cos( radians(51) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-3) ) + sin( radians(51) ) * sin( radians( lat ) ) ) )) AS distance FROM stockists HAVING distance < 70 ORDER BY distance LIMIT 0 , 20

I would appreciate it if someone could point me in the right direction.

Upvotes: 2

Views: 1579

Answers (1)

OmegaDirective
OmegaDirective

Reputation: 366

You shouldn't floor the numbers. This map of lat/lon lines in California shows that each decimal digit accounts for quite a distance. The paper also says,

With the current zoom capabilities of Google Maps, you should only need 6 digits of precision after the decimal.

I would try using the exact values and see if that works. If not, try using the following formula. I have used it in the past so I know for a fact it works.

SELECT id, company_name, 3956*2*ASIN(SQRT(POWER(SIN((yourTestLat - Latitude)*pi()/180 / 2), 2) + COS(yourTestLat * pi() / 180) * COS(Latitude * pi()/180) * POWER(SIN((yourTestLon - Longitude)*pi()/180 / 2),2))) AS distance FROM stockists HAVING distance < 70 ORDER BY distance LIMIT 0 , 20

Here yourTestLat, yourTestLon are your test numbers (51.690495, -3.780315, not rounded) and Latitude, Longitude are the SQL table columns containing the table locations. Good luck!

Upvotes: 3

Related Questions