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