PostGIS is an extension to PostgreSQL that allows for geospatial queries. Here’s how to integrate it into your Rails app

This post is the first of a 2-part series about Rails and PostGIS. Read Part 2.

Installing PostGIS

First up, you’ll need to install the PostGIS extension.

On a Mac with Homebrew:

$ brew update          # fetch latest formulae
$ brew install postgis

This will install PostgreSQL (if you don’t have it already), PostGIS and the necessary dependencies

On Ubuntu 14.04:

$ sudo apt-get update
$ sudo apt-get install postgis postgresql-9.3-postgis-2.1

Integrating PostGIS with Rails

Once you’ve got everything installed, it’s time to integrate PostGIS into your Rails app. To achieve this, we’ll be using the activerecord-postgis-adapter gem, which provides a nice Rails wrapper around the PostGIS query functions and allows you to write geo-aware migrations – all without dropping down into SQL.

The adapter itself uses another gem called RGeo, which provides Ruby bindings to the C libraries GEOS and Proj. These libraries are what PostGIS uses itself internally, and means you can calculate distances and such in Ruby without having to hit the database. The gem provides all the geospatial primitives that are serialised in and out of the database for you when you declare a geospatial column.

The README for activerecord-postgis-adapter covers most of what you need to know, so refer to that to get yourself up and running.

Setup issues

Integrating activerecord-postgis-adapter into our existing app was mostly smooth sailing, but wasn’t without its issues. Here’s some we came across:

Rails 4.0 has a create_extension migration function you can use to include PostGIS in your db/schema.rb, but you may find you need to install the extension manually – particularly if you get errors relating to the absence of a geometry_column relation or a spatial_ref_sys table. Run the Postgres console (psql or rails dbconsole) and then run:

> CREATE EXTENSION postgis;

Error: “Cannot find SRID (4326) in spatial_ref_sys”

One particular error to watch out for is PG::InternalError: ERROR: GetProj4StringSPI: Cannot find SRID (4326) in spatial_ref_sys, which means you’ve probably truncated the internal table PostGIS uses to store information about different projections. In our case, database_cleaner was to blame.

To fix, first amend your (test|spec)_helper.rb to exclude the spatial_ref_sys table whenever truncation is mentioned. If you’re using Avdi Grimm’s database_cleaner setup with RSpec, here’s what you need to change:

Before:

config.before(:suite) do
  DatabaseCleaner.clean_with(:truncation)
end

# ...

config.before(:each, js: true) do
  DatabaseCleaner.strategy = :truncation
end

After:

config.before(:suite) do
  DatabaseCleaner.clean_with(:truncation, except: %w(spatial_ref_sys))
end

# ...

config.before(:each, js: true) do
  # This looks weird, but it is valid Ruby syntax,
  # since #strategy= is just a method call
  DatabaseCleaner.strategy = :truncation, { except: %w(spatial_ref_sys) }
end

You’ll then need to restore the contents of the spatial_ref_sys table from the SQL file that should be bundled with the PostGIS package you installed:

$ psql -d name_of_database -f path/to/spatial_ref_sys.sql

With Homebrew, this file is located at:

$ $(brew --prefix)/share/postgis/spatial_ref_sys.sql

For the Ubuntu package above, this file is located at:

/usr/share/postgresql/9.3/contrib/postgis-2.1/spatial_ref_sys.sql

Other Linux distros will vary based on the package, but some Googling around should help.

Deploying and CI

Since the activerecord-postgis-adapter gem requires you to specify postgis instead of postgresql as the adapter name in your config/database.yml, you may have issues with services that overwrite or change your config/database.yml file. Heroku, in particular, requires you to re-overwrite the database configuration in an initializer file, which is explained very well in their docs. A similar approach will probably work in other cases.

In our case, Codeship changes the name of the database that it connects to in the test environment to simply <adapter_name>_test. This works normally when using the postgresql adapter, but blew up when we switched to postgis, complaining that the database didn’t exist. We managed to fix it by adding:

bin/rake db:create

into our test setup commands, before bin/rake db:schema:load.

Finished!

Hopefully, you should now be fully set-up and ready to start integrating some geospatial data into your app. Next time we’ll cover migrations and choosing the right projection, as well as geocoding and using geospatial queries.

Photo by denise.weerke on Flickr