Spatial analytics is a powerful tool that provides insights into geographical aspects of data. This technology can be employed to optimize delivery routes, visualize and interact with the geographical distribution of customers, or even plan sales campaigns based on locality. By applying spatial analytics, businesses can go beyond traditional analytics of knowing who their suppliers and customers are, and use location-based data for more comprehensive, data-driven decision-making. A recent enhancement to Oracle Autonomous Database – Shared has new Geocoding features available. The Geocoding API is available to ADW and ATP.
The new Geocoding facility enables address data to be geocoded by the Autonomous Database in multiple ways, making it readily consumable by platforms such as Oracle APEX or Oracle Analytics. This opens a world of possibilities for nuanced spatial analysis and visualization. Karin Patenge beautifully outlined Oracle’s cutting-edge technology in her recent blog post
Karin provides examples to transform addresses into coordinate values, a process called geocoding, and to turn coordinate values into an actual address, called reverse geocoding.
In this blog, we’ll build on Karin’s insights to showcase an example of the output of geocoding being used by other applications. It’s great when we have a full customer address but often address data in source systems is not fully complete, but even so we can still use this as source data. Using the example of customer data where we just have the Postcode (ZipCode) and country, we can use the sdo_gcdr.eloc_geocode_as_geom function, to obtain a fully-formed Oracle SDO_GEOMETRY column. It is important to note that using just the postal and country codes, the longitude and latitude values returned by the function are approximate values.
Create The Spatial Data
To illustrate this let’s consider customer data stored in a warehouse which includes just the postcode and country part of the customer address and a few other columns about the customer, including a metric detailing the sales generated from each customer.
We can add a geometry column to the table which we will get the database to populate and thus allow us to perform spatial analysis.
alter table my_customers add (gc_geometry sdo_geometry);
Next we get the database to geocode the address data by executing a simple update, using the sdo_gcdr.eloc_geocode_as_geom function. This is documented here which when passed some address details performs the following:
“Geocodes a formatted (address parts in separate fields) or an unformatted (complete address in a single string field) address and returns the standardized address with geographic coordinates and geocoding metadata as an SDO_GEOMETRY object.”
Update my_customer set gc_geometry=sdo_gcdr.eloc_geocode_as_geom(address => postcode||' '||country);
commit;
Once this executes, we can see the data has been geocoded.
Using SQL Developer we can see the structure of the geocoded data
This is a point representation of the data (indicated by the SRID 4326), with the latitude and longitude columns made visible. We can now use this data in our applications.
Visualize Using Oracle APEX
Firstly, let’s use Oracle Application Express (APEX) to create an application.
Then create a map page.
We select the table with the customer data in it and choose points for this example as we want to see each customer individually. Then all we need to do is to identify which is the SDO_GEOMETRY column.
That is it, now just create the application
Then run it and we have all the customers on the page
We can perform a variety of formatting on these points in APEX. We can apply different shapes, sizes, and colours. Let’s set some colours based on the sales.
Then just set the colouring attribute in the “Appearance” section.
Note also in column mapping section that the Geometry column is set to the GC_GEOMETRY column. There are three options in APEX 23.1, we can also specify GeoJSON and Longitude/Latitude.
Running the application now we can see the customers with their new colouring.
The “Sample Maps” gallery application available with Oracle APEX has a wealth of useful features that are really worthy of investigation, and we show some of the techniques from that application in our example. What we can do is to click any point on the map and then find the closest 10 customers and highlight them with a polygon.
Firstly, we need to capture the latitude and longitude of the click. One way is to create a dynamic action and use a “Map Clicked” event and then store the two values. Firstly, we create an item to store the latitude and longitude values as JSON.
Then we create the dynamic action to populate the item.
Now set the value of the latitude/longitude item in the TRUE events.
We can add a spatial index to our data to make data access more efficient.
INSERT INTO USER_SDO_GEOM_METADATA
VALUES
(
'my_customers',
gc_geometry',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('Lon', -180, 180, 0.005),
MDSYS.SDO_DIM_ELEMENT('Lat', -90, 90, 0.005)),
4326 -- SRID for WGS84
);
Note that the SDO metadata contain longitude and latitudes values in reverse order compared with the JSON item value.
CREATE INDEX my_customer_index ON my_customers (gc_geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX_v2 PARAMETERS (‘layer_gtype=POINT’);
I can now create a new layer for the nearest neighbours and feed that into the polygon. Please refer to the previous blog for background about how I used Spatial Studio to get the code templates to use in APEX for creating the list of the Nearest Neighbours and also the polygon. Spatial Studio is a fantastic application for spatial analysis and I highly recommend that you check it out. For more details please check out the FAQ
In this simplified single piece of code, we get the nearest 10 customers to the address held in the :P2_CLICK_LAT_LONG variable. The spatial function SDO_AGGR_CONVEXHULL takes these records and creates a polygon which we can then visualize with minimal effort.
Now we can click and select a point on the map and the polygon is created. I tweaked the setup to have a light green polygon that is semi-transparent and also submitted the page items on refresh.
If we click elsewhere we see another polygon dynamically created encompassing the 10 customers nearest to where we clicked.
We can even enhance to display the customers too and change the colour to make it more opaque
Or we can even re-use the query to add a chart of the selected customers and their sales values, so we only see those associated to where we clicked. What this demonstrates that we are not just looking at the data, but we know who those customers are and can contact them if we chose to, perhaps if we were were going to open a new store and see who were the closest existing customers to invite them to a special pre-opening event.
As you can see, we can rapidly start to make use of the data that the database geocoded quickly and effectively.
Visualize Using Oracle Analytics Cloud
As we have an SDO_GEOMETRY column we can interrogate that in several ways. Analytics needs to have the latitude and longitude columns to create a dataset, so we can use a spatial function like SDO_UTIL.GETVERTICES or SDO_POINT as we show below.
select cust.customer_name, sales, g.x, g.y from my_customers cust,table(SDO_UTIL.GETVERTICES(cust.gc_geometry)) g
Alternatively
select cust.customer_name, sales, cust.gc_geometry.sdo_point.x as Longitude, cust.gc_geometry.sdo_point.y as Latitude
from my_customers cust
It is simple to create a dataset based on this query directly from the database.
Optionally Analytics Cloud allows us to designate the latitude and longitude columns as “location” columns, so that it is aware it is dealing with location columns and subsequently they can just be dragged onto a canvas and a map will be automatically created. We can easily set these when creating the dataset.
When they are tagged as location columns, you see the following icon.
Now we have the data, we can create a workbook and drag the location columns and sales onto the canvas to create visualisations that allow our data to come to life, like the following example.
Summary
We’ve had a quick tour of some simple possibilities of consuming address data that has been geocoded by the Autonomous Database. With address and spatial data being ever more popular I hope that this has given you some inspiration to look at ways that spatial data can be used for competitive advantage by your organisation, using tools that complement the database.