Räumliches Aggregieren - Teil 4: Spatial SQL
Die Fragestellung 'Wieviele Punkte liegen in einer Fläche und wie sehen die Attributstatistiken aus?' kann mit ArcGIS auf unterschiedliche Weise beantwortet werden.
Im ersten Blog Räumliches Aggregieren - Teil1: ArcMap habe ich mir ArcMap angesehen.
Im zweiten Blog Räumliches Aggregieren - Teil 2: ArcGIS Online Map Viewer ist die Frage mit dem ArcGIS Online MapViewer beantwortet worden.
Im dritten Blog habe ich mich größtenteils von Nutzeroberflächen verabschiedet und habe das REST Interface der Dienste benutzt.
Jetzt bewege ich mich noch näher an die Datenhaltung und mache das Ganze direkt in der Datenbank mit SQL. Diesen Weg kann man nur wählen, wenn die Daten in einer eigenen Datenbank abgelegt sind. Bei in ArcGIS Online gehosteten (Feature) Daten kann man nicht direkt auf die dahinterliegenden Datenbank zugreifen.
Es gibt mit SQL verschiedene Wege um ans Ziel zu kommen, wir haben einen Workflow herausgegriffen und soweit möglich optimiert. Vielen Dank an Martin Förster, der die Formulierung der SQL Statements übernommen hat.
Die Aufgabenstellung und auch die Daten sind die gleichen wie in den anderen Teilen:
Aggregieren der Attribute einer Punktwolke in Flächen. Ich nehme als georeferenzierte Punkte EEG Daten (Erneuerbare Energie Anlagen) und als Flächen ein paar Gemeindegrenzen in Bayern. Die EEG Anlagen enthalten den Energieträger (Sonne, Bio,...) und die installierte Leistung in Kilowatt. Die Punkte und Flächen haben keine gemeinsamen Attribute und ich möchte für jede Gemeinde wissen, welcher Energieträger in der Gemeinde wie viel Gesamtleistung bringt.
Hintergrund: ArcGIS und Datenhaltung in RDBMS
Wenn man in ArcGIS Daten in einer RDBMS speichert, dann hat man verschiedene Wege. Man kann aus der Datenbankinstanz eine Geodatabase machen. Dafür wird ein eigenes Datenbankschema benötigt.
Dies braucht man z.B. für Multiuser Editing, Versionierung, Historisierung,...
Es ist aber auch möglich einfach nur mit Tabellen (oder Views) zu arbeiten. Das benötigt dann kein spezielles Schema, man hat dann aber auch nur Simple Features und keinen Zugriff auf die oben erwähnten Funktionen.
Beim Datentyp für die Geometrien kann man entweder den Datenbankeigenen Typ nehmen (z.B. SDO_Geometry bei Oracle) und den Esri Typ ST_Geometrie. Der Esri Datentyp hat den Vorteil, dass die SQL Funktionen gegen diesen Typ in allen Datenbanken identisch sind. Wer mehr über ArcGIS und Databanken wissen will: Geodatabase Administration
Schritt 1: Daten in die Datenbank laden
In unserem Beispiel nutzen wir eine Oracle 11 Datenbank (weil sie gerade da ist) und als Typ ST_Geometry.
Die Daten liegen schon in ArcMap als Feature Services vor (Teil 1 der Blogserie) und können einfach in die Datenbank exportiert werden, wir nutzen hier keine GDB Schemas, sondern schreiben die Daten als platte Tabellen nach Oracle.
- Verbinden mit der Datenbank
- Exportieren der Daten in ArcMap als Tabellen
Es gibt auch natürlich noch andere Wege Geodaten ohne ArcGIS Desktop in die DB zu laden, aber das ist der Einfachste.
Schritt 2: Das SQL Statement
Jetzt kann z. B. am SQL* Plus Prompt von Oracle das SQL Statement formuliert werden, dass uns die entsprechenden Ergebnisse liefert. Unser Ansatz war es mit einem einzigen Statement zu arbeiten ohne Tabellen/Views mit Zwischen- oder Teilergebnissen.
select gemschl, sum(biomass) biomass, sum(solar) solar, sum(wasser) wasser, sum(wind) wind from ( select b.rs gemschl, sum(a.installierte_leistung_in_kw) biomass, 0 solar, 0 wasser, 0 wind from eeganlagen a, gemeinden b where a.energietraeger='Biomasse' and sde.st_within (a.shape, b.shape)=1 group by b.rs, a.biomass UNION select b.rs gemschl, 0 biomass, sum(a. installierte_leistung_in_kw) solar, 0 wasser, 0 wind from eeganlagen a, gemeinden b where a.energietraeger ='Solar' and sde.st_within (a.shape, b.shape)=1 group by b.rs, a.solar UNION select b.rs gemschl, 0 biomass, 0 solar, sum(a. installierte_leistung_in_kw) wasser, 0 wind from eeganlagen a, gemeinden b where a.energietraeger ='Wasserkraft' and sde.st_within (a.shape, b.shape)=1 group by b.rs, a.wasser UNION select b.rs gemschl, 0 biomass, 0 solar, 0 wasser, sum(installierte_leistung_in_kw) wind from eeganlagen a, gemeinden b where a.energietraeger='Windenergie' and sde.st_within (a.shape, b.shape)=1 group by b.rs, a.wind ) group by gemschl;
In den Zeile 3,4 und 5 werden alle Anlagen die im Attribut 'energietraeger' den Wert 'Biomasse' haben ausgewählt. Die SQL Funktion st_within nimmt dann alle Biomasse Anlagen die innerhalb (within) von Gemeindeflächen liegen (Zeile 6) und gruppiert (group by) sie nach dem Gemeindeschlüssel (RS) der einzelnen Flächen (Zeile 7). Mit sum(a.installierte_leistung_in_kw) werden die Inhalte des Feldes (installierte.....) aller Punkte (gruppiert nach Gemeinde) aufsummiert.
Die Zeilen 9-13 und 15-19, sowie 21-25 machen das Gleiche für die anderen Energieträger, das UNION in den Zeilen 8, 14 und 20 macht aus den Teilergebnissen eine Tabelle.
Die Statements in Zeile 1 und 27 erzeugen für jede Gemeinde (group by gs) eine Zeile mit der jeweiligen Summe (sum (...), sum(..)...) in Kilowatt der vier verschiedenen Energieträger.
Schritt 3: Das SQL Statement in ArcGIS Desktop ausführen
Mit einem Query Layer in ArcGIS kann das Statement in Schritt 2 auch direkt in ArcMap ausgeführt werden. Die Ausführung des Statements kann je nach Datenbank Server ein wenig dauern (bei mir ca. 1 Minute, wenn niemand sonst auf dem Server war) und wird immer wieder ausgeführt, wenn man den Query Layer anspricht. Es empfiehlt sich also, nach dem ersten ausführen, die Tabelle zu exportieren und damit weiterzuarbeiten.
Weitere Infos zu Spatial SQL und ArcGIS
Völlig ohne ArcGIS
Die Abfrage kann auch komplett ohne ArcGIS mit der Datenbank eigenen Spatial Type Implementierung durchgeführt werden. Im Falle von Oracle mit SDO Geometry und einem darauf angepassten SQL Statement. Wer es probieren will, findet hier die Oracle Hilfe.
Einschätzung für die Verwendung von SpatialSQL für räumliche Analysen
Vorteil
+ Man benötigt im besten Fall keine weitere Software ausser der Datenbank
Nachteil
- Benötigt einiges an (SQL und DB) Know-how
- Daten laden ist nicht ohne Weiteres möglich
- keine unmittelbare Visualisierung und Weiterverarbeitung