Andre on Tech: Latitude and Longitude Columns in Rails Migrations

So you're building a map application in Rails? Awesome. What datatypes are you going to use to store latitude and longitude in your database? There are three options:

1. Just use Floats.

This may be the first thing you try. The migration looks like this:

class CreatePlaces < ActiveRecord::Migration
  def self.up
    create_table :places do |t|
      t.column :lat, :float 
      t.column :lng, :float
    end
  end

  def self.down
    drop_table :places
  end
end

Floats work, but their precision is limited. For example, if you geocode 101 Market st, San Francisco, you'll get latitude=-122.395899, longitude=37.793621. Store those values in the MySql table created by the above migration, and you'll get -122.396 and 37.7936 back out, thereby losing three decimal places of precision on your latitude. Still, they work fine if you're experimenting, and I use floats for some examples in my upcoming Rails/Gmaps book.

By the way, try this link if you want to see Google's XML output for geocoding this address. Yes, you can even use my personal Google API key embedded in this link -- I don't mind:
http://maps.google.com/maps/geo?q=101+market+st,94105.........

2. Alter a column using post-hoc SQL in your migration

Use this approach if you are running Rails 1.1.6, and need better precision than what Float provides. The migration looks like this:

  def self.up
    create_table :places do |t|
      t.column :lat, :float 
      t.column :lng, :float
    end
    execute("ALTER TABLE places MODIFY lat numeric(15,10);")
    execute("ALTER TABLE places MODIFY lng numeric(15,10);")
  end

This gives you full-on decimal precision, but now your migration is MySQL-specific and not very DRY. That's the breaks if you're on 1.1.6. You should really upgrade to Rails 1.2RCx and . . .

3. Use proper decimal-type migrations in Rails 1.2

  def self.up
    create_table :places do |t|
      t.column "lat", :decimal, :precision => 15, :scale => 10
      t.column "lng", :decimal, :precision => 15, :scale => 10
    end
  end

Much nicer! This doesn't work in 1.1.6 but was fixed in 1.2. The columns it generates is identical to the post-hoc ALTER TABLE we did earlier, but without the DB-specific ugliness or redundancy. By the way, see here if you want to learn about scale and precision in the DB.

In summary

use Rails 1.2 and :decimal, :precision => 15, :scale => 10 migrations if possible. Use floats if you're on 1.1.6 and just messing around. Use ALTER TABLE if you're stuck on Rails 1.1.6, and can stomach it.

Next: Four choices to Jumpstart your Rails App »

Comments

Johannes de Jong on Dec 29

Thanks Andre :-)

Luc Dubois on Dec 30

"drop_table :markers" should be "drop_table :places" I guess?
But otherwise, just what I needed right now. Thanks a lot!

Peter Hulst on Jan 08

What about using your database's geospatial support and the Georuby plugin?

I was surprised how easy it was to define a POINT column in my (mysql) database and read/write points using Georuby plugin. The major advantage of that approach (as I understand it), is that you can actually do geographical queries on your database (such as: 'return me all points within x distance of a given point').
PostgreSQL has excellent support for geospatial extensions, and it looks like even with the basic support in MySQL you could do some really cool stuff using these datatypes. I admit I haven't tried to use migrations with geospatial datatypes yet though.

Are you covering database geospatial extensions and georuby in your new book at all, or is there any reason why you would advice against using these?

Andre on Jan 21

Peter, I looked into geospatial support in MySQL, and was struck by the disparity between what the docs said it would do and the support that is actually implemented. Until the support matures in MySQL, I'm sticking with numeric datatypes.

GPS on Jan 19

Hi Andre,

Thanks for this. I've actually been using your book. It's been a wonderful resource.

I am working on a Rails, Google Maps app. I have several rows in my db (MySQL 5.0.27) with lat & lng fields.

change_column(:catches, :latitude, :decimal, :null => true, :precision => 15, :scale => 10)
change_column(:catches, :longitude, :decimal, :null => true, :precision => 15, :scale => 10)

example values: 43.621551514, -70.998718262

I encountered some peculiar behavior with CocoaMySQL. Changing the value of a lat field caused the lng field to be altered and vice-versa.

example values after lat field change: 41.1234567890, 2.0000000000

Any idea why this is happening?

Do you have any suggestions for GUI MySQL management tools?

Thanks again!

rails friend on Apr 16

I just found your post and I laughed about myself, because I have your book at home and could have found it there as well :-) Greets, Daniel