India Geo Model from HANA live with SAP Analytics Cloud

Hence, I tried a quick POC to represent India data on geo map from HANA live. This was further enhanced to actually include drill up/down capabilities on the states. I thought it would be useful for many India customers who intends to achieve similar usecases.

I started with India 2014 central elections dataset. This dataset is already enriched with latitude and longitude information for the contituencies. (This itself might be useful for you). You can download the dataset from – India elections 2014

In the solution below we plan to achieve the following
1. Display all states on map of India
2. Color of the state signifies the votes casted in each state (use any Measure)
3. Click on state to filter the other visualizations on the page

Prerequisites

◈ The live HANA system must be at least on SPS11 on 112.07 or higher or SPS12 on

122.03 or higher

◈ Your HANA system must be licensed for SAP HANA Spatial

◈ The HANA system has to be configured with a valid Spatial Reference Identifier (SRID)

used by SAP Analytics Cloud

Solution

Setting up the right SPATIAL REFERENCE SYSTEM

First thing that needs to be checked if the Spatial Reference System (3857) that is used by SAP Analytics Cloud is actually installed in HANA or not.

In order to find that execute the following query on your HANA system

SELECT * FROM ST_SPATIAL_REFERENCE_SYSTEMS

If you see the highlighted entry in the results, then we are all good. Else we need to install this Spatial reference system.

Use the following query To add Spatial Reference Identifier (SRID) 3857 in case its not already there.

CREATE SPATIAL REFERENCE SYSTEM “WGS 84 / Pseudo-Mercator” IDENTIFIED BY 3857

TYPE PLANAR

SNAP TO GRID 1e-4

TOLERANCE 1e-4

COORDINATE X BETWEEN -20037508.3427892447 AND 20037508.3427892447

COORDINATE Y BETWEEN -19929191.7668547928 AND 19929191.766854766

ORGANIZATION “EPSG” IDENTIFIED BY 3857

LINEAR UNIT OF MEASURE “metre”

ANGULAR UNIT OF MEASURE NULL

POLYGON FORMAT ‘EvenOdd‘

STORAGE FORMAT ‘Internal‘

DEFINITION ‘PROJCS[“Popular Visualisation CRS / Mercator”,GEOGCS[“Popular Visualisation CRS”,DATUM[“Popular_Visualisation_Datum”,SPHEROID[“Popular Visualisation Sphere”,6378137,0,AUTHORITY[“EPSG”,”7059″]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY[“EPSG”,”6055″]],PRIMEM[“Greenwich”,0,AUTHORITY[“EPSG”,”8901″]],UNIT[“degree”,0.01745329251994328,AUTHORITY[“EPSG”,”9122″]],AUTHORITY[“EPSG”,”4055″]],UNIT[“metre”,1,AUTHORITY[“EPSG”,”9001″]],PROJECTION[“Mercator_1SP”],PARAMETER[“central_meridian”,0],PARAMETER[“scale_factor”,1],PARAMETER[“false_easting”,0],PARAMETER[“false_northing”,0],AUTHORITY[“EPSG”,”3785″],AXIS[“X”,EAST],AXIS[“Y”,NORTH]]’

TRANSFORM DEFINITION ‘+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null 

Create the location data from the latitude and longitude information

If you are just trying out a POC and using the data attached above or your data is in some xls or csv. You can import that data into SAP HANA in the form of a table. 

I have the data imported as a table and let me call that table “INDIA_TMP”. Next step would be create a Geo table that will hold the spatial information.

In my Original data I have City/Constituency which is what I want to represent and their corresponding latitude and longitude.

CREATE COLUMN TABLE "MYPACKAGE"."India_LocationData" 

("City_LD" VARCHAR(255) PRIMARY KEY, "Shape" ST_GEOMETRY(3857));

City_LD is the Identifier for the location data in this table.

ST_GEOMETRY is the abstract field type that can hold any shape type. Whether be it a point or line or so on.

Next is to basically populate this table with list of all City/Constituency.

UPSERT "MYPACKAGE"."India_LocationData" ("City_LD")

SELECT "City" FROM "MYPACKAGE"."INDIA_TMP" GROUP BY "City";

Next step is to now populate the spatial information for each City/Constituency by creating a ST_GEOMETRY object in 3857 spatial referece system.

Note that Latitude and Longitude data that we have, when represented in decimal format comes in 4326 Spatial Reference System. Which actually needs to be transformed to 3857 Spatial reference system to be represented in SAC.

The update query for this transformation would be

UPDATE "MYPACKAGE"."India_LocationData"

SET "Shape" = new ST_GEOMETRY('POINT(' || "Longitude" || ' ' ||"Latitude" ||

')', 4326).ST_Transform(3857)

FROM (SELECT MIN("Latitude") "Latitude", MIN("Longitude") "Longitude", "City"

FROM "MYPACKAGE"."INDIA_TMP" GROUP BY "City"),

"MYPACKAGE"."India_LocationData"

WHERE "City" = "City_LD";

So, if you are here you have create the geo table based on the latitude and longitude information.

Import the shape files for India Region

If you intend to use point representation on the India Map like below then this step is not really required.

Else you need to import the India shape files into your HANA system. You can import ESRI shape files.

Now, to import the shape files in SAP HANA you can use SAP HANA Studio

Use File Menu > Import

Search for ESRI shape file.

Import from your local folder

In the next step add all shapefiles to the right. And use SRID as 4326 while importing in the subsequent dialog.

Expose the Location Data as HANA calculation view

You can now wrap the Geo Location table within a calculation view. When creating the calculation view, select DIMENSION as the Data Category.

Mark the Key column correctly and save and activate calculation view. 

Create a HANA live Model with location dimension

Now, while you are consuming your actual calculation view (not the geo calculation view) which has all the data. You can create a Geo dimension.

In the Data View of the model, select the Create a Location Dimension icon shown below

From Create Location Dimension dialog, map the Location Identifier from your calculation view with the Location Data identifier from your Location Data view containing your spatial data

Tip: At present, all column names across your calculation view and any Location Data view that will be joined with your calculation view have to be unique. If an error due to non-unique names is displayed, contact the person who prepared the Location Data in SAP HANA Studio

Save the model and now you can use the same in your  SAC Story.

Consume the Geo Model in SAC Story

Insert a Geo Map from the Insert options in the top toolbar in SAP Analytic Cloud Story. Insert the geo location dimension in the designer options

Enabling Drill up/down option

Note that for the above given example the dataset that has been used has both city/constituency and the state information. Using both the columns we can actually create a Parent-Child hierarchy in SAP HANA in the calculation view itself.

Define State as parent of City

Post that you can actually refresh and observe that you have drill up/down option when clicking on the map.

Enabling Linked Analysis for the Geo map

You can also enable linked analysis on this geo map, this will allow you to select a point or state on the map and filter rest of the charts with that context.