Kurs PostGIS für Fortgeschrittene

Aus Geoinformation HSR
Version vom 19. Januar 2012, 23:11 Uhr von Stefan (Diskussion | Beiträge) (Unterlagen/Übungen)

Wechseln zu: Navigation, Suche

GISpunkt-Seminar "PostGIS für Fortgeschrittene" (Kürzel Sem_PostGIS_FF).

Fr. 20. Januar 2012. Nicht zu verwechseln mit GISpunkt-Seminar PostgreSQL für Fortgeschrittene. Siehe auch:


  • 09:00-09:15 Einführung, Vorstellung. (sep. Gruppe (A) diejenigen, die installieren, (B) die schon installiert haben) (SK)
  • 09:15-10:00 (A): Installation inkl. Daten einlesen. (SK mit Helfer)
  • 09:15-10:00 (B): Teil 1. Daten einlesen, Teil 2. Selbsttest lösen (Repetition aus SQL und Kurs "Einführung in PostGIS). (AN+SK)
  • 10:00-10:15 Überblick PostGIS 2.0, inkl. Extensions, Datentyp Geography. (SK)
  • 10:15-10:30 SQL-Shell in QGIS. (AN)
  • 10:30-11:00 PAUSE
  • 11:00-11:45 "Spatial SQL Advanced" mit PostgreSQL: DE-9IM, Views (mit IDs), Trigger (Nachführung Fläche, Constraints im Leitungskataster), Rules (Updatable Views), Konsistenz-Checks. (AN)
  • 11:45-12:30 Neue Vektorgeometrie-Funktionen in PostGIS 2.0 mit Übung. (SK)
  • 12:30-13:30 MITTAGESSEN
  • 13:30-14:00 Übung komplexe räumliche SQL-Abfragen. (AN)
  • 14:00-15:00 PostGIS Raster mit Übung. (AN)
  • 15:00-15:30 PostGIS 3D und PostGIS Topologie. (SK)
  • 15:30-16:15 Ev. Performance-Optimierung, PostGIS-spezifischer Server-Setup (Konfiguration, Tablespaces, Clustering, Read-only-Optimierung). (SK)
  • 16:15-16:30 Ausblick (u.a. Routing). (SK)


  • Kurs "Einführung in PostGIS/PostgreSQL", bzw. Kenntnisse von SQL sowie Spatial SQL-Funktionen
  • PostgreSQL Tools (psql, pgadminIII), PostGIS-Tools (shp2pgsql)
  • Kenntnisse eines GIS zur Visualisierung und zum Editieren von Geodaten



Siehe auch Materialien im Kursordner.





Werden separat abgegeben.


Installation of PostgreSQL 9.1 (production, 32bit) with PostGIS 1.5 (production), PostGIS 2.0 Experimental (32bit) and QGIS:

  • Step 0: Overview of installation of PostgreSQL 9.1.x and PostGIS 1.5 Instructions (PDF)
  • Step 1: Installation of PostgreSQL 9.1.x (Download PostgreSQL for Windows 32bit)
    • NOTE FOR ALL USERS! Don't choose '64bit Build' - even if 64bit-CPU available (because of PostGIS 2.0 Experimental which is 32bit-only)
    • Make a note of the password. If PostgreSQL is used on your own laptop (localhost) and for educational purposes, we uses user 'postgres' with password 'postgres'
  • Step 2: PostGIS 1.5 (production, 32bit)
    • Use Stackbuilder from PostgreSQL postprocessing step (NOTE: Internet access is needed here)
  • Step 3: Installation of PostGIS 2.0 (experimental) (Windows 32bit, see weblink "For PostgreSQL 9.1 (compiled against PostgreSQL 9.1.0...")
    • Make a copy of the whole <<POSTGRE_DIR>>\bin to a backup directory since some PostGIS 1.5 executables will be replaced by PostGIS 2.0.
    • Unzip ZIP-file of PostGIS 2.0 to an temporary installation directory (=> <<POSTGIS_INSTALL_DIR>> = 'postgis-pg91-binaries-2.0.0svn/').
    • Follow instructions of the README file:
      • Make a copy of makepostgisdb_using_extensions.bat and makepostgisdb.bat, and edit POSTGIS_INSTALL_DIR to adapt following variables: PGPASSWORD, THEDB, PGINSTALL (see listing below).
      • The only difference in these edits is one enviironment variable: for makepostgisdb_using_extensions.bat its 'THEDB=postgis20_extension' and for makepostgisdb.bat its 'THEDB=postgis20'.
      • Open a CMD-Shell as administrator (NOTE FOR WINDOWS USERS! Choose cmd.exe from Start menu but run cmd.exe with right click on it 'Run as administrator'!)
      • In CMD-Shell run makepostgisdb_using_extensions.bat and makepostgisdb.bat
  • Step 4: ActivePerl
    • The database (Uster) needs pl/perl scripts inside PostsgreSQL db. We install ActivePerl (Windows-Version).
  • Step 5: Installation of QGIS (Quantum GIS) Stable Version and Trunk Version
    • There are two different installers. We use OSGeo4W (osgeo4w-setup.exe) (NOTE: Internet access is needed here)
      • When asked about the installation/runtime directory path, choose default (e.g. 'C:\OSGeo4W') on Windows. Usually you would install programs under 'C:\Program Files (x86)'. But there is evidence that some tools could be bothered by spaces in paths.
      • Choose "Advanced Installation" (use the newest version of each software => mostly the first click in the installation tree)
      • Category „Commandline Utilities“: setup: OSGeo4W Installer/Updater
      • Category „Desktop“: qgis, qgis-dev (NOTE: 'qgis-dev' means "Trunk"-Version)
      • Category „Libs“: psycopg2, pyqwt5-qt4, qt4-devel
    • NOTE: This installation includes by default Python (2.5.2)
  • Done.

File 'makepostgisdb_using_extensions.bat':

 REM this is an example of how to create a new db and spatially enable it using CREATE EXTENSION
 set PGPORT=5432
 set PGHOST=localhost
 set PGUSER=postgres
 set PGPASSWORD=postgres
 set THEDB=postgis20_extension
 set PGINSTALL=C:\Program Files (x86)\PostgreSQL\9.1
 xcopy bin\*.* "%PGBIN%"
 xcopy /I /S bin\postgisgui\* "%PGBIN%\postgisgui"
 xcopy /I plugins.d "%PGADMIN%\plugins.d"
 xcopy lib\*.* "%PGLIB%"
 xcopy share\extension\*.* "%PGINSTALL%\share\extension"
 "%PGBIN%\psql"  -d "%THEDB%" -c "CREATE EXTENSION postgis;"
 "%PGBIN%\psql"  -d "%THEDB%" -c "CREATE EXTENSION postgis_topology;"
 REM Uncomment the below line if this is a template database
 "%PGBIN%\psql" -d "%THEDB%" -c "UPDATE pg_database SET datistemplate = true WHERE datname = '%THEDB%';GRANT ALL ON geometry_columns TO PUBLIC; GRANT ALL ON spatial_ref_sys TO PUBLIC"

Tipps und Tricks


  • tbd.