Benchmark Geospatial Databases

Diplomarbeit am Institut für Informatik der Freien Universität Berlin,
Arbeitsgruppe Datenbanken und Informationssysteme
A Benchmark of Geospatial Databases
Philipp Ledermann
Matrikelnummer: 3996792
[email protected]
Erstgutachter: Prof. Dr. Agnès Voisard
Zweitgutachter: Prof. Dr. Elfriede Fehr
Betreuer: Daniel Kreßner
Berlin, den 13.08.2013
abstract
Due to the popularity and importance of spatial data, it is not surpring that many commercial and free databases start to put continous
effort into the support and optimization of spatial operations. This however rises the question, how each of them perform and compare to another.
While some spatial benchmarks exists, most of them are either discontinued, very limited or too specialized. Therefore the goal of this work was
to design and execute a current benchmark of spatial data, that should be
easily modifiable and extensible by utilizing xml-based configuation files.
Since the SQL-standard does not support spatial data by itself, databases have to rely on additional standards like the SQL/MM extension for
a common syntax between them. This work will make use of these standards as a basis for creating and executing a series of queries, that shall
then be evaluated. The selected candidates for this comparison consists
of Oracle Spatial, PostGIS, MySQL and SpatialLite.
Eidesstattliche Erklärung
Ich versichere hiermit an Eides Statt, dass diese Arbeit von niemand anderem als meiner Person verfasst worden ist. Alle verwendeten Hilfsmittel wie
Berichte, Bücher, Internetseiten oder ähnliches sind im Literaturverzeichnis
angegeben, Zitate aus fremden Arbeiten sind als solche kenntlich gemacht. Die
Arbeit wurde bisher in gleicher oder ähnlicher Form keiner anderen Prüfungskommission vorgelegt und auch nicht veröffentlicht.
Berlin, 13.08.2013
Inhaltsverzeichnis
1 Einführung
1.1 Einleitung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2 Motivation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.3 Verwandte Arbeiten . . . . . . . . . . . . . . . . . . . . . . . .
1
1
1
2
2 Thematische Grundlagen
2.1 Kandidaten . . . . . . . . . . . . .
2.1.1 PostgreSQL + PostGIS . .
2.1.2 Oracle DB + Oracle Spatial
2.1.3 MySQL . . . . . . . . . . .
2.1.4 SQLite + SpatiaLite . . . .
2.2 Geodaten . . . . . . . . . . . . . .
2.3 Geodatenbanken . . . . . . . . . .
2.4 Relevante Standards . . . . . . . .
2.4.1 Simple Features For SQL .
2.4.2 SQL/MM . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
5
5
5
6
6
7
7
8
9
9
12
3 Benchmark
3.1 Testprogramm .
3.2 JDBC . . . . . .
3.3 XML-Schemas . .
3.4 Query-Translator
3.5 Test-Datenbasis .
3.6 Queries . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
14
14
15
17
20
22
24
4 Auswertung
4.1 Vorwort . . . . . . . . . . . . . . . . . . . .
4.2 Benchmarks . . . . . . . . . . . . . . . . . .
4.2.1 Benchmarks - Beziehungsfunktionen
4.2.2 Benchmarks - Funktionen . . . . . .
4.2.3 Benchmarks - Spezialtests . . . . . .
4.2.4 Komplexe Benchmarks . . . . . . . .
4.3 Scoring . . . . . . . . . . . . . . . . . . . . .
4.4 Zusammenfassung . . . . . . . . . . . . . .
4.5 Vergleich mit verwandten Arbeiten . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
27
27
30
31
41
48
59
63
66
67
5 Schlussteil
5.1 Probleme . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.2 Ausblick . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.3 Fazit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
69
69
69
70
A Anhang
A.1 Tabellenlayouts . . . . . . . . . . . . . . . . . . . . . . . . . . .
A.2 Komplexe Queries . . . . . . . . . . . . . . . . . . . . . . . . .
A.2.1 Komplexe Queries - I . . . . . . . . . . . . . . . . . . . .
76
76
79
79
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
A.2.2 Komplexe Queries - II . . . . . . . . . . . . . . . . . . .
A.2.3 Komplexe Queries - III . . . . . . . . . . . . . . . . . . .
85
90
Abbildungsverzeichnis
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Geometrietyp-Hierarchie, Quelle: OGC, Simple Features For SQL
[21, p.14] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Mapping der Datentypen . . . . . . . . . . . . . . . . . . . . . .
Relationsfunktionen - Punkte-Punkte . . . . . . . . . . . . . . .
Relationsfunktionen - Punkte-Punkte - Nachtest I . . . . . . . .
Relationsfunktionen - Punkte-Punkte - Nachtest II . . . . . . .
Relationsfunktionen - Punkte-Kantenzüge . . . . . . . . . . . .
Relationsfunktionen - Punkte-Kantenzüge - Nachtest . . . . . .
Relationsfunktionen - Punkte-Polygone . . . . . . . . . . . . . .
Relationsfunktionen - Punkte-Polygone - Nachtest . . . . . . .
Relationsfunktionen - Kantenzüge-Kantenzüge . . . . . . . . . .
Relationsfunktionen - Kantenzüge-Kantenzüge - Nachtest . . .
Relationsfunktionen - Kantenzüge-Polygone . . . . . . . . . . .
Relationsfunktionen - Kantenzüge-Polygone - Nachtest . . . . .
Relationsfunktionen - Polygone-Polygone . . . . . . . . . . . . .
Relationsfunktionen - Polygone-Polygone - Nachtest I . . . . . .
Relationsfunktionen - Polygone-Polygone - Nachtest II . . . . .
Zugriffsfunktionen - Polygone . . . . . . . . . . . . . . . . . . .
Ein- und Ausgabefunktionen - Polygone . . . . . . . . . . . . .
Ein- und Ausgabefunktionen - Polygone - Nachtest . . . . . . .
Messfunktionen - Kantenzüge . . . . . . . . . . . . . . . . . . .
Messfunktionen - Polygone . . . . . . . . . . . . . . . . . . . . .
Mengenoperationen - Polygone . . . . . . . . . . . . . . . . . .
Mengenoperationen - Polygone - Nachtest . . . . . . . . . . . .
Transformationsfunktionen - Polygone . . . . . . . . . . . . . .
Index Filterfunktion - Polygone . . . . . . . . . . . . . . . . . .
Geodetischer Datentyp - Polygone . . . . . . . . . . . . . . . .
Geodätischer Datentyp - Polygone - Nachtest I . . . . . . . . .
Geodätischer Datentyp - Polygone - Nachtest II . . . . . . . . .
Komplexitätsvergleich - Postgres - Polygone . . . . . . . . . . .
Komplexitätsvergleich - Postgres - Polygone - Nachtest . . . . .
Komplexitätsvergleich - Oracle - Polygone . . . . . . . . . . . .
Komplexitätsvergleich - MySQL - Polygone . . . . . . . . . . .
Komplexitätsvergleich - MySQL - Polygone - Nachtest . . . . .
Komplexitätsvergleich - SQLite - Polygone . . . . . . . . . . . .
Komplexitätsvergleich - SQLite - Polygone - Nachtest . . . . . .
Datenmanipulation - Einfügen - Polygone . . . . . . . . . . . .
Datenmanipulation - Einfügen - Polygone - Nachtest . . . . . .
Datenmanipulation - Aktualisieren - Polygone . . . . . . . . . .
Datenmanipulation - Aktualisieren - Polygone - Nachtest . . . .
Datenmanipulation - Löschen - Polygone . . . . . . . . . . . . .
Oracle Toleranz-Parameter - Polygone . . . . . . . . . . . . . .
Komplexe Queries I - Punkte . . . . . . . . . . . . . . . . . . .
Komplexe Queries II - Kantenzüge . . . . . . . . . . . . . . . .
10
18
32
32
33
34
35
36
36
37
37
38
38
39
39
40
42
43
43
45
45
46
46
47
49
49
50
50
51
52
52
53
53
54
54
55
56
56
57
57
58
60
62
44
45
46
Komplexe Queries III - Polygone . . . . . . . . . . . . . . . . .
Ranking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Scoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
63
65
66
1 Einführung
1
1.1
Philipp Ledermann
Einführung
Einleitung
Durch das Internet wurde die Art wie Menschen auf Informationen zugreifen und mit diesen interagieren können, grundlegend verändert. Während es
früher nötig war viele Enzyklopädien zu besitzen, genügt heute ein Klick auf
Wikipedia, um sich einen ungefähren Überblick über ein bestimmtes Thema
zu verschaffen. Dabei ist aber nicht nur die Menge der Daten oder der schnelle
Zugang so beeindruckend. Oft sind es gerade die neuen Interaktionsmöglichkeiten, die sich uns angeboten werden. Digitale Karten und Geodaten sind dafür
ein gutes Beispiel. Vorbei sind die Zeiten wo Fahrtstrecken per Hand geplannt
werden mussten. Ein Navigationssystem erechnet uns nicht nur in sekundenschnelle eine effiziente Route, es kann uns in der Regel auch zuverlässig an den
gewünschten Zielort führen. Genauso helfen uns Umkreissuchen beispielsweise
beim beim Finden des nächstgelegenen Facharztes.
Damit dies möglich ist, werden einerseits Geodaten und andererseits eine Umgebung benötigt, um mit diesen zu interagieren. Dabei müssen nicht nur Abfragen durchgeführt werden, sondern bei Bedarf sollen auch neue Elemente
hinzugefügt, Bestehende verändert und Alte entfernt werden. Die besondere
Schwierigkeiten ist dabei die Größe der Datensätze. Da Datenbankmanagementsysteme sowohl auf das Speichern, als auch Verwalten von großen Datenmengen ausgelegt sind, eignen sie sich dafür am besten.
Lange Zeit waren Geodaten eine Herausforderung, die nur wenige, vorwiegend
kommerzielle, Datenbanken meistern konnten. Doch weil diese Art der Daten immer wichtiger und gefragter wird, haben viele der freien Open-Source
Datenbanken damit begonnen, diesen Rückstand aufzuarbeiten.
1.2
Motivation
Heute existiert eine ganze Reihe von Datenbankmanagmentsystemen, die räumliche Daten unterstützen und deren Entwicklung rasch voranschreitet. Zwischen diesen gibt es jedoch beträchtliche Unterschiede hinsichtlich Funktionsumfang, Bedienbarkeit, Robustheit und nicht zuletzt der Geschwindigkeit. Daher wäre es wünschenswert, wenn ein Vergleich bezüglich solcher Kriterien
existieren würde. Das Problem dabei ist, dass räumliche Daten nicht Teil des
SQL-Standards sind und sich externe Standards bislang nur langsam durchsetzen konnten, so dass eine einheitliche Verwendung von Geodaten über mehrere
Datenbanken hinweg, derzeit nicht praktikabel ist. Und dies stellt nur eine von
vielen Schwierigkeiten dar. So gibt es zwar einige wenige Performancebenchmarks bezüglich Geodatenbanken, aber diese sind entweder veraltet, zu speziell
oder in ihrem Umfang äußerst beschränkt.
Ziel dieser Diplomarbeit war es daher, einen aktuellen und erweiterbaren Benchmark für räumliche Datenbanken zu entwicklen und durchzuführen. Dabei wur1
1.3 Verwandte Arbeiten
Philipp Ledermann
de der Fokus auf die Performance der geometrischen Berechnungen und damit auf möglichst unabhängige Funktionstests gelegt. Multiuser-Simulationen
oder Clustering wurden in dieser Arbeit nicht betrachtet, zumal die getesteten
Kandidaten diese momentan nur teilweise unterstützen. Es sei allerdings angemerkt, dass sich die Ergebnisse auf andere Tests übertragen lassen, da sich
Laufzeiten mindestens additiv verhalten.
1.3
Verwandte Arbeiten
Es folgt ein kurzer Überblick in verwandte Arbeiten, die sich ebenfalls mit dem
Thema Benchmarking von Geodaten beschäftigt haben.
Sequoia 2000 Storage Benchmark
In dem Tech-Report Sequoia 2000 Storage Benchmark [3] von Stonebraker,
Frew, Gardels und Meredithaus aus dem Jahre 1993, wurde ein Benchmark
beschrieben der, im Rahmen des Sequoia 2000 Projekts entstanden ist. Dieser hatte zum Ziel, eine Rechenumgebung für Geowissenschaften zu schaffen.
Dabei sollte es möglich sein, sehr große Datenmengen (Terabytes) von Klimadaten zu verwalten und zu analysieren. Der dazugehörige Benchmark ist
aus der Not entstanden, eine räumliche Datenbank zu finden, die in der Lage ist, den Anforderungen des Projektes gerecht zu werden. Es beschäftigt
sich ausschließlich mit Rasterdaten und besteht vorwiegend aus einer Reihe
von generischen Queries mit den dazugehörigen Beschreibungen. Eine konkrete Implementierung wird, nach außen, nicht angeboten. Getestet wurden neben
Postgres, ArcInfo1 , GRASS2 sowie IPW3 . Der allgemeine Nutzen des Benchmarks ist eingeschränkt, da er klar auf das Sequoia Projekt zugeschnitten ist
und auch als Anreiz für Datenbankhersteller dienen sollte, ihre Systeme weiter
zu optimieren [3, p.2].
VESPA Benchmark
In ihrem Paper VESPA Benchmark [4] haben Patton, Williams, Dietrich, Liew,
Dinn und Patrick im Jahre 2000 einen Vergleich von Postgres mit Rock &
Roll durchgeführt. Rock & Roll ist eine objektorientierten Datenbank, die die
beiden Sprachen Rock und Roll umfaßt. Es beinhaltet eine Reihe von Queries unterschiedlichen Typs, die nur generisch beschrieben wurden, so dass
die genaue Anzahl der verwendeten unterschiedlichen räumlichen Funktionen,
unklar bleibt. Statt realer wurden künstlich-generierte Daten verwendet, aus
denen nicht klar hervorgeht, wie vergleichbar sie mit echten Geodaten sind.
Insbesondere wegen Rock & Roll ist auch dieser Benchmark sehr speziell und
allgemein wenig aussagekräftig.
1
ArcInfo ist ein geographisches Informationssystem von ESRI, das mittlerweile als Variante von ArcGIS fortgeführt wird.
2
GRASS steht für Geographic Resources Analysis Support System und ist ein freies, OpenSource GIS-System mit eingeschränkter Unterstützung für Vektordaten.
3
IPW steht für Image Processing Workbench und ist eine UNIX-basierte Funktionsbibliothek für die Bildverarbeitung.
2
1.3 Verwandte Arbeiten
Philipp Ledermann
Jackpine Benchmark
Der Jackpine Benchmark ist ein Benchmark vom Jahre 2011, entwickelt an der
University of Tornoto von Ray, Simion und Brown. Im dazugehörigen Paper
[2] wird der Benchmark beschrieben, der auf Bristlecone basiert, einem Javabasierten Databankbenchmark für nicht-räumliche Daten. Jackpine erweitert
Bristlecone mit einer räumlichen Komponente. Die Queries wurden unterteilt
in Micro- und Macrobenchmarks, die mit den einfachen und komplexen Queries dieser Arbeit vergleichbar sind und im Abschnitt 3.6 eingeführt werden.
Die Microbenchmarks testen nur einen Ausschnitt der verfügbaren Funktionen
über unterschiedlichen Geometriekombinationen. Die Macrobenchmarks enthalten Abfragen aus realen Anwendungsbereichen, wie dem Hochwasserschutz.
Es wurden außerdem Geocoding und Reverse-Geocoding Abfragen untersucht.
Verglichen wurden die Geodatenbanken Postgres, Informix4 sowie eine ältere
Version von MySQL (mit deutlichen Funktionseinschränkungen). Als Datensatz wurden die TIGER-Daten des United States Census Bureau verwendet.
Zhonghai Zhou
In dem Paper Evaluating Query Performance on Object-Relational Spatial Databases [5] von Zhonghai Zhou aus dem Jahre 2009 wird die Performance von
IBM DB2 Spatial Extender, Postgres, Oracle sowie MySQL verglichen. Als
SQL-Basis dienten die SFS- sowie SQL/MM-Spezifikationen, die in dieser Arbeit in Abschnitt 2.4 beschrieben werden. Die Testdaten stammten aus der
United States Geological Survey und dem United States Bureau of Census und
waren vom Umfang her sehr begrenzt (Megabytes). Anstatt einem einheitlichen Testsystem, wurde drei unterschiedliche System verwendet, die MySQL,
Oracle und IBM DB2 enthielten. Zusätzlich wurde auf jedem System Postgres
installiert. Auf diese Weise sollten die unterschiedlichen Hardwareanforderungen der Kandidaten berücksichtigt werden, wobei die Ergebnisse im Anschluß
über Postgres normiert worden sind.
TPC Benchmarks
Der Transaction Processing Performance Council (TPC) bietet gleich eine
ganze Reihe von unterschiedlichen Benchmarks für verschiedene Anwendungsgebiete an. Der eigentliche Benchmark ist nicht frei verfügbar, sondern nur
über Mitgliedschaften zugänglich. Der offiziellen Dokumentation [28] wurde
entommen, dass beim TPC-C Benchmark eine OLTP5 -Umgebung, anhand eines Online-Shops, simuliert wird. Dabei führen eine hohe Anzahl von Klienten
eine zufällig ausgewählte, in diesem Umfeld typische, Operation durch, wie das
Bestellen eines Artikels, das Vornehmen eines Bezahlvorgangs oder das Versenden eines Artikels. Da in OLTP die Abfragezeit eine wichtige Rolle spielt,
wurden bestimmte Maximalzeiten oder Timeouts festgelegt. Getestet werden
vornehmlich kommerzielle Datenbanken und zwar immer in Kombination auf
4
5
Informix ist ein relationales DBMS von IBM.
Online Transaction Processing
3
1.3 Verwandte Arbeiten
Philipp Ledermann
eine darauf abgestimmte Server-Hardware. Die Resulte werden in Form einer Performance/Preis-Metrik präsentiert. Neben dem TPC-C gibt es noch den
TPC-E OLTP Benchmark, der eine modernere Variante des TPC-C darstellt und
statt eines Online-Shops eine Maklerfirma mit anderen Abfragen simuliert. Ein
weiterer Benchmark ist der TPC-H der auf OLAP6 und Business Intelligence
ausgerichtet ist. Dort werden komplexere Queries verwendet, die sowohl seriell, als auch parallel durchgeführt werden. In allen Varianten sind zwar die
Abfragen vom Aufbau her gleich, doch die Prädikate verwenden Zufallswerte
um unterschiedliche Ergebnisse zu erzielen. Zum heutigen Stand existiert vom
TPC leider noch kein Benchmark der Geodaten verwendet oder testet.
Weitere Arbeiten
Neben diesen Arbeiten existieren noch anderer interessanter Artikel, wie das
Paper von Hoel und Samet von 1995 [6] in dem die Performance der JOINOperation bezüglich verschiedener räumlicher Indexstrukturen untersucht wird.
Da es dort aber weniger um den Vergleich von bestehenden Datenbanken, sondern eher um das Finden der effizientesten Datenstrukturen geht, wurde diese
und andere Arbeiten nicht weiter untersucht.
6
Online Analytical Processing
4
2 Thematische Grundlagen
2
Philipp Ledermann
Thematische Grundlagen
In diesem Teil der Arbeit wird auf die thematischen Grundlagen eingegangen. Zunächst werden im Abschnitt 2.1 die vier Kandidaten vorgestellt. Im
Anschluss darauf, sollen in den Abschnitten 2.2 und 2.3 auf Geodaten und
Geodatenbanken eingangen werden. Danach werden in Abschnitt 2.4 zwei für
diese Arbeit relevanten Standards eingeführt.
2.1
Kandidaten
In dem folgendem Abschnitt sollen die in diesem Benchmark getesteten Geodatenbanken vorgestellt werden. Da die Lizenzen für die kommerzielen Systeme
wie ArcGIS oder Informix nicht zur Verfügung standen, wurde die Auswahl
hauptsächlich auf freie, Open-Source Datenbanken beschränkt.
2.1.1
PostgreSQL + PostGIS
PostgreSQL ist ein freies, objektrelationales Datenbankmanagementsystem.
Seine Ursprung liegt im Ingres-Projekt der University of California aus den
80er Jahren. Postgres, als Nachfolger von Ingres, wurde die folgenden Jahre
stetig weiterentwickelt, bis es Jahre 1995 schließlich um einen SQL-Intepreter
erweitert wurde. Die so modifizierte Variante trug den Namen Postgres95 und
war 1996 die Erste als Open-Source veröffentlichte Postgres-Version unter dem
heutigen Namen: PostgreSQL. Heute ist es eines der beliebtesten und fortschrittlichsten, freien Datenbankmanagmentsysteme [12].
PostgreSQL ist in der Programmiersprache C geschrieben, erfüllt zum Großteil die ANSI-SQL-2005 Spezifikation und ist voll ACID7 konform und mit
voller Transaktionsunterstützung. Mit GiST8 steht Entwicklern eine Schnittstelle zur Verfügung um selbstdefinierte Sortier- und Suchfunktionen mittels
Erweiterungen in die Datenbank einzufügen. Unter diesen, von Drittanbietern
angebotenen, Erweiterungen, fällt auch PostGIS. Zur Verfügung gestellt wird
Postgres es über die eigene Postgres-Lizenz, welche vergleichbar mit der MITLizenz ist [11].
PostGIS ist eine geographische Erweiterung für PostgreSQL, entwickelt seit
2000 von der Firma Refractions Research. Intern verwendet es für Geometrieberechnungen die GEOS-Bibliothek [13][33].
Im Folgenden werden die Bezeichner Postgres, PostgreSQL und PostGIS als
Synonyme verwendet. Benutzt wurden PostgreSQL Version 9.2.4 sowie PostGIS 2.0.
7
Die ACID-Eigenschaften stehen für Atomarität, Konsistenz, Isoliertheit und Dauerhaftigkeit.
8
Generalized Search Tree
5
2.1 Kandidaten
2.1.2
Philipp Ledermann
Oracle DB + Oracle Spatial
Die Oracle Datenbank [7] ist ein kommerzielles, objektrelationales DBMS der
Oracle Corporation. Es ist heute eines der bekanntesten, verbreitetsten und
mächtigsten Datenbanken überhaupt. Es ist ACID-konform und bietet volle
Transaktionsunterstützung. Oracle ist auch für den Einsatz auf Großrechner
konzipiert und bietet sowohl Clustering, als auch Replizität an, daher ist der
Datenspeicher vom eigentlich arbeitenden Prozesskern, der Instanz, getrennt.
Da Oracle wie schon erwähnt ein kommerzielles Produkt ist, besitzt es eine
proprietäre Lizenz.
Die Geokomponente von Oracle trägt den Namen Oracle Spatial, bzw. aktuell Oracle Spatial and Graph, und muss für gewerbliche Nutzung mit einer
seperaten Lizenz erworben werden. Zum ersten mal war sie als Spatial Data
Option (SDO) in Oracle 7 verfügbar, deren Namensgebung auch heute noch
in den Geofunktionen vorzufinden ist. Mit Oracle 8 wurde schließlich die Bezeichnung Oracle Spatial eingeführt.
Die Bezeichner Oracle, Oracle Database und Oracle Spatial werden fortan als
Synonyme verwendet. Es wurde Oracle-Version 11.2.0.1 verwendet.
2.1.3
MySQL
Der MySQL-Community-Server ist ein mit freies, relationales Datenbankmanagmentsystem. MySQL bietet mehrere unterschiedliche Engines zum Verwalten und Speichern von Tabellen an, die sich in ihrer Funktionalität und ihren
Optimierungen voneinander unterscheiden. Seit 2001 liefert die InnoDB-Engine
eine Struktur mit voller Transaktionsunterstützung und ACID Konformität an.
Im Kontrast dazu verfügt die Engine MyISAM über besonders schnellen Zugriff und Volltextsuche, dabei aber ohne jegliche Transaktionssicherung. MySQL ist insbesondere für Webdienste in dem sogenannten LAMP9 -Stack beliebt
und damit eines der verbreitetsten Datenbanken der Welt. MySQL unterstützt
einen Großteil der SQL-98 Spezifikation [14].
Die Geoerweiterung Spatial Extension wurde mit Version 4.1 im Jahre 2004 eingeführt und beschränkt sich ursprünglich auf MyISAM. Zwar wurde der Support für andere Engines nachgereicht, jedoch bietet auch heute nur MyISAM
die Möglichkeit der Verwendung von Geoindizes. Die Erweiterung orientiert
sich dabei an den gängigen Standards, umfaßt aber erst seit 2011 mit Version
5.6 Berechnungen über echte Objektgeometrien. Davor waren Beziehungsoperationen nur über minimale Bounding-Boxen durchführbar [14, p.1307-1333].
9
LAMP steht für Linux, Apache, MySQL und PHP, ein beliebtes Softwarekombination
für dynamischen Webserver.
6
2.2 Geodaten
2.1.4
Philipp Ledermann
SQLite + SpatiaLite
SQLite ist keine traditionale Datenbank, sondern vielmehr eine C-Programmbibliothek, die ein relationales DBMS umfaßt. Es wird seit 2000 entwickelt
und fortgeführt und ist gemeinfrei. SQLite überstützt dabei einen Großteil
der SQL-92-Spezifikation, ist ACID-konform und bietet volle Transaktionsunterstützung. Da es aber eine Programmbibliothek und damit kein echtes
Multiuser-System ist, besitzt es kein Rechte- oder Sicherheitssystem. Es wurde als eingebettete Datenbank konzipiert und eignet sich daher besonders für
die Integration in Applikationen, wenn eine externe Datenbank nicht benötigt
oder gewünscht wird. Neben der C-Schnittstelle existieren noch Wrapper für
zahlreiche andere Programmiersprachen, darunter Java mit JDBC. Im Gegensatz zu anderen Datenbanken bietet es nur eine kleine Auswahl an statische
Datentypen an. Diese werden erst zur Laufzeit dynamisch in die verschiedenen
SQL-Standardtypen umgewandelt [16][15].
Die Geoerweiterung SpatiaLite entwickelt seit 2008 von Alessandro Furieri,
führt geometrische Objekte und Funktionen in SQLite ein. Es wird angeboten
mit einer Tri-Lizenz bestehend aus MPL, GPL und LGPL. SpatiaLite verwendet intern sowohl die PROJ.4-Bibliothek für Koordinaten-Projektionen, als
auch die GEOS-Bibliothek für Geometrieberechnungen [17].
Da SQLite und damit SpatiaLite C-Bibliotheken sind, stellt der JDBC-Treiber
zu SQLite von Saito [19] ein JNI10 -basierter C-Wrapper für Java dar. Es basiert dabei auf den mittlerweile eingestellten Zentus’s SQLite JDBC -Treiber.
Aufgrund der Schwerigkeiten mit JNI, implementiert der Wrapper die JDBCSchnittstelle nicht vollständig, so dass weder eine Batch-Verarbeitung, noch
Transaktionsmechanismen möglich sind. Es sei außerdem angemerkt, dass der
Treiber insgesamt relativ instabil ist, zumal SQL-Fehlermeldungen entweder
nicht vorhanden oder irreführend sind.
2.2
Geodaten
Geodaten liegen, wie bei der Bildverarbeitung, in der Regel als Vektordaten
oder Rasterdaten vor. Bei Ersteren werden geometrische Objekte über ihre
Form definiert, z.B. als Punkte, Linien und Polygone, während bei Letzterem
die Daten in einem festgelegten räumlichen Gitter angeordnet sind und jede
Zelle dieses Gitters einen konkreten Datenpunkt darstellt. Vektordaten lassen
sich prinzipiell beliebig überlagern und voneinander unterscheiden, sind platzsparender und skalieren besser. Rasterdaten eignen sich unter anderem für
kontinuierliche Daten wie z.B. für Höhe oder Temperatur und stammen meist
von analogen Messungen wie Satellitenbilder oder eingescannten Karten. Da
sie sich leicht darstellen lassen, werden sie häufig als Hintergrund für Vektordaten, z.B. in GoogleMaps verwendet, während Netzwerke und geometrische
Objekte, deren Form analysiert und verarbeitet werden soll, als Vektordaten
10
Java Native Interface, eine Java Schnittstelle zum Durchführen von fremden Maschinencode
7
2.3 Geodatenbanken
Philipp Ledermann
vorliegen. Insbesondere wegen dem letzten Punkt beschränkt sich diese Arbeit
auf die Verwendung von Vektordaten [25].
Zur Bestimmung von Positionen auf der Erdoberfläche werden geographische
Koordinaten, bestehend aus Längen- und Breitengraden, verwendet. Möchte
man die Entfernung zwischen zwei Punkten in Metern bestimmen, muss man
feststellen, dass dies keineswegs trivial ist und komplizierte Formeln erfordert
[24], da die Erde keine Ebene ist und Längen- und Breitengraden nicht kartesischen Koordinaten gleichzusetzen sind. Tatsächlich ist die Erde auch keine
Kugel, sondern besitzt durch Höhen und Tiefen eine relativ unregelmässige
Form. Zur Annäherung existieren dafür heute Geoiden, die aber recht komplex
sind. Mit einem Ellipsoiden hingegen lässt sich ein gutes Mittelmaß zwischen
Komplexität und Annäherung erreichen. Ein häufig verwendetes Referenzsystem, welches über einen Ellipsoiden arbeitet und z.B. in GPS11 zum Einsatz
kommt ist WGS8412 [26].
Referenzsysteme oder Koordinatenreferenzsysteme stellen Methoden dar, mit
denen sich ein Bezug, zwischen einem Koordinatensysteme und der Erde schaffen lässt. Die schon beschriebenen Geodätische Koordinatenssysteme verwenden dafür einen Referenzellipsoiden, wie er auch in WGS84 verwendet wird
und nutzen beispielweise geographische oder Kugelkoordinaten. Sollen stattdessen kartesischen Koordinaten in einer Ebene benutzt werden, so kann man
eine Kartenprojektion wie den Welt-Merkator nutzen, bei dem, wie schon der
Name vermuten lässt, mittels einer Projektion geodätische Koordinaten auf
kartesische Koordinaten abgebildet werden. Aufgrund der Vielzahl von unterschiedlichen Referenzsystemen, die auf der Welt zum Einsatz kommen, werden diese von Organisationen wie der ehemaligen European Petroleum Survey
Group (EPSG) gesammelt und in öffentliche Datenbanken eingetragen, wo
jedes Referenzsystem eine eindeutige Identifikationsnummer, der Spatial Reference Identifier (SRID), zugewiesen bekommt. So steht beispielsweise die SRID
4326 [29] für das WGS84 Referenzsystem [26].
2.3
Geodatenbanken
Die Hauptaufgabe eines Datenbankmanagmentsystems (DBMS) ist das Speichern, Verwalten und Verarbeiten von Datenmengen, deren Größe das Halten
im Arbeitspeicher problematisch oder unerwünscht macht. Als Speichermedium dient üblicherweise eine Festplatte. Diese Arbeit beschäftigt sich ausschließlich mit relationalen Datenbanksystemen (RDBMS) deren Datenstrukturen auf
Relationen bzw. Tabellen basieren. Um Transparenz für den Nutzer zu gewährleisten, arbeiten Datenbanken mit mehreren Abstraktionslayern, die die interne
Arbeitsweise der Datenbank bzw. des Datenbankmanagmentsystems vor dem
11
Global Positioning System, ein globales Satellitennavigationssystem, welches zur Positionsbestimmung und Navigation eingesetzt wird.
12
WGS84 ist die letzte Revision des World Geodetic System, einem geodätischem Referenzsystem.
8
2.4 Relevante Standards
Philipp Ledermann
Nutzer verbergen soll. Typische Aufgaben in einem solchen System beinhalten
das
• Definieren,
• Erstellen,
• Manipulieren,
• Abfragen,
• sowie Aktualisieren
von Relationen.
Als Schnittstelle zum Durchführen dieser Aufgaben existiert die standardisierte Abfragesprache SQL.
Da der normale SQL-Standard keine Unterstützung für Geodaten vorsieht, ist
das erste Problem für eine mögliche Erweiterung, wie die Daten in eine Datenstruktur eingegliedert werden können. Ein möglicher Ansatz ist die Integration
der Daten in das bestehende System. D.h. es werden Erweiterungen verwendet,
um die Geodaten direkt mittels dieser in SQL verarbeiten zu können. Entsprechend müssen neue Datentypen hinzugefügt und andere Mechanismen, wie der
Optimizer angepasst werden [1, p.25]. So bleibt nur das Problem, dass eine
Erweiterung von SQL so vorgenommen werden muss, dass diese über verschiedene Datenbanken möglichst einheitlich sein soll. Mit den Simple Features for
SQL des OGC und der SQL/MM-Spezifikation wurde solche Standards für
SQL nachgereicht, da wie bereits erwähnt der SQL-Standard bis heute keine
Erweiterung für räumliche Daten vorsieht.
2.4
Relevante Standards
Als Nächstes werden die zwei, bereits mehrfach erwähnten, wichtigen Standards bezüglich Geodaten vorgestellt, die in allen getesteten Kandidaten zumindest teilweise implementiert sind und sich in der Praxis, angesichts der
vielen Datenbanken, die diese unterstützen oder unterstützen wollen, durchsetzen konnten.
2.4.1
Simple Features For SQL
Der erste Standard wurde in Koorperation des Open Geospatial Consortium
(OGC) und der International Organization for Standardization (ISO) entwickelt. Das OGC ist eine gemeinnützige Organization, die sich auf die Standardisierung von raumbezogenen Inhalten, wie den Geodaten, spezialisiert hat.
Gegründet wurde sie 1994, damals als Open GIS Consortium und als Nachfolger der Open Grass Foundation. Ihre Mitglieder setzen sich aus Experten der
Regierung, Industrie und Forschung zusammen [20].
9
attributes (such as the dimension of a geometric object) from functions that may compute values given a Type
instance (such as the centroid of a Polygon). In particular, an implementation of this standard would be free to
nominate any set of functions as observer methods on attributes of a User Defined Type, as long as the
signatures of the SQL functions described in this standard are preserved.
6.2.6
0..*
The
SQL Geometry Type hierarchy
2.4 Relevante Standards
Philipp Ledermann
SQL Geometry Types are organized into a type hierarchy shown in Figure 4.
+element
+spatialRS
ReferenceSystems::
1 SpatialReferenceSystem
Geometry
+mesureRS
ReferenceSystems::
0..1 MeasureReferenceSystem
Point
Curv e
2..*
Surface
GeometryCollection
+vertex
+element
LineString
Polygon
PolyhedralSurface
MultiSurface
MultiCurv e
MultiPoint
0..*
0..*+element
1..* +patch
+ring
1..*
Line
LinearRing
MultiPolygon
MultiLineString
Figure 4: Figure: SQL Geometry Type hierarchy
Abbildung 1: Geometrietyp-Hierarchie, Quelle: OGC, Simple Features For SQL
The root type,
named
[21,
p.14]Geometry, has subtypes for Point, Curve, Surface and Geometry Collection. A Geometry
Collection is a Geometry that is a collection of possibly heterogeneous geometric objects. MultiPoint, MultiCurve
and MultiSurface are specific subtypes of Geometry Collection used to manage homogenous collections of Points,
Curves and Surfaces.
The 0 dimensional
Geometry
TypesFor
are SQL
Point and
MultiPoint.
Die Spezifikation
der Simple
Features
(SFS)
bzw. Simple Feature
Access (SFA)
besteht
aus
verbundenen,
Teilen.
Im ersten Teil
The one-dimensional
Geometry
Types
arezwei,
Curveinhaltlich
and MultiCurve
together with
their subclasses.
The
two-dimensional
Geometry
Types
are
Surface
and
MultiSurface
together
with
their
subclasses.
[21] aus dem Jahre 2011 wird eine Reihe von geometrischen Objekten definiert, siehe Abbildung 1. Es beinhaltet eine Klassenhierarchie für Einzel- und
Multigeometrien, wobei Erstere genau ein Objekt dieser Art umfaßen, während Letztere, als Multivarianten, beliebig viele Objekte dieser Art enthalten
können. So beinhaltet beispielsweise ein Geometrie vom Typ MultiPoint eine
Menge von Point-Objekten.
Weiter wird von der Dimensionalität des Raumes und der der Objekte unterschieden. Grundsätzlich werden alle Referenzsystem, wie sie in Abschnitt 2.2
beschrieben wurden, unterstützt. Wenn man nun von diesen Koordinatensystemen abstrahiert und die Koordinaten nur als Werte betrachtet, so werden
2-D, 2.5-D und 3-D Räume unterstützt, wobei es sich bei der halben Koordinaten des 2.5-D Raumes um eine Höhenkomponente handelt, die im Gegensatz
zu 3-D bei den Berechnungen nicht zum tragen kommt. Die Geometrien selbst
können dabei 0-D (Punkte), 1-D (Linien, bzw. Kantenzüge), 2-D (Polygone)
oder 3-D (Oberflächen) sein.
Copyright © 2007 Open Geospatial Consortium, Inc. All Rights Reserved.
Zwar lautet der Obertyp für Kantenzüge Kurve, allerdings sieht der Standard
selbst, prinzipiell keine echte Unterstüzung für Kurven im Sinne der euklidi10
2.4 Relevante Standards
Philipp Ledermann
schen Geometrie vor. Kurven und damit Kantenzüge wurden hier definiert, als
die Interpolation zwischen den Endpunkten, im Falle von Kantenzügen also
der Linearkombination.
Weiterhin werden nicht-einfache, bzw. nicht-planare Linien und Polygone erlaubt, d.h. ein Kantenzug darf sich selbst schneiden, allerdings sei vorweg genommen, dass dies in der Praxis derzeit nicht unterstüzt wird, da die dazugehörigen Algorithmen komplizierter und ineffizienter sind [30].
Der Obertyp von Polygonen sind Oberflächen und es werden damit auch allgemeine Oberflächen, mittels Triangulierungen, angeboten, die aber, genau wie
nicht-planare Objekte, kaum implementiert sind. Polygone mit Löchern werden
sowohl im Standard unterstüzt, als auch von allen Kandidaten implementiert,
allerdings existieren hier bezüglich der Löcher einige Restriktionen. So darf
ein Loch den äußeren Rand des Polygons beispielsweise nur in maximal einem
Punkt schneiden. Es sei aber angemerkt, dass sich einige dieser Restriktionen
mithilfe von Multipolygonen umgehen lassen.
Es werden außerdem eine ganze Reihe von Klassenoperationen über den Geometrietypen definiert, deren Zweck und Funktionsweise (absichtlich) an denen
der SQL/MM-Spezifikation erinnern. So existieren gleich eine ganze Reihe von
Beziehungsoperatoren, darunter
• Gleichheit,
• Disjunktheit,
• Schnitt,
• Enthalten,
• Überdecken sowie
• Berühren.
Diese führen schließlich zur DE-9IM Beziehungsmatrix [13, p.39-42], in der die
Beziehungen zwischen zwei Geometrien, bezüglich ihres Randes, Inhalt und
Komplement zusammengefaßt werden. Das Ergebnis wird dann in eine kompakte (Text-)Matrix überführt und kann so, auf einfache Weise, verwendet
werden. Alle oben aufgeführten Beziehungsoperatoren lassen sich mit einer
Beziehungsmatrix abbilden. Die Berechnung der Matrix ist sehr effizient, insbesondere wenn mehrere Beziehungen auf einmal überprüft werden.
Zuletzt werden im ersten Teil der SFS-Spezifikation noch die Well-KnownText- und Well-Known-Binary-Formate eingeführt, mit der die angebotenen
Geometrietypen leicht in Text- bzw. Binärform repräsentiert werden können
und sich gut für Ein- und Ausgabe eignen. Außerdem werden Referenzsysteme besprochen, die sich ebenfalls definieren und bezogen auf die Geometrien
11
2.4 Relevante Standards
Philipp Ledermann
anwenden lassen [22].
Der zweite Teil [22] des Standards von 2010, befasst sich vorwiegend mit Implementierungsdetails bezüglich der Geometrien des ersten Teils und den darüber
definierten Methoden. Es wird auch noch der SQL/MM-Standard aufgegriffen und eine Verbindung bzw. Mapping zwischen beiden Spezifikationen angeboten, insbesondere bezüglich der Beziehungsoperationen. Zuletzt sind noch
Konformitätsqueries geben, die dafür genutzt werden können um zu überprüfen ob eine Datenbank korrekt mit dem Standard umgehen kann [22]. Diese
sind allerdings recht simpel, ähneln aber den einfachen Queries dieser Arbeit.
2.4.2
SQL/MM
Der ISO/IEC 13249-3 SQL/MM -Standard [23], kurz SQL/MM, besteht im
wesentlichen aus einer Reihe von Geometrietypen und einer Ansammlung von
Funktionen. Dabei sind sowohl die Geometrietypen, als auch die darüberliegende Hierarchie dem OGC-Standard sehr ähnlich. Allerdings werden einige
zusätzliche Geometriearten eingeführt, die beispielsweise zum Definieren von
Kurven dienen können. Funktionen werden vorwiegend, wie bei der Objektorientierung, an bestimmte Datenobjekte gebunden. Dies kann eine Schwäche darstellen, wenn dabei Funktionen über dem allgemeinen Geometrietyp vernachlässigt werden, denn dies verhindert nicht nur die Interoperabilität zwischen
unterschiedlichen Typen, sondern führt auch zu Problemen, da die Ergebnisse von bestimmten Funktionen unterschiedliche Typen ausgeben. So kann der
Durchschnitt von zwei Polygonen, je nach Eingabe, entweder die leere Menge,
ein Punkt, einen Kantenzug oder ein Polygon erzeugen.
Generell sind die Beschreibungen der Funktionen sehr ausführlich und beinhalten sowohl mathematische, als auch sprachliche, Beschreibungen. Darüber
hinaus wird für viele Methoden eine Definition in Pseudo-Code gegeben, die
diese entsprechende Funktion aus anderen Funktionen erzeugt. Ein Beispiel
hierfür sind die Beziehungsoperationen, die auf eine allgemeine Beziehungsfunktion, basierend auf der DE9IM -Matrix, zurückgreift [23, p.77].
Die folgende Tabelle beinhaltet einen Auszug der SQL/MM-Funktionen, wie
sie in dieser Arbeit benutzt wurden. Es wurde außerdem ein Mapping zu den
entsprechenden Funktionen der jeweiligen Testkandidaten beigefügt. Dabei bedeutet ein X, dass diese unter dem gleichem Namen in der Datenbank zur Verfügung steht. Alle mit N/A aufgeführten Methoden stehen hingegen nicht zur
Verfügung.
12
SQL/MM
ST_Dimension
ST_GeometryType
ST_SRID
ST_Transform
ST_IsEmpty
ST_IsSimple
ST_IsValid
ST_Boundary
ST_Envelope
ST_ConvexHull
ST_Buffer
ST_Intersection
ST_Union
ST_Difference
ST_SymDifference
ST_Distance
ST_AsText
ST_AsBinary
ST_GeomFromText
ST_GeomFromWKB
ST_Equals
ST_Disjoint
ST_Intersects
ST_Touches
ST_Within
ST_Contains
ST_Overlaps
ST_Length
ST_IsClosed
ST_IsRing
ST_NumPoints
ST_Area
ST_Centroid
ST_ExteriorRing
ST_NumInteriorRing
13
Oracle
GET_DIMS()
GET_GTYPE()
N/A
SDO_CS.TRANSFORM()
N/A
N/A
SDO_GEOM.VALIDATE_GEOMETRY...()
N/A
SDO_GEOM.SDO_MBR()
SDO_GEOM.SDO_CONVEXHULL()
SDO_GEOM.SDO_BUFFER()
SDO_GEOM.SDO_INTERSECTION()
SDO_AGGR_UNION(SDOAGGRTYPE())
SDO_GEOM.SDO_DIFFERENCE()
SDO_GEOM.SDO_XOR()
SDO_GEOM.SDO_DISTANCE()
GET_WKT()
GET_WKB()
SDO_GEOMETRY()
SDO_GEOMETRY()
SDO_EQUAL()
SDO_RELATE() + ’mask=disjoint’
SDO_RELATE() + ’mask=anyinteract’
SDO_RELATE() + ’mask=touch’
SDO_RELATE() + ’mask=inside+coveredby’
SDO_RELATE() + ’mask=contains’
SDO_RELATE() + ’mask=contains’
SDO_GEOM.SDO_LENGTH()
N/A
N/A
SDO_UTIL.GETNUMVERTICES()
SDO_GEOM.SDO_AREA()
SDO_GEOM.SDO_CENTROID
SDO_UTIL.EXTRACT()
SDO_UTIL.GETNUMELEM()
MySQL
Dimension()
GeometryType()
SRID()
N/A
N/A
IsSimple()
IsEmpty()
N/A
Envelope()
N/A
X
N/A
N/A
N/A
N/A
N/A
AsText()
AsBinary()
GeomFromText()
GeomFromBinary()
X
X
X
X
X
X
X
Glength()
N/A
N/A
N/A
Area()
Centroid()
ExteriorRing()
NumInteriorRings()
Tabelle 1: Liste von SQL/MM-Funktionen und deren Mapping. [13][7][14][17]
Postgres
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
SQLite
X
X
X
X
X
X
X
X
X
X
X
X
X
X
X
N/A (Bug)
AsText()
AsBinary()
GeomFromText()
GeomFromBinary()
X
X
X
X
X
X
X
X
X
X
ST_NPoints()
X
X
X
X
2.4 Relevante Standards
Philipp Ledermann
3 Benchmark
3
Philipp Ledermann
Benchmark
In diesem Abschnitt soll der im Rahmen dieser Diplomarbeit entwickelte Benchmark vorgestellt werden. Der eigentliche Benchmark wird im nächsten Abschnitt 3.1 eingeführt. Um den Benchmark leicht änderbar und erweiterbar zu
machen, findet die Konfiguration und Ausgabe vorwiegend über XML-Dateien
statt, deren Aufbau im Abschnitt 3.3 beschrieben wird. Kapitel 3.4 befaßt sich
mit dem QueryTranslator, einer wichtigen Kernkomponente, die als Brücke
zwischen generischem SQL-Code und der jeweiligen SQL-Syntax der Datenbank agiert. Dies war notwendig, da es trotz der in Abschnitt 2.4 eingeführten
SFS- und SQL/MM-Spezifikationen, nicht möglich war, SQL-Abfragen so zu
formulieren, dass sie in allen Kandidaten mit derselben Syntax durchführbar
sind. Die letzten beiden Abschnitte 3.5 und 3.6 beinhalten schließlich die Beschreibungen der Testdaten und durchgeführten Queries.
Es sei an dieser Stelle noch einmal darauf hingewiesen, dass sich dieser Benchmark ausschließlich mit Vektordaten befaßt, da Rasterdaten nur wenig Operationen zulassen und damit schwer zu analysieren sind. Außerdem sind Rasterdaten nicht in allen hier getesten Kandidaten unterstützt.
Die im Benchmark erhobene Messwerte beschränken sich auf Zeitmessungen.
Das genaue Messverfahren wird in den folgenden Abschnitten erläutert.
3.1
Testprogramm
Das Benchmarkprogramm wurde in der Programmiersprache Java geschrieben
und sollte in der Lage sein XML-konfigurierte SQL-Queries auf den Testkandidaten laufen zu lassen, die Ergebnisse zu verarbeiten und das Ergebnis schließlich in eine XML-Ausgabedatei zu schreiben.
Die Wahl von Java als Programmiersprache erfolgte deswegen, weil es weitestgehend platformunabhängig ist, leicht zu verwenden, robust und mit JDBC
über ein standardisiertes Datenbank-Interface verfügt. Eventuelle Einbußen bezüglich Performance und übermässigen Speicherbedarf spielten für den Benchmark, auf Ebene des Testprogramms, keine Rolle, da die Suite vorwiegend
als Kontrollinstanz verwendet wird und die eigentliche Arbeit auf DatenbankEbene erfolgt ist.
Das Hauptprogramm verfügt über zwei grundlegende Modi. Der erste Modus,
der Loader, dient zum Importieren der Dateien in die Datenbanken, während
der zweite Modus, der Task-Runner, zum Ausführen der eigentlichen Tests (genannt Tasks) verwendet wird.
Voraussetzung ist, das alle Testkandidaten bereits mit ihren entsprechenden
Geoerweiterungen installiert sind und eine Testdatenbank, die diese Erweiterung beinhaltet, erstellt wurde. Außerdem muss der Testdatensatz in mindes-
14
3.2 JDBC
Philipp Ledermann
tens einer dieser Datenbanken importiert sein.
Der Haupt-Testdatensatz, der für diese Diplomarbeit verwendet wurde, beinhaltet die OpenStreetMap13 -Daten für das Bundesland Brandenburg in
Deutschland. Diese wurden mit dem Tool Osm2pgsql14 [31] in PostGIS importiert. Anschließend musste eine Bereinigung der Daten erfolgen. Da Oracle
sich im Vortest als das Restriktivste aller Datenbanken, bezüglich Geometrievalidität, erwiesen hat und dabei sogar die Restriktionen des SFS-Standards
übertrifft, wurde ein Algorithmus eingefügt, welcher den Datensatz in Oracle
bereinigt.
Nach Abschluss der Datenbereinigung, können diese mithilfe des Loaders in
die anderen Kandidaten übertragen und verwendet werden. Als logischer Optimierungsschritt können dann, mithilfe eines weiteren Befehls, Indizies und
geometrische Metadaten in die Datenbanken eingefügt werden.
Anschließend kann der Task-Runner dazu verwendet werden, um XMLgesteuerte SQL-Queries auf den Datenbanken laufen zu lassen und die Ergebnisse und Laufzeit-Messwerte in eine Ausgabedatei zu schreiben.
3.2
JDBC
Einer der Hauptargumente für die Verwendung von Java als Programmiersprache, ist die Existenz der JDBC-Schnittstelle. Für praktisch jede Datenbank,
existiert entweder ein offizieller oder inoffizieller JDBC-Treiber. JDBC steht
dabei für Java Database Connectivity und ist ein SQL-Client zum Ausführen
und Verwalten von SQL-Abfragen. Die für den Benchmark relevanten Klassen
sind Connection, Statement, PreparedStatement und ResultSet. Tatsächlich
handelt es sich bei diesen um keine vollwertigen Klassen, sondern um Interfaces, die durch einen JDBC-Treiber erst um die eigentlichen Logik ergänzt
werden. Zur Vereinfachung werden im Folgenden Klassen und Interfaces als
Synonyme verwendet.
Die Klasse Connection abstrahiert eine Datenbankverbindung und ermöglicht
das Erzeugen und Verwenden von Statements und PreparedStatements. Aufgrund des internen Kommunikations-Overheads ist das Erstellen dieser Verbindung kostspielig und verhältnismäßig zeitintensiv. Aus diesem Grund wurden
sie so entworfen, dass sie sich wiederverwenden lassen.
Der Unterschied zwischen den Klassen Statement und PreparedStatement
ist die Art und Weise, wie Abfragen vorbereitet und übertragen werden. Bei
den Statements wird die eigentliche Abfrage, bestehend aus SQL-Befehlen und
13
OpenStreetMap ist ein freies Projekt in dem weltumfassende Geodaten gesammelt werden.
14
Osm2pgsql ist ein Open Source Kommandozeiltentool zum Importieren von
OpenStreetMap-Daten nach PostGIS
15
3.2 JDBC
Philipp Ledermann
Parametern, als Ganzes an den Server geschickt, um sie dort anschließend auszuführen. Im Gegensatz dazu, wird bei den PreparedStatements zunächst nur
der Befehlsteil mit Platzhaltern an den Server geschickt. Diese werden dort je
nach Unterstützung vorverarbeitet und optimiert und erst im Anschluß darauf
werden die eigentlichen Parameter übertragen, die dann auf den kompilierten Befehlsteil angewendet werden können. Dies ist insbesondere dann nützlich, wenn ein und diesselbe Query, mehrmals hintereinander, mit den gleichen
oder unterschiedlichen Parametern durchgeführt werden sollen, da so ein Teil
des Kommunikations-Overhead wegfällt und die Query nur einmal optimiert
werden muss. Diese Mechanik lässt sich auch für Batches nutzen, d.h. die eigentlichen Füllwerte der Platzhalter werden nicht für jede Operation einzeln,
sondern stattdessen als Paketbündel von Parametern übertragen. Dies minimiert den Overhead und verbessert die Performance in der Regel deutlich.
Im Fall von Ergebnismengen, wie beispielsweise bei eine SELECT-Anweisung,
liefert die Statement-Instanz ein ResultSet-Objekt mit den Ergebnissen zurück. Dieses ist standardmäßig nicht gepuffert, hält damit also nicht alle Ergebnisse im Arbeitsspeicher, sondern beinhaltet in der Regel nur ein Cursor
oder Pointer zu den eigentlichen Ergebnisdaten in der Datenbank, über den
das Programm dann iterieren kann. Der Puffermechanismus lässt sich seperat
konfigurieren und holt dann je nach Wahl, ähnlich der Batch-Mechanik, jedesmal eine vorgegebene Anzahl von Ergebnissen vom Server ab.
Die JDBC-Treiber für Postgres, MySQL und SQLite sind Community-Treiber,
die allesamt seperat von der eigentlichen Datenbank weiterentwickelt werden.
Während die ersten beiden auf dem neuesten technischen Stand, stabil und
effizient sind, ist der SQLite Treiber relativ unterentwickelt [19]. Tatsächlich
existieren für SQLite gleich eine ganze Reihe von JDBC-Treibern, die alle noch
in den ersten Entwicklungsstufen stecken bzw. deren Entwicklung mittlerweile
eingestellt wurde. Der Grund hierfür ist, wie schon bei der Beschreibung zu
SQLite im Abschnitt 2.1.4 erläutert wurde, dass es sich bei SQLite vorwiegend
um eine C-Bibliothek handelt. Dementsprechend basieren alle JDBC-Treiber
auf JNI und sind mehr Java-C-Wrapper anstelle von konventionellen Treibern.
Dies macht sich insbesondere dahingehend bemerkbar, dass die Geometrieberechnungen nicht, wie man erwarten würde, in einem Datenbankkern ausgeführt wird, sondern da SQLite einen solchen ja nicht besitzt, wird der Code
stattdessen unter Java bzw. C, d.h. auf Betriebssystemebene ausgeführt.
Oracle selbst bietet gleich mehrere JDBC-Treibern an, die zum Einen auf unterschiedlichen JDBC-Versionen und zum Anderen auf zwei Zugriffsarten aufgesetzt sind. Der auf dem OCI-Treiber15 basierende Fat-Client und der rein
Java basierte Thin-Client. Die Performance-Eigenschaften beider Treiber wurde im Vorfeld getestet und es wurde bezüglich dieses Benchmarks, gemäß der
Oracle-Empfehlung, zugunsten der Verwendung des Thin-Clients in der aktu15
Oracle Call Interface, eine C-Schnittstelle für die Oracle-Datenbank
16
3.3 XML-Schemas
Philipp Ledermann
ellen JDBC-4 Variante (Ohne Logging) entschieden [10].
Wichtig zu erwähnen ist auch, dass ausschließlich normale JDBC-Treiber verwendet werden, die keinerlei Geoerweiterungen erfordern. Zwar bietet der OracleTreiber und ein seperater JDBC-Treiber für PostGIS einige zusätzlich ObjektKlassen an, mit denen Geometrie-Objekte direkt in Java erzeugt werden können, diese wurden aber nicht verwendet. Vortests haben gezeigt, dass sie keinerlei Geschwindigkeitsvorteile bieten und nur der einfacheren Erzeugung für
Java-Entwickler dienen. Von daher werden alle Ein- und Ausgaben von Geometrien mittels der in allen Kandidaten unterstützten WKT- und WKB-Formate
realisiert.
3.3
XML-Schemas
Diese Benchmark-Suit wird durch eine Reihe von XML-Dateien konfiguiert
von denen einige im Folgenden kurz vorgestellt werden sollen. Die restlichen
Schemas werden in der Dokumentation zum Programm beschrieben.
Die hier vorgestellten Schemas, dienen der Konfigurierung von
1. Layouts: Tabellen und Spalteninformationen für den Loader.
2. Task: Aufgaben, d.h. SQL-Queries, die zum Testen ausgeführt werden
sollen sowie die dazugehörigen, erwarteten Rückgabetypen.
3. Results: Ergebnisse von Tasks, ggf. mit Fehlermeldungen und Laufzeitinformationen.
Ein Beispiel für eine Layout-Datei ist im Listing 1 gegeben. Es definiert das
Layout einer Tabelle points, die aus den vier Spalten besteht. Diese sind:
id, welches ein künstlicher Primärschlüssel ist, osm_id, dass nicht NULL sein
darf, geom eine Punktgeometrie sowie t_poi. Die ersten beiden Spalten sind
vom Typ LONG, die Geometrie ist vom Typ POINT und die letzte Spalte ist
vom Typ STRING.
1
2
3
4
5
6
7
8
9
Listing 1: example_layout.xml
<?xml v e r s i o n = " 1.0 " ? >
< Layout >
< Table name = " points " >
< Column type = " LONG " primary = " true " > id </ Column >
< Column type = " LONG " notNull = " true " > osm_id </
Column >
< Column type = " POINT " > geom </ Column >
< Column type = " STRING " > t_poi </ Column >
</ Table >
</ Layout >
Die unterstützten Spaltentypen sind:
• BOOL: ein boolscher Wert
• INT : ein ganzzahliger Wert mit 32-bit Genauigkeit
17
3.3 XML-Schemas
Philipp Ledermann
• LONG: ein ganzzahliger Wert mit 64-bit Genauigkeit
• FLOAT : eine Gleitkommazahl mit 32-bit Genauigkeit
• DOUBLE : eine Gleitkommazahl mit 64-bit Genauigkeit
• STRING: ein Textwert mit Unicode-Unterstützung, vorzugsweise mit beliebiger Länge
• GEOMETRY : ein allgemeiner Geometrie-Typ und Oberklasse aller anderen Geometrien
• POINT : eine Punkt
• LINESTRING: ein Kantenzug
• POLYGON : ein Polygon
Alle Typen werden bestmöglich auf die jeweiligen Datenbanktypen gemappt,
siehe dazu Abbildung 2.
Der Datentyp STRING wird in Oracle auf NVARCHAR2(2000) abgebildet, weil
alle Alternativen entweder veraltet oder (im Falle von LOBs) unperformant sind.
Intern
BOOL
INT
LONG
FLOAT
DOUBLE
STRING
GEOMETRY
POINT
LINESTRING
POLYGON
Postgres
boolean
integer
bigint
real
double precision
text
geometry(GEOMETRY, ..)
geometry(POINT, ..)
geometry(LINESTRING, ..)
geometry(POLYGON, ..)
Oracle
NUMBER(1)
NUMBER(10)
NUMBER(19)
FLOAT(63)
FLOAT(126)
NVARCHAR2(1000)
SDO_GEOMETRY
SDO_GEOMETRY
SDO_GEOMETRY
SDO_GEOMETRY
MySQL
BIT(1)
INT
BIGINT
FLOAT
DOUBLE
TEXT
GEOMETRY
POINT
LINESTRING
POLYGON
SQLite
BOOLEAN
INT
BIGINT
FLOAT
DOUBLE
TEXT
GEOMETRY
GEOMETRY
GEOMETRY
GEOMETRY
Abbildung 2: Mapping der Datentypen
Das nächste Beispiel in Listing 2 zeigt eine einfache Task-Datei bestehend aus
nur einer Query, die ausgeführt werden soll. Zunächst wird eine allgemeine
Substituierung in Form eines Key-Value-Paars deklariert, welches später beim
Parsen und Übersetzen in die eigentlichen SQL-Query ungewandelt wird, siehe
dazu Abschnitt 3.4. Die Query erhält den Namen Filtertest, der beim Ausschreiben der Results-Datei wieder als Element enthalten ist. Danach folgt die
generische SQL-Query. Falls eine generische Query für ein bestimmtes Testsystem nicht anwendbar ist, können alternativ dazu auch ganze Abfragen für bestimmte Datenbanken, angegeben werden. Zuletzt muss noch für jede, von der
Datenbank zurückgegebene, Spalte angegeben werden, wie der Rückgabe-Typ
ist. Da dieses einfache Beispiel lediglich eine Zählung mit SELECT COUNT(*)
18
3.3 XML-Schemas
Philipp Ledermann
enthält, ist nur der Rückgabe-Typ LONG für die erste Ergebnisspalte erforderlich.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Listing 2: example_task.xml
<?xml v e r s i o n = " 1.0 " ? >
< Task >
< Substitutions >
< Substitution >
< Tag > MaxId </ Tag >
< Sub > 15000 </ Sub >
</ Substitution >
</ Substitutions >
< Query name = " Filtertest " >
< generalizedQuery >
SELECT [ Oracle: Hint | NO_INDEX ( a
i dx _ o sm _ po l yg o n s_ i d ) NO_INDEX ( b
id x_ os m_p ol yo ns_ id ) ] COUNT (*)
FROM osm_polygons a , osm_polygons b
WHERE a . id [ LessEqual ] [ MaxId ]
AND b . id [ Less ] a . id
AND [ Filter | a . geom , b . geom ]
[ SpatiaLite: IndexHit | osm_polygons , geom ,
a, b]
</ generalizedQuery >
< returnType > LONG </ returnType >
</ Query >
</ Task >
Die Rückgabe-Typen sind ähnlich den Spalten-Typen des Layout-Schemas.
Genauer werden folgende Rückgabe-Typen unterstüzt:
• BOOL: ein boolscher Wert
• INT : ein ganzzahliger Wert mit 32-bit Genauigkeit
• LONG: ein ganzzahliger Wert mit 64-bit Genauigkeit
• FLOAT : eine Gleitkommazahl mit 32-bit Genauigkeit
• DOUBLE : eine Gleitkommazahl mit 64-bit Genauigkeit
• STRING: ein Textwert mit Unicode-Unterstützung, vorzugsweise mit beliebiger Länge
• WKT : Geometrie, als WKT zurückgeliefert
• WKB : Geometrie, als WKB zurückgeliefert
Das letzte Beispiel in Listing 3 zeigt eine Result-Datei passend zur oben angegebenen Task-Datei. Jedes QueryResult korrespondiert zu einem QueryAbschnitt mit dem entsprechden Query-Namen. Für jede Datenbank, die für
diesen Benchmark-Test verwendet wurde, in diesem Fall Postgres und Oracle,
existiert ein DbResult Abschnitt. Jedes DbResult enthält neben dem Namen
19
3.4 Query-Translator
Philipp Ledermann
der entsprechenden Datenbank, die Laufzeiten und die eigentlichen Ergebnisse.
Die Laufzeitmessungen enthalten noch (als zusätzlichen Wert) die Messwerte
der Ergebnisabfrage, weil sich herausgestellt hat, dass einige Datenbanken (z.B.
Oracle) unter bestimmten Vorraussetzungen erst beim Zugriff auf die Ergebnisse Berechnungen durchführen. Jedes Einzelergebnis erhält den Index der
Zeile, sowie die jeweiligen Rückgabewerte im CSV-Format16 . Führt die Ausführung zu einem Fehler oder Timeout wird das XML-Element HasErrors auf
true gesetzt und statt einem oder mehreren Result-XML-Elementen wird ein
Exception-Element mit der Fehlerbeschreibung ausgegeben.
Listing 3: example_result.xml
<?xml v e r s i o n = " 1.0 " encoding = " UTF -8 " standalone = " yes " ?
>
< QueryResults >
< QueryResult >
< QueryName > Polygons Query #1 Filtertest </
QueryName >
< DbResult >
< DbName > Postgres </ DbName >
< Time >5 (5 ,0) </ Time >
< HasErrors > false </ HasErrors >
< Result index = " 0 " > 86844 </ Result >
</ DbResult >
< DbResult >
< DbName > Oracle </ DbName >
< Time > 32 (32 ,0) </ Time >
< HasErrors > false </ HasErrors >
< Result index = " 0 " > 86704 </ Result >
</ DbResult >
</ QueryResult >
</ QueryResults >
\ label { resultXML }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
3.4
Query-Translator
Wie in den vorherigen Abschnitten bereits beschrieben wurde, bietet jedes Datenbanksystem neben der Unterstützung für die SQL-Standard-Syntax auch
eigenen Syntaxen an, die leider in vielen Fällen verschieden sind. Ein bekanntes
Beispiel hierfür ist der Befehl, der die SQL-Ausgabemenge auf einem bestimmten Wert reduziert d.h. statt aller Ergebnisse werden nur n viele zurückgegeben.
In Postgres, MySQL und SQLite funktioniert dies mit dem Befehl Limit n,
der am Ende der SQL-Query angehängt wird. Bei Oracle hingegen muss man
die ggf. bestehenden Prädikaten um [WHERE|AND] rownum <= n ergänzen.
Obwohl für räumliche Erweiterungen mittlerweile Standards wie die Simple
16
Comma-Separated Values, ein Klartext-Format, indem Daten gespeichert werden können. Jede Zeile entspricht einem Tupel und jede Tupelwert wird mit einem Komma getrennt
20
3.4 Query-Translator
Philipp Ledermann
Features for SQL oder SQL-MM (2.4) existieren, werden die in diesen festgelegten Bezeichner nicht immer verwendet, so dass ein Weg gefunden werden
musste von diesen zu abstrahieren. Vergleiche dazu auch Tabelle 1 auf Seite
13.
Der Query-Translator setzt genau hier an und versucht eine einheitliche Basis
zu schaffen, die zu den jeweiligen SQL-Dialekten der einzelnen Datenbanken
übersetzen und von diesen abstrahieren kann. Dafür wurde eine Reihe von
Substitutionen geschaffen, die entweder allgemein als Variablen direkt in einer
Task -Datei angegeben werden können oder im konkreten Fall eines FunktionsMappings direkt an die Datenbank gebunden werden.
Soll ein bestimmter Abschnitt in einer Query nun substituiert werden, so wird
dieser in eckigen Klammern [...] geschrieben. Auf diese wurden z.B. die
Standard-Vergleichsoperatoren mit ihrer sprachlichen Varianten ersetzt, d.h.
[LessEqual] wird mit <= ausgetauscht. Dies war notwendig, weil spitze Klammern in der XML-Syntax eine eigene Bedeutung haben, was zu Konflikten bei
Parsen der XML-Dateien geführt hat.
Wenn es erforderlich war Parameter an bestimmten Stellen einzusetzen, wird
zusätzlich ein senkrechter Strich benutzt, hinter dem die Parameter, mit Komma getrennt, aufgeführt werden. Dies wird beispielsweise für die meisten Geometriefunktionen verwendet, so dass [Disjoint|a.geom, b.geom] in Oracle
mit SDO_RELATE(a.geom, b.geom, ’mask=disjoint’) und in PostGIS mit
ST_Disjoint(a.geom, b.geom) ersetzt wird.
Zuletzt besteht noch die Möglichkeit eine Substitution nur für eine bestimmte
Datenbank zu definieren, alle anderen Datenbanken ersetzen diese dann mit
einem leeren String. Dafür wird am Anfang der Name der Datenbank geschrieben um zu verdeutlichen, dass diese Substitution nur für den entspechenden
Kandidaten gilt.
Für ein vollständiges Beispiel greifen wir auf Query von Abschnitt 3.3 zurück.
1
2
3
4
5
6
Listing 4: Beispiel-Query 1
SELECT [ Oracle: Hint | NO_INDEX ( a id x _ os m _p o ly g o ns _ id )
NO_INDEX ( b idx _o sm _po ly on s_i d ) ] COUNT (*)
FROM osm_polygons a , osm_polygons b
WHERE a . id [ LessEqual ] [ MaxId ]
AND b . id [ Less ] a . id
AND [ Filter | a . geom , b . geom ]
[ SpatiaLite: IndexHit | osm_polygons , geom , a , b ]
Die Beispiel-Query in Listing 4 enthält alle beschriebenden Substitutionsmöglichkeiten und wird in Oracle gemäß Listing 5 und in SpatiaLite nach Listing
6 übersetzt.
21
3.5 Test-Datenbasis
Philipp Ledermann
Listing 5: Übersetzung von Beispiel-Query 1 in Oracle
SELECT /*+ NO_INDEX ( a i d x _o s m_ p ol y g on s _i d ) NO_INDEX ( b
id x_ os m_p ol yo ns_ id ) */ COUNT (*)
FROM osm_polygons a , osm_polygons b
WHERE a . id <= 15000
AND b . id < a . id
AND SDO_FILTER ( a . geom , b . geom )
1
2
3
4
5
Listing 6: Übersetzung von Beispiel-Query 1 in SQLite
SELECT COUNT (*)
FROM osm_polygons a , osm_polygons b
WHERE a . id <= 15000
AND b . id < a . id
AND 1 = 1
AND b . ROWID IN (
SELECT ROWID
FROM SpatialIndex
WHERE f_table_name = ’ osm_polygons ’
AND search_frame = a . geom
)
1
2
3
4
5
6
7
8
9
10
11
Man beachte, dass das dritte Prädikat der SQLite-Abfrage AND 1 = 1 , als
Platzhalter dient, da die eigentliche Filter-Funktion nicht existiert, dafür aber
von der Index-Abfrage abgedeckt wird. Für Oracle muss die Filterfunktion
im Normalfall nicht manuell aufgerufen werden, da Oracle diese automatisch
beim optimieren ergänzt. Da hier aber keine räumliche Operation durchgeführt
werden sollte, war ein expliziter Aufruf notwendig. Das Prädikat 1 = 1 muss
in SQLite nicht berechnet werden, da es scheinbar vom Optimizer automatisch
als Konstante erkannt und entsprechend entfernt wird, wie ein Vortest gezeigt
hat.
3.5
Test-Datenbasis
Der Testdatensatz, der für diesen Benchmark benutzt wurde, stammt von
OpenStreetMap [32], und ist von der Geofabrik [27] als PBF17 -Datei zur Verfügung gestellt worden. Der Datensatz beinhaltet alle akutellen Geometrien aus
Brandenburg, Deutschland. Die Daten wurden mithilfe von Osm2pgsql [31]
initial in PostGIS eingeladen. Dabei findet eine automatische Trennung nach
Punkten, Kantenzügen (Linien) und Polygonen statt und es ergeben sich folgende vier Tabellen:
osm_points Die Daten, die nur aus einzelnen Punkten bestehen, d.h. hauptsächlich POI’s, wie Geschäfte, Wahrzeichen aber auch Busstationen, etc.
osm_lines Alle Kantenzügen, bzw. Linien, die keine wichtigen Straßen darstellen sowie sonstige lange Strukturen wie z.B. Pipelines, Ski-Lifte oder
Stromleitungen.
17
Protocolbuffer Binary Format, ein komprimiertes Geodatenformat
22
3.5 Test-Datenbasis
Philipp Ledermann
osm_roads Alle Kantenzügen, die wichtige Straßen enthalten, d.h. Bundesstraßen, Autobahnen, usw.
osm_polygons Polygone, d.h. Flächen, die auch als solche in OSM klassifiziert
werden. Beispiele sind Gebäude, administrative Grenzen, Wälder etc.
Relationen werden ebenfalls unter Polygone abgespeichert und enthalten
aufgetrennte Multipolygone.
In allen Tabellen existieren die Spalten:
• id: eine natürliche Zahl; fortlaufender künstlicher Schlüssel.
• osm_id: eine natürliche Zahl; künstlicher Schlüssel bzgl. OSM. Diese sind
nicht eindeutig, da Multi-Geometrien automatisch getrennt werden und
deren OSM-Schlüssel somit mehrfach vorkommt.
• geom: Geometrie; die eigentliche Geometrie dieser OSM-Entität.
Darüber hinaus existieren noch eine Vielzahl von Tag-Spalten, die als Textobjekte gespeichert werden. So entspricht die Spalte t_boundary mit dem TextWert ’administrativ’ dem OpenStreetMap-Tag:
boundary:administrativ [32]. Es werden nur die gängigsten Tags importiert
[31]. Für Details siehe die genauen Tabellenlayouts im Anhang A.1.
Aus Optimierungsgründen wurde noch die Spalte g_complexity für Kantenzüge und Polygone hinzugefügt, die die Anzahl der Knoten der entsprechenden Geometrie enthält. Ein Polygon, dessen Geometrie aus 1534 Knoten bzw.
Punkten besteht hat somit den Komplexitätswert 1534.
Nach dem initialen Import der Daten nach Postgres werden diese vom Referenzsystem 4326, also WGS84, nach SRID 3875 transformiert, siehe dazu
Abschnitt 2.2. 3875 ist eine ältere Variante des populären VisualisierungsMerkators (eigentlich: Google Merkator) [29]. Der Grund für diese Transformierung ist, dass alle verwendeten Datenbanken unterschiedlich mit den an die
Geometrien gebunden Referenzsystemen umgehen. In MySQL und SpatiaLite
sind die Referenzsysteme nur ein zusätzliches Datum in den Metadaten, welches sonst keine direkte Anwendung in den Berechnungsfunktionen findet. So
wird auch nicht überprüft ob zwei Geometrien aus dem selben Referenzsystem
stammen, sondern die Koordinaten werden einfach als Punkte im euklidischen
Raum verstanden, über denen naiv gerechnet werden kann [14, p.1322] [15].
Im Vergleich dazu wird bei Postgres im Hintergrund erst überprüft, ob die
Referenzsysteme übereinstimmen und bei Bedarf findet eine automatisch Umwandlung, auf eines der Systeme, statt. Zwar kann Postgres also zwischen den
einzelnen, unterstützten Referenzsystem umrechnen, es fehlt aber auch hier das
tiefere Verständnis, um bezüglich dem Rechnen über kartesischen und geodätischen Koordinaten, zu unterscheiden. Stattdessen existieren die zwei Typen
Geometrie und Geographie, wobei die Geometrien immer mit kartesischen Koordinaten arbeiten, während die Geographien einen Ellipsoid mit geodätischen
23
3.6 Queries
Philipp Ledermann
Daten annehmen. Es liegt hier im Ermessen des Benutzers, den richtigen Datentyp zu wählen [13, p.27-29]. Diese Einschränkungen haben dann bei diesen
drei Datenbanken zur Folge, dass unsachgemäße Benutzung von geodätischen
Koordinaten zu mathematisch unsinnigen Ergebnissen führt, wie beispielsweise
die Distanz von zwei Punkten, als Differenz von Längen- und Breitengraden.
Allein Oracle ist in der Lage, die an ein Referenzsystem gebunden Metadaten
für korrekte Berechnung mit sinnvolle Ergebnissen zu nutzen [7, p.(6-3)]. Da
die Berechnungen dort aber dann wirklich für WGS84 über einen Ellipsoiden
durchgeführt werden würden, was mathematisch wesentlich anspruchsvoller
wäre, musste eine 2D-Projektion gefunden werden, die von allen getesten Datenbanken angeboten wird. Da der reguläre Welt-Merkator und die neuere Variante des Google-Merkator in Oracle 11.2.0.118 nicht zur Verfügung standen,
wurde das Koordinatensystem mit der SRID 3875 (siehe oben) als Alternative
gewählt.
Nach der Transformation des Referenzsystems in PostGIS werden die Testdaten mithilfe des Loaders nach Oracle exportiert, um sie dort vorzuverarbeiten.
Aufgrund der in Oracle verwendeten Datenstruktur zum Speichern von Geometrien, sowie der Architektur der Algorithmen, ist Oracle restriktiver bezüglich
den Geometrieeigenschaften, als es im SFS -Standard gefordert ist. So wird beispielsweise die Richtung der Katenzüge, die die äußeren und inneren Ringe von
Polygonen bilden, vorgegeben. Außerdem muss zwischen allen Punktepaaren
im Datensatz ein Mindestabstand bestehen, der wenn er verletzt wird, z.B.
durch versehentlich doppelt eingetragene OSM-Punkte, zu ungültigen Geometrien führt. Daher ist eine Datenbereinigung in Oracle notwendig. Zu diesem
Zwecke konnte die folgende Hilfsfunktion genutzt werden, die eine genau solche
Reparatur vornimmt:
SDO_UTIL.RECTIFY_GEOMETRY(...) [8, p.3-8].
Nach erneuerter Überprüfung der Validität und ggf. Entfernen von nicht-reparablen
Geometrien, ist der Vorgang abgeschlossen.
Im Anschluß an diese Bereinigung, können die Daten schließlich in alle anderen
Datenbanken geladen werden und sollten damit überall in einem konsistenten
Zustand sein.
3.6
Queries
Die Queries, die zum Testen der Geodatenbanken verwendet wurden, lassen
sich grob in zwei Kategorien unterteilen:
• Einfache Queries
• Komplexe Queries
Bei den einfachen Queries handelt es sich in der Regel um SQL-Abfragen, die
nur eine spezifische räumliche Funktion testen, wie beispielsweise ein Aufruf der
18
Unterstützung für den neuen Google-Merkator existiert erst seit 11.2.0.2
24
3.6 Queries
Philipp Ledermann
für alle Punktepaare die Distanz berechnet. In diesem konkretem Fall benötigt
die Distanzfunktion zwei Parameter, beide zu testende Punkte. Im Gegensatz
zu den Funktionen wo lediglich eine geometrischer Parameter und damit eine
Eingabemenge/Tabelle benutzt wird, existieren für die andere Variante gleich
eine ganze Reihe von Kombinationsmöglichkeiten über der Eingabemenge. Siehe dazu 4.2.1 auf Seite 31.
Ebenfalls Teil der einfachen Queries sind spezielle Benchmarks, die besondere
Features miteinander vergleichen. Ein Beispiel hierfür sind Berechnungen bzw.
Funktionen, die statt einer Merkatorprojektion ein Ellipsoidprojektion verwenden. Oracle unterstüzt dies durch Angabe des Referenzsystems und PostGIS
unterstützt dies nur mithilfe eines gesonderten Datentypen.
Generell war es bei den einfachen Queries nicht immer möglich nur die reinen
Funktionen zu testen, weil durch Selektionen und Prädikate zum Konfigurieren
der Ein- und Ausgabegröße ein geringer Overhead entstanden ist. Wie aber die
Auswertung gezeigt hat, ist dieser im Normalfall zu vernachlässigen.
Da das Übertragen der Ergebnismenge ebenfalls gemessen werden musste, siehe
Abschnitte 3.3 und 4.1, sollten die Rückgabemengen so klein wie möglich ausfallen. Um das Beispiel von oben aufzugreifen, würde man die Distanz für alle
berechneten Punktepaare ausgeben, könnte dies die Laufzeitmessung erheblich beeinflussen. Deshalb wurde nach Möglichkeit mit Standard-Aggregatoren
wie COUNT gearbeitet, die nur einen minimalen Overhead erzeugen sollten und
trotzdem ein verwendbares Ergebnis liefern.
Eine Liste aller getesten räumlichen Funktionen ist in Tabelle 1 auf Seite 13
aufgelistet. Die speziellen Benchmarks, beinhalten
• Index bzw. Filtertest,
• Vergleich von geometrischen und geodetischen Datentypen,
• Laufzeitvergleich zwischen Hinzufügen von besonders komplexen Geometrien (g_complexity),
• Geschwindigkeit von Datenmanipulationsabfragen, wie INSERT, UPDATE
und DELETE,
• Einfluss des Oracle-spezifischen Toleranz-Parameters
und werden im Zuge der Auswertung genauer erklärt.
Die zweite Kategorie von Abfragen sind die komplexen Queries, die natürlichen Praxisabfragen nachempfunden werden sollten und in der Regel mehrere
räumliche Funktionen auf einmal verwenden. Außerdem nutzen diese Abfragen
die deskriptiven Eigenschaften der Daten, d.h. die in Textform gespeicherten
OSM-Tags. Alle durchgeführten komplexen Queries sind als Beschreibungen in
Tabelle 3.6 aufgeführt.
25
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
26
Tabelle 2: Liste aller komplexen Queries
Die Bahnstation mit den meisten POI’s in der unmittelbaren Umgebung.
Auflistung aller POI’s, wo die meisten POI’s auf einem gemeinsamen, räumlichen Punkt zusammenfallen.
Drei Geschäftsarten, die summiert, die meisten POI’s in ihrer Umgebung haben.
POI in einem konkreten Bezirk, der bezüglich Straßen am isoliertesten ist.
Alle Bezirke mit der Anzahl ihrer enthalten POI’s, in einer konkreten Stadt.
Die häufigsten POI-Klassen in einer konkreten Stadt.
Längstes Straßensegment in der Stadt mit der höchsten Einwohnerzahl.
Summe der Straßensegmente in der Stadt mit der höchsten Einwohnerzahl.
Städte mit den meisten Straßen, gemessen an der Summe über alle Straßensegmente.
Alle Hauptstraßen, die eine konkrete, gegebene Linie passieren.
Alle Städte, Orte, Dörfer nahe einer konkreten Autobahn.
Straßensegment mit den meisten nahegelegenen POI’s.
Flächenmässig kleinster Bezirk in größter Stadt.
Die am dichtesten bevölkertsten Regionen.
Administrativer Bereich mit den meisten Bezirken.
Administrativer Bereich mit dem flächenmässig größten Anteil an Natur (ohne Clipping).
Administrativer Bereich mit dem flächenmässig größten Anteil an Natur (mit Clipping).
Administrativer Bereich mit den meisten historischen POI’s in der Umgebung, eigene POI’s nicht mitgezählt.
3.6 Queries
Philipp Ledermann
4 Auswertung
4
Philipp Ledermann
Auswertung
Nach der Durchführung des im letzten Kapitel eingeführten Benchmarks, erfolgt nun die Auswertung der dabei ermittelten Ergebnisse. Begonnen wird
mit einem Vorwort in Abschnitt 4.1. Im Anschluß darauf werden in den Abschnitten 4.2.1 bis 4.2.4 die einzelnen Benchmark-Kategorien präsentiert und
analysiert. Um die gesammelten Messwerte im Abschnitt 4.4 zusammenfassen,
musste vorher in Abschnitt 46 ein Scoring erfolgen. Als Abschluss findet in 4.5
schließlich ein Vergleich mit anderen verwandten Arbeiten statt.
4.1
Vorwort
Bevor die Auswertung der eigentlichen Benchmark-Ergebnisse erfolgt, soll einige besondere Eigenschaften und Aufälligkeiten beschrieben werden, die sich
im Zuge der Auswertung und dessen Vorbereitung herausgestellt haben.
Postgres
Postgres bietet von allen getesten Kandidaten die vollständigste Unterstützung des SQL/MM Standards. Geometriespalten definieren sich neben dem
Geometrietyp zusätzlich über ein Referenzsystem, welches alle in dieser Spalte
enthaltenen Geometrien verwenden müssen. Für alle GeometrieBerechnungen verwendet PostGIS die GEOS-Bibliothek. Allerdings bietet diese nur Unterstützung für kartesische Koordinaten mit allen daraus folgenden
Einschränkungen [13, p.27]. Sollen Berechnungen stattdessen über geodätischen Koordinaten z.B. für WGS84 erfolgen, muss stattdessen der zweite in
PostGIS angebotene geodätische Datentyp Geographie verwendet werden. Da
dieser wesentlich komplexer zu berechnen ist und nur bedingt mit der GEOSBibliothek arbeiten kann, ist der Funktionsumfang bezüglich dieser Datentypen
noch deutlich reduziert und beschränkt sich momentan auf wenige grundlegende Funktionen [13, p.27,529-535].
Oracle
Wie schon in Tabelle 1 auf Seite 13 gezeigt wurde ist, ist der Support für
den SQL/MM Standard stark eingeschränkt. Dies liegt weniger an der Tatsache, dass die entsprechenden Funktionen nicht existieren, sondern vielmehr
in der inkompatiblen Namensgebung. Außerdem unterscheiden sich Parameter
und Rückgabewerte in der Regel deutlich von den Signaturen, die im Standard dafür vorgesehen sind. Aus diesem Grund werden eine Reihe von Funktionssynonyme angeboten. Zusätzlich enthält die offizielle Dokumentation ein
Funktionsmapping zwischen Oracle und SQL/MM. Kurioserweise liefert das
Mapping korrektere Ergebnisse als die Synonyme, so dass momentan noch die
Verwendung der Oracle Funktionen vorzuziehen ist. Wieso der Standard bisher nicht ganz aufgegriffen wurde, ist nicht ganz klar. Da Oracle historisch die
erste Datenbanken mit Geounterstützung in diesem Test ist, stammt das ursprüngliche Design aus einer Zeit vor dem SFS- und SQL/MM-Standard und
27
4.1 Vorwort
Philipp Ledermann
Schnittstellenänderungen sind immer mit erheblichen Aufwand und Kompatiblitätsproblematiken verbunden.
Eine weitere Besonderheit ist die durchgängige Verwendung und Mitführung eines speziellen Toleranz-Parameters. Dieser legt die Genauigkeit der GleitkommaBerechnungen bezogen auf die Standard-Einheit des jeweilig verwendeten Refenzsystems dar. So bedeutet ein Toleranz-Wert von 0.05 im Refenzsystem
WGS-84 eine Genauigkeit von fünf Zentimeter, da die Standardeinheit die
an dieses Refenzsystem gebunden ist, einen Meter beträgt. Dieser Parameter
wird außer in zahlreichen Funktionen auch in der Metatabelle benutzt, wo für
jede Geometrie-Spalte ein Eintrag mit diesem Parameter und einer globalen
Bounding-Box der Daten eingetragen werden muss. Oracle bietet dabei als
einzige Datenbank vollwertiger und transparenten Support der Referenzsysteme an, so dass alle bereitgestellten Operationen über alle definierten Systeme
durchführbar sind. Eigene Refenzsysteme können ebenfalls definiert werden [7,
p.(6-52)].
Weiterhin ist anzumerken, dass nur Oracle (nach Tabellentuning) die parallele Verarbeitung einer einzelnen Abfrage erlaubt. D.h. wo immer eine solche
parallele Verarbeitung möglich war, wurde diese in den folgenden Benchmarks
auch (zum Vorteil von Oracle) verwendet.
Aufgrund der theoretischen Größe von WKT und WKB Ein- und Ausgaben
wurde von den Oracle-Entwicklern zugunsten der Verwendung von CLOB’s
und BLOB’s als Datentyp entschieden. Da dies die einzige Option, auch für
kleine Repräsentanten ist, leidet die Performance hier enorm, welches bei den
Manipulationsabfragen (4.2.3) deutlich erkennbar ist. Zwar ist auch das Laden
von Daten mittels eines Dump oder des eigenen SQL*Loaders möglich, aber
nur wenn die Daten bereits einmal in Oracle geladen wurden oder explizit
für Oracle Spatial bereitgestellt sind. Daher finden diese Optionen in diesem
Benchmark keine Anwendung.
Ebenfalls sein noch darauf hingewiesen, dass die bereits angesprochene Datenarchitektur, wesentlich restriktiver ist, als dies im OGC-Standard vorgeschrieben ist, siehe dazu auch Abschnitt 3.5. Insbesondere die Reihenfolge und damit
die Richtung der Knoten in Polygonen, als auch der Toleranzparameter und
der daraus resultierende Mindestabstand zwischen zwei Knoten, sind für den
Großteil der nicht akzeptierten Geometrien verantwortlich.
Oracle definiert eine Reihe von besonderen Befehlen, die nicht Teil des SQLStandards sind. Einige dieser Befehle die Hints dienen als Hinweise für den
Query-Optimizer, bzw. Query-Planner um die Performance von bestimmten
Abfragen zu verbessern. Wegen einem bekannten Bug 19 [9, p.27-28] im Optimizer, die zu Problemen mit räumlichen Indizies führt musste ein Bugfix bzw.
19
Oracle Doc ID 1268383.1, ein Bug in der Heuristik zur Laufzeitvorhersage von räumlichen
Indizes
28
4.1 Vorwort
Philipp Ledermann
Workaround verwendet werden, der dieses Problem umgeht.
Als letztes seien noch auf eine ganze Menge von Anomalien hingewiesen, die das
Testen mit Oracle unnötig erschwert haben. Gemeint ist mit Anomalien, Verhalten, welches zu unerwarteten Performance-Einbußen geführt hat. Ein gutes
Beispiel hierfür ist, dass ab einem bestimmten Punkt x in der Eingabemenge
(id => x), Abfragen plötzlich unverhältnismässig lange gedauert haben oder
gar nicht mehr terminiert sind. Dieses wie ähnliche Verhaltensaufälligkeiten
sind höchstwarscheinlich auf Bugs im Oracle-Code insbesondere des Optimizers zurückzuführen und mussten im Vorfeld eliminiert werden. Siehe dazu
auch 5.1.
MySQL
MySQL besitzt erst seit 2011 und Version 5.6 die Fähigkeit Beziehungsfunktionen über echten Geometrieformen durchzuführen. Davor waren alle diese
Operationen nur über die minimalen Bounding-Boxen definiert. Interessanterweise waren trotzdem alle Operationen vertreten, wie beispielsweise die Funktion MbrTouch(Geometry, Geometry) die prüft ob sich die Bounding-Boxen
von zwei Geometrien berühren, was natürlich keine Aussage über das Verhalten der echten Geometrien gibt und damit äußerst irreführend ist.
Im Gegensatz zu Postgres und Oracle bietet MySQL keine automatische Indexbenutzung für Geometrien an. D.h. im Gegensatz zu traditionellen Index-Arten
muss dieser Index manuell in die Abfrage hinzugefügt werden, wenn eine Query
diesen benutzen soll. Hier kommen die bereits erwähnten minimalen BoundingBox-Funktionen zum tragen.
Ebenfalls wird derzeit ein Großteil der im SQL/MM-Standard definierten Funktionen nicht angeboten und konnte deshalb nicht getestet werden. Vergleiche
dazu Tabelle 1 auf Seite 13.
Zuletzt bietet MySQL nur einen Pseudo-Support für räumlichen Referenzsysteme an. Zwar können diese für Geometrien festgelegt werden, es findet aber
keinerlei Verwendung dieser statt und alle Punkt- bzw. Knotenwerte werden
lediglich als kartesische Koordinaten im euklidischen Raum ausgewertet [14,
p.1322].
SQLite
Seit einer Überarbeitung der R-Baum20 -Unterstüzung, die notwendig geworden ist, weil die bisher verwendeten Callback-Funktionen nicht mehr unterstüzt
wurden, benutzt SpatiaLite eine virtuelle Tabelle, in der alle räumlichen Indizes zusammengefaßt werden. Wie bei MySQL, ist es hier notwendig einen
20
Der R-Baum ist eine Baumstruktur, die häufig als Index für Geodaten verwendet wird.
Dazu wird in jedem Knoten die minimale Boundingbox gebildet, die aller darunterliegenden
Kindknoten bzw. Geometrien einschließt.
29
4.2 Benchmarks
Philipp Ledermann
Geoindex manuell zu benutzen. Dabei wird eine Unterabfrage verwendet, die
eine Geometrie als Fenster verwendet und alle Geometrien (mittels RowIDs21 )
auflistet, die mit diesem Fenster interagiert. Die zweite Geometrie muss dann
mittels des IN-Operators in dieser Liste gesucht werden. Diese Methode hat
neben einer schlechten Performance noch den Nachteil, dass für eine Negation (z.B. zum Finden von disjunkten Geometrien) NOT IN verwendet werden
müsste, welches in SQLite noch unperformanter ist.
Wie MySQL bietet auch SQLite keinen echten Support für räumliche Referenzsysteme und rechnet mittels der GEOS-Bibliothek ausschließlich im euklidischen Raum und ohne jegliche systembezogene Einheiten [33][17].
4.2
Benchmarks
Alle nachfolgenden Benchmarks wurden auf einem AMD Athlon 64-Bit Prozessor @ 3,9 GHz mit 3 Kernen und 4 GB Arbeitsspeicher durchgeführt. Als
Betriebssystem wurde Oracle Linux 6.4 (64-Bit), ein Linux-Derivat basierend
auf Red Hat Enterprise gewählt. Die SQL-Queries wurden mit dem für diese
Diplomarbeit entwickelten Benchmark-Suit unter Java mittels JDBC ausgeführt.
Um ein WarmUp der Datenbank zu erreichen, wurde jede Query drei mal hintereinander ausgeführt und die Laufzeiten schließlich gemittelt. Es hat sich
dabei herausgestellt, dass das WarmUp, insbesondere bei großen Laufzeiten,
nur einen geringen Einfluss hat. So liegen die Schwankungen bei einer Laufzeit
von 500 Sekunden, etwa bei +/- 5 Sekunden, mit oder ohne WarmUp. Bei
Laufzeiten im Bereich von 20 Sekunden, liegen die Schwankungen bei +/- 2
Sekunden, während das WarmUp bis zu 4 Sekunden betragen kann.
Falls das allgemeine Timeout von 45 Minuten erreicht wurde, ist die entsprechende Abfrage an dieser Stelle abgebrochen worden. Resultierten alle drei
Durchläufe mit einem Timeout wurde dies in den folgenden Diagrammen mit
Timeout gekennzeichnet. Auf gleich Art wurden Abfragen, die nicht unterstützt
wurden, mit Unsupported gekennzeichnet.
Alle Laufzeiten sind in Sekunden angegeben. Wenn nicht anders angegeben
wurde folgendes Farbschema angewendet:
Postgres
Oracle
MySQL
SQLite
hellblau
rot
magenta
grün
Die Abfragen wurden in verschieden Tasks gruppiert, wobei ein zusätzlicher
Tuning-Parameter MAXID, der maximalen Wert für den künstlichen Schlüssel
21
RowID stellen einen automatischen, künstlichen und einzigartigen Schlüssel dar, der in
vielen Datenbanken als zusätzliche Spalte verfügbar ist.
30
4.2 Benchmarks
Philipp Ledermann
dieser Tabelle, verwendete worden ist. Die entsprechenden Werte sind in den
Diagramm-Titel aufgeführt und wurden durch Tuning in Vortests ermittelt. So
liegt dieser beispielsweise für die erste Task, wo Beziehungsoperatoren zwischen
Punkten und Punkten verglichen werden, bei einem Wert von 15.000 . Daraus
ergeben sind 15.000 × 15.000 also 225.000.000 Paare, auf die der Kandidat
die jeweilige Operation anwenden musste. Über dies wurde noch ein zweiter
Tuning-Parameter, eine maximale Komplexität, d.h. eine Beschränkung der
einzelnen Knoten, die ein Kantenzug, bzw. ein Polygon haben darf, eingeführt.
Es sei aber darauf hingewiesen, dass die verwendete Standard-Einschräkung
von g_complexity < 1000 weniger als 130 Polygone und weniger als 110 Kantenzüge aus dem kompletten Datensatz herausfilterte.
Da sich in einigen Task bestimmte Queries oder bestimmte Kandidaten als
besonders zeitintensiv herausgestellt haben, wurde iterativ nachgetestet, insbesondere um eine bessere Auflösung über besonders schnelle Laufzeiten zu
erzielen.
Da trotz Nachtests immer noch erhebliche Unterschiede zwischen den Laufzeiten festzustellen waren, musste zur besseren Präsentation logarithmische
Skalen verwendet werden.
Der Datensatz mit seinen vier Tabellen beinhaltet die folgende Anzahl von
Geometrien:
Tabelle
osm_points
osm_lines
osm_road
osm_polygons
4.2.1
Anzahl der Geometrien
298.984
398.098
44.830
579.240
Benchmarks - Beziehungsfunktionen
In der ersten Benchmarkserie wurden Beziehungsfunktionen, wie sie in Abschnitt 2.4.1 eingeführt wurden, miteinander verglichen. Dabei wurde alle möglichen Kombinationen zwischen den Eingabetabellen verwendet, wie die folgenden Tabelle zeigt. Mit - markierte Kombinationen mussten nicht getestet
werden, da sie redundant sind:
Points
Lines
Polygons
Points
X
-
Lines
X
X
-
Polygons
X
X
X
Es wurde in jeder Task noch zusätzlich eine Count-Query hinzugefügt, die keine geometrische Berechnungen enthält und nur die Anzahl der Kombinationen
bezogen auf den Tuning-Parameter ermittelt und gleichzeitig als zusätzlicher
WarmUp diente. Diese werden in die Diagramme nicht mit aufgeführt.
31
4.2 Benchmarks
Philipp Ledermann
Weiter sei darauf hingewiesen, dass nicht die paarweisen Ergebnisse ausgegeben
wurden, sondern stattdessen nur ein aggregierter Wert mittels dem StandardSQL COUNT. Dies wurde so festgelegt, weil sonst die Ergebnisrückgabe über
JDBC zu Verzerrungen in den Zeitmessungen führen würden, weil beispielsweise beim obrigen Beispiel mit 15.000 Polygonen, 225.000.000 Ergebniszeilen
übertragen werden würden. Dies war dem Verhältnismäßig geringen Einfluß
von COUNT vorzuziehen.
Timeout
Unsupported
Unsupported
100
Distance
Overlaps
Within
Intersects
Disjoint
1
Touches
10
Equals
Runtime in s
1000
Relationship Functions − Points−Points − 15.000
Abbildung 3: Relationsfunktionen - Punkte-Punkte
100
Overlaps
Within
Intersects
1
Touches
10
Equals
Runtime in s
1000
Relationship Functions − Points−Points − 30.000
Abbildung 4: Relationsfunktionen - Punkte-Punkte - Nachtest I
32
4.2 Benchmarks
Philipp Ledermann
100
Overlaps
Within
Intersects
1
Touches
10
Equals
Runtime in s
1000
Relationship Functions − Points−Points − 120.000
Abbildung 5: Relationsfunktionen - Punkte-Punkte - Nachtest II
Beziehungsfunktionen Punkte - Punkte Wie man in den Abbildungen
3, 4 und 5 erkennt, gibt es bereits bei den Punkten, die geometrisch am anspruchslosten sind (0D), große Unterschiede zwischen den einzelnen Kandidaten. Scheinbar ist die Distanz-Operation, die statt einem boolschen Wert
einen numerischen Wert zurückliefert, von diesen Funktionen die anspruchsvollste und nur Postgres konnte bei der kleinsten Eingabemenge, innerhalb des
Timeouts, ein Ergebnis liefern. Dies ist besonders deswegen überraschend, da
wir es mit bereits projezierten Daten und folglich nur mit einer simplen kartesischen Berechnung zu tun haben.
Weiter fällt auf, dass mit Ausnahme von Oracle alle Kandidaten Schwierigkeiten mit der Disjunktheit hatten. Die Ursache hierfür liegt in der Funktionsweise und Verwendung des räumlichen Index. Da der R-Baum, der in allen
Kandidaten verwendet wird, nur Geometrien filtert die potentiell miteinander
interagieren, d.h. deren Bounding-Boxen nicht disjunkt sind, muss der Index
umgekehrt verwendet werden. D.h. die im regulären Filter-Schritt [1, p.203]
ausgewählten Geometrien müssen auf echte Disjunktheit geprüft werden. Alle
nicht ausgewählten Geometrien interagieren nicht und sollten daher ebenfalls
disjunkt sein, müssen nicht extra getestet werden und können sofort zur Ergebnissmenge hinzugefügt werden. Da aber die Disjunktheit auch in späteren
Test äußerst ineffizient ist, wäre es möglich, dass die Algorithmen anders vorgehen und beispielsweise ohne den Index arbeiten. Interessant ist auch das der
R-Baum Index über einer Punktmenge in der tiefsten Ebene, wieder nur den
Punkt selbst erhält, wodurch die höchste Ebene des Baumes leicht zu berechnen ist. Insbesondere SQLite hat aufgrund der speziellen Verwendungsweise
des Index offensichtlich große Probleme mit der Disjunktheit. Fairerweise muss
noch angemerkt werden, dass obwohl Oracle als einziger Kandidat eine gute
Performance für diese Operation liefert, die Ergebnisse nicht mit den Werten
33
4.2 Benchmarks
Philipp Ledermann
der anderen Datenbanken übereinstimmen, da die relationalen Operationen,
wie schon im Vorwort in Abschnitt 4.1 beschrieben, unterschiedlich ausfallen.
Generell liefern Postgres und SQLite die schnellsten Ergebnisse, gefolgt mit
beträchtlichem Abstand von von Oracle und schließlich MySQL. Dies wird
insbesondere in den Nachtests ersichtlich, bei denen eine größere Eingabemenge verwendet wurde und die problematische Disjoint-Operation weggelassen
wurde. Wie man im letzten Nachtest sehen kann, ist SQLite für einige Operationen geringfügig effizienter als Postgres.
Beziehungsfunktionen Punkte - Kantenzüge
Overlaps
Within
Touches
1
Intersects
10
Distance
Timeout
Unsupported
Unsupported
100
Disjoint
Runtime in s
1000
Relationship Functions − Points−Lines − 10.000
Abbildung 6: Relationsfunktionen - Punkte-Kantenzüge
Die Abbildungen 6 und 7 zeigen die Ergebnisse der Benchmarks zwischen Punkten und Kantenzüge. Aufällig im Vergleich zum vorherigen Test ist, dass die
Laufzeiten im Durchschnitt hier deutlich größer ausfallen. Auch hier tritt wieder das gleiche Problem mit der Disjunktheit auf, wenn auch nicht ganz so
ausgeprägt und deutlich wie im letzten Test.
Man beachte die deutlichen Unterschiede in der Performance der OracleOperatoren. Obwohl diese alle dieselbe Funktion SDO_Relate(...) (S. 13) benutzen, resultieren durch die unterschiedliche Abfragemasken deutlich unterschiedliche Laufzeiten. Dabei liefern, die als performant erscheinenden Operationen, Within und Overlaps andere Ergebnisse, als die Konkurrenz, was
entweder dafür spricht, dass diese Operationen anders verstanden werden oder
auf anderen Algorithmen basieren.
Insgesamt fallen die Ergebnisse hier deutlich kompakter aus, was darauf schließen lässt, dass lediglich die Punkt-Punkt-Abfragen in Postgres und SQLite
34
4.2 Benchmarks
Philipp Ledermann
100
Overlaps
Within
1
Touches
10
Intersects
Runtime in s
1000
Relationship Functions − Points−Lines − 20.000
Abbildung 7: Relationsfunktionen - Punkte-Kantenzüge - Nachtest
besser optimiert sind, was nicht verwundern würde, da ja beide auf GEOS basieren und die Laufzeiten damit ähnlich ausfallen sollten.
Der Nachtest bietet ein ähnliches Bild, nur mit dem Unterschied, dass hier
deutlich wird, dass für Postgres offensichtlich noch ein besonderer Optimierungsaufwand betrieben wurde, um die Intersects-Operation zu beschleunigen. Dies mag entweder einer besseren Verwendung des R-Baums geschuldet
sein oder in der Wichtigkeit dieser Operation begründet sein.
Beziehungsfunktionen Punkte - Polygone
Als Nächstes wurde in den Abbildungen 8 und 9 die Kombination von Punkten
und Polygonen getestet.
Auch hier traten wieder die anormalen Laufzeiten bezüglich Disjunktheit auf,
wie schon in den vorherigen Tasks beschrieben.
Wie bei den Punkte-Punkte-Tests sind die Unterschiede zwischen den Laufzeiten hier wieder deutlich ausgeprägter. Postgres und SQLite sind in etwa
ebenbürtig, mit Abstand gefolgt von Oracle und MySQL. Offensichtlich ist
die GEOS-Bibliothek für diese Art der Vergleiche wieder besser gerüstet. Der
Nachtest bestätigt das Ergebnis und liefert sonst keine neuen Erkenntnisse.
Beziehungsfunktionen Kantenzüge - Kantenzüge
Die Abbildungen 10 und 11 zeigen die Ergebnisse der Beziehungsfunktionen
bei Kantenzügen. Generell ist zu bemerken, dass obwohl der Tuning-Parameter
hier relativ klein gewählt ist, trotzdem hohe Laufzeiten resultierten. Genau
35
4.2 Benchmarks
Philipp Ledermann
Timeout
Unsupported
Unsupported
100
Distance
Overlaps
Within
Intersects
1
Touches
10
Disjoint
Runtime in s
1000
Relationship Functions − Points−Polygons − 10.000
Abbildung 8: Relationsfunktionen - Punkte-Polygone
100
Overlaps
Within
1
Touches
10
Intersects
Runtime in s
1000
Relationship Functions − Points−Polygons − 20.000
Abbildung 9: Relationsfunktionen - Punkte-Polygone - Nachtest
wie bei den Polygonen, ist bei den Kantenzügen die Komplexität ein wichtiger
Faktor und mussten entsprechend beschränkt werden.
Es fällt auf das Oracle hier offenbar große Schwierigkeiten hat, was sich in
den sehr hohen Laufzeiten wiederspiegelte. Zwar ist MySQL in diesen Tests
überraschend schnell, aber die zurücklieferten Werte unterscheiden sich stark
von denen aus Postgres und SQLite, was naheliegt, dass sie nicht korrekt waren.
Die Intersects-Operation scheint in Postgres besser optimiert zu sein, wie
auch der Nachtest im Vergleich mit SQLite deutlich macht.
36
4.2 Benchmarks
Philipp Ledermann
Unsupported
Unsupported
100
Distance
Overlaps
Within
Intersects
Disjoint
1
Touches
10
Equals
Runtime in s
1000
Relationship Functions − Lines−Lines − 800
Abbildung 10: Relationsfunktionen - Kantenzüge-Kantenzüge
100
Overlaps
Within
Touches
Intersects
1
Disjoint
10
Equals
Runtime in s
1000
Relationship Functions − Lines−Lines − 5.000
Abbildung 11: Relationsfunktionen - Kantenzüge-Kantenzüge - Nachtest
Beziehungsfunktionen Kantenzüge - Polygone
Beim Vergleich der Ergebnisse zwischen Kantenzüge und Polygonen in den
Abbildungen 12 und 13, setzt sich der Trend der Vorgänger fort.
Auch hier leidet Oracle wieder unter einer schlechten Performance, während
MySQL größtenteils falsche Ergebnisswerte liefert.
Interessant ist, dass die Postgres Intersect-Operation auf den ersten Blick
nicht so effizient wie zuvor arbeitet. Ein Nachtest zeigt aber, dass dies entwe-
37
4.2 Benchmarks
Philipp Ledermann
Overlaps
Within
Intersects
1
Touches
10
Distance
Timeout
Unsupported
Unsupported
100
Disjoint
Runtime in s
1000
Relationship Functions − Lines−Polygons − 1.000
Abbildung 12: Relationsfunktionen - Kantenzüge-Polygone
100
Overlaps
Within
Intersects
1
Touches
10
Disjoint
Runtime in s
1000
Relationship Functions − Lines−Polygons − 10.000
Abbildung 13: Relationsfunktionen - Kantenzüge-Polygone - Nachtest
der durch einen Ausreißer in den Messungen oder nur durch eine ungünstigen
Eingabemenge begründet ist, da hier wieder deutlich besser Laufzeiten ermittelt wurden. Unterstützt wird die zweite Theorie von der uncharakterischen
schnellen Performance des Disjoint-Operators über alle Kandidaten hinweg,
die nur beim ersten Test und damit bei der kleineren Eingabemenge zu beobachten ist.
38
4.2 Benchmarks
Philipp Ledermann
Timeout
Unsupported
Unsupported
100
Distance
Overlaps
Within
Intersects
Disjoint
1
Touches
10
Equals
Runtime in s
1000
Relationship Functions − Polygons−Polygons − 2.000
Abbildung 14: Relationsfunktionen - Polygone-Polygone
100
Overlaps
Within
Touches
Disjoint
1
Intersects
10
Equals
Runtime in s
1000
Relationship Functions − Polygons−Polygons − 5.000
Abbildung 15: Relationsfunktionen - Polygone-Polygone - Nachtest I
Beziehungsfunktionen Polygone - Polygone
Als letzter Test in diesem Abschnitt kommen wir nun zum Vergleich zwischen
den Laufzeiten der Beziehungsfunktionen bei Polygonen, dargestellt in den Abbildungen 14, 15 sowie 16.
Auch hier weichen die tatsächlichen Ergebnisswerte von Oracle und MySQL
von denen aus Postgres und SQLite ab, im Falle von MySQL allerdings nicht
so ausgeprägt, wie noch bei den Tests der Kantenzüge.
39
4.2 Benchmarks
Philipp Ledermann
Overlaps
Within
Intersects
1
Touches
10
Equals
Runtime in s
100
Relationship Functions − Polygons−Polygons − 10.000
Abbildung 16: Relationsfunktionen - Polygone-Polygone - Nachtest II
Oracle hat offensichtlich, trotz der Beschränkung der Komplexität mittels TuningParameter, Probleme mit größeren Geometrien, was sich in der höchsten Laufzeit wiederspiegelt.
Es treten weiterhin die bekannten Anomalien bezüglich der Disjunktheit auf.
Die Nachtests zeigen einen klaren Nachteil in der Geschwindigkeit verglichen
zu Postgres und SQLite.
Zusammenfassung - Beziehungsfunktionen
Die Ergebnisse des ersten Benchmark-Segments, indem die Beziehungsfunktionen zwischen Eingabemengen verschiedenen Geometrietyps getestet wurden,
lassen sich wie folgt zusammenfassen:
Performance und Ergebniswerte von Postgres und SQLite sind bis auf wenige
Ausnahmen identisch, mit einem leichten Vorteil für Postgres bezüglich Laufzeit und Konsistenz. Zurückzuführen ist diese Ähnlichkeit auf die Tatsache,
dass beide für Geometrie-Berechnungen auf die GEOS-Bibliothek zurückgreifen. Postgres besitzt einen messbaren Vorteil in Bezug auf den IntersectsOperator.
MySQL und Oracle liefern andere Ergebnisse als Postgres und SQLite. Bei
Oracle sind mögliche Gründe das schlechte Funktions-Mapping und historisch
andere Definitionen (vergleiche Tabelle 1 und Abschnitt 4.1). Bei MySQL sind
diese Operation erst relativ neu und möglicherweise noch nicht ganz fehlerfrei
und ausgereift.
40
4.2 Benchmarks
Philipp Ledermann
Die Performance von Oracle bezüglich einfacherer Strukturen wie kleinen Polygonen, kleinen Kantenzügen und natürlich Punkten ist gut, leidet aber offensichtlich mit steigender Komplexitäten. Dies wurde insbesondere in den, nicht
enthaltenen, Vortests ersichtlich, wo Oracle ohne den Komplexitätsparameter
stetig Timeouts generierte.
MySQL scheint nicht anfällig gegenüber komplexen Geometrien und liegt mit
der Performance nur etwas hinter SQLite zurück, siehe dazu auch Abschnitt
46.
Alle Kandidaten haben Probleme mit dem Disjunkt-Operator. Dies mag wie
schon beschrieben mit der Verwendung des R-Baums als Index zusammenhängen.
Generell sind die Laufzeiten abhänigig von der jeweiligen Eingabemenge und
können dabei entweder besonders günstig oder ungünstig ausfallen, wobei die
Unterschiede nicht allzu groß sind.
4.2.2
Benchmarks - Funktionen
In der nächsten Serie von Benchmarks sollen nun weitere Funktionen aus dem
SQL/MM-Standard untersucht werden. Im Gegensatz zur ersten Serie werden
hier vorwiegend Polygone verwendet, weil diese komplexer als die Punktedaten und einfacher als die Kantenzüge waren und damit ein gutes Mittelmaß an
Komplexität darstellten. Außerdem unterstützen Polygone die meisten Funktionen. Der generelle Aufbau und die Durchführung ist äquivalent zum ersten
Teil.
Zugriffsfunktionen
Als erster Task in dieser Serie wurden einfache Zugriffsmethoden getestet, die
auf eine einzelne Geometrie anwendbar sind. So testet die Funktion IsValid
ob eine bestimmte Geometrie den Anforderungen des zugrundeliegenden Standards entspricht und sie korrekt verarbeitet werden kann. Die Ergebnisse sind
in Abbildung 17 aufgeführt.
Auffallend ist, dass der Tuning-Paramter hier sehr groß ausfallen konnte und
damit praktisch den kompletten Datensatz umfaßt, da die entsprechenden Implementierung vergleichsweise effizient ausfallen. Man muss allerdings berücksichtigen, dass hier kein Kreuzprodukt verwendet wurde. Einzig die IsValidFunktion von Oracle benötigt etwas länger. Dies mag an zwei Ursachen liegen:
Erstens sind wie schon in Abschnitt 3.5 beschrieben die Gültigkeitskriterien von
Oracle sehr viel enger gefaßt als im SFS-Standard vorgeschrieben, zweitens ist
die Ausgabe dieser Funktion bei Oracle kein einfacher boolscher Wert, sondern
vielmehr eine Textbeschreibung, die im Falle einer nicht gültigen Geometrie
detallierte Fehlerinformationen liefert und daher wesentlich sperriger ausfällt.
41
4.2 Benchmarks
Philipp Ledermann
Unsupported
NumPoints
Unsupported
IsSimple
IsValid
Unsupported
Unsupported
IsEmpty
1
GeometryType
10
SRID
Unsupported
100
Dimension
Runtime in s
1000
Access Functions − Polygons − 300.000
Abbildung 17: Zugriffsfunktionen - Polygone
Allerdings sei hier darauf hingewiesen, dass wie schon in der Erklärung des Datenimports in Abschnitt 3.5 aufgeführt wurde, die Daten mit Oracle bereinigt
worden sind und damit keine Fehler enthalten.
Warum die Funktionen zur Bestimmung der Dimension und des Geometrietyps in SQLite etwas weniger effizient sind, ist nicht ersichtlich. Möglicherweise
halten sich Postgres und MySQL diese Informationen in einer virtuellen Metatabelle.
Da Oracle weder leere noch nicht-einfache Geometrien unterstützt, wurden die
darauf testenden Funktionen offenbar nicht, auch nicht als Konstanten, implementiert.
Wie schon im Vorwort zur Auswertung beschrieben, sind die SRID-Informationen bei Oracle in einer Metatabelle gespeichert. Demnach wurde auch die
Funktion. die für eine Geometrie das Referenzsystem zurückliefert nicht integriert. Alternativ wäre es aus Sicht der Entwickler möglich gewesen, diese
Funktion stattdessen über eine Spalte auszuführen, allerdings wurde auch diese Möglichkeit nicht eingebaut, so dass diese Information nur mithilfe einer
seperaten SQL-Abfrage möglich ist.
MySQL unterstützt hier die wenigsten Funktionen. Insbesondere die schnell
erscheinde Validitätsprüfung ist nur ein Platzhalter und führt keinen echten
Code aus [14, 1322].
42
4.2 Benchmarks
Philipp Ledermann
WKB Roundtrip
ToWKB
1
WKT Roundtrip
10
ToWKT
Runtime in s
100
I/O Functions − Polygons − 20.000
Abbildung 18: Ein- und Ausgabefunktionen - Polygone
WKB Roundtrip
ToWKB
1
WKT Roundtrip
10
ToWKT
Runtime in s
100
I/O Functions − Polygons − 400.000
Abbildung 19: Ein- und Ausgabefunktionen - Polygone - Nachtest
Ein- und Ausgabefunktionen
Im nächsten Test wurden die geometrischen Ein- und Ausgabefunktionen untersucht. Dieser Test verwendet nur die allgemeinen, in der SFS-Spezifikation
aufgeführten und von allen Kandidaten unterstützten WKT- und WKB-Formate,
die bereits im Abschnitt 2.4.1 beschrieben wurden. Allerdings existieren noch
eine Reihe anderer Möglichkeiten geometrische Daten zu laden, u.a.
• Importieren mit mitgelieferten, externen Tools über Dumps, CSV-Daten
oder Shapefiles. Möglich in Postgres und Oracle und durch eigene Pfade
sehr effizient.
43
4.2 Benchmarks
Philipp Ledermann
• Oracle bietet einen zusätzlichen, auf dem eigenem Format zugeschnittenenen, Geometriekonstruktor an.
• Es ist möglich Daten über Schnittstellen wie JDBC zu laden, wobei hier
natürlich die Effizienz der Schnittstelle eine Rolle spielt. Postgres und
Oracle bieten außerdem die Möglichkeit an, Geometrien in JDBC als
Javaobjekte darzustellen. Vortests haben aber gezeigt, dass beim Senden
und Empfangen zur Datenbank tatsächlich nur die WKT- und WKBFormate verwendet werden.
Weil an dieser Stelle nur reine SQL-Funktionen getestet werden sollten und
weder Schnittstellen noch Einfüge-Operationen wurde nur die Ausgabefunktionen, sowohl für WKT als auch WKB, und zusätzlich ein Roundtrip, d.h. eine
Abfrage der Form von_WKT(zu_WKT(Geometrie)) verwendet. Bei den Roundtrips
muss demnach der doppelte Aufwand berücksichtigt werden.
Wie man an den Ergebnissen in den Abbildungen 18 und 19 sehen kann, ist
die Laufzeit von Oracle in diesem Test mit Abstand am schlechtesten. Dies ist
auch nicht verwunderlich, da ja bereits im Vorwort (4.1) erwähnt wurde, das
Oracle für diesen Funktionen CLOBs bzw. BLOBs verwendet, welche insgesamt
einen sehr hohen Overhead erzeugen und nicht auf schnellen Zugriff ausgelegt
sind.
Der Nachtest zeigt, dass die restlichen Kandidaten nur geringe Unterschiede
besitzen.
Weiter wird auch deutlich, dass das WKB-Format aufgrund seiner Binärkompression gegenüber dem WKT-Format offenbar klar im Vorteil ist, was aber
auch nicht weiter verwunderlich ist, schließlich dient das WKT-Format nur der
besseren Lesbarkeit des Menschen.
Messfunktionen
Als nächstes wurden Messfunktionen bezüglich Kantenzüge und Polygonen
getestet. Die eigentlich wichtigen Funktionen in dieser Kategorie sind Length
zur Bestimmung der Länge von Kantenzügen und Area zur Bestimmung des
Flächeninhalts von Polygonen. Da hier eine Trennung bezüglich Geometriearten erfolgen musste, wurden in dieser Task gleich andere, geometriespezifische
Funktionen mitgetestet. Die Ergebnisse sind in den Abbildungen 20 und 21
enthalten.
Wie bereits im ersten Teil des Bechmarks bezüglich der Beziehungsfunktionen
beschrieben, scheint Oracle auch hier wieder Probleme mit den doch etwas
komplexeren Kantenzügen zu haben, da es in diesem Test mit Abstand hinter
der Konkurrenz zurückliegt. Allerdings liefert es auch für die Polygone nicht
viel bessere Laufzeiten.
44
4.2 Benchmarks
Philipp Ledermann
Unsupported
IsRing
Length
1
Unsupported
10
Unsupported
Unsupported
100
IsClosed
Runtime in s
1000
Measurement Functions − Lines − 300.000
Abbildung 20: Messfunktionen - Kantenzüge
100
NumInteriorRings
ExteriorRing
1
Centroid
10
Area
Runtime in s
1000
Measurement Functions − Polygons − 300.000
Abbildung 21: Messfunktionen - Polygone
MySQL überrascht mit guter Performance bei korrekten Ergebnissen, während
SQLite etwas zurückliegt, was möglicherweise an dem SQL-SUM-Aggregator
liegt, der für diesen Test verwendet wurde.
Ansonsten fällt auf, dass zwei Funktionen in Oracle und MySQL fehlen und
nicht implementiert wurden.
Das Bild bei den Messfunktionen für Polygone ist vergleichbar. Postgres und
MySQL sind am schnellsten, gefolgt von SQLite und mit großer Differenz,
Oracle.
45
4.2 Benchmarks
Philipp Ledermann
Mengenoperationen
Unsupported
Difference
Intersection
1
SymDifference
10
Unsupported
Unsupported
100
Buffer (50.000)
Runtime in s
1000
Set Operations − Polygons − 1.000
Abbildung 22: Mengenoperationen - Polygone
100
Difference
1
SymDifference
10
Intersection
Runtime in s
1000
Set Operations − Polygons − 2.000
Abbildung 23: Mengenoperationen - Polygone - Nachtest
Neben den Beziehungsfunktionen, die sich allerdings über eine gut optimierte
Matrixfunktion konstruieren lassen (siehe 2.4.1), sind die Mengenoperationen
wie der Durchschnitt oder die Differenz die algorithmisch anspruchsvollsten
Methoden. Da hier auch wieder das Kreuzprodukt, bzw. ein JOIN verwendet
wird, ist es auch nicht verwunderlich, dass hier der Berechnungsaufwand wieder höher ist. Da die Buffer22 -Funktion zwar relativ aufwendig ist, dabei aber
22
Die Buffer-Funktion vergrößert eine Geometrie um eine bestimmte Größe. Ein Polygon
46
4.2 Benchmarks
Philipp Ledermann
nur auf einer Geometrie operiert, wurde hier ein separater Tuning-Parameter
verwendet.
Wie man in den Ergebnissen in den Abbildungen 22 und 23 sehen kann,
sind Postgres und SQLite sind in diesem Test wieder am schnellsten. Oracle
liegt wieder weit hinter der Konkurrenz zurück und MySQL bietet außer der
Buffer-Operation keine Implementierungen an, ist dafür bei dieser aber genauso schnell wie Postgres. Der Nachtest bestätigt, dass Unterschiede zwischen
Postgres und SQLite nur minimal sind.
Transformationsfunktionen
Unsupported
Unsupported
Unsupported
Unsupported
Boundary
1
Transform
10
ConvexHull
Unsupported
100
Envelope
Runtime in s
1000
Transform Functions − Polygons − 500.000
Abbildung 24: Transformationsfunktionen - Polygone
Schließlich wurden noch die Transformationsfunktionen untersucht. Die Ergebnisse zu diesem Test befinden sich in der Abbildungen 24. Man beachte die
relativ große Eingabemenge.
Hier bietet sich das gleiche Bild wie beim vorherigen Test. Postgres und SQLite
liegen noch relativ nahe beinander, während Oracle weit mehr Zeit benötigt.
MySQL unterstützt nur eine Funktion, diese dafür aber effizient.
Besonders interessant ist, dass die Funktion zur Berechnung des Envelope, also der minimalen Bounding-Box, für Oracle tatsächlich langsamer ist, als die
algorithmisch aufwendige Berechnung der konvexen Hülle.
Die Transform-Operation, die eine Geometrie von einem Refenzsystem in ein
anderes überführt, ist in Postgres effizienter als in SQLite, was daran liegen
dehnt sich aus, ein Punkt wird zu einem Kreis, ein Kantenzug wird zu einem Schlauch.
47
4.2 Benchmarks
Philipp Ledermann
mag, dass wie im Vorwort beschrieben SQLite keine echte Unterstützung für
geodätische Referenzsysteme liefert und für Transformationen auf die PROJ.4Bibliothek zurückgreifen muss.
Zusammenfassung - Funktionen
Bezüglich der zweiten Benchmarkserie fällt zunächst auf, dass MySQL etwa
die Hälfte der Abfragen nicht unterstützt. Insbesondere die recht anspruchsvollen, aber wichtigen, Mengenoperationen werden nicht angeboten, dafür sind
die unterstützen Operationen dieses Abschnittes um so effizienter.
Auch Oracle unterstüzt einige Funktionen nicht, wobei aber alle wichtigen
Funktionen vertreten sind. Im Gegensatz zu MySQL ist die Performance, aber
über alle Tests hinweg ernüchternd, speziell bei den Ein- und Ausgabefunktionen sowie den Mengenoperationen.
SQLite liefert in dieser Testserie schlechtere Ergebnisse ab, als noch im ersten
Teil und kann nicht zu Postgres aufschließen. Dafür unterstüzt es wie Postgres
alle Funktionen und ist frei von Ausreißern.
Postgres ist in diesem Abschnitt der beste Kandidat und konnte mit vollständiger Unterstützung und bester Geschwindigkeit überzeugen.
4.2.3
Benchmarks - Spezialtests
In der letzten Serie von einfachen Queries sollen sonstige Eigenschaften überprüft werden. Wie im zweiten Benchmarkteil, werden auch hier, mit dem gleichen Argument, vorwiegend Polygone verwendet. Die zu Beginn festgelegten
Farbeinteilungen werden in einigen der folgenden Tests abgeändert und durch
die in den Diagrammen befindlichen Farblegende ersetzt.
Filter
Da die Analyse der Beziehungsfunktionen bereits ausgiebige Verwendung von
den geometrischen Indizes macht, soll nun der Filter-Schritt gesondert betrachtet werden. Es wurden je ein Polygonpaar ausgewählt und deren potentielle
Interaktion mittels der Filter -Operation überprüft. Diese sollte eigentlich keine echten Berechnungen durchführen, sondern nur den den jeweiligen R-Baum
abfragen. Die Ergebnisse sind in Abbildung 25 präsentiert.
Wie man sieht, sind die Implentierungen von Postgres und SQLite am schnellsten. Oracle folgt mit einigem Abstand und die mit Abstand langsamste Implementierung, scheint in MySQL gegeben zu sein.
Geodätischer Datentyp
Als nächstes wurden die geodätischen Datentypen untersucht, d.h. Geometrien deren Berechnung nicht in einem projezierten Koordinatensystem, sondern
48
4.2 Benchmarks
Philipp Ledermann
Runtime in s
1000
100
10
Filter
1
Filter − Polygons − 15.000
Abbildung 25: Index Filterfunktion - Polygone
Postgres Geometry
Postgres Geodetic
Oracle Geometry
Oracle Geodetic
100
Intersection
Buffer (Area)
Area
1
Intersects
10
Filter
Runtime in s
1000
Geodetic Data − Polygons − 1.000
Abbildung 26: Geodetischer Datentyp - Polygone
über einem Ellipsoid mit geodätischen Koordinaten bestehend aus Längenund Breitengraden erfolgten. Damit lassen sich beispielsweise Berechnungen
im WGS84 -Referenzsystem durchführen.
Da lediglich Postgres und Oracle geodätische Berechnungen unterstützen und
die Funktionen in Postgres bezüglich des Geographietyps massiv eingeschränkt
sind, konnte nur ein geringe Untermenge aller Funktionen getestet werden. Die
Ergebnisse sind in den Abbildungen 26, 27 sowie 28 aufgeführt.
Man beachte, dass die Filter- und Intersects-Operatoren zwei Geometrien
erfordern und deren Eingabemenge durch einen JOIN damit deutlich größer
ausfällt.
49
4.2 Benchmarks
Philipp Ledermann
10000
Postgres Geometry
Postgres Geodetic
Oracle Geometry
Oracle Geodetic
Runtime in s
1000
100
Buffer (Area)
Area
Filter
1
Intersects
10
Geodetic Data − Polygons − 5.000
Abbildung 27: Geodätischer Datentyp - Polygone - Nachtest I
Postgres Geometry
Postgres Geodetic
Oracle Geometry
Oracle Geodetic
100
Buffer (Area)
1
Area
10
Filter
Runtime in s
1000
Geodetic Data − Polygons − 50.000
Abbildung 28: Geodätischer Datentyp - Polygone - Nachtest II
Wie man vorallem in den Nachtests gut erkennen kann, entstehen durch die
geodätischen Koordinaten in Oracle scheinbar kein großer Mehraufwand, im
Gegenteil scheint sich die Performance sogar etwas zu verbessern. Dabei schwankt
Intersects mit unterschiedlichen Eingabegrößen, was sich durch günstige
bzw. ungünstige Polygonkombinationen in der Eingabemenge erklären lässt.
Ein Grund für die besseren Laufzeiten bei geodätische Daten, mag in einer
besseren Optimierung, zugunsten von WGS84, liegen.
50
4.2 Benchmarks
Philipp Ledermann
Die Geographie-Performance der unterstützten Funktionen ist in Postgres, bis
auf die Intersects-Operation, zur Geometrie-Performance vergleichbar.
Generell ist Oracle auch mit dieser Art von Daten langsamer als Postgres.
Dafür unterstützt es für projezierten, als auch geodätische Daten, die gleiche
Menge an Funktionen.
Komplexitätsvergleiche
45
40
Runtime in s
35
30
Postgres
Postgres HC
25
20
15
10
Distance
Overlaps
Within
Touches
Intersects
Disjoint
0
Equals
5
HighComplexity − Postgres − Polygons − 1.500
Abbildung 29: Komplexitätsvergleich - Postgres - Polygone
In diesem Abschnitt wurde der Performanceverlust durch Geometrien mit
hoher Komplexität untersucht. In den folgenden Diagrammen bezeichnet HC
dieselbe Eingabemenge nur mit Unterlassung der Komplexitätsbeschränkung.
Die Menge, der normalerweise durch diesen Tuning-Parameter herausgefilterten Polygone beläuft sich, für den ganzen Datensatz, auf weniger als 130 und
vergrößert die Eingabemenge daher nur minimal. Die wenigen KomplexitätsAusreißer in den Testdaten beinhalten teilweise weit mehr als 1.000 Knoten
und sind faßt ausschließlich Bounding-Polygone der administrative Gebiete,
d.h. im Falle von Deutschland der Landes-, Kreis- und Städtegrenzen. Da alle
Kombinationen berechnet werden, verhält sich die Eingabemenge nicht mehr
linear zu Größe des Kreuzproduktes, was besonders in den Nachtests ersichtlich wird. Außerdem kommen dort (zwischen den ID’s 5.000 und 10.000) die
aufwendigsten bzw. komplexesten Geometrien zu tragen: Die Bundeslandgrenzen von Berlin und Brandenburg.
Wie man in den jeweils ersten Abbildungen sieht, ist der Unterschied bei den
kleineren Eingabemengen noch relativ subtil. Dies ändert sich bei den zweiten
Eingabemengen, wo bereits relativ große Unterschiede sichtbar sind. Man beachte das hier lineare Skalen verwendet wurden.
51
4.2 Benchmarks
Philipp Ledermann
60
Runtime in s
50
40
30
Postgres
Postgres HC
20
Overlaps
Within
Intersects
Equals
0
Touches
10
HighComplexity − Postgres − Polygons − 10.000
Abbildung 30: Komplexitätsvergleich - Postgres - Polygone - Nachtest
450
400
Oracle
Oracle HC
300
250
200
150
100
Overlaps
Within
Touches
Intersects
0
Disjoint
50
Equals
Runtime in s
350
HighComplexity − Oracle − Polygons − 1.500
Abbildung 31: Komplexitätsvergleich - Oracle - Polygone
52
4.2 Benchmarks
Philipp Ledermann
7
MySQL
MySQL HC
6
Runtime in s
5
4
3
2
Overlaps
Within
Intersects
Disjoint
Equals
0
Touches
1
HighComplexity − MySQL − Polygons − 1.500
Abbildung 32: Komplexitätsvergleich - MySQL - Polygone
MySQL
MySQL HC
90
80
Runtime in s
70
60
50
40
30
20
Overlaps
Within
Touches
Intersects
0
Equals
10
HighComplexity − MySQL − Polygons − 10.000
Abbildung 33: Komplexitätsvergleich - MySQL - Polygone - Nachtest
53
4.2 Benchmarks
Philipp Ledermann
18
16
SQLite
SQLite HC
Runtime in s
14
12
10
8
6
4
Overlaps
Within
Intersects
Disjoint
Equals
0
Touches
2
HighComplexity − SQLite − Polygons − 1.500
Abbildung 34: Komplexitätsvergleich - SQLite - Polygone
70
60
Runtime in s
50
SQLite
SQLite HC
40
30
20
Overlaps
Within
Touches
Intersects
0
Equals
10
HighComplexity − SQLite − Polygons − 10.000
Abbildung 35: Komplexitätsvergleich - SQLite - Polygone - Nachtest
54
4.2 Benchmarks
Philipp Ledermann
Die Ergebnisse von SQLite in den Abbildungen 34 und 35 sind vergleichbar
mit dem denen von Postgres in Abbildungen 29 und 30, was wieder auf GEOS
zurückzuführen ist.
MySQL (Abbildungen 32, 33) überrascht hier mit relativ geringen Unterschieden selbst bei der größeren Eingabemenge mit den komplexesten Geometrien.
Im Vergleich dazu hat Oracle (Abbildung 31)bereits mit wenigen, weniger komplexen Geometrien zu kämpfen. Die Within und Overlaps-Operationen liefern,
wie auch schon früher beschrieben, andere Ergebnisse und erscheinen deshalb
effizienter, als sie es vermutlich sind. Da die Nachtests für alle Queries zu Timeouts führten, wurde die betreffende Abbildung weggelassen.
Manipulationsoperationen
Sheet2
Runtime in s
Benchmark - Insert (1)
2000
1800
1600
1400
1200
1000
800
600
400
200
0
Postgres
Oracle
MySQL
SQLite
0
1000
2000
3000
4000
5000
6000
Batchsize
Abbildung 36: Datenmanipulation - Einfügen - Polygone
Nachdem bereits in der zweiten Benchmarkserie, die Performance der geometrischen Ein- und Ausgabefunktionen untersucht wurden, soll nun die Effektivität
der SQL Manipulationsoperationen INSERT, UPDATE sowie DELETE, bezüglich
des Geometrietyps, untersucht werden.
Zu diesem Zweck wurden 100.000 Polygone aus dem Datensatz in eine CSVDatei exportiert, um diese anschließend in eine leere Tabelle einzufügen, abzuändern und schließlich wieder zu entfernen. Im Gegensatz zu den bisherigen
Test, spielt hier auch die Performance des JDBC-Treibers eine Rolle, wobei
dieser nun einen relativ geringen Einfluss auf die Laufzeiten hatte, wie ein
Vortest gezeigt hat. Da für JDBC die Batchgröße, also die Anzahl der Tupel
bzw. Zeilen, die mit einer Abfrage übermittelt werden, ein wichtiger Faktor ist,
wurden mehrere Einstellungen getestet. Wie bereits im Einleitung (2.1.4) erwähnt, besitzt der SQLite-JDBC-Treiber keinen echten Batch-Modus und alle
55
4.2 Benchmarks
Philipp Ledermann
Sheet3
Benchmark - Insert (2)
60
Runtime in s
50
40
Postgres
MySQL
SQLite
30
20
10
0
0
1000
2000
3000
4000
5000
6000
Batchsize
Abbildung 37: Datenmanipulation Sheet2
- Einfügen - Polygone - Nachtest
Runtime in s
Benchmark - Update (1)
1000
900
800
700
600
500
400
300
200
100
0
Postgres
Oracle
MySQL
SQLite
0
200
400
600
800
1000
1200
Batchsize
Abbildung 38: Datenmanipulation - Aktualisieren - Polygone
Manipulationsabfragen mussten daher einzeln übertragen werden.
Wie man in den Abbildungen 36, 37, 38, 39 für INSERT und UPDATE sehen kann,
liegt Oracle weiter hinter der KonkurrenzPage
zurück.
Außerdem wird hier die bes4
te Performance durch einen sehr kleinen Batchwert erreicht. Die Ursachen für
dieses Verhalten liegen in der Verwendung von BLOB’s und CLOB’s und wurden
bereits mehrfach diskutiert. Hierzu existieren in JDBC leider keine Alternativen. Da die DELETE-Funktion, in der Abbildung 40, keine LOB’s verwendet,
konnte Oracle hier eine wesentlich bessere Leistung erzielen, die mit der Konkurrenz vergleichbar ist.
Trotz des fehlenden Batch-Modus bietet SQLite die jeweils schnellste Leistung,
56
4.2 Benchmarks
Philipp Ledermann
Sheet3
Benchmark - Update (2)
90
80
70
Runtime in s
60
Postgres
MySQL
SQLite
50
40
30
20
10
0
0
200
400
600
800
1000
1200
Batchsize
Sheet2- Nachtest
Abbildung 39: Datenmanipulation - Aktualisieren - Polygone
Benchmark - Delete
60
Runtime in s
50
40
Postgres
Oracle
MySQL
SQLite
30
20
10
0
0
200
400
600
800
1000
1200
Batchsize
Abbildung 40: Datenmanipulation - Löschen - Polygone
was nicht umbedingt verwundert, da hier nur ein minimaler Verwaltungsoverhead des überliegenden Datenbankmanagmentsystems, auch bezüglich Datensicherheit, existiert.
Page 5
Mit gut gewählter Batchgröße, die bei der verwendeten Testhardware und Konfiguration für INSERT’s bei 1000 und für UPDATE’s und DELETE’s bei 500 liegt,
ist Postgres nur minimal langsamer als SQLite und bietet damit das zweitschnellste Ergebnis.
Page 3
Für MySQL liegen die optimalen Paketgrößen für das Einfügen, Aktualisieren
und Löschen bei je 2000, 500 sowie 1000, benötigen dabei allerdings wesentlich
mehr Laufzeit als SQLite und Postgres. Es sei angemerkt, dass die verwen-
57
4.2 Benchmarks
Philipp Ledermann
dete MySQL MyISAM-Engine weder Transaktionen unterstüzt, noch ACIDkonform ist, welches eigentlich der Geschwindkeit zugute kommen sollte.
Oracle Tolerance
SymDifference
Difference
Intersection
450
400
350
300
250
200
150
100
50
0
Buffer (50000)
Runtime in s
Oracle Fine Tolerance
Oracle Finer Tolerance
Tolerance−Parameter − Polygons − 1.000
Abbildung 41: Oracle Toleranz-Parameter - Polygone
Als letzter Benchmark in dieser Serie wurde noch der Einfluss des Oracle
Toleranz-Parameters untersucht. Dieser dient zum Festlegen der Präzision der
Gleitkomma-Arithmetik. Dabei entspricht ein Wert von 0.05 einer Genauigkeit
von fünf Zentimetern, wenn Meter die Standardeinheit dieses Referenzsystems
ist. Getestet wurden die algorithmisch anspruchsvollsten Funktionen.
Offenbar hat der Toleranzparamter, wie man in Abbildung 41 nachprüfen kann,
für sich gesehen nur einen minimalen Einfluss auf die Performance. Der wesentlich entscheidenere Faktor ist die Komplexität der Geometrie, die im vorherigen
Test ermittelt wurde. So lässt sich der Toleranzparameter, der ebenfalls Teil
der Metadatentabelle ist, verwenden, um eine bestimmte maximal Auflösung
festzulegen. Dazu sei allerdings gesagt, dass dies eine aufwendige Reparatur
aller Geometrien nach sich zieht, wie bereits im Abschnitt 3.5 diskutiert wurde
und daher nicht durchgeführt wurde.
Zusammenfassung - Spezialtests
Im dritten Benchmarkteil wurde einige Sonderfälle betrachtet.
Dabei konnte Oracle mit guter Unterstützung für geodätische Koordinatensysteme überzeugen. Der Tolerance-Parameter hat, zumindest auf Funktionsebene, einen wesentlich geringer Einfluss auf die Geschwindkeit, als einige wenige,
58
4.2 Benchmarks
Philipp Ledermann
komplexe Geometrien im Datensatz. Manipulationsabfragen leiden abermals
durch den LOB-Datentyp.
Bei Postgres existieren noch große Unterschiede zwischen Geometrie- und Geographietypen und letztere können zwar in Geschwindkeit, aber nicht bezüglich
Unterstützung, an erstere anschließen. Manipulationsabfragen konnten schnell
durchgeführt werden, konnten aber nicht SQLite übertroffen, dass trotz fehlendem Batch-Modus, dort das beste Ergebnis liefert.
Bezüglich MySQL fiel nur die etwas langsameren Manipulationabfragen und
die deutlich schlechtere Filterfunktion auf.
4.2.4
Komplexe Benchmarks
Abschließend wurde eine Reihe von komplexen SQL-Abfragen ausgeführt, bei
denen jeweils eine konkrete Frage im Vordergrund steht, die mittels der bisher
besprochenen räumlichen Funktionen, über den Datensatz, beantwortet werden sollte. Dabei stellte sich das Erstellen von generischen Queries als recht
anspruchsvoll heraus, da neben syntakischen auch eine Reihe von semantischen
Unterschieden bestehen, die sich nicht direkt beheben ließen.
Ein häufig auftretendes Problem, war die effiziente Verwendung von Unterabfragen. Postgres und Oracle unterstützen dafür, die im SQL-99 hinzugefügten,
WITH-Klauseln. In Postgres, MySQL und SQLite ist es dafür möglich, Unterabfragen direkt in die FROM-Klausel einzubinden. Der generelle Ansatz war es
daher, die letzte Möglichkeit zu verwenden und die Abfrage für Oracle äquivalent umzuschreiben. Es sei hier anzumerken, dass bei Postgres für beide
Varianten kein relevanter Performanceunterschied festgestellt werden konnte.
Ein weiteres häufiges Problem, war die Limitierung der Ausgabe. Postgres,
MySQL und SQLite verwenden dafür eine zusätzliche LIMIT-Klausel am Ende
der Subquery. In Oracle hingegen funktioniert diese Mechanik über ein zusätzliches Prädikat in der WHERE-Klausel. Erschwerend kommt hier hinzu, dass
diese Variante nicht mit GROUP BY-Aggregation und ORDER BY-Sortierungen
funktioniert, so dass zwischen Ober- und Unterabfrage eine zusätzlichen Ebene eingefügt werden musste.
Die vollständig ausformulierten SQL-Abfragen sind dem Anhang A.2 beigefügt. Es wurde auch hier ein allgemeines Timeout von 45 Minuten festgelegt.
Die Abfragen wurden wie zuvor in Tasks zusammengefaßt und jeweils drei mal
ausgeführt.
Komplexe Benchmarks I
Es folgt die erste Serie von komplexen Queries. Die Ergebnisse sind in Abbildung 42 aufgeführt.
59
4.2 Benchmarks
Philipp Ledermann
1. Die Bahnstation mit den meisten POI’s in der unmittelbaren Umgebung.
2. Auflistung aller POI’s, wo die meisten POI’s auf einem gemeinsamen,
räumlichen Punkt zusammenfallen.
3. Drei Geschäftsarten, die summiert, die meisten POI’s in ihrer Umgebung
haben.
4. POI in einem konkreten Bezirk, der bezüglich Straßen am isoliertesten
ist.
5. Alle Bezirke mit der Anzahl ihrer enthalten POI’s, in einer konkreten
Stadt.
6. Die häufigsten POI-Klassen in einer konkreten Stadt.
Complex6
Complex4
Timeout
Complex3
Complex2
Complex1
1
Timeout
10
Complex5
Unsupported
100
Timeout
Runtime in s
1000
Complex Queries 1 − Points
Abbildung 42: Komplexe Queries I - Punkte
Die einzelnen Laufzeiten lassen sich nur schwer begründen, weil hier eine Vielzahl von Faktoren zum tragen kommen, wie beispielsweise
• Größe und Komplexität der vollständigen Eingabemenge,
• Größe und Komplexität der Eingabemengen der Unterabfragen,
• Größe und Komplexität der letztendlichen Ausgabemenge,
• Performance von verwendeten Aggregationen,
• Performance der Sortierverfahren,
• Verwendung von Puffer und Caches, etc.
60
4.2 Benchmarks
Philipp Ledermann
Speziell in dieser Serie ist keine klare Rangfolge zu erkennen. Einzig Postgres
bietet über alle Abfragen hinweg, eine beständig, gute Leistung. Oracle bietet
ein höchst wechselhaftes Bild, scheint aber hier besonders bei Abfragen mit
vielen Polygonen zu leiden. Eine erwartet grundlegende Ähnlichkeit von Postgres und SQLite ist erkennbar, wenn auch mit einer höchst unterschiedlichen
Differenzen. MySQL ist hier tendenziel am langsamsten (Timeouts), wenn auch
ohne ersichtliche Erklärung.
Komplexe Benchmarks II
Eine besondere Schwierigkeit der zweiten komplexen Serie war unter anderem
die Qualität und Architektur der Testdaten. So sind dort keine Straßen gegeben, sondern vielmehr Straßenabschnitte. Zwar mag es sinnvoll sein, Straßen
zu trennen, sobald sie ihren Namen ändern, allerdings wurden auch zusammenhängende Straßenstücke mit gleichem Namen in verschiedene Abschnitte
unterteilt. Ein nachträgliches Zusammenfügen ist leider keineswegs trivial und
mit Standard-SQL nicht realisierbar. Gründe für diese Aufteilungen können generelle Effizienzverbesserungen durch weniger komplexe Geometrien oder kleinere Bounding-Boxen zugunsten der R-Bäumen sein.
7. Längstes Straßensegment in der Stadt mit der höchsten Einwohnerzahl.
8. Summe der Straßensegmente in der Stadt mit der höchsten Einwohnerzahl.
9. Städte mit den meisten Straßen, gemessen an der Summe über alle Straßensegmente
10. Alle Hauptstraßen, die eine konkrete, gegebene Linie passieren.
11. Alle Städte, Orte, Dörfer nahe einer konkreten Autobahn.
12. Straßensegment mit den meisten nahegelegenen POI’s.
Im Gegensatz zur ersten Serie ist bei diesem Test (Abbildung 43) eine wesentlich klarere Rangfolge ersichtlich. Postgres liefert hier wieder die besten
Ergebnisse, während Oracle am schlechtesten ist. Dies ist nicht verwunderlich, da es beim Relationsbenchmark bezüglich der Kantenzüge ebenfalls sehr
schlecht abgeschnitten hat und diesmal die Komplexitätseinschränkung natürlich entfallen musste. MySQL liefert in diesem Test ein gutes Bild und liegt,
bis auf einer Ausnahme, nur knapp hinter Postgres zurück. Die Ergebnisse von
SQLite sind auch hier wieder recht unterschiedlich und zeitlich hinter Postgres
angeordnet.
Komplexe Benchmarks III
Ein weiteres Problem in den Testdaten, stellt die fehlende, klare Definition
einer Stadt dar. Die administrative Aufteilung in OpenStreetMap ist dabei an
die Gebietskörperschaften Deutschlands angelehnt, in der eine Stadt, je nach
61
4.2 Benchmarks
Philipp Ledermann
Complex10
Complex9
Complex8
Complex7
1
Unsupported
Unsupported
Complex12
10
Complex11
Unsupported
100
Timeout
Runtime in s
1000
Complex Queries 2 − Lines
Abbildung 43: Komplexe Queries II - Kantenzüge
Größe in eines von mehreren administrative Level fallen kann.
Abfragen 16 und 17 unterscheiden sich nur im Clipping der Naturflächen, welches durch die Intersection-Operation, über sehr komplexen Geometrien,
extrem aufwendig ist. Von daher wurde ein Query ohne Clipping vorgezogen,
die damit aber nicht das korrekte Ergebnis liefern kann.
13. Flächenmässig kleinster Bezirk in größter Stadt.
14. Die am dichtesten bevölkertsten Regionen.
15. Administrativer Bereich mit den meisten Bezirken.
16. Administrativer Bereich mit dem flächenmässig größten Anteil an Natur
(ohne Clipping).
17. Administrativer Bereich mit dem flächenmässig größten Anteil an Natur
(mit Clipping).
18. Administrativer Bereich mit den meisten historischen POI’s in der Umgebung, eigene POI’s nicht mitgezählt.
Die flächenlastigen Abfragen (Abbildung 44) liefern ein ähnliches Bild wie der
vorherige Test. Allerdings liegt MySQL diesmal in 3 von 6 Abfragen laufzeittechnisch vorn, gefolgt von Postgres, SQLite und schließlich Oracle. Obwohl
Abfragen 16 und 17 von SQLite theoretisch durchführbar sein müssten, resultierten sie in einem Fehler und konnten von daher nicht ausgewertet werden.
Oracle war nicht in der Lage die aufwendigeren Queries in der erforderlichen
Zeit zu berechnen. Beachtenswert ist, dass nur Postgres in der Lage war, die
höchst aufwendige Abfrage 17 durchzuführen.
62
4.3 Scoring
Philipp Ledermann
Unsupported
Unsupported
Complex18
Timeout
Unsupported
Unsupported
Complex17
Complex16
Complex15
Complex14
Complex13
1
Timeout
10
Unsupported
100
Timeout
Runtime in s
1000
Complex Queries 3 − Polygons
Abbildung 44: Komplexe Queries III - Polygone
Zusammenfassung - komplexe Queries
Postgres liefert in dieser Serie das beste Gesamtbild bezüglich Performance und
Beständigkeit ab. Außerdem war Postgres als einziger Kandidat in der Lage
alle Queries auszuführen und dies auch noch in vertretbarer Zeit.
Auch SQLite lieferte insgesamt gute Laufzeiten, reicht dabei aber nicht an
Postgres heran. Es gibt nur wenig Ausreißer, allerdings konnte es mit 13 von
18 überraschenderweise die wenigsten Queries ausführen. Eine Abfrage war
nicht möglich aufgrund der fehlerhaften ST_DISTANCE-Funktion. Zwei weitere
Abfragen wären nur mit der WITH-Klausel durchführbar gewesen und die beiden
letzten Queries scheiterten möglicherweise durch mathematische Berechnung
im ORDER BY.
Mit einigem Abstand zu Postgres und SQLite folgte MySQL, dass zwar in
einigen Abfrage gute Einzelergebnisse erzielen konnte, aber insgesamt ein recht
unbeständiges Bild mit vielen Ausreißern und Timeouts lieferte. Auch waren
nur 14 von 18 Queries durchführbar.
Ebenfalls unbeständig waren die Ergebnisse von Oracle, welche insgesamt am
langsamsten waren und die meisten Timeouts produzierte. Positiv ist, dass alle
18 von 18 Queries unterstützt wurden. Die schlechten Laufzeiten lassen sich
unter anderem durch die komplexen Geometrien erklären.
4.3
Scoring
Nachdem die durchgeführten Benchmarks nun einzeln ausgewertet wurden, soll
nun ein übergreifendes Scoring bzw. Ranking erfolgen. Dabei gibt es aber eine
63
4.3 Scoring
Philipp Ledermann
Reihe von Störfaktoren, die das Zusammenfassen der Messdaten erschwerten.
Das erste Problem stellen nicht unterstützte Abfragen dar. Mögliche Lösungsansätze sind das Auffüllen der Lücken mit einem Standardwert, z.B. beste/mittlere/durchschnittliche Laufzeit der andere Kandidaten und weglassen der
entsprechende Lücke, die damit nicht als Teil der Endformel einbezogen wird.
Da die erste Variante auf das Einbringen von nicht gemessenen Werten beruhrt, wurde zugunsten der zweiten Option entschieden.
Die nächste Schwierigkeit stellten Timeouts dar. Da hier ebenfalls keine echten
Messwerte verfügbar sind, verhält es sich ähnlich wie beim ersten Punkt. Da wir
aber hier wissen, dass die benötigte Laufzeit mindestens dem Timeout-Wert
entsprechen muss, wurden solche Lücken mit einem vordefinierten Wert gefüllt. Allerdings lässt sich argumentieren, dass dies Unfair ist, da Timeouts und
schlechte Laufzeiten negativer bewertet werden, als eine fehlende Implementierung. Um diesem Dilemma einigermaßen eingezuwirken, wurde als Füllwert
1200 Sekunden gewählt, was wesentlich unter der eigentlichen Timeoutgrenze
von 45 Minuten bzw. 2700 Sekunden liegt.
Zum Schluß stellt sich noch die Frage, wie mit den anderen hohen Laufzeiten
umgegangen werden soll. Insbesondere Ausreißer, die nur sporadisch auftreten,
sollten nicht zu hoch ins Gewicht fallen.
Nach ausgiebiger Analyse der Messwerte, wurden zwei Ansätze ausgewählt,
die sich hoffentlich gut für ein endgültiges Scoring eignen. Der erste Ansatz
verwendet ein Ranking über alle einzelnen, unterstützen Queries. Sind zwei
Laufzeiten identisch, beispielsweise bei (A, B, C) = (12s, 26s, 12s), erhalten
beide Kandidaten (A und C) denselben Rankingwert 1, wobei leere Plätze
nicht aufgefüllt werden und somit C den Platz 3 erhält. Der Gesamtwert in
einer Kategorie entspricht dabei dem arithmetischen Mittel von allen Rankingwerten dieser Datenbank. Durch das Ranking fallen die Differenzspannen
zwischen den Kandidaten weg, wodurch sich das Gesamtergebnis bei Queries,
die besonders lange benötigten, verbessert.
Um ein besseres Gefühl für die Differenzen zu bekommen, bzw. um schlechte
Laufzeiten etwas mehr zur Geltung zu bringen, wurde noch folgender zweiter
Ansatz gewählt:
Zunächst werden alle Laufzeiten bezüglich einer Referenzdatenbank bzw. eines
Referenzkandidaten normiert. Die Wahl fiel in diesem Fall auf Postgres, da es
nicht nur mit guten Laufzeiten überzeugen konnte, sondern vorallem weil es
alle Abfragen und Funktionen unterstüzt hat. Um lange Laufzeiten nicht überzubewerten wurde statt eines arithmetischen Mittels ein geometrisches Mittel
verwendet. Die genaue Formel lautet daher:
64
4.3 Scoring
Philipp Ledermann
v
u
N RuntimeP G
uY
u
Q
N
ScoreDB,N = t
DB
Q=1
RuntimeQ
Dabei steht N für die Anzahl der durchführbaren Queries, die Zählvariable Q
für die jeweilige Query selbst und DB ist der jeweilige Kandidat. Dieser Ansatz ist eine umgekehrte Variante, der in Jackpine [2, p.11] verwendet ScoringMetrik.
Postgres
Oracle
MySQL
SQLite
4
3.5
Average Rank
3
2.5
2
1.5
1
Manipulation
Complex
Functions
0
Relation
0.5
Ranking
Abbildung 45: Ranking
Die Ergebnisse sind in den Abbildungen 45 und 46 aufgeführt. In beiden Diagrammen sind kleinere Werte besser bzw. schneller.
Das Ranking-Diagramm zeigt den durchschnittlich Platz im Ranking bezüglich
der Kategorien. Postgres liefert insgesamte das beste Ergebnis, wobei es nur
in den Manipulationsabfragen von SQLite übertroffen wird. Oracle ist jedesmal klarer Verlierer. MySQL und SQLite wechseln sich bezüglich des zweiten
und dritten Platz ab, wobei beachtet werden muss, dass es bei den gut ausgefallenen Funktionsbenchmarks einen Großteil der Abfragen nicht unterstützt
hat und diese Lücken hier, wie oben erklärt, nicht aufgefüllt wurden. Außerdem spielen Laufzeitausreißer und Timeouts hier keine Rolle, was man beim
Vergleich mit dem Scoring-Diagramm deutlich erkennen kann, da hier SQLite,
speziell bei den komplexen Queries, besser abschneidet. Ansonsten zeigt das
65
4.4 Zusammenfassung
Philipp Ledermann
25
Postgres
Oracle
MySQL
SQLite
20
15
10
Manipulation
Complex
0
Functions
5
Relation
Score (smaller = faster)
30
Scoring
Abbildung 46: Scoring
Scoring deutlich welche Laufzeitunterschiede zwischen der Referenz Postgres
und den restlichen Kandidaten besteht. Besonders die Unterschiede zu Oracle
fallen negativ auf und das trotz Verwendung eines geometrischem Mittels.
4.4
Zusammenfassung
Abschließen lässt sich die Auswertung wie folgt:
Bester Kandidat in diesem Benchmark ist mit Abstand die Kombination PostgreSQL + PostGIS, welches die Konkurrenz nicht nur in Bezug auf die Performance übertrifft, sondern auch mit der besten Unterstützung, größter Beständigkeit und guter Bedienbarkeit auffällt. Lediglich der geodätische Datentyp
bedarf noch Arbeit.
Auf einem guten zweiten Platz liegt SQLite + SpatiaLite, welches zwar bezüglich der Geschwindkeit nicht immer mit Postgres mithalten konnte, dafür
aber eine gute Unterstützung und Beständigkeit erreichte. Dafür fehlt eine
echte SRID-Unterstützung und damit auch ein geodätischer Datentyp. Der
JDBC-Treiber benötigt ebenfalls noch viel Arbeit zumal er derzeit nur von
einer Drittanbieter gestellt wird. Weiter sollte die Indexbenutzung in Zukunft
automatisch durch den SQL-Optimizer erfolgen und nicht jedesmal manuell
angestoßen werden müssen.
Den dritte Platz teilen sich MySQL und Oracle Spatial. Zwar liefert MySQL
bessere Laufzeiten, aber die Unterstützung und Menge der angebotenen Funktionen ist leider viel zu gering und liefern teilweise unterschiedliche Ergebnisse.
Des weiteren ist es wie SQLite nicht in der Lage automatisch räumlichen Indizes
zu verwenden und kann weder mit SRID’s noch mit geodätischen Koordinaten
66
4.5 Vergleich mit verwandten Arbeiten
Philipp Ledermann
arbeiten.
Die schlechtesten Laufzeiten in diesem Benchmark lieferte Oracle, welches außerdem eine umständliche Bedienung und eine schlechter Kompatibilität zum
SQL/MM-Standard bietet. Man beachte bezüglich der Laufzeiten auch, dass
die meisten Queries, sofern möglich, automatisch den Parallel-Modus verwendet haben, wodurch im Gegensatz zu den anderen Kandidaten, nicht ein sondern drei Prozessorkerne verwendet wurden. Allerdings ist es auch als einziges
kommerzielles System in diesem Benchmark, für weitaus größere und komplexere Umgebungen mit leistungsfähigerer Hardware optimiert. Zusätzlich bietet
es eine faßt ebenso gute Unterstützung wie SQLite, wenn auch eben außerhalb
der Namenskonventionen. Vorbildlich hingegen ist die volle Unterstützung bezogen auf geodätische Daten, die aber mangels sonstiger Unterstützung nur am
Rande betrachtet werden konnten. Aus Entwicklersicht wäre es warscheinlich
sinnvoll alternative Ein- und Ausgabefunktionen zur Verfügung zu stellen, die
statt der problematischen LOB’s einfachere Textdatentypen wie VARCHAR2 verwenden. Ansonsten ist noch anzumerken, dass Oracle eine Reihe von Optionen
und Möglichkeiten anbietet, die zumindest bei MySQL und SQLite fehlen. Dies
beinhaltet z.B. Geocoding und Unterstützung für Rasterdaten.
Schließlich sei darauf hingewiesen, dass die im Zuge dieser Ausarbeitung durchgeführten Benchmarks natürlich bei nicht alle möglichen Optionen und Kombinationen von Optionen abdecken konnten. Die Ergebnisse sind vielmehr als
Tendenz bezüglich der durchgeführten Funktionen unter der gegebenen Testhardware zu verstehen. Es sei damit auf den Abschnitt 5.2 verwiesen, indem
noch auf wichtige Faktoren wie der Skalierung hingewiesen wird.
4.5
Vergleich mit verwandten Arbeiten
Als Abschluss dieses Kapitels sollen, die ermittlelten Ergebnisse, mit denen
anderer verwandter Arbeiten, verglichen werden. Ein direkter Vergleich ist deshalb nicht möglich, da wie schon in Abschnitt 1.3 erklärt wurde, die meisten
Arbeiten entweder veraltet oder Kombinationen von Kandidaten enthalten, die
keine hinreichende Schnittmenge produziert. Der Sequoia 2000 Benchmark [3]
fällt unter beide Kategorien und beschäftigt sich überdies nur mit Rasterdaten,
die in dieser Ausarbeitung nicht berücksichtigt wurden. Der VESPA Benchmark [4] liefert lediglich einen Vergleich von Postgres und Rock & Roll und
fällt damit ebenfalls heraus, während die TCP Benchmarks völlig andere Anwendungsbereiche abdecken und keine Geodaten testen.
Der erste Benchmark, der sich ansatzweise vergleichen lässt, ist Jackpine [2].
Getestet wurden hier PostgreSQL mit Version 8.4.2 (die PostGIS-Version wird
leider nicht genannt), MySQL mit Version 5.0.91, sowie Informix [2, p.8]. Da
Informix in dieser Ausarbeitung nicht getestet wurde und MySQL zum damaligen Zeitpunkt keine echten Beziehungsfunktionen unterstützt hat [2, p.8], ist
ein Vergleich schwierig. Die in MySQL etwas schlechteren Ladezeiten gegen-
67
4.5 Vergleich mit verwandten Arbeiten
Philipp Ledermann
über Postgres [2, p.10] konnten auch in diesem Benchmark festgestellt werden,
wobei der Import nicht wie bei Jackpine über Drittanbieter-Tools sondern von
Postgres ausgehend über JDBC erfolgt ist. Bezogen auf die Datengröße hat
sich dieser aber insgesamt verbessert. Schnellere INSERT-Operationen bezüglich MySQL konnten dafür aber gerade nicht festgestellt werden [2, p.8], siehe
Abschnitt 4.2.3. Das endgültige Scoring ist eine umgekehrte Variante, der in
dieser Ausarbeitung präsentierten Scoring-Metrik und zeigt einen deutlichen
Geschwindigkeitsvorteil für MySQL bezüglich einzelner Funktionstests und ungefähre Gleichheit bei komplexen Abfragen. Da die Beziehungsoperationen nun
echte Berechnungen über den Geometrien ausführen, ist es nicht verwunderlich, dass sich das Verhältnis deutlich zugunsten von Postgres verschiebt. Wie
auch bei Jackpine sind hier die komplexen Queries noch eine Stufe langsamer,
als die Einzeltests [2, p.10-11].
Als zweiter Vergleich kann das Paper von Zhonghai Zhou1.3 herangezogen werden, wo Postgres, MySQL, Oracle sowie IBM DB2 miteinander getestet wurden. Es wird leider weder auf die genauen Datenbank-Versionen eingegangen,
noch wird ein endgültiges Ranking oder mathematisches Scoring angeben, dafür wird aber im Text die Konkurrenzfähigkeit von Postgres gegenüber Oracle
und IBM DB2 betont und es wird explizit für Geodaten im Megabyte-Bereich
empfohlen [5, p.491]. Die Einzeltests beziehen sich ähnlich zu den einfachen
Queries dieser Arbeit auf jeweils eine einzige SQL/MM-Funktion und sind als
Differenzen in Prozent zu Postgres angegeben [5, p.492].
Da MySQL auch zum Entstehungszeitpunkt dieser Arbeit (2009) nur wenig
Funktionen unterstützte und wenn überhaupt dann nur über minimale Boundingboxen, ist ein ernsthafter Vergleich mit MySQL nicht möglich. Beim Vergleich Postgres mit Oracle fällt auf, dass Oracle bei 5 von 10 getesten Funktionen langsamer als Postgres ist. In einem von 10 Fällen wird eine Funktion
von Oracle nicht unterstützt und in 4 von 10 Tests ist Oracle schneller. Letztere sind die SQL/MM Funktionen ST_Within, ST_Intersection, ST_Centroid
und ganz besonders ST_Buffer. Dazu sei gesagt, dass wie im Vorwort (4.1) und
später in der Auswertung erklärt wurde, ST_Within eine der Funktionen ist,
die auch heute noch andere Ergebnisse zurückliefern und damit augenscheinlich schneller sind, als sie es bei korrekten Ergebnissen sein dürften. Auch die
Funktionen ST_Intersection (nicht zu verwechseln mit ST_Intersects) und
ST_Centroid waren in diesem Benchmark bei Postgres wesentlich schneller,
siehe 4.2.1. Die Herkunft dieses Unterschiedes lässt sich in nachhinein schlecht
abklären. Es fehlen einerseits die verwendeten Parameter, z.B. bezüglich der
Oracle-Toleranz und der Komplexität der Geometrien, andererseits mag PostGIS in der Zwischenzeit durch Optimierungen an Geschwindkeit hinzugewonnen haben. Ganz besonders auffällig ist aber die unglaubliche Differenz bezüglich der ST_Buffer-Operation wo Oracle nur f rac1100000 der Laufzeit von
Postgres benötigt. Entweder hat hier des GEOS-Bibliothek von PostGIS einen
gigantischen Geschwindigkeitzusatz widerfahren, oder Oracle lieferte dort unbemerkt falsche Ergebnisse [5].
68
5 Schlussteil
5
Philipp Ledermann
Schlussteil
Als Abschluss dieser Ausarbeitung, soll zunächst auf Probleme, die im Zuge
der Durchführung aufgetreten sind, eingegangen werden. Anschließend werden
im Ausblick Möglichkeiten aufgezählt, wie dieser Benchmark fortgeführt und
verbessert werden kann. Zum Ende wird sich noch ein kurzes Fazit anschließen.
5.1
Probleme
Ein Problem bei der Durchführung dieser Benchmarks waren die teilweise umfangreichen Tuning-Parameter der einzelnen Geodatenbanken. Insbesondere
Oracle besitzt wegen seiner Herkunft aus dem kommerziellen Bereich eine nahezu unüberschaubare Menge von Paramtern und Einstellungen. Nach ausgiebiger Durchsicht der Handbücher wurden die wichtigsten Konfigurationen
bestmöglich auf die verwendete Testhardware abgestimmt, eine Garantie dafür
kann aber nicht gegeben werden. Auch traten bei SQLite und Oracle Anomalien und Fehler auf, die nach bestem Wissen korrigiert wurde. Ein Beispiel
hierfür ist der in Abschnitt 4.1 erwähnte Oracle Optimizier-Bug [9, p.27-28]
bezüglich Geoindizies.
Weiter waren wie im Teil 3.5 beschrieben, umfangreiche Säuberungen der Testdaten notwendig. Dabei traten nicht nur Geometrien auf, die nicht repariert
werden konnte, sondern es existiert hinterher noch Datenobjekte, die zwar als
gültig deklariert wurden, beim Einsatz in Oracle aber zu einer Laufzeitanomalie führten. Ein Beispiel hierfür wäre eine Beziehungsfunktion, die statt weniger
Minuten einen halben Tag rechnete und an diesem Punkt schließlich abgebrochen wurde. Wurde eine bestimmte Geometrie von 5.000 entfernt, verschwand
die Anomalie. Da es sonst keine Möglichkeit gab solche Geometrien zu entfernen, musste dieses per Hand erledigt werden, was dementsprechend aufwendig
war.
Hinderlich waren auch die in OpenStreetMap durchgeführten Aufteilungen von
komplexen geometrischen Objekten, die die Verwendung zusätzlich erschwerten. Siehe dazu Abschnitt 4.2.4.
5.2
Ausblick
Da dieser Benchmark nicht alle möglichen Tests zu Geodaten und deren Verarbeitung umfaßen konnte, sollten folgende Punkte als nächstes untersucht
werden:
Die hier ausgewählten Kandidaten beschränkten sich vorwiegend auf besonders populäre und freie Vertreter. Einzige Ausnahme stellte Oracle dar, dass
zwar kommerziell ist, aber zu nicht-kommerziellen und privaten Zwecken genutzt werden darf und deshalb getestet werden konnte. Andere wichtige Kandidaten, die zukünftig untersucht werden sollten, beinhalten ESRI’s ArcGIS,
IBM’s DB2 und Informix sowie Microsoft’s SQL Server.
69
5.3 Fazit
Philipp Ledermann
Als nächstes ist es ratsam weitere Datensätze zu untersuchen, zumal nicht gewährleistet werden konnte, dass die Geodatenbanken bei anderen Datensätzen
nicht anders skalieren. Ebenso sollten Rasterdaten untersucht werden.
Generell ist die Skalierung im Zusammenhang mit Geodatenverarbeitung schwierig. SQLite ist designtechnisch nur auf Singleuser-Betrieb ausgelegt, während
die für Geoindizes benötigte MyISAM-Engine von MySQL keine Transaktionen unterstützt. Bei Postgres und Oracle hingegen, existieren diese Einschränkungen nicht. So wäre es interessant zu untersuchen, wie diese Datenbanken
horizontal und vertikal skalieren. Die GEOS-Bibliothek, die sowohl bei Postgres als auch bei SQLite zum Einsatz kam, benötigt eine gewisse Menge an
Arbeitsspeicher um zu operieren. Ist diese Größe zu gering angesetzt, können
aufwendige Berechnungen nicht mehr durchgeführt werden und brechen mit
Fehlern ab. Mehr als der benötigte Arbeitsspeicher führt hingegen zu keinem
Vorteil. Lediglich die Puffergrößen der Datenbanken können vergrößert werden.
Dafür skalieren alle Datenbanken mit besserer, sprich schnellerer, Hardware.
Dies ist besonders für Oracle der Fall, dessen Mindestanforderungen zwar deutlich überschritten wurden, dabei aber vermutlich nicht dem typischen Hardwareprofil entspricht. Da Oracle ein vollwertiges Clustering unterstützt, dürfte
es als einziges auch horizontal skalieren. Für Postgres gibt es dafür zwar eine
experimentelle Modifikation, diese arbeit zum jetzigen Stand aber nicht mit
der PostGIS-Erweiterung zusammen. Bei sehr großen Datenmengen ist dies
also ein enormer Vorteil für Oracle, der im Zuge dieses Benchmarks nicht hinreichend gewürdigt werden konnte.
Ein weiterer wichtiger Punkt ist die Untersuchung der Performance bezüglich multipler Benutzer. Vergleichbar zu den Benchmarks des TPC müssen
domainspezifische, typische Profile bzw. Workloads erstellt werden, bei denen
mehrere Benutzer gleichzeitig auf der Datenbanken agieren. Doch dies erfordert umfangreiche Domainkenntnisse und eine leistungsfähigere Hardware, die
über den Rahmen dieser Arbeit hinausgehen.
Durch die immer noch steigende Popularität bezüglich Geodaten und deren
Anwendung, verbessern sich Funktionsumfang und Geschwindigkeit von potentiellen Kandidaten fortlaufend, so dass Benchmarks immer wieder durchgeführt
werden müssen. Dabei wäre besonders ein größerer Test über geodätischen Koordinatensystemen ratsam, vorzugsweise sobald Postgres seinen diesbezüglich
Funktionsumfang erweitert hat.
5.3
Fazit
Das Ziel einen möglichst erweiterbaren Benchmark für Geodaten zu entwickeln
und durchzuführen wurde erreicht. Durch die Verwendung von Konfigurationsdateien können jederzeit und ohne Quellcodeänderungen SQL-Queries hinzugefügt oder Einstellungsparameter geändert werden. Ein Hinzufügen neuer
Kandidaten ist ebenso möglich. Durch den integrierte QueryTranslator müssen
70
5.3 Fazit
Philipp Ledermann
Abfragen nicht für jede Datenbank einzeln geschrieben werden, sondern können mithilfe eines Mappings von einer generischen Query übersetzt werden.
Die entwickelten einfachen Queries decken einer Großteil der unterstützten
SQL/MM-Funktionen ab, während die komplexen Queries schwierigere Abfragen simulierten und echte Fragen über den Datensatz beantworten konnten.
Spezialtests wurden verwendet um andere Eigenschaften wie die Geschwindigkeit von Einfügeoperationen oder das Laufzeitverhalten bei komplizierten
Geometrien zu überprüfen.
In der Auswertung wurden die Ergebnisse aller durchgeführten Abfragen präsentiert und die Laufzeiten wurden einzeln durch Theorien bzw. Erklärungen
zu begründen versucht. Das zum Abschluß durchgeführte Scoring faßt alle Messergebnisse zusammen und gibt damit einen Aufschluß über die Performance
aller Kandidaten hinsichtlich Testdaten und Testhardware.
71
Literatur
Philipp Ledermann
Literatur
[1]
P. Rigaux, M. Scholl, A. Voisard.
Spatial Databases With Application To GIS.
Morgan Kaufmann, Elsevier, 2002.
[2]
S. Ray, B. Simion, A. Brown.
Jackpine: A Benchmark To Evaluate Spatial Database Performance.
Data Engineering (ICDE), IEEE 27th International, 2011.
[3]
M. Stonebraker, J. Frew, K. Gardels, J. Meredith.
The SEQUOIA 2000 Storage Benchmark (TechReport).
SIGMOD ’93, Proceedings of the 1993 ACM SIGMOD international conference on Management of data, 1993.
[4]
N. Patton, M. Williams, K. Dietrich, O. Liew, A. Dinn, A. Patrick.
VESPA: A Benchmark For Vector Spatial Databases.
Advances In Databases, Proceedings of 17th British National Conference
on Databases: Advances in Databases, 2000.
[5]
Z. Zhou, B. Zhou, W. Li, B. Griglak, C. Caiseda, Q. Huang.
Evaluating Query Performance On Object-Relational Spatial Databases.
Computer Science and Information Technology (ICCSIT), 2nd IEEE International Conference, 2009.
[6]
E. Hoel, H. Samet.
Benchmarking Spatial Join Operations With Spatial Output.
Proceedings of the 21st International Conference on Very Large Data
Bases, 1995.
[7]
Oracle Corporation.
Oracle Spatial Developer’s Guide 11gRelease2 (11.2).
Manual, 2013.
[8]
D. Geringer, Oracle.
Best Practices with Oracle Spatial 11g and Oracle Fusion Middleware’s
MapViewer.
2010 OpenWorld, 2010.
[9]
Rich Pitts, Oracle.
Oracle Spatial User Conference 2012.
OSUC12, 2012.
[10] Oracle Corporation.
Oracle JDBC Developer’s Guide 11gRelease2 (11.2).
Manual, 2013.
[11] The PostgreSQL Global Development Group.
PostgreSQL 9.2.4 Documentation.
Manual, 2013.
72
Literatur
Philipp Ledermann
[12] Postgres Community.
Postgres History. (Website)
http://www.postgresql.org/about/history/ ,
Stand: 06/2013.
[13] Refractions Research.
PostGIS 2.0 Manual - SVN Revision (11536).
Manual, 2013.
[14] Oracle Corporation, MySQL.
MySQL 5.6 Reference Manual.
Manual, 2013.
[15] SQLite. (Website)
http://www.sqlite.org ,
Stand: 06/2013.
[16] SQLite Community.
SQLite. (Website)
http://www.sqlite.org/about.html ,
Stand: 06/2013.
[17] A. Furieri.
SpatiaLite. (Website)
https://www.gaia-gis.it/fossil/libspatialite/index ,
Stand: 06/2013.
[18] A. Furieri.
Spatial Index UPDATE.
http://www.gaia-gis.it/gaia-sins/SpatialIndex-Update.pdf ,
Stand: 06/2013.
[19] T. Saito.
SQLite JDBC Driver. (Website)
https://bitbucket.org/xerial/sqlite-jdbc ,
Stand: 06/2013.
[20] Open Geospatial Consortium Inc.
OGC History. (Website)
http://www.opengeospatial.org/ogc/historylong ,
Stand: 06/2013.
[21] Open Geospatial Consortium Inc.
OpenGIS Implementation Standard for Geographic information - Simple
feature access - Part 1: Common architecture.
OGC 06-103r4, 1.2.1, 2011.
[22] Open Geospatial Consortium Inc.
OpenGIS Implementation Standard for Geographic information - Simple
feature access - Part 2: SQL option.
OGC 06-104r4, 1.2.1, 2010.
73
Literatur
Philipp Ledermann
[23] M. Ashworth, International Organization for Standardization.
(ISO/IEC Working Draft) SQL Multimedia and Application Packages
(SQL/MM) Part 3: Spatial: Editor’s Notes.
ISO/IEC JTC 1/SC 32, 2003.
[24] C. Veness, Movable Type Scripts.
Vincenty formula for distance between two Latitude/Longitude points.
(Website)
http://www.movable-type.co.uk/scripts/latlong-vincenty.html ,
Stand: 06/2013.
[25] Environmental Systems Research Institute, Inc.
WHAT is raster data. (Website)
http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=What_is_raster_data%3F ,
Stand: 06/2013.
[26] R. Obe, L. Hsu.
Spatial reference system: What is it? (Website)
https://weblogs.java.net/blog/manningpubs/archive/2013/02/13/spatialreference-system-what-it ,
Stand: 06/2013.
[27] Geofabrik.
Geofabrik Downloads. (Website)
http://www.geofabrik.de/data/download.html ,
Stand: 07/2013.
[28] Transaction Processing Performance Council.
TPC Benchmarks (Website)
http://www.tpc.org/information/benchmarks.asp ,
Stand: 07/2013.
[29] OGP Geomatics Committee, European Petroleum Survey Group.
EPSG Registry. (Website)
http://www.epsg-registry.org/ ,
Stand: 08/2013
[30] Rolf Klein.
Algorithmische Geometrie.
Springer, 2005.
[31] OpenStreetMap.
Osm2pgsql. (Website)
http://wiki.openstreetmap.org/wiki/Osm2pgsql ,
Stand: 08/2013
[32] OpenStreetMap.
OpenStreetMap. (Website)
74
Literatur
Philipp Ledermann
http://wiki.openstreetmap.org/wiki/Map_Features ,
Stand: 08/2013
[33] GEOS.
GEOS - Geometry Engine, Open Source (Website)
http://trac.osgeo.org/geos/ ,
Stand: 08/2013
75
A Anhang
A
A.1
Philipp Ledermann
Anhang
Tabellenlayouts
Die Basislayouts der Tabellen als SQL-DDL-Statements in Postgres:
CREATE TABLE osm_points (
id
BIGSERIAL PRIMARY KEY,
osm_id BIGINT NOT NULL,
geom GEOMETRY(Point,3785) NOT NULL,
t_poi TEXT,
t_capital TEXT,
t_ele TEXT
);
CREATE TABLE osm_lines (
id
BIGSERIAL PRIMARY KEY,
osm_id BIGINT NOT NULL,
geom GEOMETRY(LineString,3785) NOT NULL,
t_tracktype TEXT
);
CREATE TABLE osm_roads (
id
BIGSERIAL PRIMARY KEY,
osm_id BIGINT NOT NULL,
geom GEOMETRY(LineString,3785) NOT NULL,
t_tracktype TEXT
);
CREATE TABLE osm_polygons (
id
BIGSERIAL PRIMARY KEY,
osm_id BIGINT NOT NULL,
geom GEOMETRY(Geometry,3785) NOT NULL,
t_tracktype TEXT
);
Für die deskriptiven Attribute aus OpenStreetMap mussten einheitlich folgende Spaltenergänzungen für jede Tabelle durchgeführt werden. Die Attribute mit dem Prefix t_ korrespondieren dabei zu den in der OpenStreetMapDokumentation festgelegten Features.
ALTER TABLE osm_points
ADD COLUMN t_access TEXT,
ADD COLUMN t_addr_name TEXT,
76
A.1 Tabellenlayouts
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
Philipp Ledermann
t_addr_nr TEXT,
t_addr_interpol TEXT,
t_admin_lvl INTEGER,
t_aerialway TEXT,
t_aeroway TEXT,
t_amenity TEXT,
t_area
TEXT,
t_barrier TEXT,
t_bicycle TEXT,
t_brand
TEXT,
t_bridge TEXT,
t_boundary TEXT,
t_building TEXT,
t_construction TEXT,
t_covered TEXT,
t_culvert TEXT,
t_cutting TEXT,
t_denomination TEXT,
t_disused TEXT,
t_embankment TEXT,
t_foot
TEXT,
t_gen_src TEXT,
t_harbour TEXT,
t_highway TEXT,
t_historic TEXT,
t_horse
TEXT,
t_intermittent TEXT,
t_junction TEXT,
t_landuse TEXT,
t_layer
TEXT,
t_leisure TEXT,
t_lock
TEXT,
t_manmade TEXT,
t_military TEXT,
t_motorcar TEXT,
t_name
TEXT,
t_natural TEXT,
t_office TEXT,
t_oneway TEXT,
t_operator TEXT,
t_place
TEXT,
t_population TEXT,
t_power
TEXT,
t_power_src TEXT,
t_public_transp TEXT,
t_railway TEXT,
77
A.1 Tabellenlayouts
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
ADD
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
COLUMN
Philipp Ledermann
t_ref
TEXT,
t_religion TEXT,
t_route
TEXT,
t_service TEXT,
t_shop
TEXT,
t_sport
TEXT,
t_surface TEXT,
t_toll
TEXT,
t_tourism TEXT,
t_tower_type TEXT,
t_tunnel TEXT,
t_water
TEXT,
t_waterway TEXT,
t_wetland TEXT,
t_width
TEXT,
t_wood
TEXT,
z_order
INTEGER;
78
A.2 Komplexe Queries
A.2
Philipp Ledermann
Komplexe Queries
Es folgen die XML-Taskdateien für die komplexen Queries:
A.2.1
Komplexe Queries - I
<?xml version="1.0"?>
<Task>
<Candidates>
<Candidate>Oracle</Candidate>
<Candidate>Postgres</Candidate>
<Candidate>MySQL</Candidate>
<Candidate>SQLite</Candidate>
</Candidates>
<Substitutions>
<Substitution>
<Tag>BoundId</Tag>
<Sub>153461</Sub>
</Substitution>
<Substitution>
<Tag>BoundId2</Tag>
<Sub>78276</Sub>
</Substitution>
</Substitutions>
<Query name="#1 Trainstation With Most Pois Nearby">
<generalizedQuery>
SELECT p1.id, p1.t_name, COUNT(p1.id) AS count
FROM osm_points p1, osm_points p2
WHERE p1.t_railway [Equal] ’station’
AND p2.id [Greater] p1.id
[Postgres: AND][Postgres: Filter|p2.geom,[Buffer|p1.geom,1000]]
[MySQL: MbrIntersects|p2.geom, [Buffer|p1.geom,1000]]
[SQLite: IndexHitF|osm_points, p2, [Buffer|p1.geom,1000]]
AND [Intersects|p2.geom,[Buffer|p1.geom,1000]]
GROUP BY p1.id, p1.t_name
ORDER BY COUNT(p1.id) DESC
[Limit|1]
</generalizedQuery>
<oracleQuery>
SELECT /*+ NO_INDEX(p1 idx_osm_points_id)
NO_INDEX(p2 idx_osm_points_id) */
p1.id, p1.t_name, COUNT(p1.id) AS count
FROM osm_points p1, osm_points p2
WHERE p1.t_railway [Equal] ’station’
AND p2.id [Greater] p1.id
AND sdo_relate(p2.geom, sdo_geom.sdo_buffer(p1.geom,1000,0.05),
’mask=anyinteract’) = ’TRUE’
GROUP BY p1.id, p1.t_name
ORDER BY COUNT(p1.id) DESC
</oracleQuery>
<returnType>LONG</returnType>
<returnType>STRING</returnType>
79
A.2 Komplexe Queries
Philipp Ledermann
<returnType>LONG</returnType>
</Query>
<Query name="#2 Cuddled Pois">
<generalizedQuery>
SELECT p1.id, p1.t_name
FROM osm_points p1, (
SELECT pa.id, pa.geom, COUNT(pa.id) AS count
FROM osm_points pa, osm_points pb
WHERE pa.id [Less] pb.id
[MySQL: MbrEquals|pa.geom, pb.geom]
[SQLite: IndexHit|osm_points, geom, pa, pb]
AND [Equals|pa.geom, pb.geom]
GROUP BY pa.id
ORDER BY COUNT(pa.id) DESC
[Limit|1]
) AS qp
WHERE [Equals|p1.geom, qp.geom]
</generalizedQuery>
<oracleQuery>
SELECT /*+ NO_INDEX(p1 idx_osm_points_id) NO_INDEX(p2 idx_osm_points_id)
NO_INDEX(px idx_osm_points_id) NO_INDEX(py idx_osm_points_id) */
p1.id, p1.t_name
FROM osm_points p1, osm_points p2
WHERE p2.id = (
SELECT id
FROM
(
SELECT /*+ NO_INDEX(px idx_osm_points_id)
NO_INDEX(py idx_osm_points_id) */
px.id, COUNT(px.id) AS cnt
FROM osm_points px, osm_points py
WHERE px.id [Less] py.id
AND sdo_equal(px.geom, py.geom) = ’TRUE’
GROUP BY px.id
ORDER BY COUNT(px.id) DESC
)
WHERE rownum [LessEqual] 1
)
AND sdo_equal(p1.geom, p2.geom) = ’TRUE’
</oracleQuery>
<returnType>LONG</returnType>
<returnType>STRING</returnType>
</Query>
<Query name="#3 Sum Of Pois Near The Top 3 Most Common Shops">
<generalizedQuery>
SELECT p1.t_shop, q.count, COUNT(p1.t_shop) AS sum
FROM osm_points p1, osm_points p2, (
SELECT px.t_shop, COUNT(px.t_shop) AS count
FROM osm_points px
GROUP BY px.t_shop
ORDER BY COUNT(px.t_shop) DESC
[Limit|3]
80
A.2 Komplexe Queries
Philipp Ledermann
) AS q
WHERE p1.t_shop = q.t_shop
[Postgres: AND][Postgres: Filter|p2.geom, [Buffer|p1.geom, 500]]
[MySQL: MbrIntersects|p2.geom, [Buffer|p1.geom,500]]
[SQLite: IndexHitF|osm_points, p2, [Buffer|p1.geom,500]]
AND [Intersects|p2.geom, [Buffer|p1.geom, 500]]
GROUP BY p1.t_shop, q.count
ORDER BY COUNT(p1.t_shop) DESC
</generalizedQuery>
<oracleQuery>
WITH q AS (
SELECT t_shop, count
FROM (
SELECT px.t_shop, COUNT(px.t_shop) AS count
FROM osm_points px
GROUP BY px.t_shop
ORDER BY COUNT(px.t_shop) DESC
)
WHERE rownum [LessEqual] 3
)
SELECT /*+ NO_INDEX(p1 idx_osm_points_id)
NO_INDEX(p2 idx_osm_points_id) */
p1.t_shop, q.count, COUNT(p1.t_shop) AS sum
FROM osm_points p1, osm_points p2, q
WHERE p1.t_shop = q.t_shop
AND sdo_relate(p2.geom, sdo_geom.sdo_buffer(p1.geom, 500, 0.05),
’mask=anyinteract’) = ’TRUE’
GROUP BY p1.t_shop, q.count
ORDER BY COUNT(p1.t_shop) DESC
</oracleQuery>
<returnType>STRING</returnType>
<returnType>LONG</returnType>
<returnType>LONG</returnType>
</Query>
<Query name="#4 Most Isolated Pois In Specific District">
<generalizedQuery>
SELECT a.id, a.t_name, (
SELECT ST_Distance(p.geom, l.geom)
FROM osm_points p, osm_lines l, (
SELECT p.id, p.t_name, p.geom
FROM osm_polygons p
WHERE p.id = [BoundId]
) AS bound
WHERE p.id = a.id
AND l.t_name IS NOT NULL
[Postgres: AND][Postgres: Filter|l.geom, bound.geom]
[MySQL: MbrIntersects|l.geom, bound.geom]
[SQLite: IndexHitF|osm_lines, l, bound.geom]
AND [Intersects|l.geom, bound.geom]
ORDER BY ST_Distance(p.geom, l.geom)
[Limit|1]
) AS MinDistance
81
A.2 Komplexe Queries
Philipp Ledermann
FROM osm_points a, (
SELECT p.id, p.t_name, p.geom
FROM osm_polygons p
WHERE p.id = [BoundId]
) AS bound
WHERE a.t_name IS NOT NULL
[Postgres: AND][Postgres: Filter|a.geom, bound.geom]
[MySQL: MbrIntersects|a.geom, bound.geom]
[SQLite: IndexHitF|osm_points, a, bound.geom]
AND [Intersects|a.geom, bound.geom]
ORDER BY MinDistance DESC
[Limit|1]
</generalizedQuery>
<oracleQuery>
WITH bpoly AS (
SELECT poly.id, poly.t_name, poly.geom
FROM osm_polygons poly
WHERE poly.id = [BoundId]
), mins AS (
SELECT /*+ NO_INDEX(p idx_osm_points_id)
NO_INDEX(l idx_osm_lines_id) */ p.id, p.t_name, l.t_name,
SDO_GEOM.SDO_Distance(p.geom, l.geom, 0.05) AS min
FROM osm_points p, osm_lines l, bpoly
WHERE p.t_name IS NOT NULL
AND l.t_name IS NOT NULL
AND SDO_RELATE(p.geom, bpoly.geom, ’mask=anyinteract’) = ’TRUE’
AND SDO_RELATE(l.geom, bpoly.geom, ’mask=anyinteract’) = ’TRUE’
ORDER BY SDO_GEOM.SDO_Distance(p.geom, l.geom, 0.05)
)
SELECT /*+ NO_INDEX(a idx_osm_points_id) */ a.id, a.t_name, (
SELECT m.min
FROM mins m
WHERE m.id = a.id
AND rownum [LessEqual] 1
) AS MinDistance
FROM osm_points a, bpoly
WHERE a.t_name IS NOT NULL
AND SDO_RELATE(a.geom, bpoly.geom, ’mask=anyinteract’) = ’TRUE’
ORDER BY MinDistance DESC
</oracleQuery>
<sqliteQuery>
SELECT p.id, p.t_name, p.g_complexity
FROM osm_polygons p
LIMIT 1
</sqliteQuery>
<returnType>LONG</returnType>
<returnType>STRING</returnType>
<returnType>DOUBLE</returnType>
</Query>
<Query name="#5 Pois In Each District In Specific City">
<generalizedQuery>
SELECT bnd.t_name, COUNT(bnd.t_name)
82
A.2 Komplexe Queries
Philipp Ledermann
FROM osm_points p, (
SELECT poly.id, poly.t_name, poly.geom
FROM osm_polygons poly, osm_polygons poly2
WHERE poly2.id = [BoundId2]
AND poly.t_boundary = ’administrative’
AND poly.t_admin_lvl = 9
[MySQL: MbrIntersects|poly.geom, poly2.geom]
[SQLite: IndexHitF|osm_polygons, poly, poly2.geom]
AND [Within|poly.geom, poly2.geom]
) AS bnd
WHERE 1 = 1
[MySQL: MbrIntersects|p.geom, bnd.geom]
[SQLite: IndexHitF|osm_points, p, bnd.geom]
AND [Intersects|p.geom, bnd.geom]
GROUP BY bnd.t_name
ORDER BY COUNT(bnd.t_name) DESC
</generalizedQuery>
<oracleQuery>
WITH bnd AS (
SELECT /*+ NO_INDEX(poly idx_osm_polygons_id)
NO_INDEX(poly2 idx_osm_polygons_id) */
poly.id, poly.t_name, poly.geom
FROM osm_polygons poly, osm_polygons poly2
WHERE poly2.id = [BoundId2]
AND poly.t_boundary = ’administrative’
AND poly.t_admin_lvl = 9
AND SDO_RELATE(poly.geom, poly2.geom, ’mask=anyinteract’)
= ’TRUE’
)
SELECT /*+ NO_INDEX(p idx_osm_points_id) */ bnd.t_name,
COUNT(bnd.t_name)
FROM osm_points p, bnd
WHERE SDO_RELATE(p.geom, bnd.geom, ’mask=anyinteract’) = ’TRUE’
GROUP BY bnd.t_name
ORDER BY COUNT(bnd.t_name) DESC
</oracleQuery>
<returnType>STRING</returnType>
<returnType>LONG</returnType>
</Query>
<Query name="#6 Pois Of Each Class In Specific City">
<generalizedQuery>
SELECT p.t_shop, COUNT(p.t_shop)
FROM osm_points p, (
SELECT poly.id, poly.geom
FROM osm_polygons poly
WHERE poly.id = [BoundId2]
) AS bnd
WHERE 1 = 1
[MySQL: MbrIntersects|p.geom, bnd.geom]
[SQLite: IndexHitF|osm_points, p, bnd.geom]
AND [Intersects|p.geom, bnd.geom]
GROUP BY p.t_shop
83
A.2 Komplexe Queries
Philipp Ledermann
ORDER BY COUNT(p.t_shop) DESC
</generalizedQuery>
<oracleQuery>
WITH bnd AS (
SELECT poly.id, poly.geom
FROM osm_polygons poly
WHERE poly.id = [BoundId2]
)
SELECT /*+ NO_INDEX(p idx_osm_points_id) */ p.t_shop, COUNT(p.t_shop)
FROM osm_points p, bnd
WHERE SDO_RELATE(p.geom, bnd.geom, ’mask=anyinteract’) = ’TRUE’
GROUP BY p.t_shop
ORDER BY COUNT(p.t_shop) DESC
</oracleQuery>
<returnType>STRING</returnType>
<returnType>LONG</returnType>
</Query>
</Task>
84
A.2 Komplexe Queries
A.2.2
Philipp Ledermann
Komplexe Queries - II
<?xml version="1.0"?>
<Task>
<Candidates>
<Candidate>Oracle</Candidate>
<Candidate>Postgres</Candidate>
<Candidate>MySQL</Candidate>
<Candidate>SQLite</Candidate>
</Candidates>
<Substitutions>
<Substitution>
<Tag>BoundId</Tag>
<Sub>153461</Sub>
</Substitution>
<Substitution>
<Tag>BoundId2</Tag>
<Sub>78276</Sub>
</Substitution>
<Substitution>
<Tag>LineString</Tag>
<Sub>’LINESTRING(1470988 6893112, 1488319 6894921)’</Sub>
</Substitution>
</Substitutions>
<Query name="#7 Find Longest Street Segment In Most Populated City">
<generalizedQuery>
SELECT r.id, r.t_name, [Length|r.geom] AS length
FROM osm_roads r, (
SELECT p.id, p.t_name, p.t_boundary, p.t_admin_lvl, p.t_population,
p.geom
FROM osm_polygons p
WHERE p.t_boundary = ’administrative’
AND p.t_population IS NOT NULL
ORDER BY [ToNumber|p.t_population] DESC
[Limit|1]
) AS bnd
WHERE r.t_name IS NOT NULL
AND r.t_highway IS NOT NULL
[MySQL: MbrIntersects|r.geom, bnd.geom]
[SQLite: IndexHitF|osm_roads, r, bnd.geom]
AND [Intersects|r.geom, bnd.geom]
ORDER BY [Length|r.geom] DESC
</generalizedQuery>
<oracleQuery>
WITH q1 AS (
SELECT p.id, p.t_name, p.t_population
FROM osm_polygons p
WHERE p.t_boundary = ’administrative’
AND p.t_population IS NOT NULL
ORDER BY CAST(p.t_population AS Numeric) DESC
), bnd AS (
SELECT q1.id, q1.t_name, q2.geom
85
A.2 Komplexe Queries
Philipp Ledermann
FROM q1, osm_polygons q2
WHERE q2.id = q1.id
AND rownum [LessEqual] 1
)
SELECT /*+ NO_INDEX(r idx_osm_roads_id) */ r.id, r.t_name,
SDO_GEOM.SDO_LENGTH(r.geom,0.05) AS length
FROM osm_roads r, bnd
WHERE r.t_name IS NOT NULL
AND r.t_highway IS NOT NULL
AND SDO_RELATE(r.geom, bnd.geom, ’mask=anyinteract’) = ’TRUE’
ORDER BY SDO_GEOM.SDO_LENGTH(r.geom,0.05) DESC
</oracleQuery>
<returnType>LONG</returnType>
<returnType>STRING</returnType>
<returnType>DOUBLE</returnType>
</Query>
<Query name="#8 Nr Of Street Segments In Most Populated City">
<generalizedQuery>
SELECT COUNT(r.id)
FROM osm_roads r, (
SELECT p.id, p.t_name, p.t_boundary, p.t_admin_lvl, p.t_population,
p.geom
FROM osm_polygons p
WHERE p.t_boundary = ’administrative’
AND p.t_population IS NOT NULL
ORDER BY [ToNumber|p.t_population] DESC
[Limit|1]
) AS bnd
WHERE r.t_name IS NOT NULL
AND r.t_highway IS NOT NULL
[MySQL: MbrIntersects|r.geom, bnd.geom]
[SQLite: IndexHitF|osm_roads, r, bnd.geom]
AND [Intersects|r.geom, bnd.geom]
</generalizedQuery>
<oracleQuery>
WITH q1 AS (
SELECT p.id, p.t_name, p.t_population
FROM osm_polygons p
WHERE p.t_boundary = ’administrative’
AND p.t_population IS NOT NULL
ORDER BY CAST(p.t_population AS Numeric) DESC
), bnd AS (
SELECT q1.id, q1.t_name, q2.geom
FROM q1, osm_polygons q2
WHERE q2.id = q1.id
AND rownum [LessEqual] 1
)
SELECT /*+ NO_INDEX(r idx_osm_roads_id) */ COUNT(r.id)
FROM osm_roads r, bnd
WHERE r.t_name IS NOT NULL
AND r.t_highway IS NOT NULL
AND SDO_RELATE(r.geom, bnd.geom, ’mask=anyinteract’) = ’TRUE’
86
A.2 Komplexe Queries
Philipp Ledermann
</oracleQuery>
<returnType>LONG</returnType>
</Query>
<Query name="#9 Top 5 Cities With Most Streets">
<generalizedQuery>
SELECT *
FROM (
SELECT [Oracle: Hint | NO_INDEX(p idx_osm_polygons_id)
NO_INDEX(r idx_osm_roads_id) ] p.t_name, SUM([Length|r.geom])
AS streetsum
FROM osm_polygons p, osm_roads r
WHERE p.t_name IS NOT NULL
AND (p.t_place = ’city’ OR p.t_place = ’town’ OR p.t_place = ’village’)
[MySQL: MbrIntersects|p.geom, r.geom]
[SQLite: IndexHitF|osm_roads, r, p.geom]
AND [Intersects|p.geom, r.geom]
GROUP BY p.t_name
ORDER BY SUM([Length|r.geom]) DESC
) AS q
WHERE 1 = 1
[Limit|5]
</generalizedQuery>
<oracleQuery>
WITH q AS (
SELECT /*+ NO_INDEX(p idx_osm_polygons_id)
NO_INDEX(r idx_osm_roads_id) */ p.t_name,
SUM( SDO_GEOM.SDO_LENGTH(r.geom, 0.05) ) AS streetsum
FROM osm_polygons p, osm_roads r
WHERE p.t_name IS NOT NULL
AND (p.t_place = ’city’ OR p.t_place = ’town’
OR p.t_place = ’village’)
AND SDO_RELATE(p.geom, r.geom, ’mask=anyinteract’) = ’TRUE’
GROUP BY p.t_name
ORDER BY SUM( SDO_GEOM.SDO_LENGTH(r.geom, 0.05) ) DESC
)
SELECT *
FROM q
WHERE rownum [LessEqual] 5
</oracleQuery>
<returnType>STRING</returnType>
<returnType>DOUBLE</returnType>
</Query>
<Query name="#10 Major Roads That Cross Specific Line">
<generalizedQuery>
SELECT DISTINCT r.t_name
FROM osm_roads r
WHERE r.t_name IS NOT NULL
AND [Intersects|r.geom, [FromWKT|[LineString],3785]]
</generalizedQuery>
<returnType>STRING</returnType>
</Query>
<Query name="#11 All Cities, Town, Villages Near Specific Highway">
87
A.2 Komplexe Queries
Philipp Ledermann
<generalizedQuery>
SELECT p.t_name
FROM osm_polygons p, (
SELECT [Buffer|[Union|r.geom], 5000] AS buffer
FROM osm_roads r
WHERE r.t_ref = ’A 10’
) AS bnd
WHERE p.t_name IS NOT NULL
AND p.t_boundary = ’administrative’
AND p.t_admin_lvl [GreaterEqual] ’6’
[SQLite: IndexHitF|osm_polygons, p, bnd.buffer]
AND [Intersects|p.geom, bnd.buffer]
</generalizedQuery>
<oracleQuery>
WITH bnd AS (
SELECT SDO_GEOM.SDO_Buffer(SDO_AGGR_UNION(SDOAGGRTYPE(r.geom,0.05)),
5000, 0.05) AS buffer
FROM osm_roads r
WHERE r.t_ref = ’A 10’
)
SELECT p.t_name
FROM osm_polygons p, bnd
WHERE p.t_name IS NOT NULL
AND p.t_boundary = ’administrative’
AND p.t_admin_lvl [GreaterEqual] ’6’
AND SDO_RELATE(p.geom, bnd.buffer, ’mask=anyinteract’) = ’TRUE’
</oracleQuery>
<returnType>STRING</returnType>
</Query>
<Query name="#12 Streets With Most Pois Nearby">
<generalizedQuery>
WITH bnd AS (
SELECT poly.id, poly.geom
FROM osm_polygons poly
WHERE poly.id = 153461
), roads AS (
SELECT r.id, r.t_name, r.geom, [Buffer|r.geom,250] as buffer
FROM osm_roads r, bnd
WHERE r.t_name IS NOT NULL
AND [Intersects|r.geom, bnd.geom]
)
SELECT roads.id, roads.t_name, COUNT(roads.id)
FROM osm_points p, roads, bnd
WHERE 1 = 1
[MySQL: MbrIntersects|p.geom, bnd.buffer]
[SQLite: IndexHitF|osm_points, p, bnd.buffer]
AND [Intersects|p.geom, bnd.geom]
[Postgres: AND][Postgres: Filter|p.geom, roads.buffer]
[MySQL: MbrIntersects|p.geom, roads.buffer]
[SQLite: IndexHitF|osm_points, p, roads.buffer]
AND [Intersects|p.geom, roads.buffer]
GROUP BY roads.id, roads.t_name
88
A.2 Komplexe Queries
Philipp Ledermann
ORDER BY COUNT(roads.id) DESC
</generalizedQuery>
<oracleQuery>
WITH bnd AS (
SELECT poly.id, poly.geom
FROM osm_polygons poly
WHERE poly.id = 153461
), bnd2 AS (
SELECT poly2.id, poly2.geom
FROM osm_polygons poly2
WHERE poly2.id = 153461
),roads AS (
SELECT r.id, r.t_name, r.geom,
SDO_GEOM.SDO_BUFFER(r.geom, 250, 0.05) as buffer
FROM osm_roads r, bnd2
WHERE r.t_name IS NOT NULL
AND SDO_RELATE(r.geom, bnd2.geom, ’mask=anyinteract’) = ’TRUE’ )
SELECT /*+ NO_INDEX(p idx_osm_points_id) */ roads.id, roads.t_name,
COUNT(roads.id)
FROM osm_points p, roads, bnd
WHERE 1 = 1
AND SDO_RELATE(p.geom, bnd.geom, ’mask=anyinteract’) = ’TRUE’
AND SDO_RELATE(p.geom, roads.buffer, ’mask=anyinteract’) = ’TRUE’
GROUP BY roads.id, roads.t_name
ORDER BY COUNT(roads.id) DESC
</oracleQuery>
<returnType>LONG</returnType>
<returnType>STRING</returnType>
<returnType>LONG</returnType>
</Query>
</Task>
89
A.2 Komplexe Queries
A.2.3
Philipp Ledermann
Komplexe Queries - III
<?xml version="1.0"?>
<Task>
<Candidates>
<Candidate>Oracle</Candidate>
<Candidate>Postgres</Candidate>
<Candidate>MySQL</Candidate>
<Candidate>SQLite</Candidate>
</Candidates>
<Substitutions>
<Substitution>
<Tag>BoundId</Tag>
<Sub>153461</Sub>
</Substitution>
<Substitution>
<Tag>BoundId2</Tag>
<Sub>78276</Sub>
</Substitution>
</Substitutions>
<Query name="#13 Smallest District In Biggest City">
<generalizedQuery>
SELECT p.t_name, p.t_admin_lvl, [Area|p.geom] AS areasize
FROM osm_polygons p, (
SELECT p2.id, p2.t_name, p2.t_boundary, p2.t_admin_lvl,
p2.t_population, p2.geom
FROM osm_polygons p2
WHERE p2.t_boundary = ’administrative’
AND p2.t_population IS NOT NULL
ORDER BY [ToNumber|p2.t_population] DESC
[Limit|1]
) AS bnd
WHERE p.t_name IS NOT NULL
AND p.t_boundary = ’administrative’
AND p.t_admin_lvl = 9
[MySQL: MbrWithin|p.geom, bnd.geom]
[SQLite: IndexHitF|osm_polygons, p, bnd.geom]
AND [Within|p.geom, bnd.geom]
ORDER BY [Area|p.geom]
</generalizedQuery>
<oracleQuery>
WITH bnd AS (
SELECT *
FROM (
SELECT p2.id, p2.t_name, p2.t_boundary, p2.t_admin_lvl,
p2.t_population
FROM osm_polygons p2
WHERE p2.t_boundary = ’administrative’
AND p2.t_population IS NOT NULL
ORDER BY CAST(p2.t_population AS Numeric) DESC
) WHERE rownum [LessEqual] 1
)
90
A.2 Komplexe Queries
Philipp Ledermann
SELECT /*+ NO_INDEX(p idx_osm_polygons_id) */ p.t_name, p.t_admin_lvl,
SDO_GEOM.SDO_AREA(p.geom, 0.05) AS areasize
FROM osm_polygons p, osm_polygons px, bnd
WHERE px.id = bnd.id
AND p.t_name IS NOT NULL
AND p.t_boundary = ’administrative’
AND p.t_admin_lvl = 9
AND SDO_RELATE(p.geom, px.geom, ’mask=inside+coveredby’) = ’TRUE’
ORDER BY CAST(SDO_GEOM.SDO_AREA(p.geom, 0.05) AS Numeric)
</oracleQuery>
<returnType>STRING</returnType>
<returnType>LONG</returnType>
<returnType>DOUBLE</returnType>
</Query>
<Query name="#14 Most Densly Populated Areas">
<generalizedQuery>
SELECT p.t_name, p.t_place,
(([ToNumber|p.t_population]) / [Area|p.geom]) AS density
FROM osm_polygons p
WHERE p.t_boundary = ’administrative’
AND p.t_name IS NOT NULL
AND p.t_population IS NOT NULL
ORDER BY (([ToNumber|p.t_population]) / [Area|p.geom]) DESC
</generalizedQuery>
<returnType>STRING</returnType>
<returnType>STRING</returnType>
<returnType>DOUBLE</returnType>
</Query>
<Query name="#15 Administrative Area With Most Districs">
<generalizedQuery>
SELECT [Oracle: Hint | NO_INDEX(p idx_osm_polygons_id)
NO_INDEX(p2 idx_osm_polygons_id) ] p.t_name, COUNT(p.t_name)
FROM osm_polygons p, osm_polygons p2
WHERE p.t_boundary = ’administrative’
AND p2.t_boundary = ’administrative’
AND p.t_name IS NOT NULL
AND p2.t_name IS NOT NULL
AND p2.t_admin_lvl > p.t_admin_lvl
[MySQL: MbrIntersects|p.geom, p2.geom]
[SQLite: IndexHit|osm_polygons, geom, p2, p]
AND [Within|p2.geom, p.geom]
GROUP BY p.t_name
ORDER BY COUNT(p.t_name) DESC
</generalizedQuery>
<returnType>STRING</returnType>
<returnType>LONG</returnType>
</Query>
<Query name="#16 Administrative Areas With Most Nature">
<generalizedQuery>
SELECT p2.id, p2.t_name, SUM([Area|p.geom]) / [Area|p2.geom]
AS nature_density
FROM osm_polygons p, osm_polygons p2
91
A.2 Komplexe Queries
Philipp Ledermann
WHERE p2.t_boundary = ’administrative’
AND p2.t_admin_lvl [Greater] ’4’
AND p2.t_name IS NOT NULL
AND (p.t_landuse = ’forest’
OR p.t_landuse = ’meadow’
OR p.t_landuse = ’grass’
OR p.t_landuse = ’basin’
OR p.t_landuse = ’wetland’
OR p.t_landuse = ’grassland’
OR p.t_leisure = ’park’
OR p.t_leisure = ’garden’
OR p.t_leisure = ’nature_reservoir’
OR p.t_leisure = ’golf_course’
OR p.t_natural = ’water’
OR p.t_water = ’pond’)
[MySQL: MbrIntersects|p.geom, p2.geom]
[SQLite: IndexHit|osm_polygons, geom, p, p2]
AND [Within|p.geom, p2.geom]
GROUP BY p2.id
ORDER BY SUM([Area|p.geom]) / [Area|p2.geom] DESC
</generalizedQuery>
<oracleQuery>
WITH py AS (
SELECT p2.id,
SUM(CAST(SDO_GEOM.SDO_AREA(p.geom, 0.05) AS Numeric)) AS areasum
FROM osm_polygons p, osm_polygons p2
WHERE p2.t_boundary = ’administrative’
AND p2.t_admin_lvl > ’4’
AND p2.t_name IS NOT NULL
AND (p.t_landuse = ’forest’
OR p.t_landuse = ’meadow’
OR p.t_landuse = ’grass’
OR p.t_landuse = ’basin’
OR p.t_landuse = ’wetland’
OR p.t_landuse = ’grassland’
OR p.t_leisure = ’park’
OR p.t_leisure = ’garden’
OR p.t_leisure = ’nature_reservoir’
OR p.t_leisure = ’golf_course’
OR p.t_natural = ’water’
OR p.t_water = ’pond’)
AND SDO_RELATE(p.geom, p2.geom, ’mask=inside’) = ’TRUE’
GROUP BY p2.id
)
SELECT px.id, px.t_name,
(py.areasum / SDO_GEOM.SDO_AREA(px.geom, 0.05)) AS density
FROM osm_polygons px, py
WHERE px.id = py.id
ORDER BY (py.areasum / SDO_GEOM.SDO_AREA(px.geom, 0.05))
</oracleQuery>
<returnType>LONG</returnType>
<returnType>STRING</returnType>
92
A.2 Komplexe Queries
Philipp Ledermann
<returnType>DOUBLE</returnType>
</Query>
<Query name="#17 Administrative Areas With Most Nature">
<generalizedQuery>
SELECT p2.id, p2.t_name,
SUM([Area|[Intersection|p.geom,p2.geom]]) / [Area|p2.geom]
AS nature_density
FROM osm_polygons p, osm_polygons p2
WHERE p2.t_boundary = ’administrative’
AND p2.t_admin_lvl [Greater] ’4’
AND p2.t_name IS NOT NULL
AND (p.t_landuse = ’forest’
OR p.t_landuse = ’meadow’
OR p.t_landuse = ’grass’
OR p.t_landuse = ’basin’
OR p.t_landuse = ’wetland’
OR p.t_landuse = ’grassland’
OR p.t_leisure = ’park’
OR p.t_leisure = ’garden’
OR p.t_leisure = ’nature_reservoir’
OR p.t_leisure = ’golf_course’
OR p.t_natural = ’water’
OR p.t_water = ’pond’)
[MySQL: MbrIntersects|p.geom, p2.geom]
[SQLite: IndexHit|osm_polygons, geom, p, p2]
AND [Intersects|p.geom, p2.geom]
GROUP BY p2.id
ORDER BY SUM([Area|[Intersection|p.geom,p2.geom]]) / [Area|p2.geom] DESC
</generalizedQuery>
<returnType>LONG</returnType>
<returnType>STRING</returnType>
<returnType>DOUBLE</returnType>
</Query>
<Query name="#18 Administrative Areas With Close Historics Places">
<generalizedQuery>
WITH bnd AS (
SELECT a.id, a.t_name, a.geom, [Buffer|a.geom, 1000] AS buffer
FROM osm_polygons a
WHERE a.t_boundary = ’administrative’
AND a.t_admin_lvl > ’4’
AND a.t_name IS NOT NULL
), his AS (
SELECT p.id, p.geom
FROM osm_points p
WHERE p.t_historic IS NOT NULL
)
SELECT bnd.id, bnd.t_name, COUNT(bnd.id)
FROM bnd, his
WHERE [Intersects|his.geom, bnd.buffer]
AND NOT [Intersects|his.geom, bnd.geom]
GROUP BY bnd.id, bnd.t_name
93
A.2 Komplexe Queries
Philipp Ledermann
ORDER BY COUNT(bnd.id) DESC
</generalizedQuery>
<returnType>LONG</returnType>
<returnType>STRING</returnType>
<returnType>LONG</returnType>
</Query>
</Task>
94