Oracle Autonomous Database includes a powerful built-in function called SDO_GCDR.ELOC_GEOCODE_AS_GEOM, which can geocode an address and return its location as an SDO_GEOMETRY object. This function works with both formatted (structured) and unformatted (free-text) addresses and returns the geographic coordinates along with standardized address metadata.

This feature is only available within Oracle Autonomous Database and is especially useful when combined with Oracle Analytics for building location-aware workbooks.

How Geocoding Works

The function geocodes a formatted (with the address parts like street, city, and country separated) or unformatted (single-line full address) string and returns a spatial point.

Formatted Address Example

SELECT (SDO_GCDR.ELOC_GEOCODE_AS_GEOM(
  ‘123 Beacon St’, ‘Boston’, ‘MA’, ‘02116’, ‘US’)
).GET_GEOJSON() FROM DUAL;
Returns:
{ “type”: “Point”, “coordinates”: [-71.07355, 42.35517] }

Unformatted Address Example

SELECT SDO_GCDR.ELOC_GEOCODE_AS_GEOM(
  ‘123 Beacon St, Boston, MA 02116, US’
).GET_GEOJSON() FROM DUAL;
Returns:
{ “type”: “Point”, “coordinates”: [-71.07355, 42.35517] }

Example: Static Geocoding Query

You can use this function to geocode a specific address and return the geometry data type of the location:

SELECT
    SDO_GCDR.ELOC_GEOCODE_AS_GEOM(‘1 Oracle Dr, Nashua NH, 03062, US’) AS GEOCODE_AS_GEOM
FROM
    DUAL;

To make this geocoding process dynamic and interactive, you can create session variables within the semantic model and bind the session variables 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:

  • Define two session variables: MY_ADDRESS and MY_COUNTRY.
  • Assign default values for testing.
  • Deploy the semantic model.
Figure 1: The session variable called MY_ADDRESS in the semantic model created to override the default input for Address.
Figure 1: The session variable called MY_ADDRESS in the semantic model created to override the default input for Address.

 

Figure 2: The session variable called MY_COUNTRY shown in the semantic model to override the user input for Country.
Figure 2: The session variable called MY_COUNTRY shown in the semantic model to override the user input for Country.

 

  • Then, use the following SQL to geocode addresses dynamically based on user input, and use the SQL to create a dataset:
SELECT
    SDO_GCDR.ELOC_GEOCODE_AS_GEOM(‘VALUEOF(NQ_SESSION.MY_ADDRESS), VALUEOF(NQ_SESSION.MY_COUNTRY)’) AS LOCATION_PIN
FROM
    DUAL;
Figure 3: Spatial SQL with session variable defined as a dataset in Oracle Analytics. This returns the geometry of the location using the reference column LOCATION_PIN from the SQL.
Figure 3: Spatial SQL with session variable defined as a dataset in Oracle Analytics. This returns the geometry of the location using the reference column LOCATION_PIN from the SQL.

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

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

  • Create parameters in your workbook for the address and country and expose them as dashboard filter controls on your canvas. For example, parameters by the name ‘Enter Your Address’ and ‘Enter Your Country’ 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.

When a user enters an address and country via the dashboard filter controls, the geocoding function dynamically returns the coordinates. Later, you can expose the parameters to your users through a dashboard filter with the filter type, Text Box. Users can input their choice of address and country using these text boxes.

Figure 4: A parameter by the name ‘Enter Your Address’ which will be bound with the session variable ‘MY_ADDRESS’. Similarly, a parameter ‘Enter Your Country’ can be created and bound with the session variable ‘MY_COUNTRY’.
Figure 4: A parameter by the name ‘Enter Your Address’ which will be bound with the session variable ‘MY_ADDRESS’. Similarly, a parameter ‘Enter Your Country’ can be created and bound with the session variable ‘MY_COUNTRY’.

 

Figure 5: Session variables MY_ADDRESS and MY_COUNTRY connected to parameters through parameter binding in the workbook
Figure 5: Session variables MY_ADDRESS and MY_COUNTRY connected to parameters through parameter binding in the workbook.

The resulting spatial geometry LOCATION_PIN can be visualized directly on a map.

Figure 6: Map rendering of the geocoded location with default input for MY_COUNTRY and MY_ADDRESS. MapBox Satellite Map Background is used in the map visualization.
Figure 6: Map rendering of the geocoded location with default input for MY_COUNTRY and MY_ADDRESS. MapBox Satellite Map Background is used in the map visualization.

 

Figure 7: Map rendering of the location of the Eiffel Tower in France based on dynamic user input through the dashboard filter controls. MapBox Satellite Map Background is used in the map visualization.
Figure 7: Map rendering of the location of the Eiffel Tower in France based on dynamic user input through the dashboard filter controls. MapBox Satellite Map Background is used in the map visualization.

 

Figure 8: Another example where the user input is changed from Eiffel Tower to Liverpool FC’s stadium, Anfield in the UK. ARCGIS World Imagery Satellite map background is used in the map visualization.
Figure 8: Another example where the user input is changed from Eiffel Tower to Liverpool FC’s stadium, Anfield in the UK. ARCGIS World Imagery Satellite map background is used in the map visualization.

Geocoding is a key step in spatial analytics, and with Oracle Autonomous Database and Oracle Analytics, it becomes seamless. You can convert addresses into map locations dynamically, visualize them instantly, and create interactive, location-aware workbooks with no external tools required.

Stay Tuned

In the next part of this series, we will explore Drive Time Polygons through Spatial SQL and Oracle Analytics

Reference

Geometry 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.