5 Minuten Lesezeit (981 Worte)

Import of GeoJSON into Oracle Spatial - no problem with PowerShell

Donnerstag, 17. November 2022

The GeoJSON format (https://de.wikipedia.org/wiki/GeoJSON) is a popular exchange format because it is supported by many applications. In addition, the JSON structure makes it easy to read for both humans and machines.

With the help of the procedure "sdo_util.from_geojson" provided by Oracle Spatial the format can be converted very easily into the data type SDO_GEOMETRY used by Oracle Spatial. And this even directly during the INSERT of records in the VALUES clause, which I have already made use of frequently, and therefore would like to present to you here.

Why PowerShell?

Quite simply: Because it's there. Completely without the installation of additional components PowerShell is present on every Windows system. This is especially important for my customers, I can not always wish to install additional software.

And for processing GeoJSON very important: PowerShell can not only handle JSON very well, but also process long strings (i.e. CLOBs) without problems and transfer them into the database.

How to connect PowerShell with Oracle?

Here I refer to my article "PowerShell and Oracle - a good team", which is the basis for this example and explains the following first code block.

Add-Type -Path '<Hier Pfad einfügen>\Oracle.ManagedDataAccess.dll' 
. < Insert path here>\Connect-OraInstance.ps1 
. < Insert path here>\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

We need some data

So that you can follow the entire process directly at your site, I use publicly available data here. On the website DataHub (https://datahub.io/core/geo-countries) you can download the geodata of all countries of the world in GeoJSON format.

For downloading, I use the PowerShell command "Invoke-RestMethod", which is actually intended for communication with REST APIs, but in the end is also just a combination of "load web page" and "convert JSON format to PowerShell objects". And that's exactly what we want to do:

$uri = 'https://datahub.io/core/geo-countries/r/0.geojson'$geoJSON = Invoke-RestMethod -Method Get -Uri $uri
However, if the data contains UFT-8 characters, this shortcut will not work, because the command unfortunately interprets the data as "ISO-8859-1". Therefore here is the alternative, if you ever need it:

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

In addition, you can also see how you can read in data that you already have as a file.

We need a table

But let's first take a look at the data to determine the necessary columns.
With the command „$geoJSON | Format-List" we can display the imported data in a list view:

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

We see here that the downloaded data at the top level contains a single object of GeoJSON type "FeatureCollection". However, since we want to insert one row per country rather than just one row in the Oracle database, let's take a closer look at the "features" attribute, which contains a list of elements. With the PowerShell expression „$geoJSON.features.Count" we can display the number of elements: 255 countries are included.
Let's take a look at the first country in the list with "$geoJSON.features[0] | Format-List":

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

Besides the specification of the GeoJSON type "feature" we find two more attributes: "properties" and "geometry". The attribute "properties" again contains an object with two attributes: The attribute "ADMIN" contains the name of the country, the attribute "ISO_A3" contains the 3-letter country code according to ISO-3166-1 (https://en.wikipedia.org/wiki/ISO_3166-1). We want to create a column for each of these two attributes in the Oracle table.
The attribute "geometry" contains the actual geodata, which we want to store in a column of the data type SDO_GEOMETRY.
Now we have gathered all the information to be able to create the table:

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

Now the import can start

In a loop we will now go through all "features", i.e. all countries, and compile the data necessary for the INSERT in each case. For this we use bind variables, which can also be named in Oracle, which is much clearer than using ":1", ":2", etc.. The query itself is thus always the same, so that the execution plan can be reused.
We get the values for the country name and the ISO code directly from the "properties" of the "feature", but we have to convert the geometry information from "geometry" back to JSON. Since the structure may be nested up to four levels deep, we need to use "-Depth 4" to get correct JSON. Since we want to dispense with all wrapping and indentation (which only increases readability for us humans), we also select "-Compress".

And this is what the code looks like:

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 
}

After only a few seconds, all countries are available in the database and can also be displayed via a GIS tool of choice.