SAP HANA: Calculating distance between 2 cities using Geo-Spatial functions

[former_member182302]

Hi Folks,

I got into one of the requirements where we were supposed to get the nearest cities (by distance) based on the user location. This is a simple requirement which has already been done in multiple ways.

Having never used the Geo spatial functions before getting myself started in learning them and blogging here to share my initial experiences.

Let take an example of how to calculate distance between 2 cities.

Let us create a table Cities, where we store the city name and its Coordinates.

CREATE COLUMN TABLE CITY(

Id BIGINT not null primary key generated by default as IDENTITY, /* To automatically fill the Id column */

City NVARCHAR(40) NULL,

LongLat ST_GEOMETRY (4326)) /* Using ST_GOEMETRY shape which is a super set to load points information */

Some observations while creating the table:

1) ID Column:

Here I used Identity column to generate the numbers for the ID column, you can see more details about it in the below blog mentioned by Lars:

Quick note on IDENTITY column in SAP HANA

2) Longitude & Latitude points:

I have loaded the Latitude and Longitude details with the information I got from this website: Geographic coordinates of Hyderabad, India. Latitude, longitude, and elevation above sea level of Hy...

3) ST_GEOMETRY:

We are using this data type ST_GEOMETRY to load our coordinates for the city.

SRID Value 4326:

Spatial reference identifier (SRID) and that the 4326 refers to the WGS84 standard which is commonly used.

Now let us load the data:

insert into CITY (City,LongLat) values('Hyderabad', new ST_POINT('POINT(78.4744400 17.3752800)'));

insert into CITY (City,LongLat) values('Vishakapatnam', new ST_POINT('POINT(83.3000000 17.7000000)'));

Note: While Inserting also we can mention the SRID value as shown below but it will not make any effect and will remain as 4326 only ( because we created with 4326 as reference while creating the table ) as shown below. With 4326 and if we try to calculate the distance then it would give the result in metres.

If we had create the table like below :

CREATE COLUMN TABLE CITY(

Id BIGINT not null primary key generated by default as IDENTITY, /* To automatically fill the Id column */

City NVARCHAR(40) NULL,

LongLat ST_GEOMETRY) /* Using ST_GOEMETRY shape which is a super set to load points information */

And you have used the below insert statements:

insert into CITY (City,LongLat) values('Hyderabad', new ST_POINT('POINT(78.4744400 17.3752800)',4326));

insert into CITY (City,LongLat) values('Vishakapatnam', new ST_POINT('POINT(83.3000000 17.7000000)',4326));

Still the SRID will refer to the default value i.e 0 as shown below:

SELECT LongLat.ST_AsEWKT() FROM CITY;

Hence we are using 4326 as reference while creating itself.

OK ! now we have data so now let us create stored procedure to calculate the distance between the 2 cities Hyderabad and Vishakapatnam. And also convert the distance into KM's or Metres as required.

Procedure Code:

CREATE PROCEDURE SP_CALC_DISTANCE

( In Latitude DECIMAL(18,10), In Longitude DECIMAL (18,10), In Convesion NVARCHAR(10))

LANGUAGE SQLSCRIPT AS

BEGIN

DECLARE STRING_STR varchar(200);

/* Converting the Metres to KM */

IF :Convesion = 'KM'

THEN

EXECUTE IMMEDIATE ('select A.City AS "Origin City",B.City AS "Destination City"

,A.LongLat.st_distance(B.LongLat)/1000 AS "Distance(KM)"

from CITY A,CITY B

where A.id = 1 and B.id = 2');

   

ELSE

EXECUTE IMMEDIATE ('select A.City AS "Origin City",B.City AS "Destination City",

A.LongLat.st_distance(B.LongLat) AS "Distance(meters)"

from CITY A,CITY B

where A.id = 1 and B.id = 2');

END IF;   

/* Calculating the distance from the location points given in the input against the table */

  

STRING_STR:= 'SELECT NEW ST_Point(''POINT

(' || :Latitude ||' ' || :Longitude || ')'',4326).ST_Distance(LongLat)/1000 AS "Distance(KM)" FROM CITY

WHERE id = 2';

         

EXECUTE IMMEDIATE ( :STRING_STR);

   

END;  

CALL SP_CALC_DISTANCE (78.4744400,17.3752800,'KM')     

Output:

CALL SP_CALC_DISTANCE (78.4744400,17.3752800,'Metres')     

Output:

Note that the distance you are seeing is the distance between those 2 points.

Am mentioning the below referenced documents which has helped me to learn and should also help you guys in further exploring.

References:

Hana SPS07, the spatial engine and taking a byte out of the Big Apple

Hana SPS07 and spatial data

Reverse Geocode your HANA Data with this XS JavaScript Utility

Serving up Apples & Pears: Spatial Data and D3

Well My first exercise on spatial , we got some results.  Hope you enjoyed the blog and you will join in my journey of learning this.

Your's

Krishna Tangudu :smile: