Data visualization | Geospatial panels

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contents

In my previous article, we discuss how to use Qlikview to display tabular information. Now, Let's think about a scenario where we need to represent location-based sales for a company operating in India..

We can represent this information in many ways: tabular format, graphical representation or representation on geographic maps. Let's take a look at each of these representations and see why we should represent information in Geo-Map.

main-1024x373-9232102

Some key benefits of using geospatial visualization are:

  • We can easily understand the distribution of the presence of organizations across the country.
  • Easy to represent a large number of locations compared to tabular or graphical representation
  • More intuitive decision making for the business owner.

Due to these benefits, there has been a marked increase in geospatial visualizations. Almost all BI tools have started offering this visualization as a standard option. (for instance, Qlikview, Painting, SAS Visual Analytics, etc.).

When I started using Qlikview a few years ago, I wanted this visualization to be part of the dashboards that I prepare, but i couldn't find a well structured tutorial about it. Therefore, I thought about contributing to this article for the benefit of other analysts like me, who are struggling with a similar question.

Steps to represent information on Google Map:

  1. Load base data (with location latitude and longitude).
  2. Generate variables from google maps
  3. Create a scatter plot and set up Google Map

Loading the base data with the latitude and longitude of the location:

Below is the Excel file used for this tutorial. Upload this to Qlikview:

qlikview_base_data_google_map-7424423

Qlikview Google Maps Base Data

Make sure the latitude and longitude variable names are in lowercase, since the google map variable is generated in lowercase only.

Generate Google Map Variables:

Create another tab in the script (Archive -> Edit script. Later, in a new window Tab.> Add new). Then paste the following code to generate the Google Map variable.

// Google Maps key

// get a key here http://code.google.com/apis/maps/signup.html

gmap_key = 'ABQIAAAAPTYGDOAE5g8mVF5kIaY54RSHwNu1JdqUbdjDP-YY4oym4d2mTxSRdsISI-1rwwnl-9gjJrMO3oTkMA';
max_zoom_level = 5; //maximum value 17 
def_map_size = 400;

// Variables required for calculating map
// No need to change these
var_pi180= '=pi()/180';
var_lat_offset ="0";
var_mc2 = '= 256 * pow(2,$(var_zoom))';
var_mc1 = '= 256 * pow(2,($(var_zoom)-1))';
var_mid_lat ="= min(latitude)+(1+var_lat_offset)*((max(latitude)-min(latitude))/2)";
var_mid_long= '=min(longitude)+(max(longitude)-min(longitude))/2';
var_zoom = '= max(aggr(if(max( round(256*pow(2,(_zoom_level -1)))+( longitude *((256*pow(2,_zoom_level ))/360)) )-min( round(256*pow(2,(_zoom_level -1)))+( longitude *((256*pow(2,_zoom_level ))/360)) ) <map_size_x AND max((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(without((latitude)*pi()/180)))/(1-(without((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))-min((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(without((latitude)*pi()/180)))/(1-(without((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))<map_size_y,_zoom_level,1),_zoom_level))';
var_maptype ="=if(isnull(only(maptype)),fieldvalue( "&chr(39)&'maptype'&chr(39)&', 1 ),maptype)';
map_size_x= '400';
map_size_y= '400';
SET HidePrefix='_' ;
// Field required for calcualting best zoom level
_zoom_level:
Load RecNo( ) as _zoom_level autogenerate(max_zoom_level);
maptype:
LOAD * INLINE [maptype roadmap mobile satellite terrain hybrid ];

After pasting the code, click on 'Save’ and then on 'OK’ to close the window and reload the script.

qlikview_google_map_variable-9549363

Google map variable

Create a scatter chart and configure it for Google map:

To create a chart, “New sheet object” -> “Graphic” -> Follow the steps shown in the images below

qlikview_google_map_1-9797272

qlikview_google_map_2-9072213

qlikview_google_map_31-1986077

Paso -10: Then, go to the SORT tab, press the back button and write the following code in the expression:

length: “= SUM (round (256 * pow (2, ($ (var_zoom) -1))) + (longitude * ((256 * pow (2, $ (var_zoom))) / 360)))”

latitude: “= SUM (((256 * pow (2, ($ (var_zoom) -1))) + ((0.5 * log ((1+ (without ((latitude) * pi () / 180))) / (1 – (without ((latitude) * pi () / 180))))) * ((- 256 * pow (2, $ (var_zoom))) / (2 * pi ()))))) ”

Paso -11: add two more expressions: City and Sales

Town:= “Town”

Sales: “= Sum (sales)”

qlikview_google_map_51-1024x451-2819873 qlikview_google_map_6-1024x452-3139163

Copy and paste the following code in the steps 21 Y 24

X axis:

stactical minimum: = (256 * pow (2, ($ (var_zoom)-1))) + ( var_mid_long * ((256 * pow (2,$ (var_zoom))) / 360)) -redondo (map_size_x/ 2)

Static Max .: = ((256 * pow (2, ($ (var_zoom)-1))) + ( var_mid_long * ((256 * pow (2,$ (var_zoom))) / 360)) + ronda (map_size_x/ 2))

Axis y:

Static minimum: = ((256 * pow (2, ($ (var_zoom)-1))) + ((0.5 * log ((1+ (without (var_mid_lat* pi () / 180))) / (1- (without (var_mid_lat* pi () / 180))))) * ((- 256 * pow (2,$ (var_zoom))) / (2 * pi ()))) + ronda (map_size_y/ 2))

Static Max .: = ((256 * pow (2, ($ (var_zoom)-1))) + ((0.5 * log ((1+ (without (var_mid_lat* pi () / 180))) / (1- (without (var_mid_lat* pi () / 180))))) * ((- 256 * pow (2,$ (var_zoom))) / (2 * pi ()))) – redondo (map_size_y/ 2))

qlikview_google_map_7-9757087
Copy and paste the following code in step 26:

 ='http://maps.google.com/staticmap?center="
 &
 on one(var_mid_lat, "##############', '.', ',' ) 
 &
 ','
 &
 on one(var_mid_long, '##############', '.', ',' ) 
 &
 '&zoom=$(var_zoom)'
 &
 '&maptype="&var_maptype
 &
 "&size="&map_size_x&"x'&map_size_y
 &
 '&key='&gmap_key & '&.jpg'

qlikview_google_map_8-2504565

qlikview_google_map_92-1024x888-8000006

On the Title tab, width and height according to map_size_x and map_size_y (defined in the script).

Finally, we get the desired result.s:

qlikview_google_map_101-3770031

This is an example where we represent information on a geographic map in Qlikview. Have you done something similar in the past? Do you have any experience or case studies illustrating the same in Qlikview or other visualization tools?

Share your thoughts through the comments below..

If you like what you have just read and want to continue learning about analytics, subscribe to our emails, Follow us on twitter or like ours page the Facebook.

Subscribe to our Newsletter

We will not send you SPAM mail. We hate it as much as you.