Get the closest place in the database

Question

I am trying to get the closest user login location from the database (the nearest store based on latitude and longitude), so based on the users zip code I convert this to latitude and longitude, and from these results I need to search in my database to find The store closest to these values. I have the latitude and longitude of all stored repositories (from viewing previous questions) I 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 displays the result, but it is not the nearest repository, not sure if this could be due to negative numbers, and my latitude + longitude columns are stored as decimal data types?

Answer 1

You have a logical operation in order by, not in arithmetic. Try the following:

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;

ANDin the original version it will generate a logical value, either 0, or 1- and this will only 1be when the values ​​match exactly with the last decimal point. Not very interesting.

There are many reasons why this is not the closest distance, but it can be close enough for your purposes. Here are a few reasons:

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