PostGIS Workshop
From Seven
This workshop introduces to PostGIS, the spatial language extension of PostgreSQL.
- Downloads
- Slides as PDF
- Slides as ODT
- Slides as ugly OpenOffice HMTL export (bitmaps only)
- Paul Ramsey's PostGIS Spatial DB Tips
Contents |
Installation
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:~# 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 postgres@ushuaia:/home/seven/$exit
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.
PGAdmin3
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
Done!
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.
