Handling lat/long and mysql spatial points in rails

Question

There have been a decent amount of questions about mysql spatial datatypes, however mine is more specific to how best to deal with them within a rails MVC architecture.

I have an input form where an admin user can create a new point of interest, let's say, a restaurant and input some information. They can also input a human-readable latitude and longitude in decimal format.

However, for distance calculations, etc... I am storing the location data as a spatial point in the database.

My question therefore, is how to best handle this in the MVC architecture in rails?

Here are some ideas I had, but nothing really seems clean:

• Call :after_filter method that takes the new instance of the object and does a raw SQL update that handles the "GeomFromText('POINT(lat long)' ))" goodness. The issue with this is that "lat/long" would be text fields in my create form, although this disrupts the clean form_for :object architecture that rails provides since lat/long aren't really attributes, they're just there to let a human input values that aren't mysql spatials.

• Maybe creating a trigger in the db to run after a row insert that updates that row? I have no idea and it doesn't seem like these triggers would have access to the lat/long, unless I stored the lat/long as well as the spatial point, and then created the row in the db with the lat/long decimals, and then ran the trigger after creation to update the spatial. I guess i could also do that with an after_filter if I added the lat/long columns to the model.

Any other ideas? I think storing the lat/long is redundant since I'll really be using the spatial point for distance calculations, etc... but it might be necessary if I'm allowing for human editing.

Answer 1

Check out the geokit-rails plugin for Rails which does distance calculations using plain lat/lng columns as floats (and uses the geokit gem). However, if you'd like to use your database's geo-spatial abilities, GeoRuby supports the basic spatial features like Point or LineString as column types. I hope these help.

Answer 2

I agree with hopeless, geokit is nice, I use it too.

If you want to do it yourself, I would do an after_filter but externalize the update method to a thread. Like that you don't have a slow down while saving but still nice code and timely updated columns.

Triggers are not nice, the database should deliver data but not do logic.