PostgreSQL PostGIS create template_postgis

If spatial data need to be stored in a database, usually PostgreSQL with its PostGIS-extension is the first choice.
In order to create databases that can work with geographic data, you must specify a template when creating the database, which copies the necessary extensions, data types and functions to the new database. This template is usually named template_postgis and is generated automatically, when the PostGIS extension is installed .
For Debian and Ubuntu – as I saw today – unfortunately not. There is indeed a special package ( postgresql-X.Y-postgis ), but the installation of this package does not produce the desired template.

So this needs to be done manually.

Find the necessary files
Fortunately, two SQL-files are included – postgis.sql and spatial_ref_sys.sql, which will do everything necessary. But first you have to find them.
So let’s search:

user@desktop:~$ find / -name postgis.sql

In Ubuntu for example, they are hidden in the directory


If you have found them, you can start.

If these files are not on your system, you can download them here. (~ 220 Kb)

Create the Database:

user@desktop:~$ sudo -u postgres createdb -E UTF8 template_postgis

Then, with

user@desktop:~$ sudo -u postgres createlang -d template_postgis plpgsql
PLPGSQL language support for the database should be added. For me, however was pointed out that this was already done.

Execute the two SQL-files

user@desktop:~$ sudo -u postgres psql -d template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql


user@desktop:~$ sudo -u postgres psql -d template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql

That’s it. Now with the command

createdb - T template_postgis DatabaseName

database can be generated, which can process geographic data.

3 Gedanken zu “PostgreSQL PostGIS create template_postgis

Schreibe einen Kommentar