SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SAP HANA Modeling
+
-
Spatial Processing

Calculate Distance Between 2 Cities

In previous article SAP HANA Spatial Processing, we explained main features of Spatial processing in HANA. We also explained various methods and geometry types in the article Accessing and Manipulating Spatial Data.

Now we will see an example on calculating distance between 2 cities.

SAP HANA Spatial Processing

Create table and insert city coordinates

Let us create a table Cities, where we store the city name and its coordinates.
SET SCHEMA "<YOUR_SCHEMA>"
DROP TABLE CITY;
CREATE COLUMN TABLE CITY( 
ID BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, /* To automatically fill the Id column */ 
CITY_NAME NVARCHAR(40) NULL, 
CITY_COORDINATE ST_GEOMETRY (4326));

Note that the data type of column CITY_COORDINATE is ST_GEOMETRY.
Now we will insert record of 2 cities, New York and Los Angeles. I have used following site to get the city coordinates.
New York - http://dateandtime.info/citycoordinates.php?id=5128581
Los Angeles - http://dateandtime.info/citycoordinates.php?id=5368361

INSERT INTO CITY (CITY_NAME, CITY_COORDINATE) VALUES ('NEW YORK', new ST_POINT ('POINT (74.0021 40.4251)')); 
INSERT INTO CITY (CITY_NAME, CITY_COORDINATE) VALUES ('LOS ANGELES', new ST_POINT ('POINT (118.1437 34.0308)'));


Calculate Distance between New York and Los Angeles

Now we have data so now let us create stored procedure to calculate the distance between the 2 cities New York and Los Angeles.  Here we have used ST_Distance method to calculate the distance between 2 points. For more information on this method check help.sap.com

Copy following procedure code and execute it.

SET SCHEMA "<YOUR_SCHEMA>";
DROP PROCEDURE CALCULATE_DISTANCE;
CREATE PROCEDURE CALCULATE_DISTANCE 
( In OriginCity nvarchar(50), In DestinationCity nvarchar(50), In UnitOfMeasure nvarchar(50)) 
LANGUAGE SQLSCRIPT AS 
BEGIN 
DECLARE STRING_STR varchar(2000); 


STRING_STR :=  'select A.CITY_NAME AS "Origin City",B.CITY_NAME AS "Destination City", A.CITY_COORDINATE.ST_Distance(B.CITY_COORDINATE,' || '''' || :UnitOfMeasure || '''' || ') AS "Distance" 
             from CITY A,CITY B where A.CITY_NAME = ' || '''' || :OriginCity || '''' || ' and B.CITY_NAME = ' || '''' ||  :DestinationCity || '''';

EXECUTE IMMEDIATE (:STRING_STR); 
END;

Let us call the procedure using below script.
CALL CALCULATE_DISTANCE ('NEW YORK', 'LOS ANGELES', 'kilometre'); 

The output should be like
SAP HANA Spatial Processing

Note: The unit parameter passed to this procedure (for example kilometre) must be a value in  SYS.ST_UNITS_OF_MEASURE table with UNIT_TYPE = ‘LINEAR’.
SELECT * FROM SYS.ST_UNITS_OF_MEASURE WHERE UNIT_TYPE = 'LINEAR'
SAP HANA Spatial Processing

For example, you can also call the procedure using ‘yard’.
CALL CALCULATE_DISTANCE ('NEW YORK', 'LOS ANGELES', 'yard'); 





Support us by sharing this article.



Explore More
Close X
Close X

Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy