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.
Create SAP_BOC_SPATIAL Namespace
Create a new package under content. The name needs to be SAP_BOC_SPATIAL.
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
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
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:
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.
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.
Sync Calc view within SAC.
Create a model in SAP Analytics Cloud. In this case a Live HANA connection
In the data view of your model, select the icon where you can create a new location dimension. See icon below.
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
The model is now ready to be consumed by a story.