Oracle Spatial: funktionsbasierter Geo-Index

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).

CREATE TABLE staedte 
( name       VARCHAR2(20)
, longitude  NUMBER
, latitude   NUMBER
);
INSERT INTO staedte (name, longitude, latitude) VALUES ('Berlin', 13.388854800, 52.517039700);
INSERT INTO staedte (name, longitude, latitude) VALUES ('Paderborn', 8.752653000, 51.717704400);
INSERT INTO staedte (name, longitude, latitude) VALUES ('Mainz', 8.271111000, 50.000000000);
INSERT INTO staedte (name, longitude, latitude) VALUES ('Frankfurt', 8.682222000, 50.110556000);
INSERT INTO staedte (name, longitude, latitude) VALUES ('Hamburg', 9.993333000, 53.550556000);
INSERT INTO staedte (name, longitude, latitude) VALUES ('Düsseldorf', 6.782778000, 51.225556000);
INSERT INTO staedte (name, longitude, latitude) VALUES ('Dresden', 13.738360000, 51.049259000);
INSERT INTO staedte (name, longitude, latitude) VALUES ('Essen', 7.014761000, 51.458069000);
INSERT INTO staedte (name, longitude, latitude) VALUES ('München', 11.575556000, 48.137222000);
INSERT INTO staedte (name, longitude, latitude) VALUES ('Köln', 6.956944000, 50.938056000);
COMMIT;

SELECT * FROM staedte; 

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.

CREATE OR REPLACE FUNCTION fnc_lon_lat_to_geom ( pi_lon IN NUMBER
                                               , pi_lat IN NUMBER )
RETURN SDO_GEOMETRY
DETERMINISTIC
IS
  geom SDO_GEOMETRY;
BEGIN
  IF (pi_lon BETWEEN -180 AND 180) AND (pi_lat BETWEEN -90 AND 90)
  THEN RETURN SDO_GEOMETRY( 2001 -- 2-dimensionaler Punkt
                           , 4326 -- SRID
                           , SDO_POINT_TYPE( pi_lon -- x-Koordinate
                                           , pi_lat -- y-Koordinate
                                           , NULL   -- z-Koordinate
                                           )
                           , NULL -- SDO_ELEM_INFO_ARRAY
                           , NULL -- SDO_ORDINATE_ARRAY
                           );
  ELSE RETURN NULL;
  END IF;
END;
/


-- Testaufruf der Funktion in einer SELECT-Abfrage
SELECT s.*
     , fnc_lon_lat_to_geom(s.longitude, s.latitude) AS geom
  FROM staedte s;
 

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.

INSERT INTO user_sdo_geom_metadata
VALUES ( 'STAEDTE'  -- Tabellenname
       , 'ora01.fnc_lon_lat_to_geom(longitude,latitude)' -- Spaltenname, hier: Funktionssignatur
       , MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT('X', -90, 90, 0.005)
                            , MDSYS.SDO_DIM_ELEMENT('Y', -180, 180, 0.005)
                            )
       , 4326 -- SRID
       );
COMMIT;

SELECT * FROM user_sdo_geom_metadata; 

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.

CREATE INDEX idx_staedte
ON staedte (fnc_lon_lat_to_geom(longitude,latitude))
INDEXTYPE IS mdsys.spatial_index
PARAMETERS ('layer_gtype=POINT'); 

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.

-- Aufbau der Spatial-Operatoren als Pseude-Code:
SELECT *
FROM <Tabelle>
WHERE <Spatial_Index_Funktion>( fnc_lon_lat_to_geom(pi_lon, pi_lat) -- erster Parameter = indizierte Spalte
                              , <zweite_geometrie> -- 
Prüfgeometrie
                              , <weitere Parameter> -- evtl. weitere Paramter für die Funktion
                              );

-- Beispiel mit den Demodaten und dem Operator SDO_NN:
-- gesucht werden die nächsten fünf Städte zu einem gegebenen Punkt.
SELECT *
FROM staedte s
WHERE SDO_NN( fnc_lon_lat_to_geom(s.longitude, s.latitude) -- "indizierte Geospalte"
            , SDO_GEOMETRY( 2001
                          , 4326
                          , SDO_POINT_TYPE(8.292635, 50.054683, NULL)
                          , NULL
                          , NULL
                          ) -- Punkt, dessen 5 nächsten Städte gesucht werden sollen
            , 'sdo_num_res=5' -- Parameter für SDO_NN: die 5 nächsten Nachbarn suchen
            ) = 'TRUE'; 

Ergebnis

Quellen und Bildnachweis