Kurs PostGIS Einführung: Unterschied zwischen den Versionen

Aus Geoinformation HSR
Wechseln zu: Navigation, Suche
K (Weiterleitung nach Kurs PostGIS Einführung I erstellt)
 
(142 dazwischenliegende Versionen von 3 Benutzern werden nicht angezeigt)
Zeile 1: Zeile 1:
GISpunkt/UNIGIS-Seminar: Einführung in PostGIS/PostgreSQL (Kürzel Sem_PostGIS)
+
#redirect[[Kurs PostGIS Einführung I]]
 
 
  Nächste Durchführung: 30. Mai 2008, 9:00 bis 16:30 Uhr, PC-Raum 1.213 HSR.
 
 
 
Siehe auch:
 
* [http://gis.hsr.ch/index.php?option=com_content&task=view&id=230&Itemid=151 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:
 
* [[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/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 ===
 
# Ganzer Datensatz sowie ein 'Thema/Topic' (d.h. eine thematisch zusammengehörende Ebene/Layers)
 
# Umprojizieren (z.B. CH1903->WGS84)
 
# Als KML speichern
 
# Ausschnitt (Perimeter, definiert mittels BBox)
 
# Buffer rund um einen Ausschnitt
 
# Spatial Join (Overlay, Flächenverschnitt)
 
# 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/postgis/pgch_uebungsdaten.zip 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 ===
 
<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)
 
 
 
=== Südlichste Geometrie ermitteln ===
 
<pre>
 
-- 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;
 
</pre>
 
 
 
Resultat: Roveredo (GR)
 
 
 
=== 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; ...
 
 
 
=== Selektiere angrenzende Flächen mit ST_Touches() ===
 
<pre>
 
-- 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;
 
</pre>
 
 
 
Resultat: 25 Records; Altendorf, Erlenbach (ZH, Freienbach, ...
 
 
 
=== Crosses(), Selektion von Flächen die von Linien geschnitten werden ===
 
<pre>
 
-- 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;
 
</pre>
 
 
 
=== 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
 
-- 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;
 
</pre>
 
 
 
Resultat: Aargau, 146; Appenzell Ausserrhoden, 20; Appenzell Innerrhoden, 8; ...
 
 
 
=== Buffer() und Point in Polygon ST_Within() Abfrage ===
 
<pre>
 
-- 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;
 
</pre>
 
 
 
Resultat: 144 Orte; Aedermannsdor, Aelgäu, Aeschau, Affoltern, ...
 
 
 
=== Anzahl Teilgeometrien ermitteln ===
 
<pre>
 
-- 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;
 
</pre>
 
 
 
Resultat: Bern, 4; Fribourg, 4; Solothurn, 4
 
 
 
=== Räumliche Beziehungen mit ST_Relate() ===
 
<pre>
 
-- 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;
 
</pre>
 
 
 
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 ===
 
<pre>
 
-- 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);
 
</pre>
 
 
 
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]].
 
 
 
[[Kategorie:Weiterbildung]]
 

Aktuelle Version vom 17. Oktober 2013, 22:28 Uhr

Weiterleitung nach: