Loading GeoJSON Format GeoSpatial Data into Oracle Autonomous Database

• This blog is part of a series about aspects and features of Oracle 26 and Autonomous Transaction Processing Database.

As a training exercise, I have created a database application that loads GPS data from activities logged on Strava and compares them with geospatial data to identify the named places visited by the activity. This geospatial data is publicly available in many places, often from government, and is usually freely available, at least for non-commercial purposes.

ESRI Shapefiles

When I first wrote the spatial processing in 2020, I used spatial data formatted as ESRI shapefiles.  These were then converted to Oracle spatial geometries and loaded into a database using the oracle.spatial.util: Class SampleShapefileToJGeomFeature Java conversion.  

See also

• Go-Faster Blog: Spatial Data 4: Obtaining Geographical Data
• Oracle 26 Spatial Developers Guide: Loading ESRI Shapefiles into Spatial

It reads attributes from a dBase file and geometries from a Shapefile, and then writes them to a database table.  I was able to run it on the database server.

export clpath=$ORACLE_HOME/suptools/tfa/release/tfa_home/jlib/ojdbc5.jar:$ORACLE_HOME/md/jlib/sdoutl.jar:$ORACLE_HOME/md/jlib/sdoapi.jar
java -cp $clpath oracle.spatial.util.SampleShapefileToJGeomFeature -h <dbhost> -p 1521 -sn oracle_pdb -u strava -d strava \
     -t $table -f $base -r 4326 -g geom

However, this approach is not suitable for an autonomous database.  ADB cannot access the local file system, there is no host execution access, and it does not allow external libraries (JARs) required by the Shapefile utility.  It would have to be run on another host.  It only makes a simple JDBC connection and cannot handle the ADB wallet out of the box.

GeoJSON

I have found that many spatial data sets are now available as GeoJSON, often as well as a shapefile.  GeoJSON is much easier to handle, especially in the Autonomous database, because it can be processed entirely within the database.

Often, GeoJSON can be downloaded directly into a database with an HTTP call.  For example, the definitions of Areas of Outstanding Natural Beauty (AONBs) in England can be downloaded as GeoJSON file from the UK Government Planning Data website.  I can make the HTTP call to download the file directly from PL/SQL.  I usually load the JSON into a staging table before trying to convert it to a spatial geometry.  The HTTP request is made by a packaged function strava_http.http_request.

TRUNCATE TABLE strava.stage_geo_data;
DECLARE 
  l_url VARCHAR2(1000) := 'https://files.planning.data.gov.uk/dataset/area-of-outstanding-natural-beauty.geojson';
  l_clob CLOB;
BEGIN
  DBMS_LOB.createtemporary(l_clob, TRUE);
  l_clob:=strava_http.http_request(l_url,99);    
  INSERT INTO stage_geo_data (name, geo_json) VALUES ('Natural England', l_clob);
  DBMS_LOB.freetemporary(l_clob);
END;
/
SELECT x.*, length(geo_json) FROM stage_geo_data x;

However, sometimes the GeoJSON must be downloaded as a file, uploaded to OCI Bucket storage, and then read into the database from there.

DECLARE
  l_blob BLOB;
BEGIN
  -- Read the file from Object Storage into CLOB
  l_blob := DBMS_CLOUD.GET_OBJECT
            (credential_name => 'OBJECT_STORE_CRED'
            ,object_uri      => 'https://objectstorage.uk-london-1.oraclecloud.com/n/l**********a/b/bucket-gofaster1/o/ch0.json'
            );
  INSERT INTO strava.stage_geo_data(name, geo_json) VALUES ('Switzerland', l_blob);
  COMMIT;
END;
/

Converting Shapefiles to GeoJSON 

Some providers still prefer to make only Shapefiles available and not GeoJSON (for example, the Swiss Federal Office of Topography - swisstopo).  However, it is simple to convert the shapefiles to GeoJSON with the ogr2ogr utility available as a part of GDAL (a translator library for various geospatial data formats).  On Windows, I use OSGeo4W and run org2ogr within that.

Many countries have their own geoid and coordinate systems.  It can be more accurate over a limited region.

• In the UK, the Ordnance Survey uses EPSG 27700 – British National Grid for Great Britain.
• The Republic of Ireland and Northern Ireland both use EPSG 2157 - Irish Transverse Mercator
• In Switzerland, the Federal Office of Topography uses EPSG 2056 (Swiss CH1903+ / LV95) and 5728 (LN02 Height)
• Etc.

I can convert a shapefile to a GeoJSON, simultaneously changing the spatial reference identifier (SRID) to WGS84 (also known as EPSG 4326).

ogr2ogr -f GeoJSON swissBOUNDARIES3D_1_5_TLM_LANDESGEBIET.geojson swissBOUNDARIES3D_1_5_TLM_LANDESGEBIET.shp -t_srs EPSG:4326

Then I can proceed with the physical JSON file as previously described.

Converting GeoJSON to an Oracle Spatial Geometry

GeoJSON is just a JSON document, but it is structured in a particular way.  

• It supports Point, LineString, Polygon, MultiPoint, MultiLineString, and MultiPolygon geometries. 
• Geometric objects with additional properties are Feature objects. 
• Sets of features are contained by FeatureCollection objects.
• The specific fields in properties can vary.  I usually create a PL/SQL script specific to each GeoJSON to be loaded.

The sample below is taken from a GeoJSON of French departments.  The properties contain the department code, the name and the region number they are within.  There is no SRID; the data is already in WGS84.

{"type":"FeatureCollection"
,"features":
  [
    {"type":"Feature"
    ,"properties":
      {"code":"01","nom":"Ain","region":"84"}
    ,"geometry":
      {"type":"Polygon"
      ,"coordinates":[[[5.825,45.939],...,[5.825,45.939]]]
      }
    }
,   {"type":"Feature"
    ,"properties":
      {"code":"02","nom":"Aisne","region":"32"}
    ,"geometry":
      {"type":"Polygon"
      ,"coordinates":[[[3.987,49.379],...,[3.987,49.379]]]
      }
    }
…
  ]
}

• See also geojson.org and the GeoJSON Specification (RFC 7946)

This PL/SQL parses the GeoJSON, extracting the descriptive data.  The geometry data is converted with Oracle's SDO_UTIL.FROM_GEOJSON function.

DECLARE 
  l_clob        CLOB;
  j_root        JSON_OBJECT_T;
  j_crs         JSON_OBJECT_T;
  j_features    JSON_ARRAY_T;
  j_feature     JSON_OBJECT_T;
  j_properties  JSON_OBJECT_T;
  j_geometry    JSON_OBJECT_T;

  l_geom        MDSYS.SDO_GEOMETRY;

  l_id         INTEGER;
  l_srid       VARCHAR2(10 char);
  l_name       VARCHAR2(100 char);
  
  e_json_syntax_error  EXCEPTION;
  PRAGMA exception_init(e_json_syntax_error,-40441);
BEGIN 
  SELECT geo_json INTO l_clob FROM strava.stage_geo_data WHERE name = 'France Regions';
  l_clob := strava_http.clean_clob(l_clob);
  --strava_http.pretty_json(l_clob);
  j_root := JSON_OBJECT_T.parse(l_clob);

  j_crs := j_root.get_object('crs');
	  
  j_features := j_root.get_array('features');
  --l_srid     := REGEXP_SUBSTR(j_crs.get_object('properties').get_string('name'),'[^:]+',1,2);
  l_srid := 4326;
  FOR i IN 0 .. j_features.get_size - 1 LOOP
    j_feature := TREAT(j_features.get(i) AS JSON_OBJECT_T);
    j_properties := j_feature.get_object('properties');
    IF j_properties IS NULL THEN 
      dbms_output.put_line('J_PROPERTIES is null');
      l_id := i;
    ELSE
      l_clob := j_properties.to_clob;
      strava_http.print_clob(l_clob); --use this to understand new GeoJSON file properties, but comment it out later
      l_id               := j_properties.get_number('code');
      l_name             := j_properties.get_string('nom');
      dbms_output.put_line(l_id||', '||l_srid||', '||l_name);
    END IF;
	
    j_geometry := j_feature.get_object('geometry');
    IF j_geometry IS NULL THEN
      dbms_output.put_line(l_name||': j_geometry is null');
      l_geom := NULL;
    ELSE
      l_clob := j_geometry.to_clob /*Get coordinates array*/; 
      
      BEGIN
        l_geom := sdo_util.from_geojson(l_clob);
      EXCEPTION 
        WHEN e_json_syntax_error THEN
          dbms_output.put_line(sqlerrm||' during parse of '||l_name||' ('||l_id||'). Switch to own function.' );
          l_geom := strava_sdo.build_sdo_geometry_from_geojson(j_geometry, l_srid);
      END;

      l_geom.SDO_SRID := TO_NUMBER(l_srid);
      IF l_srid != 4326 THEN
        l_geom := SDO_CS.TRANSFORM(l_geom, 4326);
        l_geom.SDO_SRID := 4326;
      END IF;
      l_geom := sdo_util.rectify_Geometry(SDO_CS.MAKE_2D(l_geom),0.001);
    END IF;
    
    dbms_output.put_line(i||','||l_id||','||l_name);
    INSERT INTO stage_my_areas (area_code, area_number, name, geom)
    VALUES ('REG', l_id, l_name, l_geom);
  END LOOP;
  COMMIT;
END;
/

The resulting spatial geometry is written to a staging table.  From there, I can move it to where I finally want it.

Working Around Errors in SDO_UTIL.FROM_GEOJSON

The SDO_UTIL.FROM_GEOJSON function converts a GeoJSON object (or more specifically, a geometry object in GeoJSON format) to a Spatial geometry object.  It was introduced in Oracle 12.2.  However, even in Oracle 26, I have occasionally experienced errors with this function with certain data sets. 

• ORA-40441: JSON syntax error when using the optional FORMAT JSON clause

I am working with publicly available data sets.  Therefore, I will not easily get any problem resolved, even if I could identify the exact cause.  My workaround has been to use my own function  (build_sdo_geometry_from_geojson).  It loads the array of coordinates from the JSON and passes it to Oracle's sdo_geometry constructor to create the geometry. Though I have found that my function always succeeds when from_geojson fails, my function is much slower.  Therefore, I only use it in an exception handler when I get an error from Oracle's function.

For example, I experienced ORA-40441 when loading data for Co. Carlow from the Irish government's Open Data Unit (I don't know why), but not for any of the other 25 counties.

REM ireland_counties_load.sql 
…
DECLARE 
…
  e_json_syntax_error  EXCEPTION;
  PRAGMA exception_init(e_json_syntax_error,-40441);
BEGIN 
…
  BEGIN
    l_clob := j_geometry.to_clob /*Get coordinates array*/; 
…
    l_geom := sdo_util.from_geojson(l_clob);
  EXCEPTION 
    WHEN e_json_syntax_error THEN
      dbms_output.put_line(sqlerrm||' during parse of '||l_name||' ('||l_id||'). Switch to own function.');
      l_geom := strava_sdo.build_sdo_geometry_from_geojson(j_geometry, l_srid);
  END;
…
END;
/

In the log below, you can see that the error was reported, but then the alternative function was successful.

{"OBJECTID":16,"CO_ID":"10000","ENGLISH":"CARLOW","GAEILGE":"Ceatharlach","LOGAINM_ID":"100004","GUID":"2ae19629-143d-13a3-e055-000000000001"
,"CONTAE":"Ceatharlach","COUNTY":"CARLOW","PROVINCE":"Leinster","CENTROID_X":680448.23,"CENTROID_Y":660624.58,"AREA":896306186.01}
10000, 2157, Carlow, Co. Carlow, Province:Leinster, Centre:-6.80998032929769,52.690783228944
ORA-40441: JSON syntax error during parse of Carlow. Switch to own function.
ID:10000, Province:Leinster, County:Carlow, Council:Carlow, Centre:-6.82336146940769,52.7293094021289, 14445 points

• Further reading: Tim Hall: GeoJSON Data Support in Oracle Database 19c