Dieser Blog-Artikel soll die Möglichkeit vorstellen, einen funktionsbasierten Index auf Geodaten in der Oracle-Datenbank zu erstellen. Der Geo-Index, der sogenannte R-Tree-Index, wird auf eine Tabellenspalte mit dem Datentyp SDO_GEOMETRY gelegt. Das SDO_GEOMETRY-Objekt beinhaltet die Geodaten.
Für die Arbeit mit diesen Geodaten stellt Oracle einige Spatial-Operatoren zur Verfügung. Anders als die mitgelieferten Funktionen in Packages wie SDO_GEOM und SDO_UTIL, erwarten diese Operatoren als Eingabewert zwingend eine indizierte Geo-Spalte. Diese Spatial-Operatoren sind:
- SDO_NN: den "nächsten Nachbarn" zu einer Geometrie ermitteln
- SDO_FILTER: grober Filter, um Geometrien, die sich nicht in einer Prüf-Geometrie befinden, rauszufiltern
- SDO_RELATE: ermitteln der topologischen Interaktion von zwei Geometrien
- SDO_WITHIN_DISTANCE: prüfen, ob die Distanz zwischen zwei Geometrien sich innerhalb eines bestimmten Wertes befindet
- SDO_POINTINPOLYGON (TABLE-Funktion): ermitteln von Punkten, die in einer bestimmten Geometrie liegen
- SDO_JOIN: dient dem Verknüpfen mehrerer Tabellen über deren Geometrien
Liegen die Geodaten, beispielsweise die Koordinaten von Städten, in Form von numerischen Längen- und Breitengraden in der Tabelle vor, so müssen diese für die Verwendung dieser Spatial-Operatoren zuerst in einer neuen Spalte vom Typen SDO_GEOMETRY hinzugefügt werden. Auf diese neue Spalte kann dann der Geo-Index gelegt werden und die gewünschte Abfrage ausgeführt werden. Jedoch liegen so die Informationen doppelt in der Tabelle vor: einmal als Longitude-Latitude-Wertepaar, einmal als SDO_GEOMETRY. Alternativ dazu kann auch der R-Tree-Index als funktionsbasierter Index angelegt werden. Dies bedeutet, dass nicht die in einer Tabellenspalte vorhandenen Daten indiziert werden, sondern der Index wird auf das Ergebnis einer Funktion angelegt.
In dieser Demo wird ein solcher funktionsbasierter Geo-Index angelegt, sodass die Spatial-Operatoren auch auf einer Tabelle mit nur numerischen Longitude-Latitude-Werten, angewendet werden können.
1. Demotabelle erstellen
Als Datenbasis für die Verwendung des funktionsbasierten Geo-Index wird die Tabelle "staedte" erstellt und zehn Datensätze eingefügt. Jeder Datensatz besteht aus dem Namen der Stadt sowie die Position als Längen- und Breitengrad (Longitude-Latitude-Werte).
2. Funktion erstellen
In diesem Beispiel wird eine Funktion erstellt, die als Input-Parameter die numerischen Werte für die Längen- und Breitengrade entgegen nimmt. Der Rückgabewert ist eine Geometrie vom Typen SDO_GEOMETRY. Innerhalb dieser Funktion wird lediglich geprüft, ob sich die übergebenen Werte in einem bestimmten Wertebereich befinden: die Längengrade müssen in einem Bereich von -180 bis 180 (Grad) liegen, die Breitengrade im Bereich von -90 und 90 (Grad). Des Weiteren wird das SDO_GEOMETRY-Objekt mit den übergebenen Werten für SDO_POINT_TYPE (die x-, y- und z-Koordinaten) erstellt. Weitere Eigenschaften der Geometrie sind in diesem Beispiel fest eingetragen: der GTYPE, der Typ der Geometrie, ist auf den Wert "2001" gesetzt (ein 2-dimensionaler Punkt). Die SRID, das zu verwendende Koordinatensystem, bekommt hier fix den Wert "4326" (WGS 84). Eine weitere notwendige Eigenschaft dieser Funktion für die Verwendung in einem funktionsbasierten Index ist, dass diese deterministisch sein muss: bei gleichen Input-Werten wird immer das gleiche Ergebnis geliefert. Dies wird durch das Schlüsselwort "DETERMINISTIC" im Funktionskopf deklariert.
3. Eintrag in USER_SDO_GEOM_METADATA erstellen
Damit die Geometrien in einer Tabellenspalte mit einem Geo-Index indiziert werden können, muss diese Spalte in der Metadaten-Tabelle "USER_SDO_GEOM_METADATA" eingetragen sein. Andernfalls kommt es zu einem Fehler bei der Erstellung des Index. Diese Metadaten-Tabelle benötigt die folgenden Informationen:
- TABLE_NAME: Name der Tabelle mit der Geometrie-Spalte
- COLUMN_NAME: Name der zu indizierenden Spalte
- DIMINFO: Angabe von Koordinaten einer Geometrie, wogegen die Geometrien in der Spalte geprüft werden, ob diese sich innerhalb oder außerhalb davon befinden
- SRID: die verwendete SRID
Da in diesem Beispiel aber keine Geometrien-Spalte vorhanden ist, wird an dieser Stelle die zuvor erstellte Funktion mit ihrer Signatur als "Spalte" eingetragen. Zwei Dinge sind hierbei jedoch zu beachten:
1. der Schemaname, in dem sich die Funktion befindet, muss mit angegeben werden (hier: ora01)
2. es dürfen keine Leerzeichen vorkommen, sodass die Funktionssignatur (Parameterliste) hintereinander weg geschrieben werden muss. Als Parameter für die Funktion werden die Longitude und Latitude-Spalten der Tabelle "staedte" übergeben.
4. Funktionsbasierten Index erstellen
In diesem Schritt wird der eigentliche Geo-Index mittels der CREATE INDEX-Anweisung erstellt. Einzige Besonderheit hier ist, dass statt der Angabe der Tabellenspalte der Funktionsname mit Parameterliste (wieder ohne Leerzeichen, der Schemaname vorab ist nicht notwendig) eingetragen wird. Der optionale Parameter "layer_gtype=POINT" sorgt dafür, dass in diesem Geo-Index nur Geometrien vom Typen Punkt enthalten sind.
5. Index in Abfrage verwenden
Nun kann die Funktion als Parameter für die Spatial-Operatoren, bei denen die zu prüfenden Geometrien mit einem Geo-Index indiziert sein müssen, verwendet werden.
Ergebnis
| NAME |
LONGITUDE |
LATITUDE |
| Mainz |
8,271111 |
50 |
| Frankfurt | 8,682222 | 50,110556 |
| Düsseldorf | 6,782778 | 51,225556 |
| Essen | 7,014761 | 51,458069 |
| Köln | 6,956944 | 50,938056 |
Quellen und Bildnachweis
Spatial and Graph Developer's Guide (Oracle 12c Release 2): 10 Extending Spatial Indexing Capabilities: 10.2 SDO_GEOMETRY Objects in Function-Based Indexes >10.2.1 Example: Function with Standard Types:https://docs.oracle.com/database/122/SPATL/extending-spatial-indexing.htm#SPATL800
Spatial and Graph Developer's Guide (Oracle 12c Release 2): 20 Spatial Operators:
https://docs.oracle.com/database/122/SPATL/spatial-operators-reference.htm#SPATL110
Bildnachweis:
https://technology.amis.nl/2017/05/17/oracle-database-standard-geo-location-support-using-locator-included-in-every-edition/