Implementing HANA Information view to calculate distance between Cities Using Geo-Spatial Functions

This blog is intended to share my experiences on Geo-Spatial Functions implementation in SAP HANA. One of the implementation is implementing Information view to calculate distance between Cities Using Geo-Spatial Functions which I am sharing today.

Background: Geo- Spatial function used geospatial data which stored in a geographic information system (GIS). The geospatial data needs to be combined with data from the internet and sensor data, data from business partner to implement point to point implementation, to manage coordinates of different stores, warehouse, utilities of an organization, business for real-time analysis, reporting, forecasting, threat detection.

In the below scenario we will show how to Implementing Information view to calculate distance between Cities Using Geo-Spatial Functions

Let us create a table Cities, where we store the city name and its Coordinates.
Go to catalog -> right click on schema  -> open SQL console

Create a table CITY, where we store the city name and its Coordinates.

Write the code as shown in below screenshot –

Giving you the code for your reference:

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 */

Click on execute

Here it is successfully executed.

Here you can see table under BEST schema

Some observations while creating the table:

1) ID Column:

Identity column to generate the numbers for the ID column

2) Longitude & Latitude points:

For different areas we have different latitude and longitude point.

Here I have taken latitude and longitude points of Hyderabad and Vishakhapatnam

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.

To insert the data into that table

Write the below code as shown in below screenshot

Code for your reference:

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)’));

Click on execute and it will successfully executed.

Write the select statement as shown below

Code for your reference:

SELECT LongLat.ST_AsEWKT () FROM CITY;

and execute it.

Result:

Now we have data so now let us create stored procedure to calculate the distance between the 2 cities Hyderabad and Visakhapatnam. And also convert the distance into KM’s or Meters as required.

To create procedures write the code as shown in below screenshot-

Code for your reference:

CREATE PROCEDURE SP_CALC_DISTANCE2

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

LANGUAGE SQLSCRIPT AS

BEGIN

DECLARE STRING_STR varchar (200);

/* Converting the Meters to KM */

IF: Conversion = ‘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;

Click on execute and it will successfully executed.

Here we can see the procedure under BEST schema

CALL statement to get output in KM

Code for your reference:

CALL SP_CALC_DISTANCE2 (78.4744400, 17.3752800,’KM’)

Click on execute and Output in KM:

CALL statement to get output in meters

Code for your reference:

CALL SP_CALC_DISTANCE2 (78.4744400, 17.3752800,’METERS’)

Output in Meters: