Standard service - a fixed amount of work that is performed at a fixed price.  Post Service

  

Thursday, 01 November 2018 08:02

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

Written by  http://www.saphanacentral.com/2018/11/implementing-hana-information-view-to.html
Rate this item
(0 votes)
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 –

SAP HANA Geo-Spatial Content, SAP HANA Tutorial and Material, SAP HANA Certifications, SAP HANA Study Materials

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.

SAP HANA Geo-Spatial Content, SAP HANA Tutorial and Material, SAP HANA Certifications, SAP HANA Study Materials

Here you can see table under BEST schema

SAP HANA Geo-Spatial Content, SAP HANA Tutorial and Material, SAP HANA Certifications, SAP HANA Study Materials

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

SAP HANA Geo-Spatial Content, SAP HANA Tutorial and Material, SAP HANA Certifications, SAP HANA Study Materials

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.

SAP HANA Geo-Spatial Content, SAP HANA Tutorial and Material, SAP HANA Certifications, SAP HANA Study Materials

Write the select statement as shown below
Code for your reference:
SELECT LongLat.ST_AsEWKT () FROM CITY;

SAP HANA Geo-Spatial Content, SAP HANA Tutorial and Material, SAP HANA Certifications, SAP HANA Study Materials

and execute it.

Result:

SAP HANA Geo-Spatial Content, SAP HANA Tutorial and Material, SAP HANA Certifications, SAP HANA Study Materials

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-

SAP HANA Geo-Spatial Content, SAP HANA Tutorial and Material, SAP HANA Certifications, SAP HANA Study Materials

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.

SAP HANA Geo-Spatial Content, SAP HANA Tutorial and Material, SAP HANA Certifications, SAP HANA Study Materials

Here we can see the procedure under BEST schema

SAP HANA Geo-Spatial Content, SAP HANA Tutorial and Material, SAP HANA Certifications, SAP HANA Study Materials

CALL statement to get output in KM
Code for your reference:
CALL SP_CALC_DISTANCE2 (78.4744400, 17.3752800,’KM’)

SAP HANA Geo-Spatial Content, SAP HANA Tutorial and Material, SAP HANA Certifications, SAP HANA Study Materials

Click on execute and Output in KM:

SAP HANA Geo-Spatial Content, SAP HANA Tutorial and Material, SAP HANA Certifications, SAP HANA Study Materials

CALL statement to get output in meters
Code for your reference:

CALL SP_CALC_DISTANCE2 (78.4744400, 17.3752800,’METERS’)

Output in Meters:

SAP HANA Geo-Spatial Content, SAP HANA Tutorial and Material, SAP HANA Certifications, SAP HANA Study Materials
Read 61 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.