Tuesday, 14 April 2020

Geospatial Data Processing in SAP HANA

Introduction:


To create a location enabled model in SAP Analytics Cloud from a live HANA calculation view, Location Data must be prepared through SAP HANA Studio.

The Latitude and Longitude can be converted in to Location Dimension using Spatial data types like Geometry (ST_Geometry) and POINT (ST_Point).

These data types are used to to store spatial information in SAP HANA.

Pre-requisites:


1. The live HANA system must be at least on SPS11 on 112.07 or higher or SPS12 on 122.03 or higher.

2. Your HANA system must be licensed for SAP HANA Spatial.

3. The HANA system must be configured with a valid Spatial Reference Identifier (SRID) used by SAP Analytics Cloud.

Add Spatial Reference Identifier (SRID) 3857:


In the SAP HANA Studio console, run the following query:

SAP HANA, SAP HANA Geospatial Content, SAP HANA Live, SAP HANA Studio

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’

Prepare Location Data from HANA Studio:


1. Right click on the table that contains latitude and longitude data and select Data Preview.

SAP HANA, SAP HANA Geospatial Content, SAP HANA Live, SAP HANA Studio

2. Select a column that will be the Primary key or unique Location identifier for each location. In this example, the Location Identifier is Country. Set Country as a key column in the calculation view.

3. Create a Geo Location table. This table should contain two columns:

◉ Location Data identifier: ID column values from the Location Identifier (Ex: Country) column of your calculation view.

◉ Spatial data column: containing spatial data (for example ST_GEOMETRY) generated from your latitude and longitude data with SRID 3857.

Once the table is created set the Location Data identifier column as a primary key.

SAP HANA, SAP HANA Geospatial Content, SAP HANA Live, SAP HANA Studio

SAP HANA, SAP HANA Geospatial Content, SAP HANA Live, SAP HANA Studio

SAP HANA, SAP HANA Geospatial Content, SAP HANA Live, SAP HANA Studio

Create a Calculation View:


4. Create a package (SAP_BOC_SPATIAL) to create your Calculation View containing location information. All the Geo-spatial HANA Views must be created in SAP_BOC_SPATIAL package.

SAP HANA, SAP HANA Geospatial Content, SAP HANA Live, SAP HANA Studio

SAP HANA, SAP HANA Geospatial Content, SAP HANA Live, SAP HANA Studio

5. Create a calculation view of type Dimension based on the Location Data. This view is referred to as Location Data view and should be created under Content > SAP_BOC_SPATIAL

SAP HANA, SAP HANA Geospatial Content, SAP HANA Live, SAP HANA Studio

SAP HANA, SAP HANA Geospatial Content, SAP HANA Live, SAP HANA Studio

6. Once the calculation view is created, drag the Location Data table to the Projection

SAP HANA, SAP HANA Geospatial Content, SAP HANA Live, SAP HANA Studio

7. Select the columns from the Location Data table in the Location Data view and set the Location Data Identifier column as a key column. In this example, it is Country_LD.

8. Select the Projection node, then double-click each column in the Details pane to make it display in the Output .

Select the Location Identifier column, then set the Key to True in the Properties pane.

SAP HANA, SAP HANA Geospatial Content, SAP HANA Live, SAP HANA Studio

9. Save and activate the calculation view.

SAP HANA, SAP HANA Geospatial Content, SAP HANA Live, SAP HANA Studio

10. Right click on the Calculation View and select Data Preview.

SAP HANA, SAP HANA Geospatial Content, SAP HANA Live, SAP HANA Studio

SAP HANA, SAP HANA Geospatial Content, SAP HANA Live, SAP HANA Studio

No comments:

Post a Comment