I’m currently working on a project that has recently migrated their database to a PostGIS database, it’s a Postgres database with special Spatial/Geometrical extensions. Seems pretty straightforward until you realize the geometrical data is stored in this giant-ass column requiring those special functions to decode its contents. Those functions work great when executing straight SQL in a database client, but this is Rails.
So I started looking for anything that could help bridge the gap. I found this plugin: http://agilewebdevelopment.com/plugins/spatial_adapter. The main website is no longer available and, so, I’m assuming it’s no longer maintained. Oh Joy! I moved forward with the plugin, regardless. While I was searching for the plugin I also had to learn how to build databases slated for PostGIS functionality.
In addition to the standard createdb -U [user] [database name] there are three additional steps:
createlang -U [user] plpgsql [database name]psql -d [database name] -f lwpostgis.sqlpsql -d [database name] -f spatial_ref_sys.sql
That’s what I did to create a new development database so a database dump produced for me would correctly load. I also used the development database to research a few of the PostGIS functions in the database client.
All was going smoothly. I created a Model and fired up script/console. So far so good. I then decided to start creating some tests. That’s when the fun began. Not with the tests themselves but with the Spatial Adapter and, ultimately to the Database rake tasks and the PostgreSQL Adapter.
Spatial Adapter Change Number 1
I uncovered a tiny bug when I first tried to run rake test: the schema.rb that was generated was missing column type information for my open id tables. Specifically for the binary columns. I tracked this down to the Spatial Adapter not using the right Column type in order to generate the correct schema.rb definition. The PostgreSQLColumn knows how to convert a column of sql type bytea to a column of type binary for the schema definition. The adapter was using a plain Column type which didn’t know how to do that conversion.
Database rake task and PostgreSQLAdapter changes
But after solving that problem I was faced with another one: how to create the test database such that it was PostGIS-capable? Because this app has frozen Rails to 2.0.2 I was able to find the databases.rake file. I figured I had to intercept, or at the very least override portions of the chain of tasks used to re-create the test database. What I found was kind of strange to me. Inside the databases.rake file was a mixture of shell commands and delegation to ActiveRecord::Base.connection. My thinking is: Let the adapters do all the work since they should know what they need and how to perform the tasks. MySQL is generally handled this way. Postgres is not. I needed it to be so that I could add the necessary commands at the right time in the sequence.
So I changed databases.rake such that when the adapter is ‘postgresql’ delegate to ActiveRecord::Base.connection for create_database, drop_database, and recreate_database. create_database and drop_database were my first proofs-of-concept. I basically took the shell commands out of databases.rake and put them in postgresql_adapter.rb. I added the recreate_database since the commands inside databases.rake for db:test:purge were essentially the same as drop_database and create_database; I just moved the shell commands into the Adapter. I now have a databases.rake file that delegates to the Adapter for MySQL and PostgreSQL.
Spatial Adapter Change Number 2
Now that I had the commands to recreate the test database in the PostgreSQLAdapter and I knew the SpatialAdapter sat on top of that, I just had to override the recreate_database method and add the three additional commands. Easy-Peasy. Oh, I did put the additional sql scripts (steps 3 and 4 from above) in my db directory so that the shell command to execute them would work. Now I can do rake test and my test database will be created, PostGIS-ready, and my tests will get executed.
Next Steps
Application-wise: start writing some spatial/geometrical tests.
Changes-wise: produce some diff files(patches) for anyone who wants them. And here’s where I’m asking for suggestions. What would you do with the changes?
It was a lot of fun and hard work to go through the code and make the necessary changes. I’m glad I did it. I’m doubly-glad Ruby makes it so easy to locate where you need to make the changes. 
Update
Looks like I won’t be submitting my Rails changes. I looked up the files I changed in the Rails GitHub repository and the changes are already there; they’re not in 2.0.2 though. I also looked up the author of the Spatial Adapter on RubyForge. I joined the mailing list and asked about the status of development. While waiting on the reply I went ahead and created a Spatial Adapter git repository by forking an existing one. I made my changes. You can find my Spatial Adapter git repository here: http://github.com/melriffe/spatial_adapter/tree/master. If I hear back from the author I’ll let him know where he can find my changes. Ciao
Cheers
