|
|
| (168 dazwischenliegende Versionen von 5 Benutzern werden nicht angezeigt) |
| Zeile 1: |
Zeile 1: |
| GISpunkt-Seminar: Einführung in PostGIS/PostgreSQL (Kürzel Sem_PostGIS)
| | #redirect[[Kurs PostGIS Einführung I]] |
| | |
| 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; ...
| |
| | |
| === Räumliche Beziehung mit ST_RELATE ===
| |
| <pre>
| |
| -- Es sollen sämtliche Seen abgefragt werden die im Kanton Bern sind oder an den Kanton Bern grenzen
| |
| | |
| -- Selektiere alle Seen die innerhalb des Kantons Bern liegen
| |
| SELECT s.name, 'innerhalb' AS Relation FROM seen s, kantone k WHERE k.name = 'Bern' AND ST_RELATE(s.the_geom,k.the_geom) = 'FF2F1F212' ORDER BY k.name;
| |
| | |
| -- Selektiere alle Seen die an der Grenze zum Kanton Bern liegen
| |
| SELECT s.name, 'angrenzend' AS Relation FROM seen s, kantone k WHERE k.name = 'Bern' AND ST_RELATE(s.the_geom,k.the_geom) = 'FF2F11212' ORDER BY k.name;
| |
| </pre>
| |
| | |
| == 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]]
| |