25

I'd like to create a new database in PostGIS, so I can load stuff into it while the current database is being used. According to the docs

Some packaged distributions of PostGIS (in particular the Win32 installers for PostGIS >= 1.1.5) load the PostGIS functions into a template database called template_postgis. If the template_postgis database exists in your PostgreSQL installation then it is possible for users and/or applications to create spatially-enabled databases using a single command.

In my case this appears not to be so:

$ createdb -T template_postgis my_spatial_db
createdb: database creation failed: ERROR:  template database "template_postgis" does not exist

In the past I have messed around with copying the primary gis database, then deleting the contents of all the tables. There must be a better way. What do you do if you accidentally drop it?

RyanKDalton
  • 23,068
  • 17
  • 110
  • 178
Steve Bennett
  • 5,682
  • 2
  • 44
  • 69
  • See http://gis.stackexchange.com/questions/19432/why-does-postgis-installation-not-create-a-template-postgis – Mapperz Sep 11 '13 at 01:05

3 Answers3

46

I don't know what version of PostGIS you are using but on >2.0 I first login using psql:

psql -U postgres

Then I create a database:

CREATE DATABASE example_gis;

Then I move into this database:

\connect example_gis;

And then I run the commend:

CREATE EXTENSION postgis;

This creates all the spatial functions, and object types in this database.  

mike42
  • 103
  • 2
djq
  • 16,297
  • 31
  • 110
  • 182
  • 1
    at my system, I need to write all upper case CREATE EXTENSION POSTGIS rather than CREATE EXTENSION postgis. – Learner Dec 20 '16 at 08:35
5

Following @novicegis's link, this worked for me with postgis 1.5:

db=gis
sudo -su postgres <<EOF
createdb --encoding=UTF8 --owner=ubuntu $db
psql -d $db -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
psql -d $db -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
psql -d $db -f /usr/share/postgresql/9.1/contrib/postgis_comments.sql
psql -d $db -c "GRANT SELECT ON spatial_ref_sys TO PUBLIC;"
psql -d $db -c "GRANT ALL ON geometry_columns TO ubuntu;"
psql -d $db -c 'create extension hstore;'
EOF

(The linked instructions didn't include the 'hstore' extension.)

Steve Bennett
  • 5,682
  • 2
  • 44
  • 69
2

You should create "template_postgis" in console. All errors are displayed in the console.

You can use this instructions: http://linfiniti.com/2012/05/installing-postgis-2-0-on-ubuntu/ if you want to create "template_postgis".

For example, I do:

//install postgis
su oleg
sudo apt-add-repository ppa:sharpie/for-science  
sudo apt-add-repository ppa:sharpie/postgis-nightly
sudo apt-get update
sudo apt-get install postgresql-9.1-postgis

// create template
sudo su
su postgres
createdb -E UTF8 template_postgis2
createlang -d template_postgis2 plpgsql
psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis2'"

psql -d template_postgis2 -f /usr/share/postgresql/9.1/contrib/postgis-2.1/postgis.sql
psql -d template_postgis2 -f /usr/share/postgresql/9.1/contrib/postgis-2.1/rtpostgis.sql
psql -d template_postgis2 -c "GRANT ALL ON geometry_columns TO PUBLIC;"
psql -d template_postgis2 -c "GRANT ALL ON geography_columns TO PUBLIC;"
psql -d template_postgis2 -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"
createdb osm -T template_postgis2

I got this message when I installed postgis with errors

novicegis
  • 665
  • 2
  • 13
  • 29