Import of GeoJSON into Oracle Spatial - no problem with PowerShell

last update: May 26, 2025 

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 '<Insert path here>\Oracle.ManagedDataAccess.dll' 
. <Insert path here>\Connect-OraInstance.ps1 
. <Insert path here>\Invoke-OraQuery.ps1 
$instance = '<Configure the connection to the instance here>' 
$credential = Get-Credential -Message 'Please enter user and password for the instance' 
$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:

$geoJSON = Invoke-RestMethod -Method Get -Uri https://datahub.io/core/geo-countries/r/0.geojson

Due to the SSL configuration of the website, this command can no longer be executed with PowerShell 5.1, but there are no problems with PowerShell 7 (currently 7.5.1).

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
name     : ne_10m_admin_0_countries
crs      : @{type=name; properties=}
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: 258 countries are included.
Let's take a look at the first country in the list with "$geoJSON.features[0] | Format-List":

type       : Feature 
properties : @{name=Indonesia; ISO3166-1-Alpha-3=IDN; ISO3166-1-Alpha-2=ID} 
geometry   : @{type=MultiPolygon; 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 three attributes: The attribute "name" contains the name of the country, the attribute "ISO3166-1-Alpha-3" contains the 3-letter country code according to ISO-3166-1 (https://en.wikipedia.org/wiki/ISO_3166-1) and the attribute "ISO3166-1-Alpha-2" contains the 2-letter country code. We want to create a column for the first 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.name 
            iso      = $feature.properties.'ISO3166-1-Alpha-3'
            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.

Conclusion

With a few lines of PowerShell code, data in GeoJSON format can be imported into an Oracle table.

A slightly more extensive version of this script including error handling can be found on GitHub.

Do you want to import or export geodata from other formats? Contact us, we are happy to help.

Seminarempfehlungen