How to create a new 'gis' database in PostGIS?

Question

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?

• Mapperz: See gis.stackexchange.com/questions/19432/…

Answer 1

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.

• Learner: at my system, I need to write all upper case CREATE EXTENSION POSTGIS rather than CREATE EXTENSION postgis.

Answer 2

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.)

Answer 3

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

• Steve Bennett: That all worked with postgis 1.5 except there's no "rtpostgis.sql" file. Is it important?
• novicegis: I think, postgis 1.5 is the best way. link - official documentation