Design Studio Innovation Series - Topic 7: Geo Maps Part II - Zoom, Zoom, Zoom!

[MustafaBensan]

Introduction

This is the follow-up to the blog Design Studio Innovation Series - Topic 6: Geo Maps Part I - Feature Review.  In that blog I covered the main features of the new Geo Map component introduced in Design Studio 1.5.  In this blog I'll cover an approach for zooming and drilling down through multiple regions down to a street-level location and back.

Scenario

The example involves the display of US Census population data loaded into BW, on a map with drill-down from State to County to Place (City or Town).  To demonstrate the presentation of supporting information, there is also a chart with population estimates as well as a table listing the data.  The table can also be used as an alternative method of drilling down through the map.

The end result can be viewed in the following video:

Approach

In a nutshell, the approach used here relies on a combination of:

(a)  Applying the centerMap() method of the GeoMap component;

(b)  Applying filters to the assigned data source;

(c)  Toggling the visibility of map layers.

Steps Covered

1. GeoJSON File Preparation
2. Data Preparation
3. BW Modeling
4. BEx Query Design
5. Application Design and Configuration

1.  GeoJSON Map File Preparation

(a) Base Files

To start with we need GeoJSON files representing US states and counties.  I found suitable ones at the GEOJSON AND KML DATA FOR THE UNITED STATES website:

To keep the file size to a minimum I downloaded the lowest resolution versions (20m) as highlighted above, named as follows:

gz_2010_us_040_00_20m.json - States

gz_2010_us_050_00_20m.json - Counties

(b) Merge Map Files

Initially I thought I would need to create a separate map layer for state and county.  However, after reviewing the mapping properties I noticed that conveniently, both files shared a common GeoID property (more on this later) as a key for uniquely mapping to each polygon representing the states and counties, so I thought what if we merged the files in order to use just one map layer?  I chose to combine the two files using the geojson.io tool because my requirement was simple and the tool allowed this to be done without any installation/setup.  Initially you'll see the following home page:

The first task is to simply open or drag and drop the States GeoJSON file onto the map as shown below:

We see a confirmation of the imported features corresponding to the states.  Each feature includes the properties GEO_ID, SATE, NAME, LSAD and CENSUS AREA.  Clicking the Table tab gives us a columnar view of the properties, making it easier to validate what we've imported.

Now we do the same to add the Counties GeoJSON file to the States file we just imported:

By reviewing the Table tab we can see that the counties (with GEO_ID's of prefix 050) have been added after the states (with GEO_ID's of prefix 040):

Now we simply save the combine file in GeoJSON format:

This results in a file of size roughly 4 MB.  I thought I'd try to reduce this a little with the mapshaper tool:

Again, just drag and drop the GeoJSON file onto the browser:

I chose to simplify down to 60%:

After exporting again in GeoJSON format the resulting file is around 3 MB, saving us 1MB which is reasonable for performance.

For further reading about preparation of GeoJSON files I'd recommend the following blogs:

DS15 - Create your own Map by dirk.mayrock, which provides additional tips for using geojson.io  and

Creating Custom GeoJSON maps in Design Studio 1.5 by mike.howles4, which describes QGIS, a more sophisticated tool for creating custom map files.

2.  Data Preparation

The US Census population data is provided summarised at State, County and Place levels so this is what I used to load into BW, with appropriate transformations applied to generate GEO_ID values to match the corresponding ones in the GeoJSON map file.  The raw CSV file looks like this:

This was supplemented with geolocation information for cities and towns.

3.  BW Modeling

The BW infocube and characteristics were modelled as follows:

4.  BEx Query Design

I was able to get away with just two queries:

(a)  US Mainland Population:

Important to note here is that I use a standard BEx user-exit variable to automatically calculate and filter the data for the latest year.  I have also restricted the states to the mainland states (excluding Alaska and offshore territories) to more effectively centre the maps on the screen.

(b)  US Population Estimates:

Note here I've used a standard BEx user-exit variable to calculate and filter the population estimates for the latest 5 years.  Again, I have also restricted the states to the mainland states (excluding Alaska and offshore territories).

5.  Application Design and Configuration

(a) Application Theme

The application applies the Blue Crystal theme as recommended by karol.kalisz in Why Use Blue Crystal Style in Design Studio Apps?

(b) Global Script Variables

(c) Data Sources, Components and Global Script Functions

(d) Data Source Initial Views

(e) Map Layers

Now just a point to note here, for layer US_COUNTY_PLACE_BUBBLE, I did explicitly specify the

Latitude, Longitude and Measure properties but for some reason after saving they no longer displayed but still seemed to be retained by the application.  Perhaps this is a bug.

(f) Scripts

Script for "On Select" event of GEO_MAPP

var geoIDselection = me.getSelectedMember("ZGEOID");

var geoIDselectionKey = geoIDselection.internalKey;

var geoIDselectionText = geoIDselection.text;

GEO_FUNCTIONS.processDrilldown(geoIDselectionKey, geoIDselectionText);

Script for "On Select" event of CROSSTAB_1

var geoIDselection = me.getSelectedMember("ZGEOID");

var geoIDselectionKey = geoIDselection.internalKey;

var geoIDselectionText = geoIDselection.text;

GEO_FUNCTIONS.processDrilldown(geoIDselectionKey, geoIDselectionText);

Script for Global Script Function GEO_FUNCTIONS.processDrilldown

var geoIDselectionKey = pGeoIDkey;

var geoIDselectionText = pGeoIDtext;

var geoType = GEO_FUNCTIONS.getGeoType(geoIDselectionKey);

if (currentLevel < levelCounty) {

  if (geoType==stateGeoType) {

  currentLevel = currentLevel + 1;

  currentState = GEO_FUNCTIONS.getStateFIPS(geoIDselectionKey);

  currentStateGeoID = geoIDselectionKey;

  currentStateText = geoIDselectionText;

  mapTitle = currentStateText;

  DS_1.setFilter("ZSUMLEV", countyLevel);

  DS_1.setFilter("ZSTATEFIP", currentState);

  DS_3.clearFilter("ZSUMLEV");

  DS_3.setFilter("ZGEOID", currentStateGeoID);

  GEO_MAP.centerMap(stateCountyMapLayerID);

  FIORIAPPHEADER.setShowNavButton(true);

  } else if ( geoType == countyGeoType )

  {

  currentLevel = currentLevel + 1;

  currentCounty = GEO_FUNCTIONS.getCountyFIPS(geoIDselectionKey);

  currentCountyGeoID = geoIDselectionKey;

  currentCountyText = geoIDselectionText;

  mapTitle = currentCountyText + ", " + mapTitle;

  DS_1.setFilter("ZGEOID",currentCountyGeoID);

  DS_3.setFilter("ZGEOID",currentCountyGeoID);

  GEO_MAP.centerMap(stateCountyMapLayerID);

  DS_2.setFilter("ZCNTYFIP", currentCounty );

  GEO_MAP.setLayerVisible(countyPlaceBubbleLayerID, true);

  CROSSTAB_1.setDataSource(DS_2);

  CROSSTAB_1.resetAllColumnWidths();

  }

} else if (currentLevel == levelCounty) {

  currentLevel = currentLevel + 1;

  currentPlaceGeoID = geoIDselectionKey;

  currentPlaceText = geoIDselectionText;

  mapTitle = currentPlaceText + ", " + mapTitle;

  DS_2.setFilter("ZGEOID", currentPlaceGeoID);

  DS_3.setFilter("ZGEOID",currentPlaceGeoID);

  GEO_MAP.setLayerVisible(stateCountyMapLayerID, false);

  GEO_MAP.centerMap(countyPlaceBubbleLayerID);

}

GEO_FUNCTIONS.setMapTitle(mapTitle, year);

Script for "On Back" event of FIORIAPPHEADER

Here we navigate back or drill up.

if (currentLevel == levelPlace) {

  currentLevel = currentLevel - 1;

  currentPlace = "";

  currentPlaceGeoID = "";

  mapTitle = currentCountyText + ", " + currentStateText;

  GEO_FUNCTIONS.setMapTitle(mapTitle, year);

  GEO_MAP.setLayerVisible(stateCountyMapLayerID,true);

  GEO_MAP.centerMap(stateCountyMapLayerID);

  DS_2.clearFilter("ZGEOID");

  DS_3.setFilter("ZGEOID", currentCountyGeoID);

} else if (currentLevel == levelCounty) {

  currentLevel = currentLevel - 1;

  currentCounty = "";

  currentCountyGeoID = "";

  mapTitle = currentStateText;

  GEO_FUNCTIONS.setMapTitle(mapTitle, year);

  GEO_MAP.setLayerVisible(countyPlaceBubbleLayerID,false);

  DS_1.clearFilter("ZGEOID");

  GEO_MAP.centerMap(stateCountyMapLayerID);

  CROSSTAB_1.setDataSource(DS_1);

  DS_3.setFilter("ZGEOID", currentStateGeoID);

} else if (currentLevel == levelState) {

  FIORIAPPHEADER.setShowNavButton(false);

  currentLevel = currentLevel - 1;

  currentState = "";

  currentStateGeoID = "";

  mapTitle = defaultMapTitle;

  GEO_FUNCTIONS.setMapTitle(mapTitle, year);

  DS_1.clearFilter("ZSTATEFIP");

  DS_1.setFilter("ZSUMLEV",stateLevel);

  GEO_MAP.centerMap(stateCountyMapLayerID);

  DS_3.clearFilter("ZGEOID");

  DS_3.setFilter("ZSUMLEV", "040");

}

Script for "On Item Select" event of FIORIAPPHEADER

Here we select between the GeoMap and Analytic Map (no basemap)

var mapType = me.getItemClicked();

if (mapType == "GeoMap") {

GEO_MAP.setMapUrl(basemapURL);

}

else {GEO_MAP.setMapUrl("");}

Conclusion

I hope the example presented here has given you a good idea of the creative possibilities with the GeoMap component.  There's quite a lot more I would have liked to explain based on my experiences preparing this post but that's probably best left to a lessons learned follow-up.

Comments and questions are most welcome as usual.

Blog Series Index:  Design Studio Innovation Series - Welcome

[former_member214226]

Thanks for the great post. I especially liked the code examples.

[MustafaBensan]

Thanks David.  It was an interesting exercise to come up with an effective zooming approach that minimised the number of required map layers and geoJSON files.  I like the global script functions feature in Design Studio which certainly helps in building modular applications and avoiding code repetition. 

[jean_machado]

Thanks! Great article!

[MustafaBensan]

Thanks and you're welcome.

[MustafaBensan]

Hi Jean,

It seems your question post has been removed so I will answer by responding here.

In my original prototyping with the Geo Map component I had experimented with manipulating the zoom parameters of the Base Map URL but this doesn't work as desired because of the way the Geo Map component interacts with the URL and also can result in script errors.

For your scenario, my suggestion is to create a map layer which uses a geoJSON file of Brazil (including the states), assign a dimension for from your data source that corresponds to states and filter on Rio de Janeiro and then apply the centreMap() method to the layer.

You can refer to Lauren Geraedts' comment in the Part I of this Geo Map blog: Design Studio Innovation Series - Topic 6: Geo Maps Part I - Feature Review for a suggestion about how to obtain a Brazil state map geoJSON file.

Regards,

Mustafa.

[jean_machado]

Thanks Mustafa!

[MustafaBensan]

You're welcome.  Hope you are able to achieve the desired result.

[former_member222258]

Hi Mustafa,

We are trying to have the Map View across various Regional Views with each region showing 2 KPI's which we got it achieved through the different Layers in GeoMap.

1. The different Regions has been achieved through our Bex Query Datasource View with the Basemap URL not specified. All the different regions is been visible.

The issue we are facing is with the Centering the entire Map for different Regions.

All of your above steps has been followed and we did had the Custom GEOJSON File with the different layers

We did try the GEO_MAP.centerMap(); also but no luck.

Can you please  help us of any other way to have the entire Map Centered to be displayed?

I have attached the view of US where in it is not getting centered as well as the entire US is not getting displayed so I have to manually drag and drop it in the center of the screen to get the entire view of US.

Thanks,

Arunnesh

[MustafaBensan]

Hi Arunnesh,

The GEO_MAP.centreMap() method should work.  You should check your CUSTOM GEOJSON files and make sure they don't include any distant territories off the mainland as this will affect the centring, as is the case with the US.

Regards,

Mustafa.

[former_member222258]

Hi Mustafa,

Thanks for your response.

We are not using any Basemap URL so that it is by default pointing to the SAP Maps by default and no Custom geoJSON file is also been used.

We are controlling the display of our maps for various regions by filtering at the datasource level.

We did try the Centermap() at the On startup event specifying the different layers that is being used for the US region but still it did not work.

Do you recommend us to create Custom GeoJSON file that could fix this Centering issue rather than having the Basemap URL not specified?

[Former Member]

Hi Mustafa,

Can you suggest where I can get GeoJson File which has Region(E,g APAC,Africa,NA...),Countries and Cities.

Regards,

Putta Reddy R

[MustafaBensan]

Hi Putta,

As a starting point you could try the links suggested by laurens.geraedts in his comment in Part I of this blog series.

Regards,

Mustafa.

[Former Member]

Hi Mustafa,

I am following along with your blog and all looks good until I get to the Global Scripts section.  The global script object GEO_FUNCTIONS includes 5 functions:

getCountyFIPS

getGeoType

getStateFIPS

processDrilldown

setMapTitle

I see the script for processDrilldown, but not for the other functions.  Perhaps the script is obvious so was not included, but I'm new to this area of BI so wanted to ask if you could also display the script for those functions.

Also, the processDrilldown includes an “if” statement referring to the levels:

if (currentLevel < levelCounty)

can you let me know where the values for currentLevel and levelCountry come from.

Thanks very much for any help you can provide,

Ian

[MustafaBensan]

Hi Ian,

I'll update the post with the code for the other Global Script Functions soon.

levelCounty is a constant defined as a Global Script Variable (see section 5 (b) above) used to filter the county aggregation level with the ZSUMLEV attribute of the ZGEOID characteristic.  currentLevel is simply another Global Script Variable which acts as a counter/index for the current drill level, with valid values of: levelAll, levelState, levelCounty and levelPlace.

Regards,

Mustafa.

[Former Member]

Hi Mustafa

I am using 2 Geo Json files 1 for states & other for Counties & have used the below Script for  drilldown functionality. But I am not able to zoom in when drilled down on the next layer. Can you please help what I am missing which had caused you to get the Zoom Functionality implemented

Regards

Rahul

[MustafaBensan]

Hi Rahul,

My initial suggestions and questions are as follows:

1) Change

DS_STATE.setFilterExt("Country", GEO_MAP_1.getSelectedMember("Country"));

to

DS_STATE.setFilter("Country", GEO_MAP_1.getSelectedMember("Country").internalKey);

2) Change

DS_COUNTY.setFilter("States", GEO_MAP_1.getSelectedMember("States"));

to

DS_COUNTY.setFilter("States", GEO_MAP_1.getSelectedMember("States").internalKey);

3)  Which event is your script coded in?

4)  In future, to make it easier to update script code, you should directly copy and paste the script text into your post instead of pasting a screenshot image.  This makes it easier to provide edit suggestions.

5)  Can you provide a screenshot of the Additional Properties sheet of the Geo Map to show how the layers have been configured?

Regards,

Mustafa.

[Former Member]

Hi Mustafa
Thanx for your  suggestions regarding the future posts  & efforts  have attached the screenshots of all the 3 layers

1)  changed both the filters to internal key

2)  this even is scripted in the 'onclick' event of Geomap

[Former Member]

Regards

Rahul

[MustafaBensan]

Hi Rahul,

Here are my additional comments and questions:

1)  It looks like you have not specified a GeoJSON Mapping Property for the COUNTY layer.  Can you confirm?

2)  Can you also provide screenshots if the Initial View for the three data sources DS_COUNTRY, DS_STATE and DS_COUNTY?

Thanks,

Mustafa.

[Former Member]

Hi Mustafa

I have not used a Geo Json for the Country layer as on the country layer design studio is capable to detect the countries based on their names so haven't used the Geo Json.

Correct me if I am wrong

Regards

Rahul

[MustafaBensan]

Hi Rahul,

My comment about the GeoJSON was referring to the COUNTY layer and not the COUNTRY layer.  It looks like you haven't assigned a GeoJSON Mapping Property for the COUNTY layer.

[former_member182918]

Hello Mustafa,

Hope you are doing good  !!!!

BW7.4,DS1.6

My map doesn't plot Great Britain data as the country layer.

Map is able to pick up every country data except great britain.

Maps contain United Kingdom has country assigned and from master data we are able to get Great Britain as country name.

Any insights on this .

Regards

Karthik

[former_member204732]

 

Dear Mustafa,

Thanks for your blog.
Would you share the source code related to the global function ?

GEO_FUNCTIONS

• getCountryFIPS


• getGeoType


• getStateFIPS


• processDrilldown


• setMapTitle

Thanks & Regards
Elie

[former_member335357]

Hi Mustafa,

Thanks for sharing your article!

I have a question to ask; can we do Hierarchical  display of the maps? so, the end result would automatically shows the area and metric values depending on the hierarchy level.

your demo shows it would be possible by creating layers, but will it be possible to achieve the same via BEx query ?


Thanks,
Harish

[MustafaBensan]

Hi Harish,

My demo is already based on a BEx Query but you need a flattened hierarchy for driIldown purposes.  It is not possible to use a BW hierarchy for map region drilldown because there is no way to link the nodes of the hierarchy to the corresponding GeoJSON Mapping Property in the custom GeoJSON file.  You need a query result which uses a flattened hierarchy as per my example above.

In your example, the lowest level is Store (0PLANT).  You would need to add the appropriate geographic attributes for the desired region levels to the 0PLANT dimension.

Regards,

Mustafa.

[former_member335357]

Hi Mustafa!

Okay, Thank you for your quick reply and explanation.

Appreciate your help.

Regards,
Harish

[former_member214878]

Hello Mustafa,

 

I must say the best-described post I have seen recently....

I was trying out whatever you said but got a very peculiar problem - can you please tell me what is wrong with my implementation and why all the map is colored here...

My Output is like this  ---  just want to show the regions on the map ,,, thats it ,,,

 



 

 

I have data from report / data source is -



 

 

 

 

 

 

 

 

[MustafaBensan]

Hi Rudra,

I'm glad you found the post helpful.  Regarding your issue, are you sure that the Region dimension values in in your data source, eg. "HUG/01" etc, correspond exactly to the REGION property values in your custom GeoJSON file?

Mustafa.

[former_member214878]

Hello Mustafa,

Thanks for looking into the issue –

yes, the region is compounding char. in BW so the value is exactly is same.

My geoJson file looks like this  —

If you see the property value is exactly as it’s in DataSource. Does it have to do anything with Base Map URL?

Also, would like to highlight that the regions are also getting Printed, these I saw just after I zoomed in till the "Hungary" ... 

1. I am not able to click on every region except 2 with Highest measures
2. Also initial zoom till Country is possible here? 

I have seen on your youtube video that u have initial zoom directly to the US states.. how did u achieve it? 

My output on browser is like - 

[MustafaBensan]

Hi Rudra,

Okay, I see what you mean.  The values seem to be consistent.  You could try removing the Basemap URL just to rule that out, as the basemap isn't needed to display the map.

To set the initial zoom you can apply the CentreMap() method.

By the way, what does your custom GeoJSON file look like when tested at http://geojson.io/

Regards,

Mustafa.

[former_member214878]

Hello Mustafa,

 

After a day break and 2 coffee mugs, I figured out there was a problem with my gEoJson file ...

Its looking great now...

 

Thank you very much for the help 🙂

[MustafaBensan]

Hi Rudra,

Thanks for the confirmation.  What was the problem with your geoJSON file?

[Former Member]

Hello Mustafa,

This blog is great, even it works in Lumira 2.0 (Lumira Designer).

However I saw a weird senerio in my geomap but not exist in your sample. Let me try to explain my senerio.

I created a geo_map with 3 layers, COUNTY layer >> DIST layer(District/Town) >> STORE layer. COUNTY and DIST layer are shape layer with geojson. Store layer is also a shape layer but it setup as latitude and longitude.

In geo_map On Select Event, I worte down code just follow your idea and everything works perfect. I can drill down from County >> Dist >> Store list within the Dist I selected

I also created another button called "Back" and wrote below code to try to drill up from store level back to Dist level.

if (currentlevel == 2) {   //currentlevel = 2 means geomap shows store list within selected Dist.
DS_DIST.clearFilter('YDIST');  //remove drill down filter YDIST dimension.
GEO_MAP.setLayerVisible(STORE, false);
GEO_MAP.setLayerVisible(DIST, true);
GEO_MAP.centerMap(DIST);
currentlevel = 1;
}

However I saw Dist layer is visible but geomap still remember the selection I made in last drill down. Please see below picture.



 

What I want is that when I do drill up, geomap shows the whole Dist for me to do select, just like your sample that County back to State level and all States will be show up in color.





There are also no such function like clear_selection, or set_selected_member.... Is there any way that I could clear the geo_map drill down value? thank you

 

Levitt

[MustafaBensan]

Hi Levitt,

Can you post a screenshot of the Geo Map Additional Properties showing the configuration of Layer DIST?

 

[Former Member]

Hi Mustafa,

 

Here comes the Geo map additional properties of Layer DIST.



 

Had you done any configuration on remove geo_map selection?

I saw the system behavior is that while I click on geo_map for example 114 DIST.

var YDISTselection = GEO_MAP.getSelectedMember('YDIST');

TEXT_1.setText(YDISTselection.internalKey);

// In TEXT_1, it shows 114

 

I also write a code in "Back button" as below.

if (currentlevel == 2) {
TEXT_2.setText(GEO_MAP.getSelectedMember("YDIST").internalKey); //To record GEO_MAP selected member for YDIST
DS_DIST.clearFilter('YDIST');
GEO_MAP.setLayerVisible(STORE, false);
GEO_MAP.setLayerVisible(DIST, true);
GEO_MAP.centerMap(DIST);
currentlevel = 1;
}

In TEXT_2, it also shows 114, which means system still know my last selection is 114. Is there any way that I can remove the geo_map dimension selection value?



 

Thanks,

Levitt

[MustafaBensan]

Can you also post a screenshot of the Initial View of data source DS_DIST?

[Former Member]

Hello Mustafa,

Kindly see below initial view of DS_DIST.

[MustafaBensan]

Hi Levitt,

Your screenshot shows a Select Data screen.  I would like to see the Data Source Initial View screen which should look like this:

[Former Member]

Hi Mustafa,

Thank for your reminding, I missed your meaning. Here it is the DS_DIST initial view.

 

Levitt

[MustafaBensan]

Hi Levitt,

For testing purposes, I suggest you place a Crosstab on your canvas and assign it to data source DS_DIST.  Then observe the contents of the Crosstab as you drill down and back.  This way you can confirm if the filter and clear filter is being applied as expected.

Let me know the results.

Regards,

Mustafa.

[former_member597402]

Hello Mustafa

Is there a way to do drill down from State -> Region with in that State -> County  for  USA

Thank You