Skip to Content

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.

Screen Shot 2014-07-09 at 2.14.40 PM.png

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;

Screen Shot 2014-07-09 at 2.08.59 PM.png

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:

Screen Shot 2014-07-09 at 2.48.13 PM.png

Screen Shot 2014-07-09 at 2.48.30 PM.png

CALL SP_CALC_DISTANCE (78.4744400,17.3752800,’Metres’)     

Output:

Screen Shot 2014-07-09 at 2.50.50 PM.png

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 🙂

To report this post you need to login first.

18 Comments

You must be Logged on to comment or reply to a post.

  1. Benedict Venmani Felix

    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

    (0) 
  2. Akhilesh Jain

    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

    into spatialShapes values(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

    into spatialShapes values(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.

    (0) 
    1. Hinnerk Gildhoff

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

      SELECT new ST_POLYGON(‘POLYGON((1 2, 3 3, 2 4, 4 4, 1 2))’, 0) from dummy;

      SELECT new ST_POLYGON(‘POLYGON((1 2, 3 3, 2 4, 4 4, 1 2))’, 1000004326) from dummy;

      SELECT new ST_POLYGON(‘POLYGON((1 2, 3 3, 2 4, 4 4, 1 2))’, 4326) from dummy;

      Best regards,

      Hinnerk

      (0) 
  3. Sergio Guerrero

    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!

    (0) 
    1. Hinnerk Gildhoff

      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.

      (0) 
      1. Sergio Guerrero

        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

        (0) 

Leave a Reply