India Geo Map via HANA live with SAP Analytics Cloud
I recently happened to work with a partner who was using HANA live connection from SAP Analytics Cloud and wanted to represent his geo data on India Maps. There is a detailed list of steps on how to do that for US region – Creating Geo model from Live HANA Calculation View
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
- Display all states on map of India
- Color of the state signifies the votes casted in each state (use any Measure)
- Click on state to filter the other visualizations on the page
- 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
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["cen tral_meridian",0],PARAMETER["scale_factor",1],PARAMETER["false_easting",0],PA RAMETER["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 +wktext +no_defs'
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. To know how to import data in SAP HANA
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.
For the purpose of this blog I picked up the ESRI shape files linked in one of the blog posted here. Here is the direct link to the shape files.
You can as well search and download the ESRI shape files. Another link for download of ESRI shape files is here http://www.diva-gis.org/gdata. Although, I have not tried importing this data myself.
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.
Note: for more details you can refer step 2 at Creating Geo model from Live HANA Calculation View.
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.
Refer here on how to enable linked analysis in SAP Analytics Cloud
Great Mate!! thanks.
This is really useful. Thanks!
Great information !
Very useful blog. I have one question. Do we need shape files as well to enable drill down in geo map apart from hierarchal data. Also, if you are aware of any table where we can get city/region data to create hierarchies on Location view.