views:

804

answers:

2

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.

+2  A: 

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.

hopeless
Cool, I had seen geokit, but for some reason had it stuck in my head that I had to store locations as spatials instead of lat/longs. I'm using ym4r_gm for the display package, which takes lat/long anyways, so these should play nice together.
ajhit406
hopeless
i can't seem to find the geokit plugin? script/plugin install git://github.com/andre/geokit-rails.git is returning "Plugin not found"...? Is this a git hosting issue maybe?
ajhit406
Nevermind, I updated rails and tried using an http protocol instead and it worked.
ajhit406
A: 

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.

Beffa