Kurs PostGIS Einführung: Unterschied zwischen den Versionen

Version vom 12. Mai 2008, 11:04 Uhr

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

 Nächste Durchführung: 30. Mai 2008, 9:00 bis 16:30 Uhr, PC-Raum 1.213 HSR.

Siehe auch:

• Startseite, Programm und Anmeldung
• UNIGIS-Tag Schweiz
• 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:

• 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/innen: (Vorlage Sem PostGIS Stefan)

• Sem PostGIS Roger
• (Sem PostGIS Juliana)

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

1. Ganzer Datensatz sowie ein 'Thema/Topic' (d.h. eine thematisch zusammengehörende Ebene/Layers)
2. Umprojizieren (z.B. CH1903->WGS84)
3. Als KML speichern
4. Ausschnitt (Perimeter, definiert mittels BBox)
5. Buffer rund um einen Ausschnitt
6. Spatial Join (Overlay, Flächenverschnitt)
7. Eigene Query, d.h. eine Anfrage gemäss eigenem Vorschlag (optional als Zusatz)

Uebungen

Hier einige Abfragen die mit dem Übungsdatensatz gemacht werden können. Die Übungsdaten können Sie von http://www.carto.net/papers/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.