In this article we demonstrate the support for GeoJSON data in Oracle Database 19c. Specifically the mapping between GeoJSON data and the Oracle Spatial SDO_GEOMETRY object type.
1. Setup
If we checkout the geojson.org site we see the following example of GeoJSON data.
Read More: 1Z0-067: Upgrade Oracle 9i/10g/11g OCA to Oracle Database 12c OCP
{
"type": "Feature",
"geometry": {
"type": "Point",
"coordinates": [125.6, 10.1]
},
"properties": {
"name": "Dinagat Islands"
}
}
We built some GeoJSON using the http://geojson.io map. The result was the following GeoJSON document.
{
"type": "FeatureCollection",
"features": [
{
"type": "Feature",
"properties": {
"marker-color": "#7e7e7e",
"marker-size": "medium",
"marker-symbol": "",
"name": "London"
},
"geometry": {
"type": "Point",
"coordinates": [
-0.15380859375,
51.50532341149335
]
}
},
{
"type": "Feature",
"properties": {
"marker-color": "#7e7e7e",
"marker-size": "medium",
"marker-symbol": "",
"name": "Birmingham"
},
"geometry": {
"type": "Point",
"coordinates": [
-1.8896484375,
52.466050361889515
]
}
},
{
"type": "Feature",
"properties": {
"marker-color": "#7e7e7e",
"marker-size": "medium",
"marker-symbol": "",
"name": "Dublin"
},
"geometry": {
"type": "Point",
"coordinates": [
-6.273193359375,
53.35710874569601
]
}
}
]
}
Let's store that as JSON data in a table.
-- DROP TABLE json_documents PURGE;
CREATE TABLE json_documents (
id RAW(16) NOT NULL,
data CLOB,
CONSTRAINT json_documents_pk PRIMARY KEY (id),
CONSTRAINT json_documents_json_chk CHECK (data IS JSON)
);
INSERT INTO json_documents (id, data)
VALUES (SYS_GUID(),
'{
"type": "FeatureCollection",
"features": [
{
"type": "Feature",
"properties": {
"marker-color": "#7e7e7e",
"marker-size": "medium",
"marker-symbol": "",
"name": "London"
},
"geometry": {
"type": "Point",
"coordinates": [
-0.15380859375,
51.50532341149335
]
}
},
{
"type": "Feature",
"properties": {
"marker-color": "#7e7e7e",
"marker-size": "medium",
"marker-symbol": "",
"name": "Birmingham"
},
"geometry": {
"type": "Point",
"coordinates": [
-1.8896484375,
52.466050361889515
]
}
},
{
"type": "Feature",
"properties": {
"marker-color": "#7e7e7e",
"marker-size": "medium",
"marker-symbol": "",
"name": "Dublin"
},
"geometry": {
"type": "Point",
"coordinates": [
-6.273193359375,
53.35710874569601
]
}
}
]
}');
COMMIT;
2. JSON_VALUE
The JSON_VALUE function can instantiate a SDO_GEOMETRY object type based on GeoJSON data. We can pull out individual rows from a collection using the JSON_VALUE function. In the following example we return the first item of the collection.
SET LINESIZE 100 FEEDBACK ON
SELECT JSON_VALUE(data, '$.features[0].geometry'
RETURNING SDO_GEOMETRY
ERROR ON ERROR)
FROM json_documents;
JSON_VALUE(DATA,'$.FEATURES[0].GEOMETRY'RETURNINGSDO_GEOMETRYERRORONERROR)(SDO_GTYPE, SDO_SRID, SDO_
----------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-.15380859, 51.5053234, NULL), NULL, NULL)
1 row selected.
SQL>
3. JSON_TABLE and the SQL NESTED Clause
The JSON_TABLE function can also instantiate a SDO_GEOMETRY object type based on GeoJSON data, but it can process the whole array. The example below returns SDO_GEOMETRY instances for all the points in the collection.SET LINESIZE 100 FEEDBACK ON
SELECT jt.*
FROM json_documents,
JSON_TABLE(data, '$.features[*]'
COLUMNS (sdo_val SDO_GEOMETRY PATH '$.geometry')
) jt;
SDO_VAL(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-.15380859, 51.5053234, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-1.8896484, 52.4660504, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-6.2731934, 53.3571087, NULL), NULL, NULL)
3 rows selected.
SQL>
The SQL NESTED clause, introduced in Oracle 19c, works in a similar way.
SET LINESIZE 100 FEEDBACK ON
SELECT sdo_val
FROM json_documents NESTED data.features[*]
COLUMNS (sdo_val SDO_GEOMETRY PATH '$.geometry');
SDO_VAL(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-.15380859, 51.5053234, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-1.8896484, 52.4660504, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-6.2731934, 53.3571087, NULL), NULL, NULL)
3 rows selected.
SQL>
4. Indexing GeoJSON Data
You can create spacial indexes on the GeoJSON data using the JSON_VALUE function call.
CREATE INDEX json_documents_geo_idx
ON json_documents (JSON_VALUE(data, '$.features[0].geometry'
RETURNING SDO_GEOMETRY))
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
5. JSON_OBJECT
Create a test table to hold SDO_GEOMETRY data and populate it using the SQL NESTED clause described earlier.
-- DROP TABLE sdo_data PURGE;
CREATE TABLE sdo_data (
id RAW(16) NOT NULL,
name VARCHAR2(50),
data SDO_GEOMETRY,
CONSTRAINT sdo_data_pk PRIMARY KEY (id)
);
INSERT INTO sdo_data (id, name, data)
SELECT SYS_GUID(), jt.name, jt.sdo_val
FROM json_documents j NESTED data.features[*]
COLUMNS (name VARCHAR2(50) PATH '$.properties.name',
sdo_val SDO_GEOMETRY PATH '$.geometry') jt;
COMMIT;
We could have created the data using the JSON_TABLE syntax, but we didn't.
INSERT INTO sdo_data (id, name, data)
SELECT SYS_GUID(), jt.name, jt.sdo_val
FROM json_documents j,
JSON_TABLE(j.data, '$.features[*]'
COLUMNS (name VARCHAR2(50) PATH '$.properties.name',
sdo_val SDO_GEOMETRY PATH '$.geometry')
) jt;
The JSON_OBJECT function returns the spatial information from the DATA column. In the following example the JSON_SERIALIZE function has been used to pretty-print the output to make it easier to read.
SET LINESIZE 100 FEEDBACK ON PAGESIZE 1000
SELECT JSON_SERIALIZE(JSON_OBJECT(data) PRETTY)
FROM sdo_data;
JSON_SERIALIZE(JSON_OBJECT(DATA)PRETTY)
----------------------------------------------------------------------------------------------------
{
"SDO_GTYPE" : 2001,
"SDO_SRID" : 4326,
"SDO_POINT" :
{
"X" : -0.15380859375,
"Y" : 51.5053234114934,
"Z" : null
},
"SDO_ELEM_INFO" :
[
],
"SDO_ORDINATES" :
[
]
}
{
"SDO_GTYPE" : 2001,
"SDO_SRID" : 4326,
"SDO_POINT" :
{
"X" : -1.8896484375,
"Y" : 52.4660503618895,
"Z" : null
},
"SDO_ELEM_INFO" :
[
],
"SDO_ORDINATES" :
[
]
}
{
"SDO_GTYPE" : 2001,
"SDO_SRID" : 4326,
"SDO_POINT" :
{
"X" : -6.273193359375,
"Y" : 53.357108745696,
"Z" : null
},
"SDO_ELEM_INFO" :
[
],
"SDO_ORDINATES" :
[
]
}
3 rows selected.
SQL>
We can see it's not displayed as GeoJSON, but we could do this conversion should be need to.
6. JSON Data Guide
Create a JSON search index on the JSON_DOCUMENTS table, which will also create a JSON Data Guide for the data in the column.
CREATE SEARCH INDEX json_docs_search_idx ON json_documents (data) FOR JSON;
Check the contents of the JSON Data Guide.
COLUMN path FORMAT A40
COLUMN type FORMAT A10
SELECT path,
type,
length
FROM user_json_dataguide_fields
WHERE table_name = 'JSON_DOCUMENTS'
AND column_name = 'DATA'
ORDER BY 1;
PATH | TYPE | LENGTH |
$.features | array | 1024 |
$.features.geometry | object | 128 |
$.features.geometry.coordinates | array | 64 |
$.features.geometry.coordinates[*] | number | 32 |
$.features.geometry.type | string | 8 |
$.features.properties | object | 128 |
$.features.properties."marker-color" | string | 8 |
$.features.properties."marker-size" | string | 8 |
$.features.properties."marker-symbol" | string | 1 |
$.features.properties.name | string | 16 |
$.features.type | string | 8 |
$.type | string | 32 |
Name | Null? | Type |
ID | NOT NULL | RAW(16) |
FEATURE_COLLECTION_TYPE | VARCHAR2(32) | |
FEATURE_TYPE | VARCHAR2(8) | |
FEATURE_GEOMETRY_TYPE | VARCHAR2(8) | |
NAME | VARCHAR2(16) | |
MARKER_SIZE | VARCHAR2(8) | |
MARKER_COLOR | VARCHAR2(8) | |
MARKER_SYMBOL | VARCHAR2(1) | |
ARRAY_NUMBER | NUMBER |
Name | Null? | Type |
ID | NOT NULL | RAW(16) |
FEATURE_COLLECTION_TYPE | VARCHAR2(32) | |
FEATURE_TYPE | VARCHAR2(8) | |
GEOMETRY | MDSYS.SDO_GEOMETRY | |
NAME | VARCHAR2(16) | |
MARKER_SIZE | VARCHAR2(8) | |
MARKER_COLOR | VARCHAR2(8) | |
MARKER_SYMBOL | VARCHAR2(1) |
7. SDO_UTIL Package
From Oracle 12.2 onward, the SDO_UTIL package has included some functions that could be used to produce a similar result to what we've seen previously.
◉ SDO_UTIL.TO_GEOJSON (12.2)
◉ SDO_UTIL.FROM_GEOJSON (12.2)
◉ SDO_UTIL.TO_JSON (18c)
◉ SDO_UTIL.TO_JSON_VARCHAR (18c)
◉ SDO_UTIL.FROM_JSON (18c)
If you created the SDO_DATA table from a previous example, you can see we have SDO_GEOMETRY data to test with.
SELECT data
FROM sdo_data;
DATA(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-.15380859, 51.5053234, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-1.8896484, 52.4660504, NULL), NULL, NULL)
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-6.2731934, 53.3571087, NULL), NULL, NULL)
3 rows selected.
SQL>
We can use the SDO_UTIL package to display this SDO_GEOMETRY data as JSON using the TO_GEOJSON, TO_JSON and TO_JSON_VARCHAR functions.
SELECT SDO_UTIL.to_geojson(data) FROM sdo_data;
SDO_UTIL.TO_GEOJSON(DATA)
--------------------------------------------------------------------------------
{ "type": "Point", "coordinates": [-.15380859375, 51.5053234114934] }
{ "type": "Point", "coordinates": [-1.8896484375, 52.4660503618895] }
{ "type": "Point", "coordinates": [-6.273193359375, 53.357108745696] }
3 rows selected.
SQL>
SELECT SDO_UTIL.to_json(data) FROM sdo_data;
SDO_UTIL.TO_JSON(DATA)
--------------------------------------------------------------------------------
{"srid": 4326, "point": {"directposition": [-0.15380859375, 51.5053234114934]}}
{"srid": 4326, "point": {"directposition": [-1.8896484375, 52.4660503618895]}}
{"srid": 4326, "point": {"directposition": [-6.273193359375, 53.357108745696]}}
3 rows selected.
SQL>
SELECT SDO_UTIL.to_json_varchar(data) FROM sdo_data;
SDO_UTIL.TO_JSON_VARCHAR(DATA)
----------------------------------------------------------------------------------------------------
{"srid": 4326, "point": {"directposition": [-0.15380859375, 51.5053234114934]}}
{"srid": 4326, "point": {"directposition": [-1.8896484375, 52.4660503618895]}}
{"srid": 4326, "point": {"directposition": [-6.273193359375, 53.357108745696]}}
3 rows selected.
SQL>
We can convert GeoJSON to SDO_GEOMETRY using the FROM_GEOJSON and FROM_JSON functions. In the following example we've used some JSON from the previous examples.
SELECT SDO_UTIL.from_geojson('{
"type": "Point",
"coordinates": [
-0.15380859375,
51.50532341149335
]
}') FROM dual;
SDO_UTIL.FROM_GEOJSON('{"TYPE":"POINT","COORDINATES":[-0.15380859375,51.50532341149335]}')(SDO_GTYPE
----------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-.15380859, 51.5053234, NULL), NULL, NULL)
1 row selected.
SQL>
SELECT SDO_UTIL.from_json('{
"type": "Point",
"coordinates": [
-0.15380859375,
51.50532341149335
]
}') FROM dual;
SDO_UTIL.FROM_JSON('{"TYPE":"POINT","COORDINATES":[-0.15380859375,51.50532341149335]}')(SDO_GTYPE, S
----------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-.15380859, 51.5053234, NULL), NULL, NULL)
1 row selected.
SQL>
Source: oracle-base.com
0 comments:
Post a Comment