Blog Name
  • Dezember 2018

Nutzung von Geodaten in der Oracle Datenbank

Die Oracle Datenbank ist eine multi-modale Datenbank. Das heißt, neben relational modellierten Daten können gleichberechtigt auch Dokument-zentrische, unstrukturierte oder eben auch Geodaten verwaltet und mit SQL analysiert werden. Bekannt ist diese Unterstützung für Geodaten unter den Namen Oracle Locator und Oracle Spatial and Graph.
Oracle Locator ist ein Feature jeder Edition der Oracle Datenbank. Spatial and Graph ist eine Option zur Oracle Datenbank Enterprise Edition. Die Option umfaßt Funktionalität, die weit über den Locator hinausgeht.
Recht neu und daher vielleicht noch nicht allen bekannt: Die Express Edition der Oracle Datenbank inkludiert seit Version 18c auch die Nutzung von Datenbank Optionen wie Spatial and Graph. Eine genaue Auflistung der verwendbaren Features und Optionen findet sich im Licensing Guide.

Die nachfolgenden Abschnitte führen ein in die Speicherung und Nutzung von Geodaten am Beispiel von Bike and Ride Stationen in Düsseldorf. Diese Daten stehen als Open Data im Europäischen Datenportal zum Download bereit.

Arten von Geodaten

Für Geodaten stehen sowohl 3 objektrelationale Datentypen bereit:

  • SDO_GEOMETRY für Vektordaten,
  • SDO_GEORASTER für Rasterdaten und
  • SDO_PC für Punktwolken, auch LiDAR genannt.

Zusätzlich gibt es 3 Datenmodelle, um

  • räumliche Netzwerke (z.B. Straßennetze),
  • topologisch zusammenhängende Vektordaten und
  • Lineare Bezugssysteme

abzubilden.

Alle benötigten Datenbankobjekte sowie Metadaten (hierzu komme ich etwas später) liegen im Schema MDSYS.

Vektordaten

Vektordaten, häufig auch Features genannt, sind geometrische Objekte, die mittels Punkten, Linien, Polygonen, Kreisbögen oder beliebigen Kombinationen davon dargestellt werden und die einen Bezug zu einem Koordinatensystem haben. Sie sind immer mindestens 2-dimensional. Als 3. Dimension wird typischerweise der Höhenwert angegeben, wohingegen die 4. Dimension häufig für die Zeit genutzt wird.
Bei den verwendeten Beispieldaten handelt es sich um 2D Daten.
(Anmerkung: Der Höhenwert ist zwar angegeben, aber immer 0. Daher kann er vernachlässigt werden.)


drop table dus_ride_and_bike_stationen
/

create table dus_ride_and_bike_stationen (
    id number(10) generated always as identity
        (start with 1 increment by 1 nocycle nocache order) primary key,
    longitude number(10,6),
    latitude number(10,6),
    altitude number(3),
    geometry varchar2(20),
    name varchar2(100),
    anschrift varchar2(100),
    strasse varchar2(100),
    plz char(5),
    ort varchar2(100),
    haltestelle_oepnv varchar2(100),
    kapazitaet varchar2(50),
    telefon varchar2(20),
    email varchar2(50),
    fax varchar2(20),
    oeffnungszeiten varchar2(255),
    radverkauf varchar2(50),
    zubehoerverkauf varchar2(50),
    wartung_reparatur varchar2(50),
    self_service varchar2(50),
    radverleih varchar2(50),
    sonstiges varchar2(50),
    location sdo_geometry)
/

Alle Spalten einer Tabelle vom Datentyp SDO_GEOMETRY müssen deklariert werden. Dazu werden die sogenannten SDO-Metadaten erzeugt. SDO steht für Spatial Data Object. Die Metadaten beschreiben,

  • welche Geodaten mit welcher Dimensionalitä in der Tabelle gespeichert werden sowie
  • auf welches Koordinatensystem sich diese Daten beziehen.


delete from user_sdo_geom_metadata 
where 
    lower(table_name) = 'dus_ride_and_bike_stationen' and 
    lower(column_name) = 'location'
/

insert into user_sdo_geom_metadata (
    table_name,
    column_name,
    diminfo,
    srid)
values (
    'dus_ride_and_bike_stationen',                       
    'location',                                        
    sdo_dim_array(
        sdo_dim_element('Longitude', -180, 180, 0.05),   -- max. Ausdehnung nach Osten oder Westen
        sdo_dim_element('Latitude', -90, 90, 0.05)),     -- max. Ausdehnung nach Norden oder Süden
    4326)
/

commit
/

select * from user_sdo_geom_metadata
/

Eine sehr einfache Methode, die Quelldaten (.csv) in die Tabelle zu laden, ist die Nutzung der "Import Data" Funktion im SQL Developer. Zu beachten ist, daß im 1. Schritt sowohl Trennzeichen als auch Encoding richtig gesetzt werden und im 4. der insgesamt 5 Schritte für einige Spalten die Zuordnung manuell erfolgt. Das liegt an der Verwendung von Umlauten in den Spaltennamen der Quelldaten.
Beispielhaft enthält der nachfolgende Code die Insert Statements für 3 der 75 Datensätze.


INSERT INTO DUS_RIDE_AND_BIKE_STATIONEN (LATITUDE, LONGITUDE, ALTITUDE, GEOMETRY, NAME, ANSCHRIFT, STRASSE, PLZ, ORT, HALTESTELLE_OEPNV, KAPAZITAET, TELEFON, EMAIL, FAX, OEFFNUNGSZEITEN, RADVERKAUF, ZUBEHOERVERKAUF, WARTUNG_REPARATUR, SELF_SERVICE, RADVERLEIH, SONSTIGES) 
VALUES (51.195687, 6.768617, 0.0, 'point', 'Bike+Ride Aachener Straße - Aachener Platz', 'Aachener Straße 40223 Düsseldorf', 'Aachener Straße', '40223', 'Düsseldorf', 'Aachener Platz', '. Stellplätze', '', '', '', '', '', '', '', '', '', '')
/

INSERT INTO DUS_RIDE_AND_BIKE_STATIONEN (LATITUDE, LONGITUDE, ALTITUDE, GEOMETRY, NAME, ANSCHRIFT, STRASSE, PLZ, ORT, HALTESTELLE_OEPNV, KAPAZITAET, TELEFON, EMAIL, FAX, OEFFNUNGSZEITEN, RADVERKAUF, ZUBEHOERVERKAUF, WARTUNG_REPARATUR, SELF_SERVICE, RADVERLEIH, SONSTIGES) 
VALUES (51.241592, 6.73752, 0.0, 'point', 'Bike+Ride Am Seestern U', 'Am Seestern 40547 Düsseldorf', 'Am Seestern', '40547', 'Düsseldorf', 'Am Seestern U', '16 Stellplätze', '', '', '', '', '', '', '', '', '', '')
/

INSERT INTO DUS_RIDE_AND_BIKE_STATIONEN (LATITUDE, LONGITUDE, ALTITUDE, GEOMETRY, NAME, ANSCHRIFT, STRASSE, PLZ, ORT, HALTESTELLE_OEPNV, KAPAZITAET, TELEFON, EMAIL, FAX, OEFFNUNGSZEITEN, RADVERKAUF, ZUBEHOERVERKAUF, WARTUNG_REPARATUR, SELF_SERVICE, RADVERLEIH, SONSTIGES) 
VALUES (51.326056, 6.775968, 0.0, 'point', 'Bike+Ride An den Kämpen', 'Angermunder Straße 40489 Düsseldorf', 'Angermunder Straße', '40489', 'Düsseldorf', 'An den Kämpen', '6 Stellplätze', '', '', '', '', '', '', '', '', '', '')
/

commit
/

Was jetzt noch fehlt, ist die Übersetzung der Werte in den Spalten LONGITUDE und LATITUDE in die SDO_GEOMETRY Spalte LOCATION. Alle Standorte von Ride und Bike Stationen sind Punkte. Daher ist die Übersetzung super einfach.


update dus_ride_and_bike_stationen set location = sdo_geometry(
    2001,                -- Definition Geometrietyp und Dimensionionalität: 2D Punkt
    4326,                -- Koordinatensystem: WGS84 = 4326
    sdo_point_type(
        longitude,       -- Spalte mit Längengrad
        latitude,        -- Spalte mit Breitengrad 
        null),           -- 3. Dimension vernachlässigt, weil immer 0
    null,                -- wird für Punkte nicht benötigt
    null)                -- wird für Punkte nicht benötigt
/

commit
/

Um die in der Oracle Datenbank bereitgestellten räumlichen Funktionen in vollem Umfang für Abfragen und Analysen zu nutzen, wird jetzt noch ein spezieller räumlicher (Domain) Index benötigt.
Hinweis: Ab Version 18.1 ist ein solcher Index nicht mehr erforderlich. Dennoch wird als gute Praxis empfohlen, diesen anzulegen. Die entsprechende Information findet sich im Spatial Developer Guide.

Das nachfolgende Statement zeigt die Syntax zum Anlegen eines für 2D Punktdaten optimierten räumlichen Index. Der Parameter SDO_INDX_DIMS kann weggelassen werden, da dessen Standardwert bereits auf 2 gesetzt ist.


create index six_dus_ride_and_bike_stationen_location 
    on dus_ride_and_bike_stationen(location) 
    indextype is mdsys.spatial_index_v2
    parameters ('layer_gtype=POINT sdo_indx_dims=2 cbtree_index=true')
/

Geschafft soweit. Jetzt sollten wir schon mal ein ersten Ergebnis in grafischer Form anzeigen können. Dazu kann wieder der SQL Developer verwendet werden. Dieser hat eine Erweiterung für die Anzeige von 2D SDO_GEOMETRY Daten.
Im Menü "View" wählen Sie dazu den Eintrag "Map View" aus. Im "Map Query" Bereich klicken Sie dann auf das grüne Plus-Zeichen, um eine neue Kartenansicht anzulegen.

Als Ergebnis sind die Ride und Bike Stationen im Kartenfenster zu sehen.
Mittels Kontext Menü können die Zoom-Stufe geändert oder die Ansicht auf einen beliebigen Punkt zentriert werden.

Die Kartenansicht im SQL Developer dient nur dazu, eine Sichtprüfung vorzunehmen. Um zu erkennen, ob die Lage der Punkte wirklich richtig mit den geografischen Orten verknüpft ist, braucht es zur Orientierung eine darunterliegende topografische Karte. Wie das genau geht, führt innherhalb dieses Artikels zu weit. Nur soviel: Die zum Erzeugen der nachfolgenden Kartenansicht verwendete Software ist Teil des Oracle Spatial Technologies Produktstacks and kann ohne zusätzliche Lizenzkosten zusammen mit der Option Oracle Spatial and Graph verwendet werden.
Für die topologische Karte wurde der frei verfügbare OpenStreetMap WMS (Kartendienst) von Terrestris verwendet.

Räumliche Abfragen

Zum Abschluß sollen beispielhaft zwei räumliche Abfragen ausgeführt werden. Abfrage 1 ermittelt für jede Ride und Bike Station, welche 5 anderen Stationen in Luftlinie jeweils am nächsten gelegen sind. Dazu wird der Operator SDO_NNverwendet. NN steht für Nearest Neighbor. SDO_NN berechnet intern die Distanz. Diese kann über SDO_NN_DISTANCE ausgegegeben werden.


select 
    station_1,
    station_2,
    dist_in_km
from (
    select
        a.name as station_1,
        b.name as station_2,
        round(sdo_nn_distance(1),2) as dist_in_km
    from
        dus_ride_and_bike_stationen a,
        dus_ride_and_bike_stationen b
    where 
        sdo_nn(
            b.location, 
            a.location, 
           'sdo_num_res=6 unit=km',  -- Zunächst 6 nächste Nachbarn
           1) = 'TRUE')
where 
    dist_in_km > 0                   -- Paare mit station_1=station_2 ausschließen (dist=0)
order by 
    station_1,
    dist_in_km
/

STATION_1                                  STATION_2                        DIST
------------------------------------------ -------------------------------- ----
Bike+Ride Aachener Straße - Aachener Platz Bike+Ride Südfriedhof            1.05
Bike+Ride Aachener Straße - Aachener Platz Bike+Ride Bilk S                 1.56
Bike+Ride Aachener Straße - Aachener Platz Bike+Ride Völklinger Straße S    1.63
Bike+Ride Aachener Straße - Aachener Platz Bike+Ride Christophstrasse       1.96
Bike+Ride Aachener Straße - Aachener Platz Bike+Ride Friedrichstadt S        2.2
Bike+Ride Am Seestern U                    Bike+Ride Lohweg U                .66
Bike+Ride Am Seestern U                    Bike+Ride Dominikus-Krankenhaus  1.19
Bike+Ride Am Seestern U                    Bike+Ride Belsenplatz            1.31
Bike+Ride Am Seestern U                    Bike+Ride Lörick                 1.45
Bike+Ride Am Seestern U                    Bike+Ride Barbarossaplatz        1.63
Bike+Ride An den Kämpen                    Bike+Ride Auf der Krone           .35
...

Der räumliche Index ist vorhanden und wird auch verwendet. Sichtbar wird dies in der Abbildung mit dem Ausführungsplan.

Abfrage 2 ist ähnlich, aber mit mehreren räumlichen Operatoren und Funktionen.
Zunächst wird die äußere Hülle über alle Stationen berechnet. Von dem daraus entstehenden räumlichen Objekt, ein Polygon, wird anschließend der Mittel- bzw. Schwerpunkt bestimmt. Und schließlich sollen diejenigen Stationen ermittelt und ausgegeben werden, welche maximal 2 km von diesem Punkt entfernt sind.


with mittelpunkt as (
    select 
        sdo_geom.sdo_centroid(
            sdo_aggr_convexhull(sdoaggrtype(a.location, 0.05)),
        0.05) centroid
    from
        dus_ride_and_bike_stationen a)
select /*+ ORDERED */
    b.name as station
from
    dus_ride_and_bike_stationen b,
    mittelpunkt m
where
    sdo_within_distance(
        b.location,
        m.centroid,
        'distance=2 unit=km') = 'TRUE'
order by
    station
/

STATION
--------------------------
Bike+Ride Burgmüllerstraße
Bike+Ride Derendorf S
Bike+Ride Flingern S
Bike+Ride Staufenplatz 
Bike+Ride Wehrhahn S
Bike+Ride Zoo S

Oracle Application Express Fans können die Unterstützung für Geodaten auf sehr komfortable Weise nutzen. Mit dem Sample Geolocation Showcase steht eine Beispielanwendung zur Verfügung, die nicht nur 2D Daten visualisieren und analysieren kann. Zusätzlich können neue Daten einfach über ihre Adresse erfaßt werden. Die Adressen werden dann in die jeweiligen Koordinaten umgerechnet.

Die nachfolgende Ansicht zeigt die Ride und Bike Stationen geclustert. Je mehr die Kartenansicht vergrößert wird, umso detaillierter sind die Stationen zu sehen.

Alle Kartenfunktionen wie auch die Geokodierung (Umrechnung von Adressen in Koordinaten) sind als Plug-ins implementiert und können in anderen APEX Anwendungen wiederverwendet werden.

Haben Sie Lust auf mehr zu Geodaten in der Oracle Datenbank bekommen, dann schauen Sie hier bald wieder rein oder stöbern Sie in unseren Blogs. Die Verweise darauf finden Sich gleich im Anschluß.

Weitere Informationen

 

Zurück zur Community-Seite
 

Visit the Oracle Blog

 

Contact Us

Oracle

Integrated Cloud Applications & Platform Services