5 Minuten Lesezeit (927 Worte)

Import von GeoJSON in Oracle Spatial – mit PowerShell kein Problem

Donnerstag, 17. November 2022

Das GeoJSON-Format ist ein beliebtes Austauschformat, da es von vielen Applikationen unterstützt wird. Zudem ist es durch die JSON-Struktur sowohl für Menschen als auch für Maschinen gut lesbar.

Mithilfe der von Oracle Spatial zur Verfügung gestellten Prozedur „sdo_util.from_geojson“ kann das Format sehr einfach in den von Oracle Spatial verwendeten Datentyp SDO_GEOMETRY umgewandelt werden. Und das sogar direkt beim INSERT von Datensätzen in der VALUES-Klausel, was ich mir schon häufiger zunutze gemacht habe, und Ihnen daher hier vorstellen möchte.

Warum PowerShell?

Ganz einfach: Weil es da ist. Ganz ohne die Installation von zusätzlichen Komponenten ist PowerShell auf jedem Windows System vorhanden. Das ist gerade bei meinen Kunden wichtig, nicht immer kann ich mir die Installation von zusätzlicher Software wünschen.

Und für die Verarbeitung GeoJSON ganz entscheidend: PowerShell kann nicht nur sehr gut mit JSON umgehen, sondern auch ohne Probleme lange Zeichenketten (also CLOBs) verarbeiten und in die Datenbank übertragen.

Wie verbinde ich PowerShell mit Oracle?

Hier verweise ich auf meinen Artikel „PowerShell und Oracle – ein gutes Team“, der die Grundlage für dieses Beispiel bildet und den folgenden ersten Codeblock erläutert.

Add-Type -Path '<Hier Pfad einfügen>\Oracle.ManagedDataAccess.dll' 
. <Hier Pfad einfügen>\Connect-OraInstance.ps1 
. <Hier Pfad einfügen>\Invoke-OraQuery.ps1 
$instance = '<Hier die Verbindung zur Instanz konfigurieren>' 
$credential = Get-Credential -Message 'Bitte Benutzer und Kennwort für die Instanz eintragen' 
$connection = Connect-OraInstance -Instance $instance -Credential $credential

Wir brauchen ein paar Daten

Damit Sie das Ganze auch direkt bei sich nachvollziehen können, verwende ich hier öffentlich zugängliche Daten. Auf der Webseite DataHub (https://datahub.io/core/geo-countries) finden Sie die Geodaten aller Länder der Erde im GeoJSON Format zum Download.

Zum Download nutze ich den PowerShell Befehl „Invoke-RestMethod“, der zwar eigentlich für die Kommunikation mit REST-APIs vorgesehen ist, aber letztendlich auch nur eine Kombination aus „Webseite laden“ und „JSON-Format in PowerShell-Objekte umwandeln“ ist. Und genau das wollen wir machen:

$uri = 'https://datahub.io/core/geo-countries/r/0.geojson'

$geoJSON = Invoke-RestMethod -Method Get -Uri $uri

Wenn die Daten allerdings UFT-8-Zeichen enthalten, funktioniert diese Abkürzung nicht, da der Befehl die Daten leider als „ISO-8859-1“ interpretiert. Daher hier noch die Alternative, falls Sie diese mal benötigen:

$filename = '.\TEMPFILE.geojson' 
Invoke-WebRequest -Uri $uri -OutFile $filename 
$geoJSON = Get-Content -Path $filename -Encoding UTF8 | ConvertFrom-Json 
Remove-Item -Path $filename

Zudem sehe Sie so auch, wie Sie Daten einlesen können, die Sie bereits als Datei vorliegen haben.

Wir brauchen eine Tabelle

Aber werfen wir zunächst einen Blick auf die Daten, um die notwendigen Spalten zu ermitteln.

Mit dem Befehl „$geoJSON | Format-List“ können wir uns die importierten Daten in einer Listenansicht darstellen lassen:

type     : FeatureCollection 
features : {@{type=Feature; properties=; geometry=}, @{type=Feature; properties=; geometry=},  
           @{type=Feature; properties=; geometry=}, @{type=Feature; properties=; geometry=}...}

Wir sehen hier, dass die heruntergeladenen Daten auf der obersten Ebene ein einzelnes Objekt vom GeoJSON-Typ „FeatureCollection“ beinhalten. Da wir in der Oracle Datenbank aber nicht nur eine Zeile, sondern eine Zeile pro Land einfügen wollen, schauen wir uns das Attribut „features“ näher an, dass eine Liste von Elementen enthält. Mit dem PowerShell-Ausdruck „$geoJSON.features.Count“ können wir uns die Anzahl der Elemente anzeigen lassen: Es sind 255 Länder enthalten.

Werfen wir mit „$geoJSON.features[0] | Format-List“ einen Blick auf das erste Land der Liste:

type       : Feature 
properties : @{ADMIN=Aruba; ISO_A3=ABW} 
geometry   : @{type=Polygon; coordinates=System.Object[]}

Neben der Angabe des GeoJSON-Typs „Feature“ finden wir zwei weitere Attribute: „properties“ und „geometry“.

Das Attribut „properties“ enthält wiederum ein Objekt mit zwei Attributen: Das Attribut „ADMIN“ enthält den Namen des Landes in englischer Sprache, das Attribut „ISO_A3“ den 3-buchstabigen Ländercode nach ISO-3166-1. Für diese beiden Attribute wollen wir in der Oracle Tabelle jeweils eine Spalte anlegen.

Das Attribut „geometry“ enthält die eigentlichen Geodaten, die wir in einer Spalte vom Datentyp SDO_GEOMETRY ablegen wollen.

Jetzt haben wir alle Informationen zusammengetragen, um die Tabelle erstellen zu können:

$query = @' 
CREATE TABLE countries ( 
  name     VARCHAR2(50), 
  iso      CHAR(3), 
  geometry SDO_GEOMETRY 
) 
'@  
Invoke-OraQuery -Connection $connection -Query $query

Jetzt kann der Import beginnen

In einer Schleife werden wir jetzt alle „features“, also alle Länder durchlaufen und jeweils die für den INSERT notwendigen Daten zusammenstellen. Dabei verwenden wir Bind-Variablen, die bei Oracle auch benannt werden können, was viel übersichtlicher als die Verwendung von „:1“, „:2“, etc. ist. Die Abfrage selbst ist damit immer die gleiche, sodass der Ausführungsplan wiederverwendet werden kann.

Die Werte für den Ländernamen und den ISO-Code holen wir direkt aus den „properties“ des „feature“, die Geometrie-Information aus „geometry“ müssen wir wieder zurück in JSON umwandeln. Da die Struktur unter Umständen bis zu vier Ebenen tief geschachtelt ist, müssen wir „-Depth 4“ verwenden, um korrektes JSON zu bekommen. Da wir auf sämtliche (nur die Lesbarkeit für uns Menschen erhöhenden) Umbrüche und Einrückungen verzichten wollen, wählen wir zudem „-Compress“.

Und so sieht der Code dann aus:

foreach ($feature in $geoJSON.features) { 
    $invokeParams = @{ 
        Connection      = $connection 
        Query           = 'INSERT INTO countries VALUES (:name, :iso, sdo_util.from_geojson(:geometry))' 
        ParameterValues = @{ 
            name     = $feature.properties.ADMIN 
            iso      = $feature.properties.ISO_A3 
            geometry = $feature.geometry | ConvertTo-Json -Depth 4 -Compress  
        } 
    } 
    Invoke-OraQuery @invokeParams 
}

Nach nur wenigen Sekunden sind alle Länder in der Datenbank vorhanden und können über ein GIS-Tool der Wahl auch angezeigt werden.