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

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 Hyderabad

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

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 ðŸ™‚

This is amazing work Krishna. This is the first time I am hearing of 'Geo-Spatial functions' and yet there have been documents and blogs around. Thanks for inviting me to read your blog.

Benedict

Thanks Benedict for the feedback. ðŸ™‚

Regards,

Krishna Tangudu

Thanks Krishna,

It's excellent blog !!

Shambhu Nath

Thanks Shambhu

Regards,

Krishna Tangudu

Hi Krishna,

Great blog, Thanks for putting it all together ðŸ™‚

Regards

Kumar ðŸ™‚

Thanks Kumar.

Regards,

Krishna Tangudu

Good piece of work. By the way the current 300 course also covers a topic about GEO and using the HANA functions for it.

Thanks Henk. Good to know HA300 covers this as well now.

Regards,

Krishna Tangudu

Super!!!!, I will implement this and revert you if i need any help.. great info keep it up .

Thanks Ganesh ðŸ™‚

Nice Try Krishna and thanks for sharing with audience.

Hi Krishna,

Great blog. I am a newbie to Geospatial engine in HANA. I tried to add the geospatial data using insert statements as mentioned in the HANA guide for GIS.. for example

intospatialShapesvalues(1, NEW ST_POLYGON('POLYGON((1.0 2.0, 3.1 3.2, 4.1 3.2, 1.0 2.0))'));this statement works fine and inserts the data into the spatialShaoes table. However, when I manually add the next insert statement

intospatialShapesvalues(2, new ST_POLYGON('POLYGON((1.0 2.0, 3.0 3.0, 2.0 4.0, 4.0 4.0))'));it throws me the error -

SAP DBTech JDBC: [266]: inconsistent datatype: Failed to parse from WellKnownText at function __st_polygon__()

the default spatial reference system 0 (Euclidean space) is being used. Is there a format as to what input goes into the arguments?? Please help.

Hi Jain

The second polygon is not closed. That's why we reject it. The start and endpoint must be the same. If you want to use a differet SRS you have to use the second parameter which is optional:

SELECT new ST_POLYGON('POLYGON((1 2, 3 3, 2 4, 4 4, 1 2))')

fromdummy;SELECT new ST_POLYGON('POLYGON((1 2, 3 3, 2 4, 4 4, 1 2))', 0)

fromdummy;SELECT new ST_POLYGON('POLYGON((1 2, 3 3, 2 4, 4 4, 1 2))', 1000004326)

fromdummy;SELECT new ST_POLYGON('POLYGON((1 2, 3 3, 2 4, 4 4, 1 2))', 4326)

fromdummy;Best regards,

Hinnerk

Krishna,

this is a nice way to showcase some of the great functionality in HANA. I am also working on a similar scenario... i was able to find that the margin of error is low when i am computing a distance between two points (similar to your scenario from hyd and vishakatapam), however when i am increasing the distance between two points and validating that distance with a google map or even bing map... the distance margin of error increases tremendously such as if i select point one in dallas, tx and point two in boston, ma. i am also wondering if anyone one knows whether or not the HANA geo-spatial functions use a direct route, a driving route, or some other type of route due to the different distance results. anyways, i like this blog and thank you for the great intro to geo-spatial for me. keep up the great work!

ST_Distance is calculating the shortest way between two points, not a driving route or something else.

For distance and area calculations the spatial reference system (SRS) is very important. Calculating distance on a projected reference system (planar world) leads to distortion. For accurate distance or area calculations, you should use a round earth model like WGS84 which has the SRID 4326.

Hinnerk, thank you for your response. is there a url that you know of with official documentation for these terms and definitions. i dont think the geo spatial guide mentioned this and i would like to read more about the differences between all these terms.

thank you again,

Sergio

Hinnerk,

i spoke too soon.. it actually says exactly what you mentioend.

thank you again

Sergio

Spatial Reference Systems (SRS) and Spatial Reference Identifiers (SRID) - SAP HANA Spatial Reference - SAP Library

More detailed blog post about this and other spatial topics are planned...