Setting up PostgreSQL + postgis for rails on ubuntu
I know there are plenty of guides out there for this, but this is to allow me to tie together all the relevant info I’ve found about this.
I already had postgres installed and configured so I just had to install the postgis extensions
sudo apt-get install postgresql-8.4-postgis
The following is adapted from this guide to use the correct locations for postgres 8.4
sudo su - postgres
createdb -E UTF8 template_postgis # Create the template spatial database.
createlang -d template_postgis plpgsql # Adding PLPGSQL language support.
psql -d osm -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d osm -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
cat <<EOS | psql -d template_postgis
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template_postgis';
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public.geometry_columns TO PUBLIC;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public.spatial_ref_sys TO PUBLIC;
GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON geography_columns TO PUBLIC;
GRANT ALL ON spatial_ref_sys TO PUBLIC;
VACUUM FULL FREEZE;
EOS
This guide shows how to setup your database.yml but you are probably better off following the advice from the spatial_adapter wiki. Your database.yml should look like this
test:
adapter: postgresql
encoding: utf8
database: myproject_test
template: template_postgis
Then you can create your db and perform any migrations with:
rake db:create
rake db:migrate
