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.

Keep exploring!

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