Creating a Data Flow in Oracle Analytics Cloud to enriching with Geo Encoding to Map visualization of data

0

In this article, I will show how I have created a Data Flow in Oracle Analytics Cloud to enrich a data set with geocoding data from a different data set, in order to be able to create a map based visualization of data. More specifically: in a previous article I introduced a data set with the Oracle OpenWorld and CodeOne 2018 session catalog. I demonstrated how I got going on Oracle Analytics Cloud with this data set, and how I created my first visualizations. The session catalog contains the room in which each session takes place – and the first part of the room indicates the venue. These venues can be plotted on a map. My objective in this article is to associate the geocoordinates of the venues with each session record and subsequently use that encoding for visualizing the sessions on their actual geographical location on the city map of downtown San Francisco.

image

I will use a Data Flow in OAC to perform two actions. First I will create a new column called venue that is derived from the first ten characters of the room column. This will derive one of eight venue values for each session. Then I will join the sessions data set with a data set that contains the geo locations for each venue. This makes the longitude and latitude of the venue location part of each session, and allows me to create a map visualization that indicates the locations of selected sessions.

Steps:

  • Extract unique venues from room values
  • Create data set with venues and their geo-coordinates
  • Join sessions data set with venues data set – in order to add geo coordinates to each session
  • Create map visualization to show sessions on map [of downtown San Francisco]

next steps: create custom map with floorplans for each of the venues

Starting from the data set prepared in the previous article – downloadable from https://github.com/lucasjellema/Oracle-OpenWorld-CodeOne-2018-SessionCatalog/blob/master/oow2018-sessions-clean.xlsx.

Extract unique venues from room values

I have opened the data set and explore the room column. The venue is included as first part of the string of the room column.

image

I can get a feel for the different values by taking a substring of the room string values. It turns out that a substring of the first 12 characters of room give me the unique values representing the venues. I create a new column venue, using an expression to derive that substring of room:

image

After Adding the Step and Applying the Script, I can turn to the visualize tab:

image

And get a list of the unique venue values.

image

I can copy the values of the venues to the clipboard and from there to a text file or an Excel sheet.

image

Create data set with venues and their geo-coordinates

Next I need to extend this text file with the geocoordinates. I use this website for getting the exact longitude and latitude value: https://www.findlatitudeandlongitude.com/?loc=Moscone+Center+west%252C+San+Francisco%252C+CA&id=2190619#.XEwbXFxKhPZ

image

After getting all coordinates, this is what the file looks like:

image

it also includes the names of the venues in full.

Next I add this data set to my OAC instance.

image

In the Create Data Set dialog, drag the CSV file with the venue data:image

The file is uploaded and processed and the result is presented. Click on Add to save the Data Set to OAC. imageIt can now be used in Data Flows – or be visualized in its own right:

image

Join sessions data set with venues data set – in order to add geo coordinates to each session

Data Flows are the data preparation pipelines in OAC that can take one or more data sets, perform one more operations on them – including adding and removing, merging and splitting columns, filtering rows, joining data sets, aggregating data sets, deriving values using machine learning models and more. I now want a data flow to add the geo coordinates and the proper venue name to each session record.

image

A Data Flows always starts from the driving data set. So specify that set:

image

Since I want to join the data set to another data set, I have to add an Add Data step to the data flow:

image

And indicate which Data Set to join with:

image

Next, I have to specify the join condition – inner, left or right or full outer – and the columns on which to match. In this case, venue in source set is joined with venue_short in the geoenrichment data set:

image

Add Save Data step – to record the output from the data flow as a (new) data set.

image

Configure the Save Data step. Then, press Save and Run Data Flow respectively.

image

image

The new data set is generated by the data flow [execution]

image

Unfortunately, we need to do one more thing: the columns lat and long in this data set should be treated as Attribute and not as Measure – otherwise we cannot use them in Map visualization.

SNAGHTML3b10088

Create map visualization to show sessions on map [of downtown San Francisco]

Open the project, add a new canvas. Click on Visualizations and click Create Visualization in the context menu:

image

A Map is added to the canvas.

Select the lat and long data elements and drag them to the Category cell for Layer 1 for the Map.

image

This should already make the outlines visible of downtown San Francisco, with blue points for each of the venues.

Drag Row Count to Size (Bubble) and set Color to venue_1. The result looks like this:

image

When I click on the map, the property palette in the lower left hand corner changes into the map properties. I can now specify if the zoom controls should be shown, what the title is for the map, if the scale is in miles or km and more.

image

Using Trellis Columns by event I can easily compare the venues used for CodeOne vs Oracle OpenWorld:

image

Clearly, CodeOne was far less spread out – using only Moscone West and North.

Note: OAC does something undesirable: the color used for Moscone North is not the same in the two maps (green vs red). In the next example this happens again.

image

In this final example, I have added a filter by day and am currently looking at only Sunday and Monday. The number of sessions in the Marriott hotel on these two days is 69.

image

Resources

Data files are in GitHub repo: https://github.com/lucasjellema/Oracle-OpenWorld-CodeOne-2018-SessionCatalog (specifically https://github.com/lucasjellema/Oracle-OpenWorld-CodeOne-2018-SessionCatalog/blob/master/oow2018-sessions-clean.xlsx and https://github.com/lucasjellema/Oracle-OpenWorld-CodeOne-2018-SessionCatalog/blob/master/oow2018_geoenrichment.csv.

findlatitudeandlongitude Website for getting fine grained longitude and latitude: https://www.findlatitudeandlongitude.com/?loc=Moscone+Center+west%252C+San+Francisco%252C+CA&id=2190619#.XEs64VxKhPY 

OAC Docs – manage map data – for adding custom map layers and backgrounds – https://docs.oracle.com/en/cloud/paas/analytics-cloud/acabi/manage-map-information-analyses.html#GUID-F5C83299-9020-4B58-B011-19C239E4A194 

DV Demo with custom maps – https://www.youtube.com/watch?v=iIsVuUW2nmQ – using GeoJson shapes

QGis – Open Source tool for creating custom GeoJson files for floorplans, fantasy maps and more – https://qgis.org/en/site/ 

Introduction to GeoJSON: https://macwright.org/2016/06/05/falsehoods-developers-believe-about-geojson.html 

Article Digital Cartography: Making Maps in The Age Of the Internet  – http://sketchbook.nclud.com/digital-cartography-making-maps-age-internet/ 

Building Map for Game of Thrones – using GeoJSON, PostGIS and more – https://github.com/triestpa/Atlas-Of-Thrones

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.