Geolocation setup

Geolocation Setup

Prerequisites: 

  • HANA licensed for SAP HANA spatial 
  • HANA system has to be configured with a valid SRID (Spatial reference Identifier) 
  • HANA Live connection setup 
  • Dataset with Latitude and Longitude 

Steps to perform: 

  • Create ‘SC_GEO_SPATIAL’ schema 
  • Create ‘SAP_BOC_SPATIAL’ namespace 
  • Add spatial reference Identifier (SRID 3857) 
  • Add Location Data to HANA catalog (SC_GEO_SPATIAL) 
  • Transform Geo Spatial data to Location data table with ST_GEOMETRY 
  • Create Location Data Calculation View 
  • Sync Calc view within SAC. 

Create SC_GEO_SPATIAL 

Create a new schema under your HANA catalog on the required system. The name doesn’t really matter, I prefer to call it SC_GEO_SPATIAL. Later on in this blog we will add our file here containing our geo data.  

loading=

Create SAP_BOC_SPATIAL Namespace 

Create a new package under content. The name needs to be SAP_BOC_SPATIAL. 

loading=

Add spatial reference identifier (SRID 3857) 

First you can check whether SRID 3857 is already available. You can do this by going in the SQL console entering following statement: 

SELECT * FROM ST_SPATIAL_REFERENCE_SYSTEMS 

loading=

Marked in yellow, you see on my system the SRS_ID 3857 is available. When this is not the case, in the SAP HANA studio console, run the following query: 

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’ 

Add Location Data to HANA catalog 

Import a new table into your HANA catalog containing your geo information and save in under the SC_GEO_SPATIAL schema. Named TB_GEO_SPATIAL 

loading=

As an example, a .csv file containing the fields, Country ID, Latitude, Longitude, and full name. 

Transform Geo Spatial data to Location data table with ST_GEOMETRY 

Create New GEO Location Table. This table should contain 2 columns: 

  • Location Data Identifier: ID column values from the location identifier column of your calculation view. 
  • Spatial Data Column: containing spatial data generated from your latitude and longitude data with SRID 3857. 

This can be achieved by running following SQL code. 

CREATE COLUMN TABLE “TB_COUNTRY_LOCATIONDATA” ( “Country_LD” NVARCHAR (50) PRIMARY KEY, 

“Location_Dimension” ST_GEOMETRY(3857)); 

UPSERT “TB_COUNTRY_LOCATIONDATA” (“Country_LD”) 

SELECT “Country” FROM “SC_GEO_SPATIAL”.“TB_GEO_SPATIAL” Group By “Country”; 

UPDATE “EPSDM”.“TB_COUNTRY_LOCATIONDATA” 

SET “Location_Dimension” = new ST_GEOMETRY(‘POINT(‘ || “Longitude” || ‘ ‘ || “Latitude” || ‘)’, 4326).ST_Transform(3857) 

FROM ( SELECT MIN(“Latitude”) “Latitude”,MIN(“Longitude”) “Longitude”, “Country” 

FROM “SC_GEO_SPATIAL”.“TB_GEO_SPATIAL” GROUP BY “Country”), “SC_GEO_SPATIAL”.“TB_COUNTRY_LOCATIONDATA” WHERE “Country” = “Country_LD” 

This transforms your geo spatial data in a readable form for SAP Analytics Cloud. Resulting in: 

loading=

Create Location Data Calculation View 

Create a calculation view based on the Location Data. This view is referred to as Location Data view and should be created under Content > SAP_BOC_SPATIAL package. The view must be under this package in order to be recognized by SAP Analytics Cloud when creating a location dimension. Make sure the SAP HANA users are given proper privileges to access the content within this package. 

loading=

Because our Calculation View is set as data category DIMENSION, the default node is a projection. Drag our newly created table into the projection and select both fields as output. Set the Country_LD field as key in the semantic node. Don’t forget to save and activate. 

loading=

Sync Calc view within SAC. 

Create a model in SAP Analytics Cloud. In this case a Live HANA connection 

loading=

In the data view of your model, select the icon where you can create a new location dimension. See icon below. 

loading=

From Create Location Dimension dialog, map the Location Identifier from your calculation view (fact data) with the Location Data identifier from your Location Data view (dimension) containing your spatial data. 

  • Location Identifier is the key column in your calculation view that contains fact data.  
  • View Name is the dimension view containing location information 
  • Location Dimension Name is automatically applied 
  • Identifier for Mapping is the key column of your dimension view containing location information 

loading=

The model is now ready to be consumed by a story. 

loading=