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 geomHowever, 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:4326Then 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.
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
@go-faster.co.uk on BlueSky
Posts
