Visualize your spatial data in SAP HANA Database Explorer
It was almost a year since my last Geospatial Tuesday post. That time it was how-to 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 the new features that became available this month in SAP HANA Database Explorer in SAP HANA Cloud: Visualizing Spatial Data.
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 in 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 transferred completely into the Database Explorer, and therefore are truncated accordingly to the setting “Byte limit for Large Objects (LOBs)”.
Display central points of all 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 receive complete byte values of some shapes to display corresponding continents.
SELECT "CONTINENT", "SHAPE".ST_Simplify(0.2) FROM "TESTGEO"."world-continents";
for interactive maps, created by the Ukrainian developer Volodymyr Agafonkin from Kyiv 🇺🇦
-Vitaliy, aka @Sygyzmundovych