Kurs PostGIS für Fortgeschrittene: Unterschied zwischen den Versionen

Aus Geoinformation HSR
Wechseln zu: Navigation, Suche
Zeile 28: Zeile 28:
* PostgreSQL Tools (psql, pgadminIII), PostGIS-Tools (shp2pgsql)
* PostgreSQL Tools (psql, pgadminIII), PostGIS-Tools (shp2pgsql)
* Kenntnisse eines GIS zur Visualisierung und zum Editieren von Geodaten
* Kenntnisse eines GIS zur Visualisierung und zum Editieren von Geodaten
Nicht behandelt:
* PostgreSQL Optimierung
* Lineare Referenzierung
* Routing
* Replikation
* Integration mit ArcGIS SDE
== Unterlagen/Übungen ==
== Unterlagen/Übungen ==

Version vom 25. Januar 2012, 16:16 Uhr

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

Fr. 20. Januar 2012, 9:00 bis ca. 17:00 Uhr, HSR.

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. "PostgreSQL Playing Cards" spielen. (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

Nicht behandelt:

  • PostgreSQL Optimierung
  • Lineare Referenzierung
  • Routing
  • Replikation
  • Integration mit ArcGIS SDE


Siehe auch Materialien im Kursordner.





Siehe sep. Installationsanleitung.



 CREATE OR REPLACE FUNCTION test.insert_update_test_trigger_function()
   RETURNS trigger AS
   myrec RECORD;
   NEW.flaeche := ST_Area(NEW.the_geom);
   NEW.umfang := ST_Perimeter(NEW.the_geom);
   SELECT array_to_string(array_agg(nz.zonenbez_gemeinde),'; ') 
   AS zonen into myrec FROM raumplanung.nutzungszonen nz WHERE ST_Intersects(NEW.the_geom,nz.the_geom);
   NEW.zonen := myrec.zonen;
 COST 100;

Tipps und Tricks


Restore... (pg_restore):

  • "File Option": Dateiendungen für Dumps sind ".backup"; wechseln Sie zu "All Files (*.*)", wenn der Dump einen anderen Namen hat. Dumps erkennt man daran...
  • "Restore Options #1" folgende Optionen, damit die Owner/Privileges Einstellungen ignoriert werden: "Don't save: Owner, Privilege, Tablespace".


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 (includes raster), and edit POSTGIS_INSTALL_DIR to adapt following variables: PGPASSWORD, THEDB, PGINSTALL (see listing below).
      • The only difference in these edits is one environment 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)
  • Finish: Restart system (in order to restore environment like paths to libraries).

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"

Creating new database 'Uster':

  • Preparation steps:
    • Close all postgres clients, if there are any open (like pgAdmin3, plsql, QGIS).
    • Cleanup database (if needed): If there is already a database 'uster' drop it!
  • Start pgAdmin3 as dbadmin
  • Create new database:
    • Name 'uster15', encoding: UTF-8, template: postgis15 (or postgis)
  • Select database 'uster15'
    • Activate extensions (right click) "New Object..." > "New Extension...":
    • Choose extensions: postgis, adminpack, ltree and plperl.

Importing data into database 'uster':

  • Start pgAdmin3 (as dbadmin)
  • Choose (right click) "Restore..."
    • Choose „uster.dump“ (All filese *.*)
    • Change „Restore Options #1“ with following 3 options in order to ignore Owner/Privileges: "Don't save: Owner, Privilege, Tablespace".
  • Import runs some minutes.

Importing another demo database:


  • tbd.