PostGIS – How to Create a New GIS Database in PostGIS

createdatabasepostgispostgresql

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?

Best Answer

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.  

Related Question