Finding transcontinental countries with SAP HANA

[Vitaliy-R]

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