
Geocoding and Maps viz
Geocoding and visualizing data on maps is a hot topic these days. In this tutorial I will explain how to get longitude and latitude coordinates from a geocoding API and use those to visualize data in QlikView.
With this post I also post a zip file with some assets that you’ll use to get started with this tutorial.
This tutorial covers the next chapters:
1. Geocoding with QlikView using the MapQuest API
2. Visualizing data with Longitudes and Latitudes on a map
1. Geocoding with QlikView using the MapQuest API
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
1.1 Understanding the GeoCoding API
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.
1.2 Loading a dataset to request GPS-coordinates
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.
1.3 Checking and loading the right variables for the request
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.
1.4 After reloading
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.
2. Visualizing data with Longitudes and Latitudes on a map
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.
2.1 Understanding the elements that are required for map visualization
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.
2.1.1 Script variables
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)
2.1.2 Objecttype
The object type to use is ‘Scatter chart’. This is the only charttype that can handle a maps visualization.
2.1.3. Dimension
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.
2.1.4. Expressions
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.
2.1.5. Axes
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))
2.1.6. Object configuration
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
2.1.6.1. Style
To make the object simple to read it is adviced to use the ‘flat’ style bubbles.
2.1.6.2. Presentation
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’)
2.1.6.3. Axes
To get off excessive details and for aesthetics it is advisable to hide the axes in this visualization. See 1 and 2.
2.1.6.4. Expressions
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.
2.1.6.5. Colors (Important! Because here you setup the image for static maps in the API-request!)
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.
2.1.6.6. Caption
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.
Summary
This could be the end result of geocoding datasets and visualizing it on a map.
It is free as long as you add the correct attribution to your page/app/map, check their ToS for the correct wording and help keep open source open by helping out 🙂
Thanks for your addition Alex, very useful.
Thank you for the tutorial – it is something I am currently working on. In 1.2 however, you say that you “load the script” from any datasource. I am not clear what you mean by this? What application are you using to do the actual API call? Is it within Qlikview or a db?
Matthew, the script loads the records, which you want to enrich with the Lon/Lat coordinates, from the excel sheet. You’ll need to download the assets in the zip file. These files will help you with what you want from this post. Thanks for your feedback I now see I could have written paragraph 1.2 a bit more clear. Sorry for that. I hope you will figure it out. If not, just ask.
The documentation states you can use streetaddress in the GroLocations script to provide more accurate returns. Can the script be updated to include UK post code ? do you have an example of this application to share for UK addresses?
Jason, you could try and use the postalCode parameter. Add a column with postalCode to your excel file and add the field to the script and the API url. Then it should return GPS coordinates based on the postalCode. Hope this helps.
For deeper understanding of the mapQuest API also visit their user forum.
Hi iim trying to use only the postalCode parameter to return UK long/lat coordinated using the following response call
FROM [http://www.mapquestapi.com/geocoding/v1/address?key=$(vKey)&outFormat=xml&postalCode=$(vPostalCode)]
It doesn’t seem to like UK postcodes and my app is based on plotting customers only with a post code field.
Any ideas or work arounds, examples welcome?
Jason,
Maybe you could also use the country parameter together with the postalCode parameter. If it doesn’t work, please visit the forums at MapQuest. Maybe there are other readers who have experience on this?
Hi Patrick,
Good tutorial! One potential improvement is to cache the addresses that you’ve already geocoded in a local QVD. this dramatically improves incremental loads. Another improvement would be to package everything into a subroutine, so you can just drop it into any app you like (that’s what I do).
Cheers,
Barry
Barry, thanks for your response. Of course this is the case, storing your incremental results will boost performance of your (partial) reloads. And adding the geocoding script to a subroutine is a very tidy and efficient solution. Thanks for the additions! I’ll see whether I’ll find the time… 😉
HI Patrick
I am new to Qlikview, how do i incorporate the street into the application? Thanking you in advance.
What is it that you want achieve exactly?
I need to have the map display the exact location of a place. Right now i have a number of places being place on the same location with the same latitude and longitude
Thanks Patrick. This is a brilliant resource. There are a lot of outdated examples and opinions on Qlik Community, but nothing that pulls together everything someone would need to get this working.
Hi Patrick
Loving your work. I’m struggling to get out the starting black and get stumped here every time…
I run Main, then GeoLocations then when trying to run GeoCoding it returns this error:
“The system cannot locate the resource specified”
Could you perhaps give me some pointers?
Thanks
Could you maybe send me a screenshot and the inputfile you are using by e-mail?
Hi Patrick, sounds like a very tutorial to solve one of my major issues today, but unfortunately i’m not able to get the script running. It always states: “The following error occurred:
LOAD statement only works with lib:// paths in this script mode
”
The error seems to be about this line: FROM [http://www.mapquestapi.com/geocoding/v1/address?key=$(vKey)&callback=renderGeocode&outFormat=xml&….
I’ve tested it with a valid key on latest version of Qlik Sense desktop
Any ideas?
You should try setting your installation to legacy mode.
https://help.qlik.com/sense/1.1/en-US/online/Subsystems/Hub/Content/LoadData/DisableStandardMode.htm
I really hope that they sort out Legacy mode at some point. The name is not helpful – why is it ‘Legacy’ to want to be able to access a URL with parameters in it, or a location on disk with parameters for that matter? It should be given a sensible name, that indicates that it is slightly less secure, and be an option that can be changed in the UI. Having to find a config file and change it makes it feel like a hack, which will put people off of getting the full benefit of the tool.
Just my humble opinion, of course.
Agree with Steve 100% on the Legacy mode point – it seems odd in 2015 when so much data is stored behind HTTP services that Qlik have introduced this confusing hurdle.
Their docs even state
“Disabling standard mode can create a security risk by exposing the file system.”
And as far as I can tell make no reference to how this helps with HTTP services.
See also this thread on the Qlik community – if you agree please add a comment!
https://community.qlik.com/thread/134666?start=0&tstart=0
Thanks, very useful and clear post. Mapquest working great with Country and city.
For spaces in your data (e.g. city or address), I found replacing with +.