Saturday, July 28, 2012

Working with Oracle's geometry column directly from active record

On the company, we develop with Ruby on Rails framework. We create GIS web applications, awesome applications well indeed ;) . Due to some integration and standard issue, three years ago, company migrated from PostgreSQL to Oracle Database.

PostgreSQL has a brilliant support in Rails community and there are excellent integration with geometry types, too; but Oracle's support isn't very promising.
In company, we repeatedly need to create geometry objects and store them in database. It was so easy in PostgreSQL with help of PostGIS addon; the Postgres' active record adapter did it like a charm. We could manage to create and save our geometry objects like this:
It was completely easy, we worked with our geometry object like any other objects with no exception. The problem aroused, when we moved to Oracle. (Trust me, It was not my choice at all ;) )

There is no support for geometry types in oracle enhanced adapter; the adapter needed to communicate with an Oracle database; so for every query involved with Geometry types, we directly connected to database and executed raw SQL to retrieve, insert or update data.

It was a pain in the neck. Sometimes, the queries were extremely lengthy. We wanted our former approach. we desired to easily interact with active_record and create and save objects directly without any raw SQL.

The question was how?

There was an spatial adapter which could handle creating oracle tables with geometry columns through rails migrations. I used it, it helped me a great deal in managing geometry types from migrations.
But, directly persistence of objects was still indefeasible. I forked default oracle enhanced adapter for active record and hacked it until I found out how to handle the situation.
The result gem is now available in github. you can use it in your rails program to directly save your ruby objects involving Oracle's SDO_Geometry types to Oracle DB.
Instead of including default oracle enhanced adapter in your Gemfile, you can include my forked version.

The process is described in this stackoverflow thread.

I hope it helps you all in handling Oracle geometry types directly from active record.