0

what could possible be the reason for Mysql does not find records that exists. It is about Lat&Lng database search. I have this simple query:

select id from addresses where lat = "42.5632172" and lng = "27.5266380"

which returns 0 results but its obvious that record exists. Here is my database structure and records: enter image description here enter image description here

Thanks in advance for your time!

Europeuser
  • 934
  • 1
  • 9
  • 32
  • Can you reproduce your issue with sample data in a [fiddle](https://dbfiddle.uk/3aMmxpPT)? – forpas Feb 19 '23 at 09:10
  • 1
    Just a hint: comparing floats and double is often some kind of a lucky bet. Especially for geo data it is easy to convert them to integers before storing to the database. In that Case you can also define the precision you need by cuttin of the last digits if not needed. – Thallius Feb 19 '23 at 09:12
  • Not sure if succeed with the fiddle: https://dbfiddle.uk/SrqK8Mzf – Europeuser Feb 19 '23 at 09:15
  • @Thallius By the way changing Lat Lng field type to varchar helps but not sure if this won't deliver further problems – Europeuser Feb 19 '23 at 09:17
  • @Europeuser no that makes no sense, since in the most cases you like to work calculate with the data – Thallius Feb 19 '23 at 09:23
  • @Europeuser You might want to check https://stackoverflow.com/questions/588004/is-floating-point-math-broken – Progman Feb 19 '23 at 12:14

1 Answers1

2

Since data type of your lat and lng columns is float which is not a precision data type, so comparing on this data type often lead to unexpected result. It's better to use some tolerance when comparing values of these float columns.

 SELECT id 
 FROM addresses 
 WHERE ABS(lat - 42.5632172) <= 0.0000001 
       AND ABS(lng - 27.5266380) <= 0.0000001

But it's much better if you could change data type of your columns to precision number data type, such as DECIMAL.

See demo here.

Trung Duong
  • 3,475
  • 2
  • 8
  • 9