GISpunkt-Seminar: Einführung in PostGIS/PostgreSQL (Kürzel Sem_PostGIS)

Durchführung vom 12. Oktober 2007, 9:00 bis 16:30 Uhr.

Siehe auch:
* [http://gis.hsr.ch/index.php?option=com_content&task=view&id=230&Itemid=151 Programm und Anmeldung]
* [[PostgreSQL]] und [[PostGIS]]

== Programm (aktuell) ==

Programm:
* Kap.1 Einführung
* Kap.2 Geodatenmodell, Datenverwaltung und -Abfrage über SQL
* Kap.3 DB-Clients / Administration und Anlegen einer DB
* Kap.4 Datensicherung / Datenaustausch
* Kap.5 PostGIS-Schnittstellen / Datenkonvertierung
* Kap.6 PostGIS-Architektur
* Kap.7 PostGIS-Funktionen
* Kap.8 Anzeigen PostGIS-Daten mit QGIS und OpenJump
* Kap.9 PostGIS in UMN MapServer und SVG (Demo)
* Kap.10 Koordinaten-Referenzsysteme; Performance
* Kap.11 Selbststudium: Projekt
* Kap.12 Ausblick

Uebungen:
* [[Sem_PostGIS_Ueb_1| Ueb 1 Installation von PostgreSQL/PostGIS]]
* [[Sem_PostGIS_Ueb_2| Ueb 2 PostgreSQL mit pgAdmin]]
* [[Sem_PostGIS_Ueb_3| Ueb 3 PostGIS-Funktionen]]
* [[Sem_PostGIS_Ueb_4| Ueb 4 CRS]]
* [[Sem_PostGIS_Selbststudium| Ueb Selbststudium]]

== Software ==
* Browser
* TextPad o.ä.
* [[PostgreSQL]]/[[PostGIS]]
* [[OpenJUMP]]
* [[QGIS]]
* [[OGR]]

== Selbststudium ==
Teilnehmer:
* (Vorlage [[Sem_PostGIS Stefan]])
* Name des Seminarteilnehmers...

Organisatorisches:
* Das Ziel ist das Festigen des Gehörten und Erlernten. Der Auftrag ist, das selbständige Erstellen und Testen eines selbst ausgewählten Datensatzes mit dokumentierten Abfragen.
* Zeitumfang: 14h
* Abgabe: 1. November 2007 (Einträge im Wiki und Mail)
* Die wichtigsten Bewertungskriterien sind u.a.:
** Termintreue (gem. Abgabetermine);
** Vollständigkeit (gem. Lieferdokumente);
** Dokumentation (Umfang > 2 Seiten; Sprache/Verständlichkeit; Darstellung);
** Originalität (keine 1:1-Übernahme der Übungen).
* An ECTS angelehnte Bewertungs-Skala:
** Wertung 'sehr gut' für eine hervorragende Leistung;
** Wertung 'gut' für eine Leistung, die erheblich über den durchschnittlichen Anforderungen liegt.
** Wertung 'befriedigend' für eine Leistung, die durchschnittlichen Anforderungen entspricht.
** Wertung 'ausreichend' für eine Leistung, die trotz ihrer Mängel noch den Anforderungen genügt.
** Wertung 'nicht ausreichend' für eine Leistung, die wegen ihrer Mängel den Anforderungen nicht genügt und Verbesserungen erfordert, bevor sie anerkannt werden kann (Verbesserung gemäss sep. Abmachungen).
** Wertung 'nicht bestanden' für eine Leistung, die erhebliche Mängel enthält, dass sie nicht anerkannt werden kann.

Lieferdokumente:
* Eintrag in diesem Wiki (vgl. "Name des Seminarteilnehmers...") gemäss Vorlage [[Sem_PostGIS Stefan]]
* Projektdaten (möglichst komplett, lauffähige Scripts, etc.)
* Mail an Seminarleiter

Verzeichnis-Struktur:
* rawdata
* data
* queries

Queries:
# Ganzer Datensatz ein „Thema“
# Umprojizieren
# Als KML speichern
# Ausschnitt
# Buffer rund um einen Ausschnitt
# Overlay

== Uebungen ==

Hier einige Abfragen die mit dem Übungsdatensatz gemacht werden können:

=== Neue räumliche Tabelle erstellen und bevölkern ===
<pre>
-- Eine neue räumliche Tabelle "seen" soll erstellt und alle Seen von der Tabelle
-- "gemeinden" eingespielt werden. Danach soll ein räumlicher Index auf der neuen Tabelle
-- "seen" erstellt werden und die Seen aus der Tabelle "gemeinden" gelöscht werden

-- neue Tabelle kantone mit Primary Key erstellen
CREATE TABLE seen
(
gid integer PRIMARY KEY,
name text
);
-- räumliche Geometriespalte dazufügen
SELECT AddGeometryColumn('public', 'seen', 'the_geom', 21781, 'MULTIPOLYGON', 2);
-- Daten von Gemeinde hereinspielen wo kt = 0 (sind Seen), sollten 22 Datensätze sein
INSERT INTO seen
SELECT gid, name, the_geom FROM gemeinden WHERE kt = 0 AND gmde >= 9000;
-- Seen und liechtensteinische Gemeinden aus gemeindetabelle löschen
DELETE FROM gemeinden WHERE kt = 0;
-- neuen räumlichen index erstellen bei der tabelle seen
CREATE INDEX seen_the_geom_gist
ON seen USING gist (the_geom GIST_GEOMETRY_OPS);
</pre>

Resultat: neue Tabelle Seen mit Index und 36 Datensätzen

=== Flächenberechnungen ===
<pre>
-- Selektiere alle Gemeinden, die eine Fläche grösser als 100 km2 haben
SELECT name, kt, Round(ST_Area(the_geom) / 1000000) As flaeche FROM gemeinden WHERE (ST_Area(the_geom) / 1000000) > 100 ORDER BY flaeche DESC;
</pre>

Resultat: Bagnes, 23, 283; Davos, 18, 255; Zermatt, 23, 243; ... insgesamt 48 Datensätze (wenn Seen in vorherigem Schritt gelöscht)

=== Koordinatentransformation ===
<pre>
-- Die Koordinate von Bern (Tabelle staedte_schweiz), urspr. Landeskoordinate,
-- als Lat/Lon, WGS84 ausgeben
SELECT AsText(Transform(the_geom,4326)) FROM staedte_schweiz WHERE "FULL_NAME" = 'Bern';
</pre>

Resultat: POINT(7.46666670742301 46.9166667102846)

=== Distanzberechnung ===
<pre>
-- Spheroidische Distanz zwischen Bern und Zürich, basierend auf Bessel 1841 Ellipsoid
SELECT ST_distance_spheroid(
(SELECT Transform(the_geom,4326) FROM staedte_schweiz WHERE "FULL_NAME" = 'Bern'),
(SELECT Transform(the_geom,4326) FROM staedte_schweiz WHERE "FULL_NAME" = 'Zürich'),
'SPHEROID["Bessel 1841",6377397.155,299.1528128]');
</pre>

Resultat: 96139.7469334825

=== Kombinierte Distanzabfragen ===
<pre>
-- Selektiere Orte (Tabelle staedte_schweiz) und zugehörige Seen (Tabelle Seen) die innerhalb eines Radius
-- von 75 km von Bern sind und ein Ort nicht mehr als 5km von einem See entfernt ist (Tabelle Seen)
-- Achtung: kartesische Distanz, nicht ellipsoidische!
SELECT sta."FULL_NAME", se.name FROM staedte_schweiz sta, seen se
WHERE ST_DWithin(sta.the_geom,(SELECT the_geom FROM staedte_schweiz WHERE "FULL_NAME" = 'Bern'),75000) AND
ST_DWithin(sta.the_geom,se.the_geom,5000) ORDER BY sta."FULL_NAME" ASC, se.name ASC;
</pre>

Resultat: Adligenswil, Vierwaldstättersee; Aecherli, Lungerer See; Aecherlis, Sarnersee; ...

=== GEOMUNION und CROSSES Abfrage (MULTIPOLYGON und MULTILINESTRING) ===
<pre>
-- Prüfe ob die kombinierten Einzel-Linienelemente des Rheinflusses die Gemeindegeometrie von Basel durchlaufen
-- Resultat ergibt "t" (true) oder "f" (false)
SELECT ST_Crosses(
(SELECT the_geom FROM gemeinden WHERE name = 'Basel'),
(SELECT geomunion(the_geom) FROM fluesse WHERE name = 'Rhein' GROUP BY name));
</pre>

Resultat: t

=== Flächen zusammenfassen mit Geomunion() ===
<pre>
-- Eine neue räumliche Tabelle "kantone" soll erstellt und Gemeinden aus der Tabelle
-- "gemeinden" zu Kantonen zusammengefasst eingespielt werden. Danach soll ein räumlicher Index auf der neuen Tabelle
-- "kantone" erstellt werden

-- neue Tabelle kantone mit Primary Key erstellen
CREATE TABLE kantone
(
gid integer PRIMARY KEY,
name text
);
-- räumliche Geometriespalte dazufügen
SELECT AddGeometryColumn('public', 'kantone', 'the_geom', 21781, 'MULTIPOLYGON', 2);
-- Kantonsgeometrien von Gemeindegeometrien zusammenfassen mit Geomunion, gruppiert nach Kantons-ID "kt"
-- ST_Multi() erzwingt Multigeometrien wegen dem constraint, es sollten 26 Datensätze resultieren
INSERT INTO kantone
SELECT kt, '', ST_Multi(Geomunion(the_geom)) FROM gemeinden GROUP BY kt;
-- neuen räumlichen index erstellen bei der tabelle seen
CREATE INDEX kantone_the_geom_gist
ON kantone USING gist (the_geom GIST_GEOMETRY_OPS);
-- danach manuell die Kantonsnamen ergänzen, etwa in QGIS, Jump-GIS oder GDV Spatial Commander
</pre>

Resultat: neue Tabelle mit 26 Kantonsgeometrien und räumlicher Index

=== Point in Polygon mit ST_Within() ===
<pre>
-- Es soll abgefragt werden wieviele Städte/Orte in jedem Kanton liegen

SELECT k.name, COUNT(*) FROM
staedte_schweiz s, kantone k
WHERE ST_WITHIN(s.the_geom,k.the_geom) GROUP BY k.name ORDER BY k.name;
</pre>

Resultat: Aargau, 146; Appenzell Ausserrhoden, 20; Appenzell Innerrhoden, 8; ...

== Quellen ==
Bücher:
* PostgreSQL, Korry Douglas & Susan Douglas, Sams Verlag, 2003, ISBN 10 0735712573.
* PostgreSQL ge-packt, Peter Eisentraut, mitp Verlag, 2005, ISBN 3-8266-1493-3.

Dokumentation und Tutorien siehe [[PostgreSQL]] und [[PostGIS]].

[[Kategorie:Weiterbildung]]

Kurs PostGIS Einführung

2007-10-16T15:52:14Z

AndreasNeumann: /* Flächen zusammenfassen mit Geomunion() */

Kurs PostGIS Einführung

2007-10-16T15:51:54Z

AndreasNeumann: /* Uebungen */

PostGIS

2007-10-16T15:14:29Z

2007-10-15T17:04:42Z

AndreasNeumann: /* Uebungen */

Kurs PostGIS Einführung

2007-10-15T16:16:31Z

AndreasNeumann: /* Uebungen */