4

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.

Anand G
  • 3,130
  • 1
  • 22
  • 28
Bryan Meyer
  • 103
  • 13

2 Answers2

4

For floating point values, it is standard to NOT use equality checks. Instead, use subtraction:

SELECT * FROM weather_cities WHERE ABS(Latitude-50.45)<0.01 AND ABS(Longitude-30.52)<0.01

You can also sort by the absolute distance from the rounded coordinate to get which city is closest. The drawback here is that it will not use an index on Latitude and Longitude because you are using those fields within a mathematical expression.

kainaw
  • 4,256
  • 1
  • 18
  • 38
  • When I try this SELECT * FROM sys_widget_owm_cities WHERE ABS(`Latitude`-'50.4501')<0.01 AND ABS(`Longitude`-'30.5234')<0.01 I get 0 results... Am I writing the query wrong ? – Bryan Meyer Jun 06 '19 at 16:44
  • 50.4501 is a number. Don't put quotes around it. Latitude should be a numeric column, not a varchar. If it is varchar, it must be converted to numeric (and you should ask whomever made the database WHY it isn't numeric). – kainaw Jun 06 '19 at 16:47
  • they are stored as float and I removed the quotes, but the result is the same – Bryan Meyer Jun 06 '19 at 17:01
  • Then no latitude is within between 50.4401 and 50.4601 with a longitude between 30.5134 and 30.5354. In case it isn't obvious, ABS simply makes the value positive. If the value in Latitude is 50.450123 and you subtract 50.4501, you will end up with 0.000023, which is less than 0.01. – kainaw Jun 06 '19 at 17:06
0

Try This Query

Mysql Rounded() function description

SELECT ROUND(weather_cities.Latitude,2),ROUND(weather_cities.Longitude,2) FROM weather_cities WHERE Latitude=12.2500 AND Longitude=54.1235

hope that query returns that your wish result-set. Thank you.

Dhanushka sasanka
  • 518
  • 3
  • 18