Creating a Playground for Spatial Analytics

[mkemeter]

You are interested in Spatial Analytics and looking for some content to get started? The combination of SAP HANA Cloud Trial with QGIS is providing you a low entry barrier into spatial data processing at scale. Spatial workflows built upon SAP HANA can ultimately be re-used in business applications to inject the location dimension into your business processes.

No matter if you're new to QGIS or if you're new to SAP HANA - or even both. This blog has the goal to provide you with a working and free-of-cost playground in less than 30 minutes. So - add it to your bookmarks and block some time in your calendar!

The tutorial will comprise the following tasks:

• ⏳5m | Create HANA Cloud Trial Instance


• ⏳2m | Install SAP HANA ODBC Driver


• ⏳3m | Install QGIS


• ⏳5m | Connect QGIS to SAP HANA Cloud


• ⏳5m | Upload your first spatial dataset


• ⏳5m | Visualize data on a base map


• ⏳5m | Pushing down Spatial Workload to SAP HANA Cloud

Also, I recommend to get a coffee before starting. This is not part of the 30 minutes as preparation times vary based on the method of preparation. So, you can see this a 'prerequisite' to start into this macro-workshop.

Another prerequisite is to download the QGIS Installer Packages for your operating system on https://download.qgis.org. I am working on a Mac and the corresponding installation packages for QGIS 3.18 have 1.2GB in size. Depending on server speed, this download may last a while...

 

Got your ☕️ ready?

 

⏳5m 👉 Create HANA Cloud Trial Instance

Here I should admit, that 5 minutes is the net effort. Actual creation times of an SAP HANA Cloud Trial instance vary. The good thing is, that you can parallelise and go ahead with the other installation steps, while your fresh instance is starting up. This way, we should still be able to maintain our 30 minute goal.

⏯ Walk-through: Creation of SAP HANA Cloud Trial Instance

Follow these steps:

1. Go to https://www.sap.com/cmp/td/sap-hana-cloud-trial.html and click Start your free trial now.


2. Enter your data and choose your preferred region (i.e. US East, Europe or Singapore).


3. It takes a minute or two until your Global Account, your Subaccount, Org and Dev Space are created. Just take a sip of your coffee and wait.


4. You will automatically receive an email with a link to your trial. Click the link Access your Trial.


5. Now, you need to enter your 'space'. To do this:
   
   
   
   1. Click Enter Your Trial Account
   
   
   2. Click on the subaccount called trial. The respective tile also shows the region that you have chosen earlier.
   
   
   3. Click on the space named dev.
   
   
   4. Click on SAP HANA Cloud on the left side.
   
   
   
   


6. Next, we will start the actual HANA Cloud Trial instance. Therefore, click on Create in the upper right corner.


7. In the following dialogs these configurations need to be taken care of:
   
   
   
   1. Instance Name. I will choose hctrial for this tutorial.
   
   
   2. Administrator Password
   
   
   3. Allowed Connections should be set to Allow all IP addresses for the sake of simplicity (and the cost of security!).
   
   
   4. Create Data Lake can be switched off as you will not require a data lake to follow the steps outlined here.
   
   
   
   


8. Your instance will be created. Continue with the other steps and we will come back later.

 

⏳2m 👉 Install SAP HANA ODBC Driver

Here, 2 minutes is usually generous. To install the SAP HANA ODBC Driver, which is part of the SAP HANA Client, you just need to download it from SAP Development Tools and execute the OS specific installer.

There is not much to consider here, since this is a Next-Next-Finish-Installer (Is that an even an official expression? Well, it should be.). There are install packages for Windows, macOS and Linux available.

 

⏳3m 👉 Install QGIS

Again, this is essentially a Next-Next-Finish installer without specific considerations. As mentioned above the OS specific installation package for Windows, macOS and Linux are available on the QGIS Download Page. You will need to have at least version 3.18 to work with SAP HANA.

Once you downloaded and installed QGIS 3.18, you can start the client. Make sure that you see SAP HANA as a data provider in the data browser on the left hand side.

 

⏳5m 👉 Connect QGIS to SAP HANA Cloud

The general instructions on how to connect QGIS to SAP HANA can be found in the QGIS documentation. Also, there is a short video available depicting the process.

⏯ Walk-through: Connect QGIS to SAP HANA Cloud Trial

But, let's have a look in detail on how to get the necessary credentials:

1. If you are not within the SAP BTP Cockpit looking at the tile with your SAP HANA Cloud instance, please repeat steps 4 & 5 from "Create HANA Cloud Trial Instance" above. You should see your created and running instance.


2. Click on Actions and Copy SQL endpoint. The endpoint will be copied to your clipboard. Keep it there.


3. Open QGIS and locate the SAP HANA entry in the browser on the left pane.


4. Right-click on SAP HANA and choose New Connection.


5. The connection dialog will open up. Fill following fields:
   
   
   
   1. Name: Fill in a name for the connection. I will use hctrial.
   
   
   2. Host: Paste your clipboard and remove ":443" at the end. The SQL endpoint has format <host>:<port> and the field expects <host>.
   
   
   3. Identifier: Switch the dropdown to Port and enter 443 in the text field.
   
   
   4. Authentication: Choose Basic authentication and enter User name and Password. If this is just a playground the DBADMIN user will do the job. Note, that the recommendation still is to create a dedicated user with limited access.
      (Note: Older versions of the ODBC driver require you to explicitly enable SSL in the connection properties. Make sure to use an up-to-date client version)
   
   
   
   


6. In the connection dialog click on Test Connection and if the two of us have done their jobs right, you should see a successful connection attempt.


7. Click OK and check that your connection (in my case hctrial) is listed on the browser on the left pane.

 

⏳5m 👉 Upload your first spatial dataset

What have we achieved so far? We have created an SAP HANA Cloud Trial instance and connected QGIS via ODBC. Unfortunately, our database is still empty and we should change this. Good news is, that we do not need any database tooling to upload our first dataset. We will simply use QGIS.

⏯ Walk-through: Uploading your first spatial dataset

As suggested in my previous blog, we can use the OpenStreetMap data provided by Geofabrik. We will start by looking at the point of interest data in the area of SAP's Headquarter in Walldorf, Germany.

Let's do this:

1. Download the Shapefile (.shp.zip) for the "Regierungsbezirk Karlsruhe". You can think of a Shapefile being a file-based database for geospatial data.
   Download Page | Direct Link


2. Open QGIS and locate the downloaded file (do not unzip!) in the browser on the left side. On my Mac, I have to navigate to Home > Downloads. Folders that you often use can also be added to Favorites.


3. The file can be further expanded in the browser. Do this and locate the layer called gis_osm_pois_free_1.


4. Locate your SAP HANA connection in the browser and expand it twice, so that you can see the schema (DBADMIN if you followed the steps above).


5. And here comes the magic: Drag and Drop the gis_osm_pois_free_1 layer onto your DBADMIN schema.


6. You successfully imported your first spatial dataset into SAP HANA Cloud Trial!

If you need evidence, you can open up the Database Explorer or DBeaver and check out the table, that has been created. But be aware, that this will not be part of our 30-minute deal!

 

⏳5m 👉 Visualize data on a base map

We now have stored points of interest (POIs) with its coordinates in our database. This kind of data (i.e. points, lines, polygons, etc.) given by coordinates is called vector data. The vector data can be printed onto a map or even satellite image (which is considered raster data).

So we need to add two different layers to our QGIS project:

1. A base map (e.g. street map or satellite image)


2. The data plotted onto this map (vector data that is managed by SAP HANA)

⏯ Walk-through: Add base layer with satellite imagery

How to add the base layer:

1. In the browser left click on XYZ Tiles and choose New Connection.


2. A dialog for a new tile service opens up:
   
   
   
   1. Enter 'Google Maps Satellite' as a name.
   
   
   2. Enter 'https://mt1.google.com/vt/lyrs=s&x={x}&y={y}&z={z}' as a URL.
   
   
   3. Set the Max. Zoom Level to 19.
   
   
   4. Confirm with OK.
   
   
   
   


3. Locate Google Satellite in the browser under XYZ Tiles.


4. Double-click on Google Maps Satellite to add the base layer with satellite imagery.

If you would like to add a different service (e.g. OpenStreetMap), you can check this blog for more URLs.

⏯ Walk-through: Visualize data in SAP HANA

How to add all the fountains around the SAP Headquarter:

1. Locate the dataset that we have previously uploaded to SAP HANA in the browser. (SAP HANA > hctrial > DBADMIN > gis_osm_pois_free_1)


2. Double-click gis_osm_pois_free_1 to add another layer on top of the base map


3. In the list of visible layers at the left bottom right-click on the newly added layer and choose Zoom to Layer(s). This will determine the bounding box of the respective data and zoom the map correspondingly.


4. We now see all POIs of the Karlsruhe region on the map. We would like to filter out only the fountains. Thus we right-click again on the layer and choose Filter...


5. The filter dialog opens up:
   
   
   
   1. As a Filter Expression enter "fclass" = 'fountain'
   
   
   2. Confirm with OK
   
   
   
   


6. Zoom in to the SAP Headquarter. You will see two fountains - the one in the East being the fountain in front of Building 3. Take some time - this is an explorative task 😉


7. On the top bar of the window choose the Identify Features tool. Your cursor will change and include a small information icon when hovering over the map.


8. Click the point of the fountain and you will see the corresponding record in the database on the right hand side.

You now know how to visualize data from SAP HANA in QGIS. If you like, you can play a bit with the filters and change the base map. Again, this enthusiasm is not counting towards our 30 minutes!

 

⏳5m 👉 Pushing down Spatial Workload to SAP HANA Cloud

Last but not least, let's check, how we can execute some spatial workload on the database level and get the results to QGIS. Since all spatial processing functions on SAP HANA are part of the SQL syntax, the best way to go is to create an SQL view, which can be consumed in QGIS.

So, we need to open up the SAP HANA Database Explorer, create an SQL View and consume it in QGIS.

⏯ Walk-through: Creating a view for spatial clustering

Create an SQL view doing a hexagonal clustering and counting the number of POIs per cluster:

1. If you are not within the SAP BTP Cockpit looking at the tile with your SAP HANA Cloud instance, please repeat steps 4 & 5 from "Create HANA Cloud Trial Instance" above. You should see your created and running instance.


2. Click on Actions and Open in SAP HANA Database Explorer


3. You may be prompted for your user credentials. Use the same user, that you have setup in QGIS (i.e. DBADMIN).


4. Click on the the SQL symbol in the upper left corner to Open a new SQL Console.


5. Copy the following SQL statement into the SQL Console and execute:
   

   CREATE VIEW POI_DENSITY AS
   
   (
   
   	SELECT 
   
   		ST_ClusterId() AS cluster_id,
   
   		ST_ClusterCell() AS cluster_cell,
   
   		COUNT(*) AS count_pois
   
   	FROM "gis_osm_pois_free_1"
   
   	GROUP CLUSTER BY "geom" USING HEXAGON X CELLS 100
   
   )

   
   You can find more information on Spatial Clustering in the SAP HANA Spatial Reference.

⏯ Walk-through: Basic symbology in QGIS

Get the data into QGIS and apply some basic styling:

1. Open QGIS and add a base map via double-click (analogue to the previous step).


2. Locate SQL view POI_DENSITY in the browser (SAP HANA > hctrial > DBADMIN > POI_DENSITY) and double-click to add it to the map.


3. Right-click the POI_DENSITY layer and choose Properties. (Make sure to do this in the Layers tab rather than in the browser!)


4. In the following dialog, you can adapt the layer styling:
   
   
   
   1. Choose Symbology on the left side.
   
   
   2. On the very top change the drop-down value from Single Symbol to Graduated
   
   
   3. As a Value choose our database field COUNT_POIS
   
   
   4. Choose a suitable Color ramp (i.e. Reds)
   
   
   5. Change the Mode from Equal Count (Quantile) to Logarithmic Scale
   
   
   6. Determine the number of Classes (lower right corner; i.e. 😎
   
   
   7. Click the Classify button to create the color ranges.
   
   
   8. Confirm with OK
   
   
   
   


5. You should now see a heatmap-like visualization on your map. The hexagonal cells are colored according to the number of POIs in the cell.

The heatmap-like visualization makes it easy to spot the major cities around the SAP Headquarter: Karlsruhe, Mannheim and Heidelberg.

Stretch task: Add some transparency to the hexagonal cells

 

🏁 What's next?

Congrats! You have accomplished the setup of a playground for spatial analytics. I would be interested in your feedback regarding the time it took to get everything set up - just use the comments section below. I acknowledge that 30 minutes may be a challenging timeline if you're seeing SAP HANA and QGIS the first time, but I do hope that even with one or the other complication, you stood below 1 hour.

If you would like to dive into more sophisticated scenarios, I recommend checking out some material on Spatial Data Science and how to add the geospatial dimension to machine learning models. A good starting point is my article on Towards Data Science. Note that this is a high-level overview. Blogs with more detailed instructions are linked in the article.

Towards Data Science: The Impact of Geospatial Features on Machine Learning

To get to know more details about SAP HANA Spatial and Graph in particular, I recommend to watch the video recordings of SAP's Devtoberfest on Youtube. This series of 11 videos is the most complete recording available online, that I can think of.

Youtube: SAP HANA Multi-model @ Devtoberfest

Also, I regularly maintain a resource blog gathering all the community content around SAP HANA Spatial. It's worth taking a look and possibly finding the one or the other gem:

SAP HANA Spatial Resources: https://cutt.ly/saphanaspatial

 

Conclusion

You have learnt, how to upload spatial data to SAP HANA Cloud and visualize it using QGIS. Also you have created an SQL view, which applied a clustering algorithm on database level. You should now be equipped to build you own spatial analysis based on your own spatial data.

What you see in this blog is a rather technical walk-through to get up-to-speed with SAP HANA Spatial. When coming from a GIS background, you may ask, why to use SAP HANA in the first place? The basics tasks, that I have described, can easily be accomplished with any GIS client - with or without database persistency or push-down of spatial workload.

I would like to quote my dear colleague tom.turchioe, who outline that the major advantages of running your ArcGIS on SAP HANA are Productivity, Agility, Acceleration and Integration (with a strong emphasis on Integration 😊). While Tom's blog is specifically tailored to ArcGIS, the outlined qualities are valid irrespective of the actual GIS client.