topic Re: Implementing (Google) maps in QV in QlikView

Implementing (Google) maps in QV

bjgrijpstra — Mon, 01 Jul 2013 08:23:27 GMT

Hey everyone,

I've been trying to implement maps in QV for a week now and I still haven't figured it out. I really hope you guys want to help me. I've followed the

'Steps to Implement Google Map in Qlikview' document but I think it is outdated. Below you can see the code I am using in the script editor (I do have a gmap key). I will also attacht the .xlsx file and the qvw. My problem is with displaying a dynamic map (as a chart) and plotting the latitudes and longitudes on it. Can someone please explain to me how it is done properly? Many thanks in advance!

- Bob

PS: If someone thinks I should try and use open layer maps I'm very open to that, but I don't know how!

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;€ -#.##0,00';
SET TimeFormat='h:mm:ss';
SET DateFormat='D-M-YYYY';
SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';
SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';
SET DayNames='ma;di;wo;do;vr;za;zo';
CustomerTable:
LOAD Customer, 
     City, 
     Quantity
FROM
(ooxml, embedded labels);
let noRows = NoOfRows('CustomerTable')-1;
for i=0 to $(noRows)
    let a = peek('Customer',$(i),'CustomerTable');
    let b = peek('City',$(i),'CustomerTable');
    let c = peek('Quantity',$(i),'CustomerTable');
       
    GeocodeResponse:
    LOAD
    status,
    '$(a)' as CustomerName,
    '$(b)' as CustomerCity,
    '$(c)' as CustomerQuantity,
    [result/geometry/location/lat] as latitude,
    [result/geometry/location/lng] as longitude
    FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(b)&sensor=false] (XmlSimple, Table is [GeocodeResponse]); 
next i;
// Google Maps Key
gmap_key = '';
max_zoom_level = 14;
def_zoom_level = 1;
def_map_size = 400;
// Variables required for calculating map
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        = '=median(latitude)';
var_mid_long    = '=median(longitude)'; 
var_zoom        = '=if(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)))<def_map_size AND max((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))-min((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))<def_map_size,_zoom_level,null()),_zoom_level))>def_zoom_level,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)))<def_map_size AND max((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))-min((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))<def_map_size,_zoom_level,null()),_zoom_level)),def_zoom_level)';
var_maptype        = '=if(isnull(only(maptype)),fieldvalue('&chr(39)&'maptype'&chr(39)&',4),maptype)'; 
// Calculate best zoom level
set HidePrefix    = '_';
_zoom_level:
LOAD RecNo() as _zoom_level AutoGenerate(max_zoom_level);
maptype:
LOAD * INLINE [
    Maptype
    roadmap
    mobile
    satellite
    terrain
    hybrid
];

Re: Implementing (Google) maps in QV

luciancotea — Mon, 01 Jul 2013 08:46:30 GMT

1. You read latitude and longitude as strings, not as number (I changed thousand separator with decimal separator)

2. Google API changed, new call is like this:

='http://maps.googleapis.com/maps/api/staticmap?center='

&var_mid_lat

&','

&var_mid_long&'

&zoom=$(var_zoom)'&'

&maptype='&var_maptype&'

&size='&def_map_size&'x'&def_map_size&'

&sensor=false'

See attachment

Re: Implementing (Google) maps in QV

bjgrijpstra — Mon, 01 Jul 2013 08:59:25 GMT

Thanks for the quick reply! You are awesome. Now for a quick follow up question, is it possible to have it zoom in on a certain region? I am currently only interested in cities in the Netherlands and at the moment it shows the entire world map.

EDIT: Changed def_zoom_level to 7 and it showed me the map I was looking for! However, the plotted circles representing the quantites are not plotted correctly.

For example: when I click on Amsterdam in my listbox, it shows Amsterdam in the center of my map. The quantity (sold in Amsterdam) should be represented as a circle on top of it.

Re: Implementing (Google) maps in QV

Mon, 01 Jul 2013 09:54:04 GMT

The way Google maps works is: you receive a map of a custom size centered in a custom point. So, you calculate the map center with a formula in the 'var_latitude' and 'var_longitude' variables. Also you have to have a certain zoom to fit all the points in the map.

Now, in the scatter chart, the first 2 expressions sets the location and the 3th parameter sets the circle size.

Re: Implementing (Google) maps in QV

bjgrijpstra — Mon, 01 Jul 2013 10:15:39 GMT

Thanks for that extra information. Could you then maybe explain to me why the 3rd expression that I've selected (CustomerQuantity) isn't shown on the map at the location corresponding to the first 2 expressions (the lat-long position).

Re: Implementing (Google) maps in QV

bjgrijpstra — Mon, 01 Jul 2013 15:53:29 GMT

Ok so I've changed my chart from scatter chart to grid chart. And it looks much better. I've used longitude and latitude as my dimensions and quantity as my expression. However when I select one customer, the circle is placed to the right of where it should be. When I select multiple customers, it makes even less sense. Can someone point out what is going wrong here? Thanks in advance!

Re: Implementing (Google) maps in QV

luciancotea — Mon, 01 Jul 2013 16:43:04 GMT

1. Using Grid-Chart instead of scatter-chart is one problem.

2. Link the Customer table with the geo-info table (Use "Customer" instead of "CustomerName")

3. Use the Customer as dimension in the scatter-chart

4. You have a problem with the "zoom" variable

... and more. Don't have the time to get into details.

Re: Implementing (Google) maps in QV

bjgrijpstra — Mon, 01 Jul 2013 18:56:20 GMT

Again, very much appreciated! I've linked the tables using "Customer" and I'm back to using a scatter plot. I've edited the zoom variable and now it works fine. It seems a bit odd to me that my longitude and latitude axes are of the order of magnitude of thousands. If in the near future you would want to check my program again I would be extremely grateful!

Re: Implementing (Google) maps in QV

luciancotea — Mon, 01 Jul 2013 19:03:41 GMT

In the chart, longitude and latitude expressions are not geo-codes anymore, but calculated distance from the axis of the chart.

Re: Re: Implementing (Google) maps in QV

neha_shirsath — Thu, 26 Dec 2013 09:56:56 GMT

Lucian Cotea

I have tried your code.

Initially it was working fine but when i tried it number of times after that it is not showing latitude and longitude now.

Is there any limitation for this?

-Neha

Re: Implementing (Google) maps in QV

luciancotea — Thu, 26 Dec 2013 11:26:23 GMT

Yes, there is a limit: Usage Limits for Google Maps API Web Services - Google Maps API for Business — Google Developers