Push Down a Spatial Workload to SAP HANA Database in SAP HANA Cloud

Follow the steps to create an SQL view doing a hexagonal clustering and counting the number of POI’s (points of interest) per cluster:

1. 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.

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 SQL icon 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.

Follow the steps to bring the data into QGIS:

1. Open QGIS and add a base map via double-click.

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

   

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

2. In the following dialog, you can adapt the layer styling. Choose Symbology on the left side.

3. On the very top, change the drop-down value from Single Symbol to Graduated.

4. As a value, choose our database field COUNT_POIS.

5. Choose a suitable Color ramp (i.e. Reds).

6. Change the Mode from Equal Count (Quantile) to Logarithmic Scale.

7. Determine the number of Classes in the lower right corner, i.e. 8.

8. Click the Classify button to create the color ranges.

9. 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?

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 Devtoberfest on 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 ArcGIS on SAP HANA Cloud. While it is specifically tailored to ArcGIS, the outlined qualities are valid irrespective of the actual GIS client.

Follow our tag in the SAP Community to stay up-to-date on the latest updates and newest content! For more learning materials on SAP HANA Cloud, click here.