Skip to Content
Technical Articles
Author's profile photo Witalij Rudnicki

Markers for geographical areas (with SAP HANA SQL)

This post has been triggered by this old Covid-19 map from WHO.

But the post itself will not be about coronavirus this time. I wrote about that in the past already: Quickly load Covid-19 data with hana_ml and see with DBeaver.

What caught my eye this time was that bubble chart for Croatia, or the fact that it seemed to be outside of the Croatian border.

We discussed using SAP HANA geospatial SQL finding central points already in SAP HANA, tell me where the center of Europe is.

Let’s check the center (centroid) of Croatia. I have World Borders dataset loaded in my SAP HANA, express edition, already, as described in Get to know your neighbors with SAP HANA.


I ran it in DBeaver, and indeed because of the shape of the country, the centroid is right outside of it.

Interesting, are there more countries like that?

WHERE SHAPE.ST_Centroid().ST_CoveredBy(SHAPE)=0

There are 49 accordingly to the World Borders data set. Another case similar to Croatia is Vietnam.

But mostly there are island countries, and even Great Britain happened to be one of these. Probably results would be different, should the geospatial data include territorial waters in the shape of countries.

Among eye-opening cases are The Gambia, where the center of the country is on the River Gambia. But this part of the river is outside of the country’s borderlines.

So, how to calculate the market for the country without placing it outside of its borders?

There is a spatial method ST_PointOnSurface that returns a point guaranteed to spatially be within the shape.

CASE WHEN SHAPE.ST_Centroid().ST_CoveredBy(SHAPE)=1 
	THEN SHAPE.ST_Centroid() 
	ELSE SHAPE.ST_PointOnSurface() 
END AS ST_Marker

Let’s verify. Now both markers — for Croatia and for Great Britain — are placed on their respective countries.

PS. That UK’s point is near Manchester. I am wondering if this is there DJ Adams lives ?

PPS. Btw, I saw that WHO updated its Covid19 map in the meantime, and the bubble chart now falls into proper shapes there.

Stay healthy, everyone ❤️
-Vitaliy (aka @Sygyzmundovych)

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Lee Oxley
      Lee Oxley

      I was having this issue, with Croatia and Norway, and your article has helped me quickly resolve it, thank you so much! Lee

      Author's profile photo Witalij Rudnicki
      Witalij Rudnicki
      Blog Post Author

      Thank you for taking time to write the comment and letting me know it was helpful, Lee Oxley!