Rails and PostGIS

If you’re using PostGIS in a Rails app, you will want to ensure that your “test” database contains the PostGIS functions and special tables every time it is created. The easiest way to do this is with a template database.

Creating a PostGIS Template Database

As your system’s “postgres” user, run “psql postgres” and paste the following into your psql client. You may need to change the paths to the PostGIS install scripts, depending on your installation.

-- Create a new database
CREATE DATABASE template_postgis;

-- Make it a template database
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis';

-- Connect to new database and install the pl/pgsql language
\c template_postgis

CREATE LANGUAGE plpgsql;

-- Install PostGIS (your file paths may vary)
\i /opt/local/share/postgresql84/contrib/postgis-1.5/postgis.sql 
\i /opt/local/share/postgresql84/contrib/postgis-1.5/spatial_ref_sys.sql
GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON geography_columns TO PUBLIC;
GRANT ALL ON spatial_ref_sys TO PUBLIC;

-- vacuum freeze: it will guarantee that all rows in the database are
-- "frozen" and will not be subject to transaction ID wraparound
-- problems.
VACUUM FREEZE;

Updating your database.yml

Use the template attribute in your configuration to tell Rails to base your test database off of your new template:

test:
  adapter: postgresql
  encoding: utf8
  database: myproject_test
  template: template_postgis

(You will probably want to add this attribute to your development and production settings as well.)