Finding transcontinental countries with SAP HANA
Last week I had a pleasure of doing another SAP CodeJam on the topic of SAP HANA and geospatial data processing. This time in Istanbul.
My plane landed in Atatürk Airport, which is located in European side of Istanbul. The location for the event was in the office of Foriba in Maltepe district – on the Asian side of the city. So, I had a chance to try The Eurasia Tunnel for the first time when crossing underneath the Bosphorus strait back and forth.
Turkey is one of two countries quite famous for spanning two continents. But are there more?
Countries and continents shapes have been already loaded for some of my previous #GeospatialTuesday blogs, so let’s find out!
The subquery is first used to find all countries that have more than one intersection with different continent, and then plugged into the main query to provide a list.
select "CNTRY_NAME", "CONTINENT" from "TESTSGEO"."cntry00" t join "TESTSGEO"."continent" c on t.shape.ST_Intersects(c.shape) = 1 where "CNTRY_NAME" in (select b.CNTRY_NAME from "TESTSGEO"."continent" a join "TESTSGEO"."cntry00" b on b.shape.ST_Intersects(a.shape) = 1 group by b.CNTRY_NAME, b.SHAPE having count(*) > 1) order by 1,2; /* RESULT ;CNTRY_NAME ;CONTINENT 1 ;Azerbaijan ;Asia 2 ;Azerbaijan ;Europe 3 ;Bulgaria ;Asia 4 ;Bulgaria ;Europe 5 ;Chile ;Oceania 6 ;Chile ;South America 7 ;Colombia ;North America 8 ;Colombia ;South America 9 ;Egypt ;Africa 10;Egypt ;Asia 11;Gaza Strip ;Africa 12;Gaza Strip ;Asia 13;Georgia ;Asia 14;Georgia ;Europe 15;Greece ;Asia 16;Greece ;Europe 17;Israel ;Africa 18;Israel ;Asia 19;Kazakhstan ;Asia 20;Kazakhstan ;Europe 21;Panama ;North America 22;Panama ;South America 23;Papua New Guinea;Asia 24;Papua New Guinea;Oceania 25;Portugal ;Africa 26;Portugal ;Europe 27;Russia ;Asia 28;Russia ;Europe 29;South Africa ;Africa 30;South Africa ;Antarctica 31;Spain ;Africa 32;Spain ;Europe 33;Turkey ;Asia 34;Turkey ;Europe 35;United States ;North America 36;United States ;Oceania */
Wow! First – quite a lot!! Second – some can come as a surprise! Third – a surprise can be caused by a mistake. Unfortunately, as I checked the source file, the Asia-Europe border line is incorrect in one place. It is going along the border of Turkey, instead of along water channels. Garbage In – Garbage Out, and that’s why we have Bulgaria on the list.
But all others seem about right. Egypt spans Africa and Asia. Kazakhstan, Azerbaijan and Georgia span Asia and Europe. There is only 0.1% of Georgian population living in European part of the country.
Panama got on the list because its southern border goes along South America, accordingly to definitions in data files.
And then there are non-contiguous examples where Chile’s Isla de Pascua is in Oceania, or South African Marion Island is a part of Antarctica.
Next SAP CodeJam on Geospatial analytics with SAP HANA is on June 15th in Budapest. In the meantime, looking forward to see you at SAPPHIRENOW next week. If you are in Orlando, FL, then please stop by to say Hi! and to try some geospatial queries like this 🙂
‘Till next time,
-Vitaliy, aka @Sygyzmundovych