= PostGIS Tutorial (Grundlagen) =

:E-Mail: [mailto:uwe.dalluege@rzcn.haw-hamburg.de  uwe.dalluege@rzcn.haw-hamburg.de ]

* unterliegt der [[GNU General Public Licence]] [http://www.gnu.org/copyleft/gpl.html http://www.gnu.org/copyleft/gpl.html]  

Um die Funktionalität von PostGIS besser demonstrieren zu können, werden hier das Tool [[pgAdmin III]] und das Programm [[OpenJUMP]] verwendet. Mit pgAdmin III kann man unter anderem eine Verbindung zum Datenbankserver aufbauen, Datenbanken verwalten, Tabellen darstellen oder auch SQL-Anweisungen ausführen.  

OpenJUMP ist ein Geoinformationssystem, mit dem man unter anderem auch PostGIS-Tabellen darstellen kann. Eine Beschreibung zu OpenJUMP und PostGIS-Anbindung finden Sie in dem OpenJUMP Tutorial (Grundlagen) in Kapitel 10.

Dieses Tutorial setzt Grundkenntnisse in SQL und OpenJUMP voraus!

== Verbindung zum Datenbankserver aufbauen ==

[[Image:Yellow.jpg|thumb|400px|Die Tabellen befinden sich unter dem Schema public]]

Unter Tables findet man die eigenen Tabellen und zwei Systemtabellen von PostGIS mit den Namen geometry_columns und spatial_ref_sys. In der Tabelle geometry_columns werden die Tabellen verwaltet, die mit Hilfe von PostGIS-Funktionen erstellt wurden. In der Tabelle spatial_ref_sys stehen Projektionsparameter für die Transformation der Geometrien.

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

SQL-Anweisungen werden in einem separaten Fenster (Query Tool) eingegeben, das über Tools>Querytool oder mit aufgerufen wird. Das Fenster teilt sich in ein EDITOR-Fenster (oberer Bereich) und ein Informationsfenster auf. Im EDITOR-Fenster können SQL-Anweisungen eingegeben, korrigiert und gespeichert werden. Im Informationsfenster stehen die Ergebnisse der Anfrage an die Datenbank. Es können ein oder mehrere SQL-Anweisungen eingegeben werden, die mit Query>Execute oder über   ausgeführt werden. Alle Eingaben können über File>Save as... in eine Datei gespeichert werden und mit File>Open geladen werden.

Mit Hilfe der PostGIS Funktionen können Geometrien erzeugt und auf bestehende Geometrien Analysen durchgeführt werden. Viele Funktionen basieren auf den OpenGIS Spezifikationen die im OpenGIS-Dokument „OpenGIS Simple Features Specification For SQL“ beschrieben sind. Anhand von Beispielen werden einige Funktionen beschrieben und angewendet. Eine vollständige Beschreibung der Funktionen befindet sich einmal im PostGIS Manual und im oben erwähnten OpenGIS-Dokument.  

Es sind drei verschiedene Basis-Geometrietypen definiert: POINT (Punkt), LINESTRING (Linienzug) und POLYGON (Fläche). Von diesen Basistypen sind vier weitere Geometrietypen abgeleitet: MULTIPOINT, MULTILINESTRING, MULTIPOLYGON und GEOMETRYCOLLECTION.  

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Geometrietypen (Quelle: JUMP, Technical Report)

Ein Punkt (Point) wird im WKT Format wie folgt in einer Tabelle gespeichert: 'POINT ( 122.123 376.985 )'

Neben den Koordinaten muss auch das räumliche Bezugssystem (Spatial Referencing System, SRS) angegeben werden. In der Tabelle spatial_ref_sys sind Informationen über verschiedene räumliche Bezugssysteme gespeichert, die über einen Schlüssel angesprochen werden. Dieser Schlüssel wird Spatial Reference System Identifier (SRID) genannt. Z.B. haben wir für eine Gauß-Krüger Projektion im 3. Streifen einen SRID von 31467.

Eine Tabelle kann Sachdaten und Geometriedaten enthalten. Die Tabellenspalten für die Sachdaten werden mit einer normalen SQL CREATE-Anweisung festgelegt. Die Spalte für die Geometriedaten muss mit der PostGIS-Funktion (OpenGIS) AddGeometryColumn ( ) erzeugt werden, wobei es nur eine Geometriespalte pro Tabelle geben kann. Es sind also zwei Schritte notwendig, um eine Tabelle mit einer Geometriespalte zu erzeugen:

* CREATE TABLE ...

* SELECT AddGeometryColumn ( ... )

Die Funktion AddGeometryColumn ( ) kann mit verschiedenen Parametern aufgerufen werden:

AddGeometryColumn ( Schemaname, Tabellenname, Spaltenname, SRID, Geometrietyp, Dimension )

AddGeometryColumn ( Tabellenname, Spaltenname, SRID, Geometrietyp, Dimension )

SchemanameVARCHARDas Schema, unter der die Tabelle erstellt werden soll

TabellennameVARCHARName der Tabelle

SpaltennameVARCHARName der Geometriespalte

SRIDINTEGERSpatial Reference System Identifier, z.B. 31467 für Gauß-Krüger oder -1 wenn nicht gesetzt.

GeometrietypVARCHARz.B. 'POINT', 'LINESTRING', 'POLYGON'

DimensionINTEGERDimension der Punkte (2 oder 3)

Dann erzeugen wir die Geometriespalte mit der SELECT-Anweisung und der Funktion AddGeometryColumn ( ):

== Beispiel LINESTRING: ==

Es soll eine Tabelle mit Straßen und Straßennamen erstellt werden. Die Straßen sollen als LINESTRING gespeichert werden.

* CREATE TABLE strassen ( ps INTEGER, name VARCHAR );

Es soll eine Tabelle mit Grundstücken und Eigentümern erstellt werden. Die Grundstücke werden als POLYGON gespeichert.

* CREATE TABLE baeume ( ps INTEGER, name VARCHAR );

* SELECT AddGeometryColumn ( 'baeume', 'geom', -1, 'POINT', 2 );

oder

* CREATE TABLE strassen ( ps INTEGER, name VARCHAR );

* SELECT AddGeometryColumn ( 'strassen', 'geom', -1, 'LINESTRING', 2 );

* CREATE TABLE grundstuecke ( ps INTEGER, eigentuemer VARCHAR );

* SELECT AddGeometryColumn ( 'grundstuecke', 'geom', -1, 'POLYGON', 2 );

erstellt wurde (siehe Kapitel 2.2.1), sollen Daten in die Tabelle geschrieben werden. Die Geometriedaten sollen im WKT-Format gespeichert werden. Dazu benötigen wir die Funktion GeometryFromText ( ).

GeometryFromText ( text, SRID )

== Beispiel POINT ==

* INSERT INTO baeume VALUES ( 1234, 'Eiche', GeometryFromText ( 'POINT ( 10 20 )', -1 ) );

Hinweis:

* INSERT INTO strassenVALUES ( 4567, 'Hofweg', GeometryFromText ( 'LINESTRING ( 30 35, 45 57, 60 83 )', -1 ) );

* Die Koordinaten eines Punktes sind durch ein Leerzeichen getrennt!  

In die Tabelle grundstuecke (siehe Kapitel 2.3) sollen der Primärschlüssel, der Eigentümer und die Koordinaten des Grundstücks P1 = ( 75, 20 ); P2 = ( 80, 30 ); P3 = ( 90, 22 ) P4 = ( 85, 10 ); P5 = P1 = ( 75, 20 ) eingefügt werden.  

* Die Koordinaten eines Punktes sind durch ein Leerzeichen getrennt!  

* Die Koordinaten des Polygons stehen in zwei öffnenden und zwei schließenden Klammern.  

Durch die Funktion AddGeometryColumn ( ) wird eine Datenzeile in die Tabelle geometry_columns geschrieben, die Informationen für PostGIS über die neu angelegte Tabelle enthält. Würde man diese neu angelegte Tabelle mit der SQL-Anweisung DROP TABLE tabellenname löschen, würde die Systemtabelle geometry_columns nicht aktualisiert werden. Die entsprechende Datenzeile würde nicht gelöscht werden! Eine Tabelle mit einer Geometriespalte muss daher mit der Funktion DropGeometryTable ( ) gelöscht werden.

DropGeometryTable ( Schemaname, Tabellenname )

Die Tabelle baeume soll gelöscht werden:

* CREATE TABLE baeume ( ps INTEGER, name VARCHAR );

soll die Tabelle in OpenJUMP dargestellt werden. Eine ausführliche Beschreibung zur Konfiguration von OpenJUMP und PostGIS finden Sie im OpenJUMP Tutorial (Grundlagen) Kapitel 10.

[[Image:Yellow.jpg|thumb|400px|Die Tabelle baeume aus der Datenbank db_s1234567 soll geladen werden]]

Mit RechtsKlick auf den Layernamen und View/Edit Attributes werden die Attribute der Tabelle baeume angezeigt.

== Erfassung von Daten ==

Geometrien und Sachdaten, die in OpenJUMP erfasst wurden, können in PostGIS-Tabellen gespeichert werden. Hierbei wird jeder Layer in einer eigenen Tabelle gespeichert. Eine ausführliche Beschreibung zur Konfiguration von OpenJUMP und PostGIS finden Sie im OpenJUMP Tutorial (Grundlagen) Kapitel 10.

Schema mit Primärschlüssel (ps) des Layers Gebaeude

Vor dem Sichern der Tabelle muss der Spatial Reference System Identifier (SRID) (siehe Kapitel 2.2.1) für den zu sichernden Layer gesetzt werden. Mit Rechtsklick Layername>Change SRID wird der Wert gesetzt.

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Mit File>Save dataset as... wird der markierte Layer in einer PostGIS-Tabelle gesichert.

Hinter Unique Column: steht der Spaltenname des Primärschlüssels (hier ps). Der zu sichernde Layername wird hinter Table: eingegeben (hier gebaeude).

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Layer gebaeude in einer PostGIS-Tabelle sichern

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

= Sichten (Views) =

In den bisherigen Beispielen wurden alle Datensätze einer Tabelle in OpenJUMP dargestellt. Es wurden z.B. alle Bäume der Tabelle baeume oder alle Gebäude der Tabelle gebaeude auf jeweils einem Layer dargestellt. Für eine sinnvolle Analyse ist das nicht ausreichend! Man möchte z.B. nur die Eichen sehen oder nur die Gebäude, die Herrn oder Frau Mayer gehören. Vielleicht möchte man auch alle Eichen sehen, die auf dem Grundstück von Frau Schulze stehen. In diesem Fall benötigt man Daten aus zwei oder mehreren Tabellen. Es soll also nur eine bedingte Sicht (View) auf die Tabellen dargestellt werden! Mit der SQL-Anweisung CREATE VIEW ... werden solche Sichten erstellt. Das Beispiel bezieht sich auf die Tabelle baeume (siehe Kapitel 3.1).

CREATE VIEW v_baeume ( Baumnamen ) AS

SELECT name, geom FROM baeume

WHERE name = 'Eiche';

( Baumname )= Spaltenname der ersten Spalte in der Sicht. Hier können mehrere Namen durch Komma  getrennt stehen.

In diesem Beispiel wird eine Sicht mit dem Namen v_baeume auf die Tabelle baeume erstellt, wo nur die Eichen von Interesse sind. Um die Tabelle in OpenJUMP darstellen zu können, darf natürlich die Geometriespalte (hier geom) hinter der SELECT-Anweisung der Sicht nicht fehlen. Die Sicht v_baeume kann man mit der SELECT-Anweisung anzeigen:

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Ergebnis der SELECT-Anfrage auf die Sicht v_baeume

Es wird nur ein Datensatz (Tupel) angezeigt, weil die Tabelle baeume nur eine Eiche enthält!

== Sichten in pgAdmin III ==

In pgAdmin III tauchen die Sichten (Views) nicht unter Tables, sondern unter Views auf:

= Verbund von Tabellen (Join) =

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Grundstückstabelle ohne direkte Eigentümer (grund_fs_nor)

Die Verknüpfung der beiden Tabellen erfolgt über Primär- (ps) und Fremdschlüssel (fs). Die Tabelle mit den Geometrien enthält als Fremdschlüssel den Primärschlüssel der Eigentümertabelle. Die Firma GlobalPlayer (ps = 30 der Eigentümertabelle) besitzt demnach 2 Grundstücke ( ps = 100 und 300 der Grundstückstabelle)!

* SELECT e.name, e.ort, g.geom

WHERE e.ps = g.fs;

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Ergebnis der SELECT-Anfrage

Sollen diese Grundstücke in OpenJUMP dargestellt werden, muss eine Sicht (View; siehe Kapitel 4) erstellt werden:

SELECT e.name, e.ort, e.telef, g.geom

SELECT e.name, e.ort, e.telef, g.geom

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

== Längenberechnung Length ( ) ==

Die Funktion Length ( ) berechnet die Länge eines Linienzuges (LineString).

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Tabelle strassen_nor

Gesucht:  

* View mit Straßennamen, Längen und Geometrien (siehe auch Kapitel 4).  

Lösung 1:

* SELECT name, Length ( geom ) AS laenge FROM strassen_nor;

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

* SELECT Sum ( Length ( geom ) ) AS gesamtlaenge FROM strassen_nor;

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

== Abstand Distance ( ) ==

Die Funktion Distance ( ) berechnet den Abstand zwischen zwei Geometrien.

Distance ( geom1, geom2 ) : Double Precision

Beispiel: Von der Schule Lütjenmoor sollen die Entfernungen zu den Haltestellen ermittelt werden.

Gesucht: Die Entfernungen der Haltestellen zur Schule Lütjenmoor.

Lösung:

* SELECT s.name, h.linie, Distance ( s.geom, h.geom ) AS entfernung

WHERE s.name = 'Lütjenmoor'

== Flächenberechnung Area ( ) ==

Die Funktion Area ( ) berechnet den Flächeninhalt eines POLYGONS.

Beispiel:

Gegeben: Tabelle grundstuecke_nor mit vier Grundstücken:

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

* Die Fläche jedes Grundstücks.

* Die Gesamtfläche der Grundstücke der Familie Mayer.

* View mit Eigentümer, Flächen und Geometrien der Familie Mayer.

Lösung 1:

* SELECT eigentuemer, Area ( geom ) AS flaeche FROM grundstuecke_nor;

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Lösung 2:

* SELECT eigentuemer, Sum ( Area ( geom ) ) AS gesamtflaeche

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

SELECT eigentuemer, Area ( geom ), geom

WHERE eigentuemer = 'Mayer';

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

  SELECT * FROM v_mayer;

== Beispiel POINT: ==

Gegeben: Tabelle baeume_nor mit Bäumen und Kronendurchmesser:

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Gesucht: View mit Geometrien der Bäume in Abhängigkeit des Kronendurchmessers.

* CREATE VIEW v_buffer_baeume_nor ( name, krone ) AS

* An die Funktion Buffer ( ) wird die Geometrie der Bäume (hier POINT) und der Kronendurchmesser übergeben. Die von Buffer ( ) erzeugte Geometrie wird ein kreisförmiges Polygon mit dem Radius krone / 2. sein.

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

== Beispiel LINESTRING: ==

Gesucht: View mit Geometrie der Straße Kohfurth, die auf 20 m verbreitert werden soll.

Lösung:

SELECT name, SetSRID ( Buffer ( geom, 20. ), 31467 ) AS geom

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

== Schnittmenge Intersection ( ) ==

Intersection ( geom1, geom2 ) : GEOMETRY oder EMPTY

Beispiel:

Gegeben: Eine Tabelle mit dem Polygon der verbreiterten Straße (b_BerlinerAllee; Buffer der Straße) und eine Tabelle mit den Geometrien der Grundstücke (grundstuecke_nor).

Gesucht: Die Schnittmenge des Polygons mit den Grundstücken.

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Die Tabelle grundstuecke_nor enthält 4 Grundstücke mit den entsprechenden Geometrien. Die Tabelle b_BerlinerAllee enthält die Buffer-Geometrie. Wird eine SELECT-Anfrage über beide Tabellen ausgeführt (join), erhält man das Kreuzprodukt aus der Zeilenanzahl beider Tabellen, also 4 Ergebnisse. Zwei Geometrien müssen leer (EMPTY) sein, weil zwei Grundstücke nicht in der Schnittmenge liegen!

* SELECT Intersection ( g.geom, b.geom )

FROM grundstuecke_nor AS g, b_BerlinerAllee AS b;

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

* SELECT g.eigentuemer, SetSRID ( Intersection ( g.geom, b.geom ), 31467 ) AS geom

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Zur Darstellung in OpenJUMP erzeugen wir eine Sicht:

* CREATE VIEW v_intersection ( name, geom ) AS

SELECT g.eigentuemer, SetSRID ( Intersection ( g.geom, b.geom ), 31467 ) AS geom

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Die Funktion ConvexHull ( ) berechnet eine konvexe Hülle um eine oder mehrere Geometrien. Eine konvexe Hülle um eine Punktmenge ist das kürzeste Polygon, das diese Punktmenge umschließt. Spannt man z.B. ein Gummiband um die Punktmenge, so erhält man eine konvexe Hülle. Zur Veranschaulichung legen wir eine konvexe Hülle (rotes Polygon), um Geometrien vom Typ POINT:

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

geomGEOMETRYGeometrie, um die eine konvexe Hülle gelegt werdensoll.

Hinweis:

Soll eine konvexe Hülle um eine Menge von Punkten (POINT) gelegt werden, müssen die Punkte mit der Funktion GeomUnion ( geometry set ) zu einer Geometrie zusammengefasst werden.

Bei einem kleinen Wäldchen in Norderstedt sind nur die Bäume in einer Tabelle erfasst. Das Wäldchen soll eingezäunt werden. Gesucht ist der kürzeste Zaun und die eingezäunte Fläche.

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Gegeben: Eine Tabelle (waldgebiet1_nor) mit den Geometrien der Bäume.

Gesucht: Konvexe Hülle um die Bäume.

Die Punkte der Tabelle waldgebiet1_nor müssen erst mit der Funktion GeomUnion ( geometry set ) zu einer Geometrie zusammengefasst werden. Danach kann die konvexe Hülle berechnet werden. Dies kann alles in einer SQL-Anweisung geschehen.

* SELECT ConvexHull ( GeomUnion ( geom ) ) FROM waldgebiet1_nor;

Soll die Fläche berechnet werden, muss nur die Funktion Area ( ) aufgerufen werden:

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Leider kann man aus der konvexen Hülle (POLYGON) nicht direkt die Länge des Polygons mit der Funktion Length ( ) berechnen, sondern muss zuerst einen Linienzug (LINESTRING) aus dem POLYGON machen. Dazu dient unter anderem die Funktion Boundary ( ). Da die Funktion Boundary ( ) ein MULTIPOLYGON als Argument benötigt, muss die Funktion multi ( ) benutzt werden. Auf die Funktion Boundary ( ) und multi ( ) wird im Moment nicht näher eingegangen.

Die Zaunlänge um das Wäldchen kann also folgendermaßen ermittelt werden:

== Contains ( ) und Within ( ) ==

* Die Funktion Contains ( ) prüft, ob die erste Geometrie geom1 die zweite Geometrie geom2 enthält.  

* Die Funktion Within ( ) prüft, ob die zweite Geometrie geom2 die erste Geometrie geom1 enthält.

Contains ( geom1, geom2 ) : Boolean oder Within ( geom1, geom2 ) : Boolean

Beispiel : Gesucht sind alle Bäume, die auf privaten Grundstücken stehen.

Gegeben: Die Tabelle grundstuecke_nor mit den Geometrien und Eigentümern der privaten Grundstücke und die Tabelle baeume_nor mit den Geometrien und Attributen der Bäume.

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Gesucht: Alle Bäume, die auf den Grundstücken stehen.

Lösung mit Contains ( ):

Hier wird geprüft, ob die Geometrien der Grundstücke die Geometrien der Bäume enthalten.

[[Image:Yellow.jpg|thumb|400px|bbbbb]]

Lösung mit Within ( ):

* SELECT gr.eigentuemer, b.name

WHERE Within ( b.geom, gr.geom ) = TRUE;

Hinweis: Es muss darauf geachtet werden, ob die Prüfung sinnvoll ist. Punktgeometrien können z.B. niemals Polygone enthalten. Die WHERE-Bedingung wäre immer FALSE!

Das Oil & Gas Producers Surveying and Positioning Committee pflegt und veröffentlicht Parameter und Beschreibungen für Koordinatenreferenzsysteme. Diese Parameter werden unter einer Kennung zusammengefasst, dem Spatial Reference System Identifier (SRID). Diese Kennungen werden z.B. in OGC konformen Diensten (z.B. WMS) und in PostGIS verwendet und ausgewertet.

(Siehe auch OGC: „Coordinate Transformation Services“).

EPSG: 31466 = Gauß-Krüger, 2. Streifen

Die entsprechenden Dateien mit den Datensätzen (EPSG geodetic parameter dataset) können von der Seite [http://www.epsg.org/ http://www.epsg.org/] geladen werden.

Feature (Objekt):  

* Features sind abstrahierte Objekte der realen Welt. Zum Beispiel werden Straßen als Linienzüge, Gebäude als Flächen oder Bäume als Punkte abstrahiert und dargestellt.

In OpenJUMP hat jedes Feature ein räumliches Attribut (Geometrie) und keins oder mehrere nicht-räumliche Attribute (non-spatial attributs, Fachdaten, Sachdaten) z.B. Straßenname, Eigentümer, Baumhöhe.

(Lexikon der Geoinformatik, 2001)

* A geographic feature is „an abstraction of a real world phenomeon ... associated with a location relative to Earth“. A feature has spatial attributes (polygons, points, etc.) and non-spatial attributes (strings, dates, numbers). (JUMP Workbench User's Guide, 2004)

GeometryCollection: Zusammenfassung von mehreren Features auf einem Layer zu einem Feature.