Ruby BigDecimal and MySQL decimal precision mismatch

Question

I'm storing some latitudes and longitudes in a MySQL database using Rails 3.0.3. Here is a part of the migration I used to create the table (note the decimal value with a given precision):

create_table :dummies do |t|
  t.decimal :something, :precision => 13, :scale => 10
end

The following RSpec example should illustrate where it's going wrong.

I use a BigDecimal for some calculations and the resulting my_value is a number with a large precision (larger than the one specified in the migration). I store the object in the database and retrieve it again.

Comparing the original value with the database value fails as their precision is not the same so it is no longer the same number:

it 'should be equals before and after save' do
  my_value = BigDecimal('4.123456789') * 5000 # more precise than defined in the migration
  dummy = Dummy.new(:something => my_value)
  location.save!
  Dummy.first.something.should == dummy.something
end

I understand why this is happening (the precision of the decimal in MySQL != the BigDecimal one) but can anyone tell me how I can limit the precision of the my_value BigDecimal before I write it to the database to make sure it respects the database constraints?

Thanks!

Answer 1

In mySQL, use FLOAT for storing these geo-coordinates. It simply makes no sense to store them as high-precision decimal. Keep in mind that 1/60th of a degree of latitude is a nautical mile, so a foot (~ 1/3 of a meter) is around 3 microdegrees. (3e-06)

The epsilon for IEEE single precision floating point (the error) is about 6e-08

GPS and geocoding aren't that detailed, even when you're at 179.9996 degrees.

If you're making highly detailed 200-scale topo maps or something like that, you probably already know you have to use a high-precision projection like universal transverse Mercator or Lambert or some such, because you're beyond the limit of approximating the earth as a sphere. But, if you're doing a store-finder type of app, you don't need, don't want, and can't get, that kind of precision.

If you MUST use decimal in your Ruby program, convert to float before storing to mySQL.

Here's Randall Munroe's explanation of geocoordinate precision.