Enhance Your Qlikview Maps with a Few Simple Tweaks (& fix them if they’ve broken).

Firstly apologise for the amount of time its taken to update the blog, I can’t believe it’s been nearly 3mths since my last post. The reasons are many but chief amongst them is the fact I’ve been thinking a bit deeper about Qlikview and BI in general and have been creating some innovations in those areas that I may be able to divulge shortly.

Anyway onto this post. Earlier this week many of you will have noticed that your Qlikview GoogleMaps integrations stopped working, the reason being that Google stopped supporting versions 2 of their ‘Static Map API’ which Qlikview utilised and moved wholly to v3. Firstly here’s a straight like for like dynamic URL replacement provided by Alexander Karlsson (@MindSpank): http://pastebin.com/sDXzd1Yg simply swapping your old v2 for his URL should get you back to where you were.

As everyone now needs to start using v3 it seems like an apt time to share a v3 mapping innovation I developed a few weeks ago.

Currently I’m working in the Insurance industry and one of their primary concerns is the risk of flooding, they really like to see their insurance exposures plotted on a map therefore allowing them to see where they may be over exposed and the standard setup allows this to a degree but doesn’t work as well as it might. This led me to trawl through the GoogleMaps API documentation (https://developers.google.com/maps/documentation/staticmaps/) to find out what the limits of it were and it turns out with v3 the limits are beyond what standard Qlikview GoogleMaps can achieve.

After reading the documentation it became clear that with the right URL many of the map elements can be tailored to suits ones needs, for instance in my case looking at flood risk I can remove all the labels, show terrain and crucially highlight all the bodies of water in a colour of my choosing and the result’s shown below:

Water Highlight Mapping

Its not showing anything that the standard setup doesn’t but it shows the details that my client is most interested in an enhanced manner and really helps the high risk exposures to jump out. The URL I’m using is: =’http://maps.googleapis.com/maps/api/staticmap?center=’&Replace(var_mid_lat,’,’,’.’)&’,’&Replace(var_mid_long,’,’,’.’)&’&zoom=$(var_zoom)’&’
&style=element:labels|visibility:off&format=png&sensor=false&size=400×400&Scale=2&maptype=terrain&style=feature:water|element:geometry.fill|visibility:on|invert_lightness:true|color:0x0000ff Now the eagle eyed amongst you will notice I’m using a higher fidelity scatter map chart than normal, this is because I’m using a custom Long Lat file that runs from the highest PostCode Area (AB) down to the full Post Code (AB1 2CD) via District and Sector with Long & Lats for each giving the higher fidelity that my clients need. Incidentally the standard ‘postcodes.xls’ file that many people in the UK seem to be using is a bit misleading; firstly it only covers the PostCode Districts (AB1) so everything ends up very aggregated on the map and also several Districts share the same Long Lat values (7 in central Birmingham for instance) this can therefore lead to misleading charts.

What else can be done with the new v3 of the maps API? Well I strongly recommend you go and read the documentation and experiment yourself but to guide you there’s a brilliant ‘wizard’ to get you started: http://gmaps-samples-v3.googlecode.com/svn/trunk/styledmaps/wizard/index.html tailor the map as you chose then copy and paste the URL the integrate the variables in Qlikview.

Basically you can colour elements how you choose, turn off labels and even add markers as well as the v2 ability to switch the map type which all told creates a fantastically flexible tool.

Here’s a URL I knocked together to show Lund in Sweden, with the roads highlighted in Qlikview Green and a maker showing where (I think) The Qliktech offices are – it’s an ugly map but it shows the flexibility! http://maps.googleapis.com/maps/api/staticmap?center=55.715892,13.220093&zoom=16&format=png&sensor=false&size=640×480&style=element:labels|visibility:off&maptype=hybrid&style=feature:road|element:geometry|visibility:on|color:0x00ff00&markers=color:0x00ff00|label:Q|55.716572,13.221248

Qlikview HO Map

So get thinking: what do your clients need to be seeing on their maps? Is the standard RoadMap really the best solution?

Hopefully when Qlikview v12 comes along this solution won’t be needed as Qliktech will have created a dedicated mapping object that takes full advantage of version 3’s functionality.

As always I hope this has been of use.

Hopefully it won’t be as long until the next post.

All the best,

Matt

Advertisements
Comments
10 Responses to “Enhance Your Qlikview Maps with a Few Simple Tweaks (& fix them if they’ve broken).”
  1. Matt,
    I love your blog and find your examples to be thrilling and inspiring. This post has raised some confusion for me. I have assumed that the google maps sample was a workable sample but would require a licensing investment that my clients found unpalatable, one told me they investigated the costs and found it would be more than the cost of their entire Qlikview deployment(SBE customer). Anyway, if you have any specific knowledge about the Terms of Use for the features you have shown please comment.

    Thanks

    Chris

    • qvdesign says:

      Chris,

      Many thanks for the kind words.

      As far as I know the situation regarding access to the GoogleMaps API keeps changing – sometimes you seem to need a key, sometimes you don’t and sometimes they suggest you need to pay.

      The situation as I understand it from the UK is that you limited to 25,000 API calls in 24hrs and you don’t need to register or provide a Key in the call. Quite how that’s calculated I don’t know: is it from one machine, IP etc?? I’ve only ever maxed out the usage once so it seems to work quite well.

      Whether this would be viable for an enterprise deployment I don’t know as it would likely be a few servers making all the calls for a wide body of users so you may then need to look at licensing…which can be very expensive!

      In my examples I’m not using any Keys and I haven’t had to register any IP Addresses.

      Hope that helps.

      All the best,

      Matt

  2. Bjorn says:

    Nice post, Matt!
    FYI the location of the QlikView Lund HQ is correct, although on the map only one building is finished 🙂

  3. mindspank says:

    Nice example! The static maps also offers up street views, nice in CRM-apps to show a customer building for example.

    On the licensing issue, if your website (in our case a qv app) resides behind a firewall and is not easily accessible to the public you would need a premier agreement with Google. It’s a bit hidden but this text is from 9.1.1(b) in the ToS:

    (b) Public Access (No Firewall). Your Maps API implementation must not operate (i) only behind a firewall; or (ii) only on an internal network (except during the development and testing phase); or (iii) in a closed community (for example, through invitation-only access).

  4. Hi – This looks great and is exactly what I need to do for a project at the moment…

    This is probably a stupid question but I’m not clear on how to get to the stage where I have a map on my qlikview document. I see you reference a URL but how do i get this to show on my qlikview document? Can you post an example ?

    Also – would you be willing to share your UK postcode file ?

    • Jim says:

      Brian

      UK postcode CSV files containing Latitude/Longitute as well as Eastings/Northings can be found at:

      http://www.doogal.co.uk/UKPostcodes.php

      I’m new to mapping in QlikView – I’m trying to map totals of occurances by postcode from an an Excel file. Any ideas would be appreciated.

      Jim

    • qvdesign says:

      Brian, Jim,

      Thats’s exactly where I got my base file from – a really great resource.

      There are some great examples on the Qlikview Community site that will give you some code samples to be ‘inspired by’.

      If you still have issues let me know and I’ll see what I can do.

      All the best,

      Matt

      • Jim says:

        Matt, I’ve used the CSV tables as I mentioned in my earlier post to populate each row in my Excel table with Lat/Long and Eastings/Northings for each full postcode.

        I would like to be able to display a postcode heatmap similar to your flooding map where the colours would vary according to the number of occurrences by postcode (I’m not a big fan of Google’s markers). I’ve searched the QlikView Community site but I keep coming up with KML files and I’m unable to find KML’s with polygons by full postcode for the area I need to map which is Bury, Oldham and Rochdale.

        Any help would be greatly appreciated.

        Jim

      • qvdesign says:

        Jim,

        Getting KML files to display in Qlikview is possible at least in theory via an Extension (it’s a bit tempremental as they all seem to be and only works w IE8+) you’ve probably already seen it on the Community: http://community.qlikview.com/thread/45125

        The KML files themselves are much trickier as anything below Postcode District seems to be a closely guarded secret. If you have some budget you could try: http://www.ordnancesurvey.co.uk/oswebsite/products/code-point-polygons/index.html To do the whole country would be expensive but just say Lancashire might not be that bad.

        I’ll keep looking and let you know if I find anything.

        All the best,

        Matt

  5. sasken says:

    Frist of all am not able to integrate the google maps with QV 11 PE ?? Can someone plz. send step by step instructions to do the same…

    regards
    Sathia
    sathia.rams@yahoo.in

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: