How Do I Create a Faceted Search Map Report in Oracle APEX Using USCG_DATA

Creating an interactive, location-based application in Oracle APEX is simpler than ever using the built-in Map region and Faceted Search components. When working with spatial or geo-tagged data like USCG_DATA, which includes geographic features in a JSON-based GEOMETRY column, APEX lets you combine powerful filtering with visual map rendering. This allows users to search and explore data by category, date, or region—while immediately seeing results plotted on a map. In this blog, you’ll learn how to build a Faceted Search page tied to a Map region using real coordinates extracted from the USCG_DATA table.Step-by-Step: Creating a Faceted Search Map Report with USCG_DATA

1. Prepare the USCG_DATA Table

   Ensure your USCG_DATA table includes a GEOMETRY column (JSON), along with identifying fields like ID, TYPE, NAME, and DATE_RECORDED. Make sure GEOMETRY contains a type and coordinates array structured like:

   {
     "type": "Point",
     "coordinates": [-87.12345, 29.98765]
   }
   

2. Extract Coordinates in a SQL View or Query
   Use SQL to extract clean longitude and latitude values:

   SELECT
     ID,
     NAME,
     TYPE,
     DATE_RECORDED,
     JSON_VALUE(GEOMETRY, '$.type') AS geometry_type,
     CASE 
       WHEN JSON_VALUE(GEOMETRY, '$.coordinates[0]') IS NOT NULL
            AND REGEXP_LIKE(JSON_VALUE(GEOMETRY, '$.coordinates[0]'), '^-?\d+(\.\d+)?$') 
       THEN TO_NUMBER(JSON_VALUE(GEOMETRY, '$.coordinates[0]')) 
       ELSE NULL 
     END AS longitude,
     CASE 
       WHEN JSON_VALUE(GEOMETRY, '$.coordinates[1]') IS NOT NULL
            AND REGEXP_LIKE(JSON_VALUE(GEOMETRY, '$.coordinates[1]'), '^-?\d+(\.\d+)?$') 
       THEN TO_NUMBER(JSON_VALUE(GEOMETRY, '$.coordinates[1]')) 
       ELSE NULL 
     END AS latitude
   FROM USCG_DATA
   WHERE JSON_VALUE(GEOMETRY, '$.type') = 'Point'
   

3. Create a New Faceted Search Page
   In APEX:

   • Go to App Builder → Create → Page.

   • Select Faceted Search with Map as the main report region.

   • Use the query or view you created above as the source.

   • Assign longitude and latitude to the correct columns in the Map attributes.

   • Set a primary key column (e.g., ID), and optionally set NAME as the label.

4. Configure Facets for Filtering
   Add facets for:

   • TYPE (Checklist facet)

   • DATE_RECORDED (Date range)

   • Any other meaningful metadata in your table (e.g., status or location category)

5. Customize the Map Region

   • Under Map region settings:

     • Choose the map style (e.g., Light or Satellite).

     • Optionally enable clustering if your data is dense.

     • Use NAME as the popup label.

     • Use TYPE as the marker category for color differentiation.

6. Test and Enhance

   • Preview the page and apply filters using facets.

   • Ensure markers appear correctly and facets refresh the map in real time.

   • Optionally link map markers to detail pages or modal dialogs.

Best Practices

• Always validate and convert coordinates safely using REGEXP_LIKE before calling TO_NUMBER.

• Use a view to centralize JSON extraction logic so the APEX page stays clean.

• Enable null checks in your WHERE clause to avoid blank or invalid markers.

• Consider adding a region below the map to list filtered records in tabular form.

• Use meaningful categories in facets—avoid overwhelming users with long picklists.

• Store derived lat/lon columns if performance becomes an issue.

 

A Faceted Search Map Report in Oracle APEX allows users to filter and visualize geographic data on an interactive map using multiple search facets. This tutorial will guide you through the process of setting up a faceted search with a Map Chart report using the USCG_DATA table.

Understanding the USCG_DATA Table

The table contains geospatial data in GeoJSON format within the GEOMETRY column. The key columns for creating a map report are:

• Longitude: Extracted from JSON_VALUE(GEOMETRY, '$.coordinates[0]')

• Latitude: Extracted from JSON_VALUE(GEOMETRY, '$.coordinates[1]')

• Attributes: Various PROPERTIES_* fields, such as PROPERTIES_MAG (magnitude), PROPERTIES_PLACE (location), and PROPERTIES_TIME (timestamp).

This data can be used to display locations on a Map Chart while allowing filtering through Faceted Search.

How Do I Create the Faceted Search Map Report

Step 1: Create a New Page

1. Open Oracle APEX and navigate to your application.

2. Click Create Page → Select Faceted Search.

3. Click Next, choose your table USCG_DATA, and set the Primary Key to ID.

4. Click Next, then Create.

Step 2: Configure the Faceted Search Region

The Faceted Search component will allow filtering the map data based on relevant attributes.

1. Go to the Faceted Search region settings.

2. Add facets for filtering, such as: 

• Magnitude (PROPERTIES_MAG) → Numeric Range

• Location (PROPERTIES_PLACE) → Search Box

• Earthquake Type (PROPERTIES_TYPE) → Select List

• Time (PROPERTIES_TIME) → Date Range Picker

• Tsunami Alert (PROPERTIES_TSUNAMI) → Checkbox

3. Click Save.

Step 3: Create the Map Report

1. Go to Page Designer and add a new Map Chart region.

2. Set Region Source to: 

SELECT 

   ID, 

   JSON_VALUE(GEOMETRY, '$.coordinates[0]') AS LONGITUDE, 

   JSON_VALUE(GEOMETRY, '$.coordinates[1]') AS LATITUDE, 

   PROPERTIES_MAG AS MAGNITUDE, 

   PROPERTIES_PLACE AS PLACE, 

   PROPERTIES_TYPE AS EVENT_TYPE, 

   PROPERTIES_TSUNAMI AS TSUNAMI_ALERT

FROM USCG_DATA

WHERE (:P1_MAG_MIN IS NULL OR PROPERTIES_MAG >= :P1_MAG_MIN)

  AND (:P1_MAG_MAX IS NULL OR PROPERTIES_MAG <= :P1_MAG_MAX)

  AND (:P1_PLACE IS NULL OR LOWER(PROPERTIES_PLACE) LIKE LOWER('%' || :P1_PLACE || '%'))

  AND (:P1_EVENT_TYPE IS NULL OR PROPERTIES_TYPE = :P1_EVENT_TYPE)

  AND (:P1_TSUNAMI_ALERT IS NULL OR PROPERTIES_TSUNAMI = :P1_TSUNAMI_ALERT)

3. Under Attributes, set: 

• Longitude Column: LONGITUDE

• Latitude Column: LATITUDE

• Title Column: PLACE

• Popup Info: 

<b>Location:</b> &P1_PLACE.<br>

<b>Magnitude:</b> &P1_MAGNITUDE.<br>

<b>Event Type:</b> &P1_EVENT_TYPE.<br>

<b>Tsunami Alert:</b> &P1_TSUNAMI_ALERT.

• Layer Type: Point

Step 4: Connect Facets to the Map Report

1. Navigate to Faceted Search > Filters.

2. Assign facet items to the report’s query parameters: 

• Magnitude → P1_MAG_MIN and P1_MAG_MAX

• Location → P1_PLACE

• Event Type → P1_EVENT_TYPE

• Tsunami Alert → P1_TSUNAMI_ALERT

3. Set Refresh Region to refresh the Map Chart when filters are applied.

This approach provides a powerful Faceted Search Map Report in Oracle APEX, allowing users to interactively filter and explore geospatial data. By leveraging JSON functions and dynamic filters, this solution enhances the visualization of location-based data.

EXAMPLE:

Using the following code:

SELECT

    ID,

    JSON_VALUE(GEOMETRY, '$.type') AS geometry_type,

    JSON_VALUE(GEOMETRY, '$.coordinates[0]') AS longitude,

    JSON_VALUE(GEOMETRY, '$.coordinates[1]') AS latitude,

    "PROPERTIES_TZ" , 

    "PROPERTIES_CDI" , 

    "PROPERTIES_GAP" , 

    "PROPERTIES_IDS" , 

    "PROPERTIES_MAG", 

    "PROPERTIES_MMI", 

    "PROPERTIES_NET" , 

    "PROPERTIES_NST" , 

    "PROPERTIES_RMS" , 

    "PROPERTIES_SIG" , 

    "PROPERTIES_URL" , 

    "PROPERTIES_CODE" , 

    "PROPERTIES_DMIN", 

    "PROPERTIES_FELT" , 

    "PROPERTIES_TIME" , 

    "PROPERTIES_TYPE" , 

    "PROPERTIES_ALERT", 

    "PROPERTIES_PLACE" , 

    "PROPERTIES_TITLE" , 

    "PROPERTIES_TYPES" , 

    "PROPERTIES_DETAIL", 

    "PROPERTIES_STATUS" , 

    "PROPERTIES_MAGTYPE" , 

    "PROPERTIES_SOURCES" , 

    "PROPERTIES_TSUNAMI", 

    "PROPERTIES_UPDATED" , 

    "APEX$SYNC_STEP_STATIC_ID" , 

    "APEX$ROW_SYNC_TIMESTAMP" 

FROM USCG_DATA;

HOW DO I Create a new Report:

Select the MAP.

Create a Map – Page definition.

Add the query found at the start of this section into the SQL Select box.

Select the table:

Select Next.

Set the Map’s Settings.

Depending on your data type, you can also do the following:

Helpful Oracle APEX Documentation Links

• Creating Map Regions in APEX

• Faceted Search Pages

• Working with JSON in SQL

Conclusion

Combining faceted search with a map visualization in Oracle APEX gives your users a powerful, intuitive way to explore location-based data. By properly parsing the GEOMETRY column in USCG_DATA, validating coordinates, and using native Map features, you can quickly build a responsive, filterable, and interactive UI. This kind of design is perfect for maritime, environmental, logistics, or emergency applications—where seeing data on a map brings clarity and speed to decision-making.