Enable Choropleth Layers for Geographical Visualizations
Choropleth maps display data such as population density, per capita income, or election results, by shading or coloring geographical areas in proportion to the value of that data. Users consuming data from SAP Datasphere may want to view it via a choropleth layer in SAP Analytics Cloud or other analytics clients.
Prerequisites
To install default or custom choropleth shapefile data, you must have access to a database user and its Open SQL Schema (see Create a Database User).
See also 3670351
.
Install Default Choropleth Shapefile Data
-
Go to https://me.sap.com/softwarecenter
and search for DSP_SPATIAL_0-70009312.ZIP. -
Download and unzip the file.
-
Create an Open SQL Schema in any space in your SAP Datasphere tenant (see Create a Database User).
-
Open the SAP HANA Database Explorer and connect to your Open SQL Schema (see Connect to Your Open SQL Schema).
-
Select your schema and open the SQL Console.
-
From the downloaded zip file, copy the contents of Create_Statements.sql to the SQL Console and run it to create six tables.
-
Open the data folder in your download and import each of the six csv files into its relevant table, specifying the pipe character (|) as separator:
Table Name
CSV Filename
CHOROPLETH_CUSTOM_HIERARCHY
DEFAULT_GEO_HIER.csv
SAMPLE.GEOGRAPHY_AREA
SAMPLE_GEOGRAPHY_AREA.csv
SAMPLE.GEOGRAPHY_AREA_DESCRIPTION
SAMPLE_GEOGRAPHY_AREA_DESCRIPTION.csv
SAMPLE.GEOGRAPHY_SHAPE
SAMPLE_GEOGRAPHY_SHAPE.csv
SAMPLE.ISO_CODES
SAMPLE_ISO_Codes.csv
SAMPLE.VERSION
SAMPLE_VERSION.csv
-
From the unzipped downloaded files, copy the contents of Expose_Content.sql to the SQL Console.
-
Edit the following line to add the name of your Open SQL schema:
GRANT SELECT ON SCHEMA "<OpenSQLSchema>" TO "PUBLIC";
-
Run the script to make the imported data available to all spaces.
Once this sharing step is complete, users consuming geographical data from any space can view it in the default choropleth hierarchy in SAP Analytics Cloud (see Add Choropleth Layers to Geo Maps in the SAP Analytics Cloud documentation).
Install Custom Choropleth Content
Some organizations need to visualize their geographical data using different area borders.
-
Obtain the custom shapefile data that you want to use, along with its Spatial Reference Identifier (SRID).
In this example, we will import the Regions (December 2019) Boundaries EN BUC
shapefile layer available from the UK government Open Geography portal, which has the SRID 27700 (OSGB 1936 / British National Grid). -
In the SAP HANA Database Explorer, open your Open SQL Schema, expand the Catalog node, right-click the Tables node, click Import Data, and complete the wizard steps as follows:
-
Select Import ESRI Shapefiles and click Step 2.
-
Select your .zip file and click Step 3.
-
Select your Open SQL schema and click Step 4.
-
Enter the appropriate Spatial Reference Identifier (SRID)
In our example, we enter the value 27700, which represents OSGB 1936 / British National Grid.
-
Click Import.
The import will create a table with the name of the shapefile in your schema and load the data into it.
-
-
Select your schema and open the SQL Console.
-
Copy the following code to the SQL Console, adapt it for your imported shapefile, and run it to convert the data into a format that SAP Analytics Cloud can read in a new table, <Table>:
CREATE COLUMN TABLE <Table> (ID INT PRIMARY KEY, NAME VARCHAR(100), SHAPE ST_GEOMETRY(3857), SHAPEPOINT ST_POINT(3857), IS_LEAF BOOLEAN); INSERT INTO <Table> SELECT ROW_NUMBER() Over () as "ID" , "<NameColumn>" as "NAME", SHAPE.ST_SRID(3857).ST_Transform(3857) as SHAPE /* SAP Analytics Cloud requires SRID 3857 */, SHAPE.ST_SRID(3857).ST_Transform(3857).ST_Centroid() as SHAPEPOINT, false AS IS_LEAF FROM "<ShapeFileName>";
Complete the parameters as follows:
Parameter
Description
<Table>
Enter the name of the table to create to contain your SAP Analytics Cloud-compatible choropleth data.
<NameColumn>
Enter the column containing your shapefile shape names.
<ShapeFileName>
Enter the name of your imported shapefile.
In our example, we enter the following :CREATE COLUMN TABLE ENGLAND_L1 (ID INT PRIMARY KEY, NAME VARCHAR(100), SHAPE ST_GEOMETRY(3857), SHAPEPOINT ST_POINT(3857), IS_LEAF BOOLEAN); INSERT INTO ENGLAND_L1 SELECT ROW_NUMBER() Over () as "ID" , "rgn19nm" as "NAME", SHAPE.ST_SRID(3857).ST_Transform(3857) as SHAPE /* SAP Analytics Cloud requires SRID 3857 */, SHAPE.ST_SRID(3857).ST_Transform(3857).ST_Centroid() as SHAPEPOINT, false AS IS_LEAF FROM "Regions__December_2019__Boundaries_EN_BUC";
You can install additional custom layers as necessary.
Before you can access your custom layers in SAP Analytics Cloud, you must add them to a hierarchy.
Add Your Custom Choropleth Layers To a Hierarchy
To make your custom choropleth layers available in SAP Analytics Cloud, you must create a new hierarchy for them. These hierarchies are selected in SAP Analytics Cloud and allow you to drill up and down through layers.
INSERT INTO "sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY" VALUES( '<HierarchyName>', <HierarchyID>,'<NameColumn>','name',<LayerID>,<LayerLevel>,'<LayerName>','SHAPE','<TableName>','','<SPACE#DBUSER>',0);Complete the parameters as follows:
|
Parameter |
Description |
|---|---|
|
<HierarchyName> |
Enter the hierarchy name to be shown in SAP Analytics Cloud. |
|
<HierarchyID> |
Enter a unique ID. The default choropleth layer has ID 1. |
|
<NameColumn> |
Enter NAME (or AREA_NAME when reusing a default layer). |
|
<LayerID> |
Enter a unique ID for the shape or layer. The default choropleth layers have IDs 1-5. |
|
<LayerLevel> |
Enter the level of the layer in the hierarchy in the form <n> or <n>.<n>. |
|
<LayerName> |
Enter the layer name to be shown in SAP Analytics Cloud. |
|
<TableName> |
Enter the name of the table containing your custom layer (or SAC_CHOROPLETH_DATA when reusing a default layer). |
|
<SPACE#DBUSER> |
Enter your Open SQL schema name (or PUBLIC when reusing a default layer). |
INSERT INTO "sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY" VALUES( 'English Regions',2,'NAME','name',6,1,'English Region','SHAPE','ENGLAND_L1','','MYSPACE#MYUSER','0');
INSERT INTO "sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY" VALUES( 'Country - English Regions',3,'AREA_NAME','name',7,1,'Country','SHAPE','SAC_CHOROPLETH_DATA','','PUBLIC','0'); INSERT INTO "sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY" VALUES( 'Country - English Regions',3,'AREA_NAME','name',8,2,'Region','SHAPE','SAC_CHOROPLETH_DATA','','PUBLIC','0'); INSERT INTO "sap.fpa.services.spatial::custom_hierarchy.CHOROPLETH_CUSTOM_HIERARCHY" VALUES( 'Country - English Regions',3,'NAME','name',9,3,'English Region','SHAPE','ENGLAND_L1','','MYSPACE#MYUSER','0');
Select Custom Choropleth Layers in SAP Analytics Cloud
-
In SAP Datasphere, a modeler must include geographical data in a dimension (see Create a Geo-Coordinates Column in a Graphical View).
-
In SAP Analytics Cloud, an analyst must add a Geo Map widget to the story and set the following properties for it:
Property
Value
Content Layers
Click Add Layer.
Layer Type
Select Choropleth/Drill.
Layer Style
Select Shape (Choropleth).
Location Dimension
Select the location dimension, and then click the Hierarchy button and choose the standard or a custom layer from the list.
Choropleth Color
Select a measure, calculation or measure input control.
For more information, see Add Choropleth Layers to Geo Maps in the SAP Analytics Cloud documentation.