Example 25-1 A Table With GeoJSON Data

This example creates table j_geo, which has a column, geo_doc of GeoJSON documents.

Only one such document is inserted here. It contains a GeoJSON object of type FeatureCollection, and a features array of objects of type Feature. Those objects have geometry, respectively, of type Point, LineString, and Polygon.

CREATE TABLE j_geo
  (id      VARCHAR2 (32) NOT NULL,
   geo_doc VARCHAR2 (4000) CHECK (geo_doc is json));

INSERT INTO j_geo
  VALUES (1,
          '{"type"     : "FeatureCollection",
            "features" : [{"type"       : "Feature",
                           "geometry"   : {"type" : "Point",
                                           "coordinates" : [-122.236111, 37.482778]},
                           "properties" : {"Name" : "Redwood City"}},
                          {"type"       : "Feature",
                           "geometry"   : {"type" : "LineString",
                                           "coordinates" : [[102.0, 0.0],
                                                            [103.0, 1.0],
                                                            [104.0, 0.0],
                                                            [105.0, 1.0]]},
                           "properties" : {"prop0" : "value0",
                                           "prop1" : 0.0}},
                          {"type"       : "Feature",
                           "geometry"   : {"type" : "Polygon",
                                           "coordinates" : [[[100.0, 0.0],
                                                             [101.0, 0.0],
                                                             [101.0, 1.0],
                                                             [100.0, 1.0],
                                                             [100.0, 0.0]]]},
                           "properties" : {"prop0" : "value0",
                                           "prop1" : {"this" : "that"}}}]}');

Example 25-2 Selecting a geometry Object From a GeoJSON Feature As an SDO_GEOMETRY Instance

This example uses SQL/JSON function json_value to select the value of field geometry from the first element of array features. The value is returned as Oracle Spatial and Graph data, not as JSON data, that is, as an instance of PL/SQL object type SDO_GEOMETRY, not as a SQL string or LOB instance.

SELECT json_value(geo_doc, '$.features[0].geometry'
                  RETURNING SDO_GEOMETRY 
                  ERROR ON ERROR)
  FROM j_geo;

The value returned is this, which represents a point with longitude and latitude (coordinates) -122.236111 and 37.482778, respectively.

SDO_GEOMETRY(2001,
             4326,
             SDO_POINT_TYPE(-122.236111, 37.482778, NULL),
             NULL,
             NULL)

Example 25-3 Retrieving Multiple geometry Objects From a GeoJSON Feature As SDO_GEOMETRY

This example uses SQL/JSON function json_table to project the value of field geometry from each element of array features, as column sdo_val of a virtual table. The retrieved data is returned as SDO_GEOMETRY.

SELECT jt.*
  FROM j_geo,
       json_table(geo_doc, '$.features[*]'
         COLUMNS (sdo_val SDO_GEOMETRY PATH '$.geometry')) jt;

The following three rows are returned for the query. The first represents the same Point as in Example 25-2. The second represents the LineString array. The third represents the Polygon.

SDO_GEOMETRY(2001,
             4326,
             SDO_POINT_TYPE(-122.236111, 37.482778, NULL),
             NULL,
             NULL)

SDO_GEOMETRY(2002,
             4326,
             NULL,
             SDO_ELEM_INFO_ARRAY(1, 2, 1),
             SDO_ORDINATE_ARRAY(102, 0, 103, 1, 104, 0, 105, 1))

SDO_GEOMETRY(2003,
             4326,
             NULL,
             SDO_ELEM_INFO_ARRAY(1, 1003, 1),
             SDO_ORDINATE_ARRAY(100, 0, 101, 0, 101, 1, 100, 1, 100, 0))

The second and third elements of attribute SDO_ELEM_INFO_ARRAY specify how to interpret the coordinates provided by attribute SDO_ORDINATE_ARRAY. They show that the first row returned represents a line string (2) with straight segments (1), and the second row represents a polygon (2003) of straight segments (1).

Example 25-4 Creating a Spatial Index For Scalar GeoJSON Data

This example creates a json_value function-based index of type MDSYS.SPATIAL_INDEX on field geometry of the first element of array features. This can improve the performance of queries that use json_value to retrieve that value.

CREATE INDEX geo_first_feature_idx
  ON j_geo (json_value(geo_doc, '$.features[0].geometry'
                       RETURNING SDO_GEOMETRY))
  INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Example 25-5 Using GeoJSON Geometry With Spatial Operators

This example selects the documents (there is only one in this table) for which the geometry field of the first features element is within 100 kilometers of a given point. The point is provided literally here (its coordinates are the longitude and latitude of San Francisco, California). The distance is computed from this point to each geometry object.

The query orders the selected documents by the calculated distance. The tolerance in meters for the distance calculation is provided in this query as the literal argument 100.

SELECT id,
       json_value(geo_doc, '$.features[0].properties.Name') "Name",
       SDO_GEOM.sdo_distance(
         json_value(geo_doc, '$.features[0].geometry'
                    RETURNING SDO_GEOMETRY),
         SDO_GEOMETRY(2001,
                      4326,
                      SDO_POINT_TYPE(-122.416667, 37.783333, NULL),
                      NULL,
                      NULL),
         100, -- Tolerance in meters
         'unit=KM') "Distance in kilometers"
  FROM  j_geo
  WHERE sdo_within_distance(
          json_value(geo_doc, '$.features[0].geometry'
                     RETURNING SDO_GEOMETRY),
          SDO_GEOMETRY(2001,
                       4326,
                       SDO_POINT_TYPE(-122.416667, 37.783333, NULL),
                       NULL,
                       NULL),
          'distance=100 unit=KM')
        = 'TRUE';

The query returns a single row:

ID    Name           Distance in kilometers
----- -------------- ----------------------
1     Redwood City   26.9443035

Example 25-6 Creating a Materialized View Over GeoJSON Data

CREATE OR REPLACE MATERIALIZED VIEW geo_doc_view
  BUILD IMMEDIATE
  REFRESH FAST ON STATEMENT WITH ROWID
  AS SELECT g.rowid, jt.*
       FROM j_geo g,
            json_table(geo_doc, '$.features[*]'
              COLUMNS (sdo_val SDO_GEOMETRY PATH '$.geometry')) jt;

Example 25-7 Creating a Spatial Index on a Materialized View Over GeoJSON Data

This example first prepares for the creation of the spatial index by populating some spatial-indexing metadata. It then creates the index on the SDO_GEOMETRY column, sdo_val, of materialized view geo_doc_view, which is created in Example 25-6. Except for the view and column names, the code for populating the indexing metadata is fixed — use it each time you need to create a spatial index on a materialized view.

-- Populate spatial-indexing metadata

INSERT INTO USER_SDO_GEOM_METADATA
  VALUES ('GEO_DOC_VIEW',
          'SDO_VAL',
          MDSYS.sdo_dim_array(
            MDSYS.sdo_dim_element('Longitude', -180, 180, 0.05),
            MDSYS.sdo_dim_element('Latitude', -90, 90, 0.05)),
          7
          4326);

-- Create spatial index on geometry column of materialized view

CREATE INDEX geo_all_features_idx ON geo_doc_view(sdo_val)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX V2;