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

/usr/share/postgresql/9.1/contrib/postgis-1.5/

If you have found them, you can start.

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

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

and

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