1

the SQL statement below is returning a count of 0 but there records with the specific location_lat. What could be the problem, the column location_lat has a datatype of FLOAT.

select count("id") as total from customers where location_lat = "-29.7864"
Bharat
  • 2,441
  • 3
  • 24
  • 36
Stephen
  • 19
  • 5

1 Answers1

0

It's not a good idea to compare floating point numbers with equality. because floating point numbers don't always store the exact value that you think it stores.

Secondly, if you are storing geographic data, you really should be doing a distance look up. Fetch items with in a distance x meters from a particular location. That's because if you two two GPS readings at the very same spot two minutes apart the readings are likely to be different.

Last but not least you should upgrade to mysql 5.7 and use the spatial data types and functions.

Community
  • 1
  • 1
e4c5
  • 52,766
  • 11
  • 101
  • 134
  • Thank you @e4c5 that was very helpful, I'm using mysql 5.7 but I never new about spatial data types. select count("id") as total from customers where location_lat like '%-29.7864%' got the right answer – Stephen Jan 10 '17 at 11:47
  • glad to have been of help – e4c5 Jan 10 '17 at 11:55