ORACLE APEX: Understanding the JSON Structure in "GEOMETRY"

Introduction
When working with spatial data in Oracle APEX, especially through RESTful Web Services, map components, or external GIS integrations, you may encounter the geometry JSON object. This structure holds spatial coordinate information—points, lines, polygons—used for rendering data on maps. Understanding the geometry JSON format is essential for parsing, displaying, and interacting with geospatial data within APEX applications.

Understanding the JSON Structure in "GEOMETRY"

In most spatial data standards (e.g., GeoJSON or Esri JSON), the geometry object contains spatial information that defines the shape and location of geographic features. A typical structure may look like:

{
  "geometry": {
    "type": "Point",
    "coordinates": [102.0, 0.5]
  }
}

Or for a polygon:

{
  "geometry": {
    "type": "Polygon",
    "coordinates": [
      [
        [30, 10], [40, 40], [20, 40], [10, 20], [30, 10]
      ]
    ]
  }
}

How to Work with This in Oracle APEX

1. Consume JSON with Geometry

   • When consuming RESTful Web Services that return geometry data, configure a Web Source Module.

   • Map the geometry field as a CLOB or JSON column in your report region.

2. Parse JSON Data in APEX

   • Use APEX_JSON or JSON_TABLE in SQL queries or PL/SQL to extract coordinates.
     Example:

   SELECT jt.type, jt.longitude, jt.latitude
     FROM your_table t,
          JSON_TABLE(t.geometry, '$'
            COLUMNS (
              type VARCHAR2(20) PATH '$.type',
              longitude NUMBER PATH '$.coordinates[0]',
              latitude NUMBER PATH '$.coordinates[1]'
            )
          ) jt;
   

3. Display on a Map Region

   • Create a Map region in your APEX page.

   • Set the data source to your SQL query.

   • Ensure that the longitude and latitude columns are mapped properly.

   • Choose geometry types like Point, LineString, or Polygon in the region’s settings.

4. Storing Geometry in the Database

   • You can also store spatial data using Oracle Spatial (SDO_GEOMETRY).

   • Convert JSON geometry to SDO_GEOMETRY using PL/SQL for advanced spatial operations.

Best Practices

• Normalize the incoming geometry JSON for performance and ease of parsing.

• Store raw geometry JSON as CLOB only when necessary—prefer structured columns.

• Use Oracle Spatial for high-performance spatial queries and rendering.

• If integrating external map services, match geometry type standards (WKT, GeoJSON).

• Secure and validate incoming JSON data to prevent malformed geometry inputs.

Oracle APEX Documentation

• Using Map Components

• APEX_JSON Reference

• Oracle Spatial and Graph

 To display JSON data stored in the "GEOMETRY" column of your USCG_DATA table in Oracle APEX, you'll need to parse the JSON stored in this CLOB column and extract the relevant data for display in a readable format.

Assuming the JSON stored in the "GEOMETRY" column is a well-structured JSON object, you can use SQL queries and the Oracle SQL functions like JSON_VALUE, JSON_QUERY, or APEX_JSON to extract values from the JSON structure.

To display the JSON data from the "GEOMETRY" column in your USCG_DATA table in Oracle APEX, you can:

• Use SQL functions like JSON_VALUE or JSON_QUERY to extract values from the JSON.

• Display the extracted values in APEX components such as Interactive Reports, Forms, or Interactive Grids.

• Use PL/SQL to perform more complex processing if necessary.

Below is a step-by-step guide on how to display and extract JSON data from the "GEOMETRY" column in your APEX application.

 

Before you proceed, inspect the JSON structure in the "GEOMETRY" column. You need to understand the structure to know what specific data you want to extract. For example, if the "GEOMETRY" column stores something like:

{

  "type": "Point",

  "coordinates": [ -118.291, 34.056 ]

}

You can extract the "type" and "coordinates" values.

Conclusion
Understanding the structure and usage of geometry JSON in Oracle APEX unlocks the ability to build spatially-aware applications. From integrating with map regions to parsing location data and rendering polygons or routes, APEX provides both declarative and programmatic tools to handle this complex data format. Mastering the geometry object allows you to visualize and analyze spatial data more effectively in your Oracle APEX solutions.