GeoJSON Without Conversion In Oracle Spatial 23c

In Oracle 23c you can store and query GeoJSON directly without conversion between JSON/GeoJSON and SDO_GEOMETRY.

Oracle 23c also ensures better compatibility with GIS applications like PostGIS, ArcGIS, QGIS and web-based mapping tools.

In the previous version,you should manually convert GeoJSON to SDO_GEOMETRY for inserting, and when you had a query required extracting SDO_GEOMETRY and manually converting it back to GeoJSON.

For example in Oracle 19c :

📙For Insert GeoJSON Data, you should First Convert it to SDO_GEOMETRY 

INSERT INTO My_Geospatial_Table (id, geom)

VALUES (1,SDO_GEOMETRY(2001, 4326,

SDO_POINT_TYPE(-122.4194, 37.7749, NULL),

NULL, NULL));

📘 And when you Query Data you should Convert it Back to GeoJSON  

SELECT

'{"type": "Point", "coordinates": [' ||

geom.sdo_point.x || ',' || geom.sdo_point.y || ']}' AS geojson

FROM My_Geospatial_Table ;

In Oracle 23c :

CREATE TABLE My_Geospatial_Table (

  id NUMBER PRIMARY KEY,

  geojson_data JSON

);

📙For Insert GeoJSON Data, you do not need to Convert it to SDO_GEOMETRY 

INSERT INTO My_Geospatial_Table (id, geojson_data)

VALUES (

  1, '{"type": "Point", "coordinates": [-122.4194, 37.7749]}'

); 

📘 And you Query Without Conversion

SELECT geojson_data FROM My_Geospatial_Table  WHERE id = 1;

FEUTURE	19c or Earlier	23c
Store GeoJSON Directly?	❌	✅😃
Query Without Conversion?	❌	✅😃
Use JSON Functions for Spatial Queries?	❌	✅😃
Native Indexing on GeoJSON?	❌	✅😃

📌 Indexing  on GeoJSON fields

Oracle 23c fully supports spatial indexing on JSON and GeoJSON fields. 

* This makes spatial queries much faster *

CREATE SEARCH INDEX geojson_spatial_idx ON geojson_table(geojson_data)
FOR JSON SPATIAL;

📌 SQL/JSON Functions to Extract Coordinates

SELECT 
  JSON_VALUE(geojson_data, '$.coordinates[0]') AS longitude,
  JSON_VALUE(geojson_data, '$.coordinates[1]') AS latitude
FROM geojson_table;

📌 SQL/JSON Functions to Find Points Within a Certain Distance

SELECT id, geojson_data
FROM geojson_table
WHERE SDO_WITHIN_DISTANCE(
  JSON_VALUE(geojson_data, '$.coordinates'),
  'POINT(-121 36)',
  'distance=500 unit=km'  
) = 'TRUE';

by this code you can find all points within 500 km of (-121, 36)

📌 SQL/JSON Functions to Find the Nearest Point to a Given Location

 SELECT id, geojson_data

FROM geojson_table ORDER BY SDO_NN( JSON_VALUE(geojson_data, '$.coordinates'), 'POINT(-121 36)' ) FETCH FIRST 1 ROW ONLY;