Geocoding and Maps viz

In some situations your dataset regarding ‘Geography’ doesn’t have GPS coordinates available. In this situation you could send a request to an online GeoCoding API which sends you a response with the longitudes and latitudes for use in your QlikView apps. A good and free API for this is the MapQuest API, which doesn’t restrict you in the number of request you can send.

In the next paragraphs we will dive into:
1.1 Understanding the GeoCoding API
1.2 Loading a dataset to request GPS-coordinates
1.3 Checking and loading the right variables for the request
1.4 After reloading

The MapQuest API let’s you send a request to an API and sends back a response for data that is available. This request is done by setting the right variables into a url. The standard url for handling these requests with MapQuest is:

http://www.mapquestapi.com/geocoding/v1/address?key=<YOUR-KEY-HERE>&callback=renderGeocode&outFormat=xml&city=$(vCity)&country=$(vCountry)&street=$(vStreet)]

In this url you can see the following variables:
1. key: This variable will take your assigned API-Key. This key can be requested here: http://developer.mapquest.com/. Register an account by clicking ‘Join’. A key will be send to you at the e-mailaddress you provided.
2. callback: is the requesttype. Here you will use ‘renderGeocode‘ for letting the API know you are requesting for Lat/Lon coordinates.
3. outFormat: this will take ‘json/csv/xml’ as input for the appropriate format in which you will read the coordinates. In this case we will define ‘xml’ as it is used for reading with QlikView.
4. city: will take search criteria for the city in which the request will look for the most adequate coordinates
5. country: will take search criteria for the country in which the request will look for the most adequate coordinates
6. street: will take search criteria for the street in which the request will look for the most adequate coordinates

In the image the generated response will be read by QlikView to enrich the data with geodata.

In the script you will find a sheet called ‘GeoLocations’. This sheet can load data from any source to use in the GeoCoding request process. In this case we only loaded the available fields for ‘City’ and ‘Country’. But if you have a street address to provide in this data, the response will be more accurate.

In the script example (see image), you will see we presetted a few variables to be handled by the API. You can adjust the script and variables according to your needs.

After you press reload, the process for doing requests at the API will be started. The execution of the script will loop through all available records in the dataset and will return the data as defined in the ‘GeoCoding’ sheet.

After reloading the data you send with the request should be enriched. An example with the added data can be seen in the image. We started with only ‘City’ and ‘Country’. The process of requests now added a few fields of possible interest to our QlikView app.

When we have a dataset that has Longitude and Latitude coordinates available, we are able to visualize data onto a map like the example image.

To get to a working example and implemantation of a maps visualization, we first need to explain the steps that are required to get there. The following steps are mandatory to get the visualization up and running.

2.1.1. Script variables
2.1.2 Objecttype
2.1.3. Dimension
2.1.4. Expressions
2.1.5. Axes
2.1.6. Object configuration

To get to know more about ‘using maps’, you could also search the QlikCommunity as this is a topic that is discussed many times.

All example apps which use maps, apart from extensions, will use the variables as seen in the example image. A few variables that are important for explaining are the following:

1. var_mid_lat: This variable is used for calculating the center of the map by looking at the latitude coordinates. This variable will therefor be used in sending a request for the static map APIs.
2. var_mid_long: This variable is used for calculating the center of the map by looking at the longitude coordinates. This variable will therefor be used in sending a request for the static map APIs.
3. var_zoom: This is the variable for calculating the appropriate zoom level on the map and is defined by the detaillevel of the selection and outer coordinates regarding this selection. This variable will also be used in the url for requesting a static map image from the API.
4. map_size_x: Is the width of the image that is requested. This is also used in the url as a parameter for the request.
5. map_size_y: Is the height of the image that is requested. This is also used in the url as a parameter for the request.

var_maptype is a variable that can be used by some APIs for configuring and selecting the different maptypes available.

The script provided in these apps is always required (and in most cases) doesn’t need any adjustments. The only thing you have to check are the fieldnames for the longitude and latitude values. These have to match the names in the scriptvariables. (See grey rectangles)

The object type to use is ‘Scatter chart’. This is the only charttype that can handle a maps visualization.

The dimension to use is a dimension that is able to plot the coordinates and therefor needs to be related to these values in some way. Most of the times this will be a city or street which holds the coordinates on the same record in the dataset.

To get the visualization up and running a few expressions have to be used.

1. The expression for longitude
round(256*pow(2,($(var_zoom)-1)))+( longitude *((256*pow(2,$(var_zoom)))/360))-8

2. The expression for latitude
((256*pow(2,($(var_zoom)-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))
/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,$(var_zoom)))/(2*pi()))))+4

3. An expression for displaying ‘bubble size’
4. An expression for a ‘text popup’

The numbers in this example correspond to the order in the example image for these expressions.

The expressions for longitude and latitude in these examples will (in most cases) be good enough to reuse in your own apps.

To get the right minimum and maximum values on the scale, you need to setup the axes with the right expressions. This will cause the chart to zoom in and out on the image according to the right detaillevel of the data that is selected. In most examples you will find the following expressions used to realize this:

X-Axis
Minimum:
(256*pow(2,($(var_zoom)-1)))+( var_mid_long *((256*pow(2,$(var_zoom)))/360)) –round(map_size_x/2)

Maximum:
((256*pow(2,($(var_zoom)-1)))+( var_mid_long *((256*pow(2,$(var_zoom)))/360)) + round(map_size_x/2)-15)

Y-Axis
Minimum:
((256*pow(2,($(var_zoom)-1)))+((0.5*log((1+(sin(var_mid_lat*pi()/180)))/(1-(sin(var_mid_lat*pi()/180)))))*((-256*pow(2,$(var_zoom)))/(2*pi())))+round(map_size_y/2))

Maximum:
((256*pow(2,($(var_zoom)-1)))+((0.5*log((1+(sin(var_mid_lat*pi()/180)))/(1-(sin(var_mid_lat*pi()/180)))))*((-256*pow(2,$(var_zoom)))/(2*pi())))-round(map_size_y/2))

To get a nice and effectively working visualization, it is adviceable to do some configuration in these next elements:

2.1.6.1. Style
2.1.6.2. Presentation
2.1.6.3. Axes
2.1.6.4. Expressions
2.1.6.5. Colors (Important! Because here you setup the image for static maps in the API-request!)
2.1.6.6. Caption

To make the object simple to read it is adviced to use the ‘flat’ style bubbles.

For the best reading and interaction options possible it is advised to use the following settings on the ‘Presentation’ tab:

1. Max Bubble Size: You should, depending on the amount of bubbles, look at the maximum bubble size. It will be harder to read when these bubbles overlap eachother for most of the time.
2. You should take off the labels when the visualization is very crowded with bubbles.
3. Switch off Pop-up labels when you want to use your own data-labels when you do a mouse-over on the bubbles. (We already discussed creating your own labels at ‘2.1.4 Expressions’)

To get off excessive details and for aesthetics it is advisable to hide the axes in this visualization. See 1 and 2.

For design purposes and clarity we could think of the following settings:

1. Colors: When you would want a certain color coding in your bullets, the ‘background color expression’ would be ideal to calculate these colorschemes for you. When bullets in some cases would overlap, it is advisable to use an argb() – code for coloring. This way the overlap of bullets is still visible.
2. Text as Pop-up: When you use this check-box, an expression value will be shown on a bullet when hovering over that bullet.

This is the section where we get to configure the requests for the static maps APIs. The following steps and variables have to be defined before the map images can be loaded into the object:

1. Check ‘Dynamic Image’
2. Open the expression editor for configuring the image response from the API:

As mentioned in 2.1.1. Script variables (Check this paragraph for explanation of the variables in the url seen in the example image), this expression will hold the url, for the request, that will respond with the image for the object.

Uncheck the checkbox for ‘Show Caption’. A better way to self-explain this object is to add a custom title in maybe the title of the object or to use a textobject with the title.

This could be the end result of geocoding datasets and visualizing it on a map.