High Performance Spatial Computing mit Oracle 12c
Teil II: Beispiel aus der Praxis
Im ersten Teil dieses Beitrags hast du erfahren welche Grundlagen geschaffen werden mussten, um in unserem Kundenprojekt in kurzer Zeit heterogene Geometrie- und Sachdaten zu einem konsistenten Gesamtdatenbestand aufzubereiten. In diesem zweiten Teil zeigen wir dir an einem Beispiel, wie wir auch bei komplexen geometrischen Aufgaben mit Oracle 12c zum Ziel gekommen sind.
Im Rahmen des Projektes zeigte sich wieder eindrucksvoll, dass auch das beste Setup nichts nützt, wenn die einzelnen Abfragen unperformant geschrieben sind oder der Optimizer überfordert ist. Gerade die Verarbeitung von räumlichen Daten bietet hier einige Herausforderungen.
Im Folgenden ein Beispiel, mit welchen Strategien wir auch bei komplexen geometrischen Aufgaben mit Oracle 12c zum Ziel gekommen sind.
Isophonen
Das Ergebnis der Modellierung waren sogenannte Isophonen, d.h. Flächen (Geometrien) mit gleicher Lautstärke.
Isophonen können als Flächen oder als Bänder vorliegen. Die Flächen zeigen je Pegelklasse die Bereiche, in denen bestimmte Lautstärken erreicht sind (Mindestlautstärke). Die Bänder dagegen zeigen jeweils die betroffene Fläche eines Lautstärkeintervalls an. Im Projekt mussten beide Arten der Darstellung erzeugt werden.
Modellierte Isophonen zeichnen sich in der Regel durch extrem viele Stützpunkte aus, da die Geometrien häufig sehr groß und gleichzeitig sehr komplex sind. Viele Stützpunkte bedeuten mehr Speicherbedarf, vor allem jedoch längere Laufzeiten bei räumlichen Abfragen bzw. Operationen. Bei Bändern ist die Anzahl der Stützstellen durch die inneren Ringe nochmals deutlich größer als bei den Flächen.
Aus dem Projekt resultierten knapp 400.000 Isophonenflächen mit in Summe 95 Millionen Stützpunkten. Hieraus mussten fast 200 Millionen Isophonenbänder berechnet werden, die in Summe über 150 Millionen Stützpunkte aufwiesen.
Implementierung
Erster Ansatz: Kurz und knackig
Mit einem kurzen Statement konnten wir aus den Flächengeometrien Bändergeometrien generieren:
CREATE TABLE Isophonen_Baender AS
SELECT o.Sachdaten,
o.Pegel,
SDO_GEOM.SDO_DIFFERENCE (
o.Geom,
(SELECT SDO_AGGR_UNION (SDOAGGRTYPE (i.Geom, 0.05))
FROM Isophonen_Flaechen i
WHERE i.Sachdaten = o.Sachdaten
AND i.Pegel > o.Pegel),
0.05) Geom
FROM Isophonen_Flaechen o;
Hier wurde von den Flächengeometrien alle im gleichen räumlichen Gebiet liegenden Flächen mit höheren Lärmwerten abgezogen. Sachdaten und Identifier blieben erhalten.
Für sehr kleine Datenmengen ist ein solches Statement vertretbar. Für größere Datenmengen ist dieser Ansatz nicht praktikabel.
Selbst wenn das Statement nach Wochen erfolgreich abgeschlossen worden wäre (und wir diese Zeit gehabt hätten), wären drei große Nachteile geblieben:
- Die gesamte Konfiguration der Redo-Logs muss auf lang laufende Abfragen ausgelegt sein, mit allen Nachteilen, die man hierdurch in Kauf nehmen muss.
- Im Falle eines Abbruchs (verlorene Connection, Stromausfall) müsste nach dem Redo das gesamte Statement nochmals von vorne verarbeitet werden.
- Mit solch einem Statement hat man keinerlei Anhaltspunkt, wie lange die Verarbeitung noch dauert.
Optimierung: Eins nach dem Anderen
DECLARE
BEGIN
FOR c IN (SELECT Sachdaten, Pegel, Geom FROM Isophonen_Flaechen)
LOOP
INSERT INTO Isophonen_Baender (Sachdaten, Pegel, Geom)
SELECT o.Sachdaten,
o.Pegel,
SDO_GEOM.SDO_DIFFERENCE (
o.Geom,
(SELECT SDO_AGGR_UNION (SDOAGGRTYPE (i.Geom, 0.05))
FROM Isophonen_Flaechen i
WHERE i.Sachdaten = o.Sachdaten
AND i.Pegel > o.Pegel),
0.05) Geom
FROM Isophonen_Flaechen o
WHERE c.Sachdaten = o.Sachdaten AND c.Pegel = o.Pegel;
COMMIT WRITE NOWAIT;
END LOOP;
END;
/
Die Berechnung erfolgte nicht mehr in einer einzigen Transaktion in der Datenbank. Vielmehr wurde in einer Schleife über alle Flächen iteriert. Jede berechnete Fläche wurde direkt in die Ergebnistabelle hinzugefügt.
Damit bot sich die Möglichkeit, dass im Falle eines Abbruchs das Statement jederzeit für alle noch nicht verarbeiteten Geometrien wieder aufgenommen werden konnte. Schon bearbeitete Datensätze blieben erhalten. Gleichzeitig war jederzeit transparent wie viele Datensätze bereits verarbeitet wurden und somit eine zeitliche Hochrechnung möglich.
Die technischen Probleme des ersten Ansatzes waren damit adressiert. Für den engen Zeitplan war die Laufzeit des Statements jedoch immer noch nicht hinreichend.
Lösung: Teile und herrsche
DECLARE
v_band_geom MDSYS.SDO_GEOMETRY;
BEGIN
INSERT INTO Isophonen_Baender (Sachdaten, Pegel, Geom)
SELECT Sachdaten, Pegel, Geom
FROM Isophonen_Flaechen
WHERE Pegel = 75;
COMMIT WRITE NOWAIT;
FOR o IN (SELECT Sachdaten, Pegel, Geom, SDO_GEOM_MBR(Geom) Mbr
FROM Isophonen_Flaechen
WHERE Pegel != 75)
LOOP
v_band_geom := o.Geom;
FOR i IN (SELECT Geom
FROM Isophonen_Flaechen
WHERE Sachdaten = o.Sachdaten
AND Pegel > o.Pegel
AND SDO_RELATE(Geom, o.Mbr, 'mask=anyinteract')
= 'TRUE'
ORDER BY Pegel desc)
LOOP
v_band_geom := SDO_GEOM.SDO_DIFFERENCE(v_band_geom,
i.Geom,
0.05);
END LOOP;
INSERT INTO Isophonen_Baender (Sachdaten, Pegel, Geom)
VALUES (o.Sachdaten, o.Pegel, v_band_geom);
COMMIT WRITE NOWAIT;
END LOOP;
END;
/
In einem ersten Schritt wurden alle innersten Geometrien (d.h. die lautesten Flächen) direkt in die Ergebnistabelle für Bänder geschrieben. Hier gab es keinen Unterschied von Fläche zu Band. Anschließend wurden die restlichen Geometrien mit einer doppelten Schleife verarbeitet.
Der Optimizer von Oracle ist an dieser Stelle nicht in der Lage,
den geometrischen Index für eine Vorselektion für die Funktion
SDO_RELATE
zu nutzen.
Stattdessen musste eine Vorselektion über das vorberechnete
minimal umgebende Rechteck
(engl.: MBR) erfolgen, was ein Vielfaches an Rechenzeit einsparte.
An dieser Stelle ein Tipp: In Oracle 12c gibt es gegenüber 11g eine
verbesserte und schnellere Implementierung
dieser Funktion: SDO_GEOM_MBR statt SDO_GEOM.SDO_MBR.
Die Aufgabe der äußeren Schleife bestand darin, alle noch zu prozessierenden Flächen zu selektieren und das MBR zu erstellen. Die innere Schleife iterierte dann über alle Objekte, welche das MBR der äußeren Schleife berührten. Es wurden also nur die Geometrien rechenintensiv miteinander verschnitten, die sich gemäß der Vorselektion an Hand der räumlichen Lage auch potentiell überlagerten.
Neben den Vorteilen der ersten Optimierung war mit dieser Anweisung auch das Laufzeitproblem adressiert.
Fazit
Mit diesem Aufbau haben wir das Projekt sehr erfolgreich abgeschlossen. Durch die Kombination der stabilen Oracle 12c und ihrer enormen Verbesserungen der Performance für räumliche Abfragen gegenüber der Leistung von Oracle 11g, sowie dem gewählten Hardware Setup, waren wir in der Lage sämtliche Ergebnisse in time, quality and budget abzuliefern.
Die von Oracle bereitgestellten räumlichen Funktionen (Locator und Spatial Option) sind ausgereift und vollumfänglich. Auch die mit Oracle 12c teilweise komplett neu geschriebenen Funktionen sind inzwischen stabil. Bei einigen Funktionen muss sich der Anwender jedoch über ihre Besonderheiten oder ihren offiziellen Funktionsumfang im Klaren sein. Der Umgang der gleichen Funktion mit 2D- und 3D-Daten ist zwischen den Versionen zum Teil unterschiedlich.
Bei der Implementierung von Abfragen oder PL/SQL-Routinen lohnt es sich, immer wieder einen Blick in die Dokumentation oder das Changelog zu werfen.
Mit 12c wurde zum Beispiel das bereits erwähnte SDO_GEOM.SDO_MBR durch SDO_GEOM_MBR ergänzt,
wobei letzteres deutlich schneller ein Resultat liefert.
Die neue Funktion
SDO_POINTINPOLYGON
ist in der Lage extrem schnell Punkte mit Polygonen zu korrelieren.
Das absolute Highlight für die Berechnung war aber das SPATIAL_VECTOR_ACCELERATION-Flag.
Die Geschwindigkeitsgewinne durch diese Funktion sind wirklich enorm.
Wenn der Projektzeitplan ein wenig lockerer gewesen wäre, hätte ich große Freude daran gehabt, die diversen Berechnungen vergleichsweise auch auf einer 11g auszuführen, um den Unterschied zwischen 11g und 12c im Gesamten zu sehen. Für meinen Teil möchte ich die Oracle 12c für Spatial-Processing-Projekte nicht mehr missen.
Die finalen Ergebnisse des Kundenprojektes und vor allem die Isophonen können Sie Online auf den Seiten des Eisenbahn-Bundesamtes finden.
Teilen via
