Visualize your spatial data in SAP HANA Database Explorer

It was almost a year since my last Geospatial Tuesday post. That time it was Import zipped shapefiles into SAP HANA Cloud using Database Explorer. But I still used an external tool called DBeaver to visualize data imported into SAP HANA Cloud. And before that I even used ASCII to display spatial shapes from SAP HANA!

This time, I would like to bring your attention to one of new features that became available this month in SAP HANA Database Explorer in SAP HANA Cloud: Visualizing Spatial Data.

Prerequisites

In this post I am using a trial account of SAP Business Technology Platform and Database Explorer in the US10 region. For other accounts, please check “Prerequisites” listed on https://help.sap.com/docs/HANA_CLOUD/a2cea64fa3ac4f90a52405d07600047b/302e72e822ea48b6a1c260119e41d8a7.html.

Spatial data for the example

To demonstrate this new capability of SAP HANA Database Explorer let me use the same data with continent shapes imported in the aforementioned post.

Please note that the shapes of continents themselves are too big to be read completely into the Database Explorer and are truncated accordingly to the setting “Byte limit for Large Objects (LOBs)”.

Display central points of continents

Let’s display central points of all continents in the table.

SELECT ST_UNIONAGGR("SHAPE".ST_Centroid())
FROM "TESTGEO"."world-continents";

Please note the use of the spatial aggregation method ST_UnionAggr to collect all points into the one geometry, as we can preview the result of only one cell in the result of the SQL query.

Double-click on the cell with a result or right-click and chose “View data” option. The spatial preview will be opened automatically, but you might need to resize the dialog window and re-zoom the output to see a result.

It is interesting to note that there are two markers placed on Australia: for the Australia continent itself, but as well for Oceania, that is a separate continent accordingly to the data we imported.

Simplify shapes to view continents

To be able to see continents I can use a new spatial method ST_Simplify, that I have not used in any of my previous examples yet: https://help.sap.com/docs/HANA_CLOUD_DATABASE/bc9e455fe75541b8a248b4c09b086cf5/46263051440c45b9a603165e4a34299a.html.

Already for the method’s epsilon value equal to 0.2 we can get complete byte value of shapes to  display continents.

SELECT "CONTINENT", "SHAPE".ST_Simplify(0.2)
FROM "TESTGEO"."world-continents";

It is nice to notice the use of Leaflet — a very popular open-source JavaScript library
for interactive maps, created by the Ukrainian developer Volodymyr Agafonkin from Kyiv 🇺🇦


Best regards,
-Vitaliy, aka @Sygyzmundovych