Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Get to Know Your Neighbors With SAP HANA

DZone's Guide to

Get to Know Your Neighbors With SAP HANA

Do you know what country has the longest land border with Germany? Read on to learn how to use SAP HANA Express and geospatial processing to answer this question.

· Big Data Zone ·
Free Resource

Hortonworks Sandbox for HDP and HDF is your chance to get started on learning, developing, testing and trying out new features. Each download comes preconfigured with interactive tutorials, sample data and developments from the Apache community.

Last month, I did a SAP CodeJam in Walldorf (this is where SAP is headquartered) on the topic of geospatial data with SAP HANA. One of my questions to local participants was, "What country has the longest land border with Germany?" And to my surprise, no one had an immediate answer. "France? Netherlands? Poland? Austria?" Participants were not sure.

OK, calling SAP HANA Express and geospatial processing to the rescue! I have created a new set of SAP HANA geospatial tutorials last week in case you missed them.

To answer the question, we need a dataset with geographical shapes of all countries. One of the most popular open datasets is World Borders from Thematic Mapping. But in this case, let's download the Countries shapefile from the same place we downloaded Continents for previous blogs, as this will help us with the exercises next week.

Once you have the file downloaded and uncompressed, load it with Spatial Reference System ID 1000004326 (planar GPS coordinates) using desktop upload in Eclipse (SAP HANA Studio) or the SQL statement below after uploading files to HANA's file system.

IMPORT "TESTSGEO"."cntry00" AS SHAPEFILE FROM '/usr/sap/HXE/home/Downloads/cntry00/cntry00' WITH REPLACE SRID 1000004326 THREADS 4;

Quick check of the loaded table:

select count(*) from "TESTSGEO"."cntry00";
--result should be 252

select CNTRY_NAME, SHAPE.ST_SRID() from "TESTSGEO"."cntry00"
where CNTRY_NAME = 'Germany';
/* result should be
CNTRY_NAME;SHAPE.ST_SRID()
Germany;1 000 004 326
*/

Now, let's find what neighbor has the longest border line with Germany! For that:

  • Join a table with itself using spatial predicate ST_Touches() to find countries sharing borders.
  • Calculate a line string representing a border between each two neighbors using the set method ST_Intersection()
  • Calculate the length of each shared border (the line string), but first, remember to convert that geometry to Spatial Reference System 4326 to do it on the Round Earth!
select 
b.CNTRY_NAME as "neighbour", 
b.shape.ST_Intersection(a.shape).ST_SRID(4326).st_Length('kilometer') as "border"
from "TESTSGEO"."cntry00"  a
join "TESTSGEO"."cntry00"  b on a."SHAPE".ST_Touches(b.shape) = 1
where a.CNTRY_NAME like 'Germany'
order by "border" desc;

And here is the result that was surprising to participants. The longest border was with Austria! And although these measurements are not exact (because of the map's scale), the ranking is still correct.

To participants'excuse, I must say I was wrong about neighbors of Poland, too! Poland has the longest border with Czech Republic, and not with Ukraine, as I had said.

The other interesting fact is that Germany has the largest number of neighbors in Europe. Check it with the following statement.

select a.CNTRY_NAME as "country", count(b.CNTRY_NAME) as "neighbours"
from "TESTSGEO"."cntry00"  a
join "TESTSGEO"."cntry00"  b on a."SHAPE".ST_Touches(b.shape) = 1
group by a.CNTRY_NAME
order by "neighbours" desc;

Russia has more neighbors, but then again, Russia is transcontinental country, which is the topic for another blog.

So, what is your country's neighbor with the longest (or the shortest) border?

Hortonworks Community Connection (HCC) is an online collaboration destination for developers, DevOps, customers and partners to get answers to questions, collaborate on technical articles and share code examples from GitHub.  Join the discussion.

Topics:
big data ,sap hana ,geospatial ,big data analytics ,data processing ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}