Finding central points of continent mainlands (with SQLScript spatial support)

Last week I told my teammate Abdel DADOUCHE about using SAP HANA geospatial capabilities to calculate the center of continents, and that based on those calculations the centroid of Europe is in Belarus and the middle point is on some isle in the Baltic Sea. “Accordingly to what borders?” he replied. That’s a good question, as even today there are discussions going on where and if the border between Asia and Europe should be placed. That “border remains a historical and cultural construct, defined by convention“, but on the map I used so far in SAP HANA it is real and defined. Let us use that map in the current exercise as well.

So far for my previous calculations I took the European continent as one geometry, which consists of the mainland and numerous islands. How many single polygons constitute European continent? In the map I loaded (and you hopefully too) there are 296 polygons of Europe accordingly to geospatial function ST_NumGeometries().

SELECT "SHAPE".ST_NumGeometries() FROM "TESTGEO"."continent" WHERE CONTINENT='Europe';
--The result is 296

Only one out of these 296 geometries is the continent’s mainland; all others are islands. And I want to find the center point of the mainland part of Europe. Obviously it will be the polygon with the biggest area, so I need to iterate through all these 296 parts to find the biggest one. This is where SQLScript with its support for spatial in SAP HANA comes handy.

Here is my code (and please feel free to comment how you would improve it!). The procedure returns three geometries: mainland, centroid and middle point – all formatted as a GeoJSON string, and not WKT this time.

SET SCHEMA TESTGEO;
DROP PROCEDURE "TESTGEO"."CONTINENT_MAINLAND_CENTER_GEOJSON";
CREATE PROCEDURE "TESTGEO"."CONTINENT_MAINLAND_CENTER_GEOJSON" 
(IN continent_name NVARCHAR (20), OUT nr_shapes INT, OUT mainland CLOB, OUT point_centroid CLOB, OUT point_middle CLOB)
 LANGUAGE SQLSCRIPT 
 READS SQL DATA 
 AS
 whole_continet ST_GEOMETRY;
 BEGIN
    DECLARE single_shape, theshape, temp_shape ST_GEOMETRY;
    DECLARE shape_area, shapei_area DECIMAL = 0;    
    DECLARE i INTEGER;
    select "SHAPE" into whole_continet FROM "TESTGEO"."continent" WHERE CONTINENT=:continent_name;
    nr_shapes := whole_continet.ST_NumGeometries();
    FOR i IN 1..nr_shapes DO
    	single_shape := :whole_continet.ST_GeometryN(i);
    	shapei_area := single_shape.ST_Area();
		IF shape_area < shapei_area
		THEN
			shape_area := shapei_area;
			theshape := single_shape;
		END IF;
    END FOR;
	--Mainland shape as a GeoJSON
	mainland := theshape.st_asGeoJSON();
	--Calculate centroid
	temp_shape := theshape.ST_Centroid();
	point_centroid := temp_shape.st_asGeoJSON();
	--Calculate midpoint
	temp_shape := theshape.ST_Envelope();
	temp_shape := temp_shape.ST_Centroid();
	point_middle := temp_shape.st_asGeoJSON();
 END;

Now let’s call the procedure with Europe as a parameter.

CALL "TESTGEO"."CONTINENT_MAINLAND_CENTER_GEOJSON"(
	CONTINENT_NAME => 'Europe',
	NR_SHAPES => ?,
	MAINLAND => ?,
	POINT_CENTROID => ?,
	POINT_MIDDLE => ?
);

And get the results.

Using GeoJSON in the output allows us to easily place results on the map for visualization using services like GeoJSONLint.

Interesting… So for the mainland both – the centroid and the middle point – are in Belarus!

Regards,

-Vitaliy, aka @Sygyzmundovych

This and other exercises were part of the very first SAP CodeJam on the topic of professing geospatial data with SAP HANA in Walldorf, Germany. I’d like to thank as well Markus Fath and Christian Schuetz from SAP HQ office joining to share some more real life use cases and demos of SAP HANA geospatial processing capabilities!

The next SAP CodeJam on this topic will be at WSB university in Wrocław on February 16th. It is open to everyone, not just students: https://www.eventbrite.com/e/sap-codejam-wroclaw-registration-41952689624. Thanks WSB for hosting and Former Member for organizing!

Interested in the topic and would like to host such a CodeJam in your home town? Just follow these steps to request and to host one.

[Picture by Fabian Lehmann]