Wednesday, June 9, 2021

GeoJSON Data Support in Oracle Database 19c

Oracle Database 19c, Oracle Database Exam Prep, Oracle Database Preparation, Oracle Database Certification, Oracle Database Career

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

Oracle Database 19c, Oracle Database Exam Prep, Oracle Database Preparation, Oracle Database Certification, Oracle Database Career
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

We don't see a GeoJSON type there, but we do if we display the data guide using the JSON_DATAGUIDE function, but not when using the DBMS_JSON.GET_INDEX_DATAGUIDE function.

SET LINESIZE 100 FEEDBACK ON PAGESIZE 1000 LONG 1000000

SELECT JSON_DATAGUIDE(data, dbms_json.format_hierarchical, DBMS_JSON.geojson + DBMS_JSON.pretty) dg_doc
FROM   json_documents;

DG_DOC
--------------------------------------------------------------------------------
{
  "type" : "object",
  "properties" :
  {
    "type" :
    {
      "type" : "string",
      "o:length" : 32,
      "o:preferred_column_name" : "type"
    },
    "features" :
    {
      "type" : "array",
      "o:length" : 1024,
      "o:preferred_column_name" : "features",
      "items" :
      {
        "properties" :
        {
          "type" :
          {
            "type" : "string",
            "o:length" : 8,
            "o:preferred_column_name" : "type"
          },
          "geometry" :
          {
            "type" : "GeoJSON",
            "o:length" : 64,
            "o:preferred_column_name" : "geometry"
          },
          "properties" :
          {
            "type" : "object",
            "o:length" : 128,
            "o:preferred_column_name" : "properties",
            "properties" :
            {
              "name" :
              {
                "type" : "string",
                "o:length" : 16,
                "o:preferred_column_name" : "name"
              },
              "marker-size" :
              {
                "type" : "string",
                "o:length" : 8,
                "o:preferred_column_name" : "marker-size"
              },
              "marker-color" :
              {
                "type" : "string",
                "o:length" : 8,
                "o:preferred_column_name" : "marker-color"
              },
              "marker-symbol" :
              {
                "type" : "string",
                "o:length" : 1,
                "o:preferred_column_name" : "marker-symbol"
              }
            }
          }
        }
      }
    }
  }
}


1 row selected.

SQL>

We can't use the data guide directly as more than one element has the preferred name of "type". If we are not using GeoJSON this works as they will be named "DATA$type", "DATA$type_1" and "DATA$type_2". If we use use the GeoJSON format this results in the "ORA-00918: column ambiguously defined" error, so we have to make sure we rename the columns. At first thought this seems easy as we have the RENAME_COLUMN procedure in the DBMS_JSON package. We might rename all the columns as follows, making sure the three columns with the name "type" are renamed.

BEGIN
  DBMS_JSON.rename_column('json_documents', 'data', '$.type', DBMS_JSON.TYPE_STRING, 'FEATURE_COLLECTION_TYPE');
  DBMS_JSON.rename_column('json_documents', 'data', '$.features.geometry.type', DBMS_JSON.TYPE_STRING, 'FEATURE_GEOMETRY_TYPE');
  DBMS_JSON.rename_column('json_documents', 'data', '$.features.type', DBMS_JSON.TYPE_STRING, 'FEATURE_TYPE');
  DBMS_JSON.rename_column('json_documents', 'data', '$.features.geometry', DBMS_JSON.TYPE_STRING, 'GEOMETRY');
  DBMS_JSON.rename_column('json_documents', 'data', '$.features.properties."marker-size"', DBMS_JSON.TYPE_STRING, 'MARKER_SIZE');
  DBMS_JSON.rename_column('json_documents', 'data', '$.features.properties."marker-color"', DBMS_JSON.TYPE_STRING, 'MARKER_COLOR');
  DBMS_JSON.rename_column('json_documents', 'data', '$.features.properties."marker-symbol"', DBMS_JSON.TYPE_STRING, 'MARKER_SYMBOL');
  DBMS_JSON.rename_column('json_documents', 'data', '$.features.properties.name', DBMS_JSON.TYPE_STRING, 'NAME');
  DBMS_JSON.rename_column('json_documents', 'data', '$.features.geometry.coordinates[*]', DBMS_JSON.TYPE_NUMBER, 'ARRAY_NUMBER');
END;
/
If we create the view without the GET_INDEX_DATAGUIDE procedure we can see the columns names have been picked up correctly, but we don't get a SDO_GEOMETRY column.

DROP VIEW json_documents_v1;

DECLARE
  l_clob CLOB;
BEGIN
  l_clob := DBMS_JSON.get_index_dataguide('json_documents', 'data', DBMS_JSON.format_hierarchical, DBMS_JSON.pretty);
  
  DBMS_JSON.create_view(
    viewname  => 'json_documents_v1',
    tablename => 'json_documents',
    jcolname  => 'data',
    dataguide => l_clob);
END;
/

DESC json_documents_v1

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 

If we use the JSON_DATAGUIDE function we still get the "ORA-00918: column ambiguously defined" error, as JSON_DATAGUIDE function doesn't recognise the column renames.

DROP VIEW json_documents_v1;

DECLARE
  l_clob CLOB;
BEGIN
  SELECT JSON_DATAGUIDE(data, dbms_json.format_hierarchical, DBMS_JSON.geojson + DBMS_JSON.pretty)
  INTO   l_clob
  FROM   json_documents;
  
  DBMS_JSON.create_view(
    viewname  => 'json_documents_v1',
    tablename => 'json_documents',
    jcolname  => 'data',
    dataguide => l_clob);
END;
/
DECLARE
*
ERROR at line 1:
ORA-00918: column ambiguously defined
ORA-06512: at "XDB.DBMS_JSON", line 597
ORA-06512: at "XDB.DBMS_JSON", line 1056
ORA-06512: at line 8

SQL>

We can manually "fix" this by generating the data guide, then manually adding the correct preferred names.

DROP VIEW json_documents_v1;

DECLARE
  l_clob CLOB;
BEGIN
  SELECT JSON_DATAGUIDE(data, dbms_json.format_hierarchical, DBMS_JSON.geojson + DBMS_JSON.pretty)
  INTO   l_clob
  FROM   json_documents;

  l_clob := REGEXP_REPLACE(l_clob, '"o:preferred_column_name" : "type"', '"o:preferred_column_name" : "FEATURE_COLLECTION_TYPE"', 1, 1);
  l_clob := REGEXP_REPLACE(l_clob, '"o:preferred_column_name" : "type"', '"o:preferred_column_name" : "FEATURE_TYPE"', 1, 1);
  l_clob := REGEXP_REPLACE(l_clob, '"o:preferred_column_name" : "geometry"', '"o:preferred_column_name" : "GEOMETRY"', 1, 1);
  l_clob := REGEXP_REPLACE(l_clob, '"o:preferred_column_name" : "name"', '"o:preferred_column_name" : "NAME"', 1, 1);
  l_clob := REGEXP_REPLACE(l_clob, '"o:preferred_column_name" : "marker-size"', '"o:preferred_column_name" : "MARKER_SIZE"', 1, 1);
  l_clob := REGEXP_REPLACE(l_clob, '"o:preferred_column_name" : "marker-color"', '"o:preferred_column_name" : "MARKER_COLOR"', 1, 1);
  l_clob := REGEXP_REPLACE(l_clob, '"o:preferred_column_name" : "marker-symbol"', '"o:preferred_column_name" : "MARKER_SYMBOL"', 1, 1);

  DBMS_JSON.create_view(
    viewname  => 'json_documents_v1',
    tablename => 'json_documents',
    jcolname  => 'data',
    dataguide => l_clob);
END;
/

DESC json_documents_v1

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) 

SQL>

Now we have the column names we were expecting and we can see the SDO_GEOMETRY data has been recognised. We can query from it like any other SDO_GEOMETRY type column.

SELECT geometry FROM json_documents_v1;

GEOMETRY(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>

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

Related Posts

0 comments:

Post a Comment