Oracle Autonomous Database Serverless Deployments offer advanced geospatial functions that make location intelligence more accessible and actionable. One such function is SDO_GCDR.ELOC_ISO_POLYGON, which computes a drive-time or distance-based polygon around a specified location. This spatial capability can be combined with Oracle Analytics to visualize reachable areas within a defined travel time or distance.

What Is ELOC_ISO_POLYGON?

This spatial function calculates a polygon representing the area that can be reached from a given point, within a specific time or distance. It returns the result as a JSON CLOB that contains the polygon geometry in GeoJSON format, which you can convert to native SDO_GEOMETRY for use in Oracle Analytics.

Key Parameters

  • iso: Time for time-based polygons or distance for distance-based polygons
  • start_address: Full address in a single string format
  • country: 2-letter ISO country code (for example, US)
  • cost: Time or distance value (for example, 10)
  • cost_unit: Unit of cost (for example, mile, minute)
  • vehicle_type: Type of vehicle (for example, auto or truck)
  • longitude/latitude: Alternative to using an address (directly inputs coordinates)

Example: Static SQL for a 10-Mile Drive Distance

This SQL calculates the drive distance polygon from “1 Oracle Drive, Nashua, NH”,“US” using a distance of 10 miles:

SELECT
    SDO_UTIL.FROM_GEOJSON(
        JSON_QUERY(RESPONSE, '$.routeResponse.driveTimePolygon.geometry' RETURNING CLOB)
        ) AS DRIVE_DISTANCE_POLYGON
FROM
    (
        SELECT
            SDO_GCDR.ELOC_ISO_POLYGON('distance', '1 Oracle Drive, Nashua, NH', 'US', 10, 'mile',
                                      'auto') AS RESPONSE
        FROM
            DUAL
    );

 

To make this spatial SQL dynamic and interactive, you can create session variables within the semantic model and bind it to parameters within a workbook in Oracle Analytics.

Step 1: Create Session Variables to Use in the SQL Query

Before you can reference a session variable in your SQL, you must define it in the semantic model:

  • Create the following session variables:
  • MY_ADDRESS
  • MY_COUNTRY
  • Time
  • Assign default values for testing.
  • Deploy the semantic model.
Figure 2: The session variable MY_COUNTRY in the semantic model created to override the default country.
Figure 2: The session variable MY_COUNTRY in the semantic model created to override the default country.

 

Figure 3: The session variable Time in the semantic model created to override the default drive time.
Figure 3: The session variable Time in the semantic model created to override the default drive time.

 

  • Then use the following SQL query in Manual SQL Query mode and create a dataset:
SELECT json_value(t, '$.routeResponse.driveTimePolygon.cost') AS cost,
       json_value(t, '$.routeResponse.driveTimePolygon.unit') AS unit,
       json_query(t, '$.routeResponse.driveTimePolygon.geometry' RETURNING clob) AS geojson_geom,
       sdo_util.from_geojson(
           json_query(t, '$.routeResponse.driveTimePolygon.geometry' RETURNING clob)
       ) AS sdo_geom
FROM (
    SELECT SDO_GCDR.ELOC_ISO_POLYGON(
               'time',
               'VALUEOF(NQ_SESSION.MY_ADDRESS)',
               'VALUEOF(NQ_SESSION.MY_COUNTRY)',
               CAST('VALUEOF(NQ_SESSION.Time)' AS INTEGER),
               'minute',
               'auto'
           ) AS t
    FROM DUAL
) x;
Figure 4: Spatial SQL to generate the geometry for a drive-time polygon based on the three session variables.
Figure 4: Spatial SQL to generate the geometry for a drive-time polygon based on the three session variables.

Step 2: Create Workbook Parameters and Bind Them to the Session Variables

Once the SQL is saved as a dataset in Oracle Analytics, you can:

  • Create parameters in your workbook matching the session variable names and expose them as dashboard filter controls on your canvas. For example, parameters named “Enter Your Address” ,”Enter Your Country”, and “Enter the Drive-Time Duration” will make sense to users as dashboard filter controls on the canvas.
  • Use parameter binding to connect the user-entered values to the session variables.
Figure 5: A workbook parameter called Enter the Drive Time Duration for entering the preferred time.
Figure 5: A workbook parameter called Enter the Drive Time Duration for entering the preferred time.

 

Later, you can expose the parameters to your users through a dashboard filter with the filter type, Text Box. Users can use these text boxes to select address, country or drive time duration.

Figure 6: Three session variables connected to the parameters through parameter binding in the workbook.
Figure 6: Three session variables connected to the parameters through parameter binding in the workbook.

 

This setup allows users to input an address, country, and time value, and instantly visualize the updated drive-time polygon.

Since the resulting polygon (sdo_geom) can be visualized directly on Oracle Analytics maps, it provides real-time insights on travel reachability, service coverage, or delivery optimization based on user input.

Figure 7: Map visualization showing the drive-time polygon based on the default values for the three session variables.
Figure 7: Map visualization showing the drive-time polygon based on the default values for the three session variables.

 

Figure 8: Map visualization showing an address in Padova, Italy based on dynamic user input through the dashboard filter controls.
Figure 8: Map visualization showing an address in Padova, Italy based on dynamic user input through the dashboard filter controls.

The SDO_GCDR.ELOC_ISO_POLYGON function in Oracle Autonomous Database Serverless Deployments, when combined with Oracle Analytics, provides a dynamic, user-friendly way to compute and visualize drive-time or distance-based polygons. With support for session variables and parameter binding, it unlocks powerful location intelligence capabilities for interactive workbooks.

Stay Tuned

In the next part of this series, we’ll explore Routing mechanisms through Spatial SQL and Oracle Analytics.

Reference

Geometry Data Type technical paper

Call to Action

Now that you’ve read this article, try it yourself and let us know your results in the Oracle Analytics Community, where you can also ask questions and post ideas. Be sure to explore the Community Gallery as well, where users share their most creative dashboards and map visualizations for inspiration.