Optimal column type for latitude and longitude on Rails and MySQL

Question

I'm wondering what's the best column type to store latitude/longitude on MySQL + Rails.

• Precision must be enough to store every bit of lat/lng degrees obtained from mobile devices and/or geocoders.
• Storage requirement should be minimal for the best query performance.

From Google's official document:

http://code.google.com/apis/maps/articles/phpsqlajax_v3.html

With the current zoom capabilities of Google Maps, you should only need 6 digits of precision after the decimal. 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). That will let the fields store 6 digits after the decimal, plus up to 4 digits before the decimal, e.g. -123.456789 degrees.

So, actually FLOAT(10,6) is recommended by Google.

However, with Rails 3, there seems no easy way to define FLOAT column type with precision after the decimal point. For instance, you could write a migration with raw SQL as follows:

def self.up
  execute <<-SQL
  ALTER TABLE places
    ADD `lat` FLOAT(10,6),
    ADD `lng` FLOAT(10,6)
  SQL
  add_index :places, [ :lat, :lng ]
end

But the schema.rb as a consequence will look like this:

t.float "lat", :limit => 10
t.float "lng", :limit => 10

which is missing the precision for the fractional part.

Here I can see several options:

• Use FLOAT(10,6) for optimal production performance, and don't dump schema (e.g. rake db:test:load) on development.
• Use DECIMAL(10,6), which is supported by Rails, but it takes 6 bytes, 1.5 times larger than FLOAT (see: http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html). Maybe this would be a good compromise?
• Use DOUBLE, which is much roomier than Google's requirement, and takes 8 bytes, 2 times larger than FLOAT. It's simple as well.

What's your recommendation?

Answer 1

KISS (i.e. submit to the framework), it's not a lot of bytes. It'll only matter more if you're using particular indexes.

Answer 2

Depending on the type of query you want to do on your data, you might want to look into the geometry type:

http://dev.mysql.com/doc/refman/5.1/en/creating-a-spatially-enabled-mysql-database.html

I'm not familiar with RoR, but this line, in particular:

add_index :places, [ :lat, :lng ]

... seems to indicate that you're planning to run geospatial queries. If so, the index will be useless for all intents and purposes, because a btree index won't help for nearest-neighbor point searches.