3

Let's say I have a rails model called Position and the two columns latitude and longitude (both defined as float). I have populated the model with some position values. Now I'm trying to find positions based on it's latitude:

positions = Position.where('positions.latitude = ?', 50.0)

returns nil. Even if I try this:

lat = Position.first.latitude
positions = Position.where('positions.latitude = ?', lat)

the result is nil. My database is mysql for production. The code above words in development (sqlite). My assumption is it has something to do with how the datatypes are handled but I dont get it. Anyone some ideas?

kernification
  • 511
  • 1
  • 5
  • 15

2 Answers2

5

Don't use FLOAT or DOUBLE when you need exact numeric values.

Read https://dev.mysql.com/doc/refman/8.0/en/problems-with-float.html

Instead, use DECIMAL or NUMERIC (they're synonyms).

See How accurately should I store latitude and longitude? for some guidance on the specific DECIMAL precision to use for latitude and longitude.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
1

This is maybe due to how floating point numbers are represented. It's not possible to compare them by using a normal equality comparison. Instead of checking for the exact match it's better to check if the number is in a range or round both numbers to some precision before comparing them.

Regarding the particular example with geographical coordinates (I assume this because latitude is used), I would recommend using decimal column type instead of float.

In case you would like to stick with floatcolumn, I can suggest checking if the value is in a range.

ABHINEET SRIVASTAVA
  • 29
  • 2
  • Also range did not work for me. You are right, i use geographical coordinates and the reason why i used float is the geocoder gem for rails uses float. – kernification Apr 22 '18 at 15:35