Rails and PostGIS databases…

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:

1. createlang -U [user] plpgsql [database name]
2. psql -d [database name] -f lwpostgis.sql
3. psql -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

Looking for Ruby in Richmond?

I have a secret to share with you: I help run one of the coolest users group in the Richmond, VA, USA area.  The name of the group is Central Virginia Ruby Enthusiasts Group (CVReg).  It’s filled with some of the nicest, most passionate, technologically savvy people.  Our focus is in Ruby and the tools and frameworks that either sit on top of Ruby or co-exists with it.  We also dabble/demo/present on other dynamic language technologies; Smalltalk and Javascript being the most recent topics covered. You can get more information from Google (of course).  However, how about I just give you the link here?  CVReg.org

Cheers!

 

Starting to look at Ruby Waves…

Hey Krusty Krew! Just a quick note to let you that I’m staring to look at Ruby Waves. Why you may ask? Good question. Probably mostly to satisfy my ADD. However I love learning about new technology and this new web application framework for Ruby seems different enough from Rails to make interesting.

I really want to expand my Ruby knowledge and I feel I’ll get that chance with Waves. Not to say I couldn’t have achieved this with Rails. It’s just that Waves is just starting up; it’s good to be on the ground floor sometimes. Waves is also my impetus for getting into Git (I’ll also start posting my experiences with it).

I’m already thinking of a personal project to try with Waves. I strongly encourage you to install it and follow the tutorial. Expect more posts about Waves.

Cheers!

Whoa! Where did the time go…

A lot has happened since my last post: Gary Gygax died, (I’m linking to Wil Wheaton’s blog entry since it struck a chord with me), I’m learning Git (I know! About time, huh?), and I’ve been looking at Ruby Waves.

Back in the day I used to play D&D. In fact my first program was a dice-rolling application for my Vic20. I could roll-up some characters quickly. Crazy fun! I played until my early 20’s; after that I only played once. I hope Gary understood his influence. He changed many lives.

And onto Git: Thanks to a friend I now have a GitHub account. However I also have a Gitorious account. What can I say I signed up before I was told to get a GitHub account. It’s all new to me right now; exciting times! I have a long history with CVS and Subversion so I’m not new to SCM. It feels good learning something new.

However a heavy influencer in my getting some Git knowledge is Ruby Waves, a new (some would say next gen) Ruby web framework. It appears to be mostly glue around other open source offerings but that’s cool too. I like it now and plan to develop an application or two in it before giving my ‘official’ review.

Cheers!