PostGIS Workshop

From Seven
(Redirected from PostGIS)
Jump to: navigation, search

Note: This workshop is totally outdated, for current tutorials please check out the PosGIS website!

For a short summary of what PostGIS is all about you may ask Arnulf to give you the PostGIS Introduction - or read through it by yourself, it is all linked and on the Web.

This workshop introduces to PostGIS, the spatial language extension of PostgreSQL.




The installation of PostgreSQL (there is a meta package called postgresql) and PostGIS (use the correct version from the sources, here postgresql-8.3-postgis) on Ubuntu (tested with 9.0.4) is straight forward:

sudo apt-get install postgresql
sudo apt-get install postgresql-8.3-postgis

Thats it. The Postgres installation will automatically also start the database cluster, so nothing else has to be done. For practical purposes it will help to give the postgres user inside PostgreSQL a password so that you can also log in from a remote server. To access the command line client psql first sudo bash, then su to postgres. From here you can set the postgres user's password. For this workshop we will simply use postgres. In the given example the system user's name is seven (that is me), ushuaia is the name of the host the I will be using in this workshop. The full set of commands then runs like this:

seven@ushuaia:~$ sudo bash
[sudo] password for seven: 
root@ushuaia:~# su postgres
postgres@ushuaia:/home/seven/$ pqsl
bash: pqsl: command not found
postgres@ushuaia:/home/seven/$ psql
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
      \h for help with SQL commands
      \? for help with psql commands
      \g or terminate with semicolon to execute query
      \q to quit
postgres=# \password postgres
postgres=# \q

If you create a separate PostgreSQL user with restricted privileges you have to edit the file /etc/postgresql/8.3/main/pg_hba.conf and add md5 as authentication mechanism if you want to allow remote logins with encrypted passwords. This is recommended for productive environments but we will not get into this in this workshop.


You can install PGAdmin3 if the command line is too elegant for you. PGAdmin3 is a powerful windows (mouse) based client front end for PostgreSQL.

sudo apt-get install pgadmin3

Installation on Microsoft Windows

For Installation on Windows boxes please use the corresponding installation packages provided by the PostGIS and PostgreSQL web sites.

Adding Data with PostGIS

You can use any standard SQL on a spatially extended database - plus a few hundred special GIS functions. In order to be able to access these functions the database has to be prepared.

Creating Database

Create database in UTF-8 encoding:

createdb -E UTF-8 test

Load language extension for PostgreSQL (required by PostGIS):

createlang plpgsql test

Add PostGIS functions, triggers and constraints (adjust path if necessary):

psql -d test -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql

Add coordinate system references functions, triggers and constraints (adjust path if necessary):

psql -d test -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql

Creating Spatial Table

First a normal database table is created:

CREATE TABLE "locations" (gid serial PRIMARY KEY, "my_name" varchar, "activity" varchar);

Then a special PostGIS function call adds the spatial column, it will later contain the geometry:

SELECT AddGeometryColumn(,'locations','the_geom','4326','POINT',2);

Adding Data

Inserting data is straight forward, except for the special field with the coordinates:

INSERT INTO locations VALUES(1, 'Daniel','Skidooing',GeometryFromText('POINT(-71.060316 48.432044)',  4326));
INSERT INTO locations VALUES(1, 'Arnulf','Motorbiking',GeometryFromText('POINT(7.088 50.736)',  4326));

Reading data

Use standard SQL:

SELECT the_geom FROM locations;

If you want to be able to read the coordinates directly they need to be converted with a PostGIS function call:

SELECT asewkt(the_geom) FROM locations;

Importing German Postal Codes

  • Download PLZ
  • use shp2pgsql to convert Shape to PostgreSQL/PostGIS SQL
  • use psql to load SQL file to PostgreSQL


Importing OSM

There is a fairly new tool to import PBF files and import to PostGIS.

Using PostGIS data in Quantum GIS

Read how to Install QuantumGIS on Ubuntu 9.0.4. Then add a new layer of type "PostGIS", the rest is self explanatory. Enjoy!

Using PostGIS data in MapServer

To use PostGIS data in MapServer simply add a CONNECTION tag to the MAP-file and exchange the DATA string with the SQL for PostGIS. Remember to add unique ids for the_geometry coumns and explicitly set a coordinate system references EPSG code if you use complex SQL clauses.

Document History

The presentation is based on Arnulf's 2005 workshop which has been thoroughly updated. The old version is still available for download.