Oracle Analytics allows users to perform powerful spatial analysis by writing manual SQL queries and save them as a datasets. One such use case is generating buffer zones using Oracle spatial functions.

For example, a static SQL query like this creates a 100-kilometer buffer around a storm path:

SELECT SDO_GEOM.SDO_BUFFER(“t1″.”GEOMETRY”, 100, 0.05, ‘unit=KILOMETER arc_tolerance=0.05’) AS “GEOMETRY”,
       “t1″.”KEY” AS “KEY”
FROM “NATE_TECH_AGGR_LINES” “t1”

 

To make the buffer distance flexible and adaptable to user needs, such as changing it to 50 km or 300 km, you can enhance the query by using session variables and parameter binding. This transforms the query into a dynamic, user-driven spatial analysis.

Step 1: Create a Session Variable and Use It in the SQL Query

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

  • Open your data model in Oracle Analytics.
  • Create a new session variable (for example, Buffer).
  • Assign it a default value (for example, 100).
  • Save and deploy the semantic model.
Figure 1: A session variable called Buffer shown in the semantic model.
Figure 1: A session variable called Buffer shown in the semantic model.

Once this is done, create a dataset by writing a manual SQL query that references the session variable using the VALUEOF function:

SELECT SDO_GEOM.SDO_BUFFER(“t1″.”GEOMETRY”, ‘VALUEOF(NQ_SESSION.Buffer)’, 0.05, ‘unit=KILOMETER arc_tolerance=0.05’) AS “GEOMETRY”,
       “t1″.”KEY” AS “KEY”
FROM “NATE_TECH_AGGR_LINES” “t1”

 

This makes the buffer size configurable at runtime.

Figure 2: Spatial SQL using session variable used as a data set.
Figure 2: Spatial SQL using session variable used as a data set.

Step 2: Create a Parameter in the Workbook

In your Oracle Analytics workbook, create a new parameter with default options. This parameter will act as the user input for the buffer size so name it accordingly, for example, Enter Your Buffer.

Later, you can expose the parameters to your users through a dashboard filter with the filter type, Text Box. Users use these text boxes to select buffer distance.

Figure 3: A workbook parameter named Enter Your Buffer.
Figure 3: A workbook parameter named Enter Your Buffer.

Step 3: Bind the Parameter to the Session Variable

To link the user’s input to the SQL logic, use parameter binding to map the workbook parameter (Enter Your Buffer) to the session variable (Buffer).

This binding ensures that whenever a user changes the parameter value in the workbook, it updates the session variable accordingly.

Figure 4: Binding the session variable Buffer to the workbook parameter Enter Your Buffer.
Figure 4: Binding the session variable Buffer to the workbook parameter Enter Your Buffer.

Because the SQL query is already referencing the session variable, it will dynamically adjust the buffer size based on the user’s input.

Figure 5: Using the Enter Your Buffer parameter as a dashboard filter to override the default session variable value.
Figure 5: Using the Enter Your Buffer parameter as a dashboard filter to override the default session variable value.

The end result is an interactive and dynamic spatial SQL.

Figure 6: User enters a value of 500 in the dashboard filter, it overrides the default value of 100, and draws a dynamic buffer of 500 kilometers around the storm path.
Figure 6: User enters a value of 500 in the dashboard filter, it overrides the default value of 100, and draws a dynamic buffer of 500 kilometers around the storm path.

With this setup, users can:

  • Choose a custom buffer distance via a dashboard filter control.
  • Automatically trigger the spatial SQL with the new distance.
  • See updated buffer zones rendered on the map, tailored to their selection.

Users can also configure additional spatial settings, such as switching the unit from kilometers to meters or adjusting the arc tolerance, by extending the session variable approach.

This approach combines backend flexibility with front-end interactivity, making spatial analysis in Oracle Analytics more dynamic and user-driven.

Stay Tuned

In the next part of this series, we’ll explore Geocoding techniques 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.