This is a quick note about storing geographic coordinates in your database.
It actually holds true whether you’re using Rails or not, but that’s the context in which I became aware of the problem.
So, what’s the problem?
As you probably know, latitude and longitude values are floating-point numbers. Their precision is important: a slight change in the decimal part makes the coordinates point to a different place in the World.
Working on a friend’s project, here’s how the problem revealed itself:
# Event is a Rails model with latitude/longitude attributes
event = Event.last
Event.where(
latitude: event.latitude,
longitude: event.longitude
).count # => 0!!
I could not find back an Event record using its own latitude and longitude values!
However, the problem would not show for all events.
I quickly discovered the source of the issue in a migration:
add_column :events, :latitude, :float
add_column :events, :longitude, :float
You see, floats are really bad at storing geographic coordinates because they trade accuracy for performance.
What’s the alternative?
What should have been used instead is the decimal type:
change_column :events, :latitude, :decimal, { precision: 10, scale: 6 }
change_column :events, :longitude, :decimal, { precision: 10, scale: 6 }
This will ensure reliable coordinates values with up to 6 digits after the decimal point and up to 10 digits in total. These are Google’s recommendation from Maps documentation:
To keep the storage space required for your table at a minimum, you can specify that the lat and lng attributes are floats of size (10,6)
As soon as I changed these types, all my problems disappeared 🙂
