Kurs PostGIS Einführung

Aus Geoinformation HSR
Version vom 16. Oktober 2007, 17:52 Uhr von AndreasNeumann (Diskussion | Beiträge) (→‎Flächen zusammenfassen mit Geomunion())

Wechseln zu: Navigation, Suche

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:

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:

Software

Selbststudium

Teilnehmer:

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 ein „Thema“
  2. Umprojizieren
  3. Als KML speichern
  4. Ausschnitt
  5. Buffer rund um einen Ausschnitt
  6. Overlay

Uebungen

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

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)

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; ...

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

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.

Von «https://giswiki.hsr.ch/index.php?title=Kurs_PostGIS_Einführung&oldid=11016»