Oceans, open data, Round Earth and SAP HANA

This month people around our blue planet celebrated The World Oceans Day to honor the ocean, which connects us all. It’s been 10 years since the United Nations officially recognized 8 June as World Oceans Day. At that day I was travelling back home from Orlando after impressive SAPPHIRENOW conference, so I did not get a chance to publish this.

Our planet is called the Blue Planet, because most of its surface is covered by water. But what percentage is it? Can we use SAP HANA’s spatial to calculate that? Yes, we can! And it will help us to learn a few things along the way. This example works on express edition of SAP HANA 2.0 SP3, and will not work on SAP HANA 1.0 SP12 that we used in some of previous #GeostialTuesday blog posts.

We will need a shapefile with oceans data (e.g. open data from Natural Earth 1:10m Physical Vectors downloads) to calculate the surface of water globally, and then divide it by the calculated Earth’s surface. Sounds simple, but…

First, checking the dataset with a tool like mapshaper we can find that its upper boundary is 90.00000000000006 degrees North.

As you know North and South poles are at 90 degrees latitude. So, this dataset has an error. SAP HANA will not be able to load it and will return error.

The mapshaper has a command affine to transform coordinates, like shifting them in our example.

Coordinates are fixed and we could export the data back into the shapefile now. But there is one more challenge with calculations on the Round Earth data, which you can find reading carefully “How Flat-Earth and Round-Earth Representations Work” in “Advanced Spatial Topics” chapter of SAP HANA Spatial documentation:

Polygon rings are automatically reordered if they are not in counter-clockwise orientation. This prevents polygons covering more than one hemisphere as well.

In Round Earth calculations, like using Spatial Reference System 4326, we cannot have polygons bigger than half of the Earth. Yet the single ocean polygon is bigger than that, so for the sake of correct calculations we need to split it. Mapshaper can help with this as well using a series of steps:

1. Command clip bbox=-180,-90,0,90 name=west_hemi to clip ocean on the Western hemisphere
2. Command each 'HEMISPHERE="West"' to add an attribute HEMISPHERE with the value West to the new layer
3. Once again add a file ne_10m_ocean.zip and import it; make sure it is an active layer in the mapshaper
4. Command affine shift=0,-0.00000000000006 on data from re-imported file ne_10m_ocean.zip
5. Command clip bbox=0,-90,180,90 name=east_hemito clip ocean on the Eastern hemisphere
6. Command each 'HEMISPHERE="East"'
7. At this moment we have two separate layers – each representing a multipolygon of an ocean on one of the hemispheres, so use the command merge-layers target=west_hemi,east_hemi name=ne_10m_ocean_2halfsto combine them into a single layer with two polygons
8. Export to a shapefile ne_10m_ocean_2halfs.zip

Now we can just import Esri shapefile ne_10m_ocean_2halfs.zip to a table "NATURAL_EARTH"."ne_10m_ocean_2halfs" using SRID=4326, and use it to find areas:

select sum("SHAPE".ST_Area('kilometer')) as "Ocean Area",
new ST_Polygon('POLYGON((-180 -90, -180 90, 0 90, 0 -90 ,-180 -90 ))', 4326).st_area('kilometer') * 2 as "Earth Total Area"
from "NATURAL_EARTH"."ne_10m_ocean_2halfs";

The answer is ~363,223,823 sq. km for the area of the ocean, and ~510,065,621 sq. km for the total Earth surface. Let’s compare these results to e.g. https://hypertextbook.com/facts/1997/EricCheng.shtml. They are really close.

Please note the way we calculated the total Round Earth area accordingly to SRID=4326: two times the area of one of hemispheres, as that spatial reference assumes ideal shape of the planet.

So, to get an answer what percents of the Blue Planet’s surface is water, let’s execute…

select 100 * sum("SHAPE".ST_Area('kilometer')) /
(new ST_Polygon('POLYGON((-180 -90, -180 90, 0 90, 0 -90 ,-180 -90 ))', 4326).st_area('kilometer') * 2) as "% of Total"
 from "NATURAL_EARTH"."ne_10m_ocean_2halfs";

And the answer is ~71.2%.

U.S. National Oceanic and Atmospheric Administration announce the whole month of June to be the National Ocean Month. But we should not stick only to one day or even only one month in a year to remember how important ocean ecosystem is for us and for our planet!

Please let me know what you would modify or improve in this approach to calculate the same! Otherwise, try to repeat this exercise, but splitting ocean into Northern and Southern hemispheres and comparing the result.

‘Till next #GeospatialTuesday,
-Vitaliy, aka @Sygyzmundovych