Kurs PostGIS Einführung: Unterschied zwischen den Versionen
(→Uebungen) |
(→Uebungen) |
||
Zeile 80: | Zeile 80: | ||
== Uebungen == | == Uebungen == | ||
− | Hier einige Abfragen die mit dem Übungsdatensatz gemacht werden können: | + | Hier einige Abfragen die mit dem Übungsdatensatz gemacht werden können. Die Übungsdaten können Sie von [http://www.carto.net/papers/postgis/pgch_uebungsdaten.zip http://www.carto.net/papers/postgis/pgch_uebungsdaten.zip] herunterladen. Sie benötigen die shapefiles "gemeinden" und "fluesse" und das SQL File staedte_ch.sql |
=== Neue räumliche Tabelle erstellen und bevölkern === | === Neue räumliche Tabelle erstellen und bevölkern === |
Version vom 16. Oktober 2007, 19:29 Uhr
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:
Inhaltsverzeichnis
- 1 Programm (aktuell)
- 2 Software
- 3 Selbststudium
- 4 Uebungen
- 4.1 Neue räumliche Tabelle erstellen und bevölkern
- 4.2 Flächenberechnungen
- 4.3 Südlichste Geometrie ermitteln
- 4.4 Koordinatentransformation
- 4.5 Distanzberechnung
- 4.6 Kombinierte Distanzabfragen
- 4.7 Selektiere angrenzende Flächen mit ST_Touches()
- 4.8 Crosses(), Selektion von Flächen die von Linien geschnitten werden
- 4.9 Geomunion() und Crosses() Abfrage (MULTIPOLYGON und MULTILINESTRING)
- 4.10 Flächen zusammenfassen mit Geomunion()
- 4.11 Point in Polygon mit ST_Within()
- 4.12 Buffer() und Point in Polygon ST_Within() Abfrage
- 4.13 Anzahl Teilgeometrien ermitteln
- 4.14 Räumliche Beziehungen mit ST_RELATE
- 4.15 Suche Punkt entlang einer Linie mit Linear Referencing
- 5 Quellen
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:
- Ueb 1 Installation von PostgreSQL/PostGIS
- Ueb 2 PostgreSQL mit pgAdmin
- Ueb 3 PostGIS-Funktionen
- Ueb 4 CRS
- 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. Die Übungsdaten können Sie von http://www.carto.net/papers/postgis/pgch_uebungsdaten.zip herunterladen. Sie benötigen die shapefiles "gemeinden" und "fluesse" und das SQL File staedte_ch.sql
Neue räumliche Tabelle erstellen und bevölkern
-- 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);
Resultat: neue Tabelle Seen mit Index und 36 Datensätzen
Flächenberechnungen
-- 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;
Resultat: Bagnes, 23, 283; Davos, 18, 255; Zermatt, 23, 243; ... insgesamt 48 Datensätze (wenn Seen in vorherigem Schritt gelöscht)
Südlichste Geometrie ermitteln
-- Selektiere die südlichste Gemeinde des Kantons Graubünden -- kt id 18 ist Graubünden SELECT name FROM gemeinden WHERE kt = 18 ORDER BY ST_YMIN(ST_ENVELOPE(the_geom)) ASC LIMIT 1;
Resultat: Roveredo (GR)
Koordinatentransformation
-- 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';
Resultat: POINT(7.46666670742301 46.9166667102846)
Distanzberechnung
-- 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]');
Resultat: 96139.7469334825
Kombinierte Distanzabfragen
-- 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;
Resultat: Adligenswil, Vierwaldstättersee; Aecherli, Lungerer See; Aecherlis, Sarnersee; ...
Selektiere angrenzende Flächen mit ST_Touches()
-- Selektiere alle Gemeinden die an den Zürichsee grenzen SELECT name FROM gemeinden WHERE ST_TOUCHES(the_geom,(SELECT the_geom FROM seen WHERE name = 'Zürichsee')) ORDER BY name;
Resultat: 25 Records; Altendorf, Erlenbach (ZH, Freienbach, ...
Crosses(), Selektion von Flächen die von Linien geschnitten werden
-- Es sollen sämtliche Gemeinden abgefragt durch die der Fluss Emme fliesst SELECT g.name FROM gemeinden g, fluesse f WHERE f.name = 'Emme' AND f.the_geom && g.the_geom AND ST_Crosses(f.the_geom,g.the_geom) ORDER by name ASC;
Geomunion() und Crosses() Abfrage (MULTIPOLYGON und MULTILINESTRING)
-- 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));
Resultat: t
Flächen zusammenfassen mit Geomunion()
-- 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
Resultat: neue Tabelle mit 26 Kantonsgeometrien und räumlicher Index
Point in Polygon mit ST_Within()
-- Es soll abgefragt werden wieviele Städte/Orte in jedem Kanton liegen -- Voraussetzung ist, dass Sie Ihren Kantonen Namen vergeben haben 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;
Resultat: Aargau, 146; Appenzell Ausserrhoden, 20; Appenzell Innerrhoden, 8; ...
Buffer() und Point in Polygon ST_Within() Abfrage
-- Selektierte Orte die innerhalb eines Buffers von 10 km des Flusses Emme liegen SELECT "FULL_NAME" FROM staedte_schweiz WHERE ST_Within( the_geom, (SELECT ST_Buffer(the_geom,10000) FROM fluesse WHERE name = 'Emme')) ORDER BY "FULL_NAME" ASC;
Resultat: 144 Orte; Aedermannsdor, Aelgäu, Aeschau, Affoltern, ...
Anzahl Teilgeometrien ermitteln
-- Selektiere alle Kantone die aus 4 oder mehr Teilgeometrien bestehen SELECT name, ST_NumGeometries(the_geom) AS NrGeom FROM kantone WHERE ST_NumGeometries(the_geom) >= 4 ORDER BY name ASC;
Resultat: Bern, 4; Fribourg, 4; Solothurn, 4
Räumliche Beziehungen mit ST_RELATE
-- Es sollen sämtliche Seen abgefragt werden die im Kanton Bern sind oder an den Kanton Bern grenzen -- Voraussetzung ist, dass Sie Ihren Kantonen Namen vergeben haben -- 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;
Resultat 1: Brienzersee, innerhalb; Thunersee, innerhalb
Resultat 2: Lac de Neuchâtel, angrenzend; Bielersee / Lac de Bienne, angrenzend
Suche Punkt entlang einer Linie mit Linear Referencing
-- Selektiere die Gemeinde die sich 20 km vom Ursprung der Emme befindet -- Achtung, die Emme wurde offenbar von der Mündung bis zum Ursprung digitalisiert -- die Geometrie muss daher umgedreht werden -- ST_GeometryN() wird verwendet, da es sich um einen Multilinestring handelt SELECT name FROM gemeinden WHERE ST_WITHIN((SELECT ST_line_interpolate_point(ST_Reverse(ST_GeometryN(the_geom,1)),(20000 / ST_LENGTH(the_geom))) FROM fluesse WHERE name = 'Emme'),the_geom);
Resultat: Eggiwil
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.