CODEWITHSUNDEEP
mysqlsqldatabase
Karina
Karina

Reputation: 663

Get nearest location in database

I am trying to get the nearest location to a users input from within a database, (nearest store based on latitude and longitude), so based on the users postcode I am converting that to latitude and longitude and from these results I need to search my database to find the store that is the nearest to these values. I have the latitude and longitude of all stores saved and so far (from looking at previous questions) I have tried something like:

SELECT * 
FROM mystore_table 
WHERE `latitude` >=(51.5263472 * .9) AND `longitude` <=(-0.3830181 * 1.1) 
ORDER BY abs(latitude - 51.5263472 AND longitude - -0.3830181) limit 1;

When I run this query, it does display a result, but it is not the nearest store, not sure if it could be something to do with the negative numbers, both my columns latitude + longitude are saved as decimal data types?

Upvotes: 4

Views: 1196

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You have a logic operation in the order by rather than an arithmetic one. Try this:

SELECT * 
FROM mystore_table 
WHERE `latitude` >=(51.5263472 * .9) AND `longitude` <=(-0.3830181 * 1.1) 
ORDER BY abs(latitude - 51.5263472) + abs(longitude - -0.3830181)
limit 1;

The AND in your original version would be producing a boolean value, either 0 or 1 -- and it would only be 1 when the values match exactly to the last decimal point. Not very interesting.

There are many reasons why this is not the nearest distance, but it might be close enough for your purposes. Here are some reasons:

  • Euclidean distance would take the square of the differences
  • Distance between two latitudes depends on the longitude (varying from about 70 miles on the equator to 0 at the poles).

Upvotes: 5

Related Questions