24

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?

42

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.  

  • at my system, I need to write all upper case CREATE EXTENSION POSTGIS rather than CREATE EXTENSION postgis. – SIslam Dec 20 '16 at 8: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.)

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.