Push Down a Spatial Workload to SAP HANA Database in SAP HANA Cloud
- How to create an SQL view using hexagonal clustering
- How to add an SQL view into QGIS
- How to apply basic styling to the SQL view on base map
- Ensure that your SAP HANA database instance within your SAP HANA Cloud trial account is running before you proceed.
- You have completed the visualization of spatial dataset on a base map. If not, complete the tutorial Visualize Spatial Dataset on SAP HANA database in SAP HANA Cloud.
This tutorial group will comprise of the following tasks:
Install SAP HANA ODBC Driver
Connect QGIS to SAP HANA Cloud
Upload your first spatial dataset
Visualize data on a base map
Pushing down the spatial workload to SAP HANA database in SAP HANA Cloud
This tutorial will cover the sixth task.
Finally, let’s check how to execute spatial workloads on the database level and get the results to QGIS. Since all spatial processing functions on SAP HANA Cloud are part of the SQL syntax, the best way to go is to create a SQL view which can be consumed in QGIS.
So, you need to open the SAP HANA Database Explorer, create an SQL View and consume it in QGIS.
- Step 1
Follow the steps to create an SQL view doing a hexagonal clustering and counting the number of POI’s (points of interest) per cluster:
Go to the SAP BTP cockpit and navigate to your SAP HANA database instance in SAP HANA Cloud. You should see your created and running instance.
Click on Actions and Open in SAP HANA Database Explorer.
You may be prompted for your user credentials. Use the same user, that you have setup in QGIS (i.e. DBADMIN).
Click on the SQL icon in the upper left corner to open a new SQL Console.
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 )
- Step 2
Follow the steps to bring the data into QGIS:
Open QGIS and add a base map via double-click.
Locate SQL view
POI_DENSITYin the browser (
POI_DENSITY) and double-click to add it to the map.
- Step 3
POI_DENSITYlayer and choose Properties. Make sure to do this in the Layers tab rather than in the browser!
In the following dialog, you can adapt the layer styling. Choose
Symbologyon the left side.
On the very top, change the drop-down value from Single Symbol to Graduated.
As a value, choose our database field
Choose a suitable Color ramp (i.e. Reds).
Change the Mode from Equal Count (Quantile) to Logarithmic Scale.
Determine the number of Classes in the lower right corner, i.e. 8.
Click the Classify button to create the color ranges.
Confirm with OK.
You should now see a heatmap-like visualization on your map. The hexagonal cells are colored according to the number of POI’s in the cell.
The heatmap-like visualization makes it easy to spot the major cities around the SAP Headquarter: Karlsruhe, Mannheim and Heidelberg.
As a challenge, why don’t you try to add some transparency to the hexagonal cells?
- Step 4
Congratulations! You have accomplished the setup of a playground for spatial analytics. If you would like to dive into more sophisticated scenarios, check out some material on Spatial Data Science and how to add the geospatial dimension to machine learning models.
A good starting point is this article on Towards Data Science. Note that this is a high-level overview. Blogs with more detailed instructions are linked in the article.
To get to know more details about SAP HANA Spatial and Graph in particular, watch the video recordings of SAP’s
Devtoberfeston YouTube. This series of 11 videos is the most complete recording available online.
Also, read this blog gathering all the community content around SAP HANA Spatial. What you see in this blog is a rather technical walk-through to get up-to-speed with SAP HANA Spatial. If you are coming from a GIS background, you may want to know why you should use SAP HANA Spatial over any other GIS client – with or without database persistency or push-down of spatial workload.
Read this blog outlining the major advantages of running your
ArcGISon SAP HANA Cloud. While it is specifically tailored to
ArcGIS, the outlined qualities are valid irrespective of the actual GIS client.
- Step 5
You are creating an SQL view using hexagonal clustering and counting the number of POIs per cluster:
(Fill in the blank)
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_______ X CELLS 100