Compute Distance using a Calculation View – XS Advanced Model

We’re going to learn how to create a Graphical Calculation View to calculate the distance between two locations or coordinates represented by a longitude and a latitude. We assume that some of these locations or coordinates are already known and the other is an input from the user.

This is achieved by using the HANA spatial capabilities. HANA support three spatial reference systems (SRS) by default. For this blog we will use the WGS84 – SRID 4326 SRS because HANA supports it and the coordinates used are based off Google Maps.

If you just right-click anywhere on google maps and select what’s here from the context menu a small pop-up will appear showing you the latitude and longitude. We will use these coordinates to create a few records in a database table.

To develop this project I am using HANA Express Edition (the free version) running on my local machine. At the time of writing this blog the version was HANA 2.0 SPS02. I will be using the SAP WebIDE Build 4.2.21.

Step 01 : Create a MTA Project and View HDI Container

1. Create a MTA by selecting Project From Template as you normally do. I called my project DistCalc.

2. Create a Database Module. Make sure you select the database version 2.0 SPS 02 and select Build module after creating to create the HDI-Container right after clicking Finish. The module was names db.

3. After the module is created and built, add the container to the Database Explorer. We will be spending most of our time there.

Step 02 : Create a CDS Artifact to Model a Table to Store Coordinates

To keep it short use the following code shown below in a CDS Artifact to create a simple table to store markers with coordinates that we know of. After the file is built, the table will look as the image below within the database explorer.

namespace DistCalc.db;

context model {

    entity Marker {

        key markerId   : Integer generated always as identity(start with 1000 increment by 1 no maxvalue);

            markerName : String(10);

            latitude   : String(35);

            longitude  : String(35);

    };

};

Step 03 : Insert Coordinate records into Table

Use the following INSERT statements to add 5 markers. The marker coordinates were taken from Google Maps. The image below will show the actual coordinates I picked.

INSERT INTO "DistCalc.db::model.Marker" VALUES( 'marker-01', '-37.784556', '145.268080' );

INSERT INTO "DistCalc.db::model.Marker" VALUES( 'marker-02', '-28.906649', '136.329625' );

INSERT INTO "DistCalc.db::model.Marker" VALUES( 'marker-03', '-27.820271', '147.298336' );

INSERT INTO "DistCalc.db::model.Marker" VALUES( 'marker-04', '-30.506094', '123.787594' );

INSERT INTO "DistCalc.db::model.Marker" VALUES( 'marker-05', '-21.766935', '133.316398' );

The records in the table should look like below. the markerId might be different to yours after the insert because it is generated automatically as per our CDS entity model we used above.

Step 04 : Create the Calculation View

This is where it gets interesting.

1. Create a graphical calculation view and add our previously created DistCalc table as a Data Source to the Projection and use all of its columns for the output.

2. Create a Calculated Column of Data Type  ST_GEOMETRY and use the following code in the expression editor. Use the Compute Engine for the expression.

ST_GeomFromText('Point(' + "longitude" + ' ' + "latitude" + ')',4326)​

This expression will use the latitude and longitude values that are mapped from the DistCalc table to create a ST_POINT within a ST_GEOMETRY spatial data type. Also the spatial reference system identifier 4326 is used. The mapped columns and the calculated column should look like the images below.

When you open the data of the calculation view within the database explorer you should see the ST_GEOMETRY type column having an alphanumeric string. This is the calculated ST_GEOMETRY coordinate value for each latitude longitude combination.

Step 05 : Add Input Parameters to the Calculation View

Things get more interesting here.

1. Add 2 Parameters to the graphical calculation view of type Input Parameter to enter a coordinate as a latitude and a longitude as shown in the images below.

in_latitude

in_longitude

2. Add 2 more Calculated Columns to map the 2 parameter inputs created above. This makes it easy to use the inputs for another Calculated Column to derive a ST_GEOMETRY type for the input coordinate. The mapping is done within the expression editor using Column Engine as shown below. Note that the spatial reference id 4326 is used here as well.

Input Latitude

'$$in_latitude$$'

Input Longitude

'$$in_longitude$$'

Input Coordinate

ST_GeomFromText('Point(' + "InputLongitude" + ' ' + "InputLatitude" + ')',4326)​

3. Run SQL statement with inputs to see the resulting InputCoordinate as a ST_GEOMETRY data type. All the values should have the same value for all the records as shown below. If the image is not clear, zoom-in.

Step 06 : Create Calculated Column for the Distance

We have reached the final and the most interesting part.

By using the InputCoordinate ST_GEOMETRY type and the previously created MarkerCoordinate ST_GEOMETRY type as inputs to the HANA Spatial Function ST_Distance we can calculate the distance in meters. The ST_Distance function returns values of type DOUBLE.

To do this we need to create our final Calculated Column and use the following expression using the Column Engine.

ST_Distance("InputCoordinate","MarkerCoordinate")

Finally when you run the Calculation View using a SQL statement providing the coordinate inputs you will see the calculated distance to the marker coordinate records created above. Based on the input coordinates below the distance to marker-01 should be 1.86 kilometres.

SELECT TOP 1000

"markerId",

"markerName",

"latitude",

"longitude",

"MarkerCoordinate",

"InputLatitude",

"InputLongitude",

"InputCoordinate",

"DistanceToMarker"

FROM "DISTCALC_HDI_DB_1"."DistCalc.db::MarkerDistance"

(placeholder."$$in_latitude$$"=>'-37.78087303495233', 

placeholder."$$in_longitude$$"=>'145.28872537183577');

This concludes our Compute Distance project. I will be creating another blog in the future to show how I consume this Calculation View and expose it via an OData service.