Last week I performed a virtual ‘awareness’ workshop for a customer. Whilst primarily focused on Oracle Analytics Cloud components, we took time to look at some other technology which they haven’t really looked at yet, just so that they had an appreciation of what is available.
One thing that particularly piqued interest was discussing performing spatial analysis using Oracle Spatial Studio to create a subset of data and then exporting that to perform exploratory analysis in Oracle Analytics. There’s a couple of ways of doing that, the one we will look at is exporting the results in CSV format so that can be consumed by Oracle Analytics to create a dataset, but there is also the option to export GeoJSON which can be used to create a Map Layer in Oracle Analytics.
Why Spatial Studio?
Why Spatial Studio? Oracle databases have a lot of spatial capability (and have for years) and Spatial Studio provides a graphical interface to browse the available algorithms whilst also offering easy entry of parameters. The resulting SQL that is generated can be interrogated and can be saved and manipulated and executed outside of Spatial Studio. We will look at this in a subseqent blog and perform some spatial analysis in SQL in an autonomous database. Note that although Spatial Studio shares some look and feel simularity with Oracle Analytics, it is licensed as part of the database and not as part of Oracle Analytics. Read the FAQ here for more information.
Let’s “Load and Geocode”
Let’s dive into Spatial Studio. One thing to note is that my data contains Post Codes, if you’re not familiar with the term then think Zip Codes.
Once I sign in, I can create a dataset.
I am going to connect to my Oracle database – and this can be an Autonomous Cloud database – where I have a demonstration table of ‘made-up’ customers in the Manchester, United Kingdom area with their addresses and the amount they have spent with us.
I can simply click on the database link I have previously setup called ‘Spatial Studio’ and I can see all the tables in my database schema and I can select my Customer_Stats table.
That creates the dataset so I can see it. I notice that it has been created with some warnings as indicated by the warning triangle.
Clicking on the triangle shows us the warnings
This shows me that I need to allocate a primary key and also there is a requirement to Geocode the data so that we can use it in spatial analysis. Let us start with the key issue by clicking on the link provided. I can set the key by simply toggling the Use As Key as shown below
Obviously for a key this needs to be unique, so the Validate Key button does this checking for us. I’m pretty certain this will work as I created it as an identity column in the database, so let’s give it a go.
This issue is cleared from the list. Now to address the second issue.
Click on Geocode Addresses and set the appropriate flags. My data has Post Code and Country data.
Pressing Apply causes the Geocoding to occur
We now have a completely validated dataset. We can see below that when we look at the dataset it has iconography that indicates there are no warnings and it has been encoded and is ready to use.
Visualizing The Dataset
Now we can create a new project which gives us a default map. Start by dragging the whole customer_stats data set across to the map.
This takes our geocoded data and creates a visual representation of every data point – i.e. every customer.
We can change the look of the datapoints in many ways. We can change their shape, colour, opacity, size, etc.
Let us change the size of the data points to be representative of the customer spend. To do this we set the Radius to be Based On Data
We can select the ‘fact’ we want to use so we shall select the PURCHASE_VALUE and create 10 buckets to create 10 different sized datapoints.
Loading A Second Dataset
Now we will add in another dataset. This contains information about the location of a ficticious company Head Office, the one from where the customers have been buying goods. I will drop into SQL Developer to take a look at this.
If we go through the same process as the table with the customers to create a dataset (we won’t repeat the steps here)
When Geocoding completes, if we look at the offices table in SQL Developer, we see that there is new column called GC_GEOMETRY added
Let’s look closer. If we describe the table we see the new column is a geometry type.
Let’s have a look at the GC_GEOMETRY column
What we have here is a ‘geometry’ object type column, where 2001 indicates it is a 2D point and 4326 is a pointer to a spatial metadata table which resolves itself as meaning we are looking at Latitude and Longitude data. Effectively, my postcode has been converted to Lat/Long. Now we can add that dataset to the existing project.
Add The Second Dataset To The Project
Click on the new Offices dataset to add that
The act of adding the dataset makes it available for selection in the the Data Elements section of the project.
Drag and drop this whole dataset onto the Map
Now we can make our Head Office stand out by making it appear as a large bold building by making some appropriate style choices.
At this point we have two layers in Spatial Studio, one showing the Customers and one showing the Head Office. We can perform spatial analysis between these layers.
Performing Spatial Analysis
We decide to run a promotion and invite all of our customers within a 3 mile radius of head office to come to a special customer appreciation party – but who are they? We all also award the biggest spending customer in that 3 mile radius a specific gift, but who are they? Let’s find out.
This opens up a smorgasboard of analytic options across different types of analysis, subdivided into Filter, Combine, Transform, and Measure.
Clicking the Return Shapes within a specific distance of another provides an easy front end to enter the parameters for the spatial analysis which will create us a new dataset based on relative analysis of the customers and head office datasets. This new data set will just contain those customers whom are within 3 miles of the head office.
We can give the dataset a name, PARTY INVITES in this case as these are the customers we are going to be inviting, and then we select the layer we are going to restrict, namely the customers
Then we select the layer to use as the filter – the office – and set the distance to be in Miles and at a radius of 3.
Now we have entered all the parameters we can press Run. When that completes successfully we get the new PARTY INVITES analysis appearing in the components panel
We can now drag this new analysis across to the map and see it participate
When we do that, this shows all the customers (CUSTOMER_STATS), the customers within a 3 mile radius (PARTY INVITES) and the head office (OFFICES).
Let us remove the layer which shows all customers to leave just the customers within a 3 mile radius
Tweak the colour and opacity of the customers within a 3 mile radius to make them stand out.
Exporting The Results
We can now export that dataset and look at it in Oracle Analytics
Export it as a CSV. The option, as we discussed at the beginning, exists to export shape files as GeoJSON.
Analysis in Oracle Analytics
Now we can load this exported datafile into Oracle Analytics. The data is exported as a .CSV so I converted to .xlsx first and then when loading into Oracle Analytics as a dataset I set the Geometry to be attributes not measures. Now I can explore the customer data in a map in Oracle Analytics.
Let’s tidy this up a bit. We did say that we wanted to identify the ‘guest of honour’ at the party and have that as the customer whom has spent the most money with us.
- Create a bar chart to show the customers and the value of their orders.
- Make the highest datapoint in the bar chart stand out.
- Create some titles and tidy the charts up.
- Change the map type to one that shows more detail
- Make the size of the customer shapes dependent on the purchase value.
- Using the bar chart we can quickly identify the customer who has bought the most and change their data point on the map to be something really distinctive, a blue cross.
Now we have done the exploratory work, we can give this project to someone and they can easily see the customers and the addition of some pre-attentive attributes help to draw the attention to the customer whom is our guest of honor.
The aim of this blog was to show how Spatial Studio can act as a visual wrapper around the deep spatial functionality available in the Oracle Database and then to export the results and surface them in Oracle Analytics cloud for further analysis.