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.

SELECT SHAPE, SHAPE.ST_Centroid()
FROM TM.WORLD_BORDERS
WHERE ISO2='HR'

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?

SELECT SHAPE, SHAPE.ST_Centroid()
FROM TM.WORLD_BORDERS
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.

SELECT ISO3, 
CASE WHEN SHAPE.ST_Centroid().ST_CoveredBy(SHAPE)=1 
	THEN SHAPE.ST_Centroid() 
	ELSE SHAPE.ST_PointOnSurface() 
END AS ST_Marker
FROM TM.WORLD_BORDERS;

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)