|
|
| (38 dazwischenliegende Versionen von 2 Benutzern werden nicht angezeigt) |
| Zeile 1: |
Zeile 1: |
| − | GISpunkt-Seminar '''"PostGIS für Fortgeschrittene"''' (Kürzel Sem_PostGIS_FF).
| + | #redirect[[Kurs PostGIS Einführung II]] |
| − | | |
| − | Fr. 20. Januar 2012. Nicht zu verwechseln mit [[GISpunkt-Seminar PostgreSQL für Fortgeschrittene]]. Siehe auch:
| |
| − | * [[GISpunkt-Seminar PostGIS]], [[GIS-Seminare]]
| |
| − | * [[PostGIS]] und [[PostgreSQL]]
| |
| − | | |
| − | == Programm ==
| |
| − | | |
| − | * 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)
| |
| − | | |
| − | Vorkenntisse:
| |
| − | * 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
| |
| − | | |
| − | == Software ==
| |
| − | | |
| − | * [[PostgreSQL]]/[[PostGIS]]
| |
| − | | |
| − | == Unterlagen/Übungen ==
| |
| − | | |
| − | Siehe auch im Kursordner.
| |
| − | | |
| − | == Daten ==
| |
| − | | |
| − | Werden separat abgegeben.
| |
| − | | |
| − | == Installation ==
| |
| − | | |
| − | 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 [http://geomatica.como.polimi.it/corsi/sw_gis/postgis_qgis_inst.pdf Instructions (PDF)]
| |
| − | * Step 1: Installation of PostgreSQL 9.1.x ([http://www.enterprisedb.com/products-services-training/pgdownload#windows 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) ([http://www.postgis.org/download/windows/experimental.php 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' and for makepostgisdb.bat its 'THEDB=postgis20_extension'.
| |
| − | *** 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 ([http://www.activestate.com/activeperl/downloads Windows-Version]).
| |
| − | * Step 5: Installation of QGIS (Quantum GIS) Stable Version and Trunk Version
| |
| − | ** There are two different installers. We use OSGeo4W ([http://download.osgeo.org/osgeo4w/osgeo4w-setup.exe osgeo4w-setup.exe]) (NOTE: Internet access is needed here)
| |
| − | *** 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.6?)
| |
| − | * 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
| |
| − | set PGADMIN=%PGINSTALL%\pgAdmin III
| |
| − | set PGBIN=%PGINSTALL%\bin\
| |
| − | set PGLIB=%PGINSTALL%\lib\
| |
| − | set POSTGISVER=2.0
| |
| − | 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" -c "CREATE DATABASE %THEDB%"
| |
| − | "%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"
| |
| − |
| |
| − | pause
| |
| − | | |
| − | == Tipps und Tricks ==
| |
| − | | |
| − | * siehe [[PostgreSQL - Tipps und Tricks]]
| |
| − | | |
| − | == Selbststudium ==
| |
| − | | |
| − | * tbd.
| |
| − | | |
| − | == Quellen ==
| |
| − | Siehe auch Register Materialien im Kursordner.
| |
| − | | |
| − | Online-Doku.:
| |
| − | * [http://www.postgresonline.com/downloads/special_feature/postgresql90_cheatsheet_A4.pdf Cheat Sheet "PostgreSQL 9.0"] (postgresonline.com)
| |
| − | * [http://www.postgis.us/downloads/postgis20_cheatsheet.pdf Cheat Sheet "PostGIS 2.0"] (postgis.us)
| |
| − | * [http://www.postgis.us/downloads/postgis20_topology_cheatsheet.pdf Cheat Sheet "PostGIS 2.0 Topology"] (postgis.us)
| |
| − | | |
| − | Literatur:
| |
| − | * "PostGIS in Action", Obe und Hsu, Manning. [http://www.postgis.us/page_buy_book Online order]
| |
| − | * http://wiki.hsr.ch/Datenbanken/DatenbankLiteratur
| |
| − | | |
| − | Support/Firmen:
| |
| − | * [http://wiki.hsr.ch/Datenbanken/PostgreSQLReferenzen Referenzen von PostreSQL-Installationen]
| |
| − | * [http://www.postgres-support.com postgres-support.com Schweiz/Deutschland/Österreich]
| |
| − | | |
| − | [[Kategorie:Weiterbildung]] [[Kategorie:PostGIS]] [[Kategorie:PostgreSQL]]
| |
