Finding extreme points of Europe with SAP HANA

And here by “extreme points” we mean “the geographical points that are farther north, south, east or west than any other location in Europe” [Wikipedia]. Sorry if you expected to find locations for some extreme sports in this blog 🙂

In my previous blog I used geospatial functions ST_Xmin() and ST_XMax() in SAP HANA (using free Express edition) to get Europe’s most West and most East points’ longitude. But what if I want to find exact coordinates, including latitude, of these specific locations making extreme points of the European continent?

Here is my approach, but please share yours if different.

In that previous blog I used ST_Envelope() function to return the bounding rectangle for the shape of the Europe. If I would combine them together on the single picture [using an export to SVG and rendering in the web browser], then the result would be like this with “envelope” shape in blue and the continent’s shape in yellow.

Locations were boundaries of these two geometries touch each other would be our extreme points! The geospatial function ST_Intersection() should come handy in this case, as it returns the geometry representing the point set intersection of two geometries.

In our case we need to remember that we are interested only in the intersection of the rectangle’s boundary, while ST_Envelope() returns a polygon [the whole blue area underneath the shape of Europe on the picture before]. So, I need to get boundary using geospatial function ST_Boundary() as the name suggests.

SELECT CONTINENT, SHAPE.ST_Envelope().ST_Boundary().ST_Intersection(SHAPE).ST_asWKT() as ExtremePoints
FROM "TESTGEO"."continent" WHERE CONTINENT = 'Europe';

So, once again, to read the query properly: I take the shape of European continent (with islands), then take a boundary of the rectangle around it, and check the intersection points of that rectangle with the shape of the continent itself.

And the result is the collection of four points with their decimal latitudes and longitudes!

MULTIPOINT ((-31.289030075 39.4109687805),(24.92805481 34.930549622),(59.164993286 81.851928711),(68.931365967 76.78276062))

Let me add them in red to SVG picture to visualize the complete picture.

Not all of these calculated extreme points are exactly the points as described in the Wikipedia article mentioned at the beginning of this blog, but then – as I have mentioned in the previous posts – the quality of the output reflects the quality of the input, i.e. the open data maps I have uploaded to my HANA instance.

Now, my biggest dream is to visit those southernmost and westernmost points. I promise to share pictures once I visit them! 😀

-Vitaliy, aka @Sygyzmundovych

PS. Are you attending SAP TechEd in Barcelona in November? Stop by AppSpace in the Developers Garage at the show floor to say Hi and to try some of SAP HANA hands-on exercises: with predictive analytics and native development in XSA.