Installing PostGISLink to this heading#

Creating a spatial database with PostGIS 2.0 and PostgreSQL 9.1+Link to this heading#

PostGIS 2 includes an extension for Postgres 9.1+ that can be used to enable spatial functionality:

$ createdb  <db name>
$ psql <db name>
> CREATE EXTENSION postgis;

GeoDjango does not currently leverage any PostGIS topology functionality. If you plan to use those features at some point, you can also install the postgis_topology extension by issuing CREATE EXTENSION postgis_topology;.

Creating a spatial database template for earlier versionsLink to this heading#

If you have an earlier version of PostGIS or PostgreSQL, the CREATE EXTENSION isn’t available and you need to create the spatial database using the following instructions.

Creating a spatial database with PostGIS is different than normal because additional SQL must be loaded to enable spatial functionality. Because of the steps in this process, it’s better to create a database template that can be reused later.

First, you need to be able to execute the commands as a privileged database user. For example, you can use the following to become the postgres user:

$ sudo su - postgres

Once you’re a database super user, then you may execute the following commands to create a PostGIS spatial database template:

$ POSTGIS_SQL_PATH=`pg_config --sharedir`/contrib/postgis-2.0
# Creating the template spatial database.
$ createdb -E UTF8 template_postgis
$ createlang -d template_postgis plpgsql # Adding PLPGSQL language support.
# Allows non-superusers the ability to create from this template
$ psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis';"
# Loading the PostGIS SQL routines
$ psql -d template_postgis -f $POSTGIS_SQL_PATH/postgis.sql
$ psql -d template_postgis -f $POSTGIS_SQL_PATH/spatial_ref_sys.sql
# Enabling users to alter spatial tables.
$ psql -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;"
$ psql -d template_postgis -c "GRANT ALL ON geography_columns TO PUBLIC;"
$ psql -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"

These commands may be placed in a shell script for later use; for convenience the following scripts are available:

Afterwards, you may create a spatial database by simply specifying template_postgis as the template to use (via the -T option):

$ createdb -T template_postgis <db name>

PostgreSQL’s createdb failsLink to this heading#

When the PostgreSQL cluster uses a non-UTF8 encoding, the create_template_postgis-*.sh script will fail when executing createdb:

createdb: database creation failed: ERROR:  new encoding (UTF8) is incompatible
  with the encoding of the template database (SQL_ASCII)

The current workaround is to re-create the cluster using UTF8 (back up any databases before dropping the cluster).

Managing the databaseLink to this heading#

To administer the database, you can either use the pgAdmin III program (Start ‣ PostgreSQL 9.x ‣ pgAdmin III) or the SQL Shell (Start ‣ PostgreSQL 9.x ‣ SQL Shell). For example, to create a geodjango spatial database and user, the following may be executed from the SQL Shell as the postgres user:

postgres# CREATE USER geodjango PASSWORD 'my_passwd';
postgres# CREATE DATABASE geodjango OWNER geodjango TEMPLATE template_postgis ENCODING 'utf8';