I have a table that stores cities with their coordinates (lat,long) and weather information. There is a possibility that a user searches for a cities that has no weather information, but a city nearby has.
Also the I don't want to search for city by name, because a city name can change if typed in another language, (ex. Kiev - Kyiv, Geneva, Genève, Genf, etc.) coordinates don't change much.
So, I use google api to get lat, long from city name, which returns something like this :
For city of Kiev : (Google)
Latitude : 50.4501 , Longitude= 30.5234 (rounded to : 50.45 - 30.52)
However, in my weather table, the city is spelled Kyiv and has coordinates as follows :
Latitude : 50.4333 , Longitude= 30.5167
So now, how would I proceed to search in my weather table to search for rounded values of coordinates (stored as floats)
SELECT * FROM weather_cities WHERE Latitude='50.45' AND Longitude='30.52'
The above query will not work because latitude in mysql (50.4333) would be rounded to 50.44 and not 50.45.If I reduce the precision to only one decimal, it would, but in other cases I would get too many matching cities.
I would like to search in floats to the closest / nearest values in mysql without outputting too much results. What do you advice me to achieve expected result? I using mysql and php.
I've looked into this solution : https://leehblue.com/match-a-float-in-mysql/ and also into this : PHP How do I round down to two decimal places? but I'm stuck.
