Gestione delle mappe geografiche in QlikView

Ermanno Goletto
Microsoft MVP Directory Services - MCSE - MCITP - MCSA
Gestione delle mappe geografiche in QlikView
Introduzione
QlikView è un software di business analysis che consente di eseguire rapidamente analisi e reporting anche in
modalità self-services. Oltre a funzionalità ETL, QlikView offre anche la possibilità di avere a disposizione una suite di
controlli grafici, al momento però non esiste un controllo nativo specificatamente pensato per la visualizzazione dei
dati su di una mappa geografica.
In ogni caso è comunque possibile gestire all’interno di QlikView analisi su mappe geografiche, ma occorre gestire
due problematiche: il rilevamento delle coordinate geografiche delle località e il rendering della mappa geografica.
E’ possibile sfruttare all’interno di QlikView servizi online, come quelli offerti di Google e OpenStreetMap, sia per
ricavare le coordinate geografiche, che per ottenere le mappe. Per quanto riguarda la gestione del rilevamento delle
coordinate geografiche, l’utilizzo di servizi online può allungare i tempi di elaborazione in quanto ogni rilevamento
coincide con una chiamata al servizio. Di conseguenza è preferibile, se possibile, avere già a disposizione le
coordinate geografiche ed evitare che sia QlikView a doversi occupare del rilevamento.
Argomenti
Ricavare latitudine e longitudine tramite Google Geocoding API (V3) ..................................................................... 2
Ricavare latitudine e longitudine tramite OpenStreetMap Nominatim service ....................................................... 3
Visualizzazione di mappe tramite l’utilizzo dell’estensione free QlikView Qlik Map ............................................... 5
Visualizzazione di mappe tramite una Google Static Maps V2 ................................................................................. 7
Passi per la creazione di un grafico a dispersione con una mappa di sfondo su cui visualizzare i dati ................ 8
Visualizzazione di mappe tramite una Open StreetMap Static Map ...................................................................... 14
Conclusioni .............................................................................................................................................................. 14
Luglio 2013
Pagine 14
Gestione delle mappe geografiche in QlikView
Sysadmin.it
Ricavare latitudine e longitudine tramite Google Geocoding API (V3)
E’ possibile ricavare latitudine e longitudine tramite le Google Geocoding API a partire da un indirizzo usando una
chiamata ai web services di Google del tipo:
http://maps.googleapis.com/maps/api/geocode/xml?address=Torino&sensor=false
L’indirizzo può avere il seguente formato:
Indirizzo, N° Civico, Città, CAP Provincia (esempio Via Roma, 55, Nichelino, 10042 TO)
Tali Web Services consentono di eseguire 2.500 chiamate al giorno e sono utilizzabili alle seguenti condizioni (per
maggiori informazioni si veda Maps API Terms of Service License Restrictions):
Use of the Google Geocoding API is subject to a query limit of 2,500 requests per day. (User of Google Maps API for
Business may perform up to 100,000 requests per day.) This limit is enforced to prevent abuse and/or repurposing of
the Geocoding API, and this limit may be changed in the future without notice. Additionally, we enforce a request
rate limit to prevent abuse of the service. If you exceed the 24-hour limit or otherwise abuse the service, the
Geocoding API may stop working for you temporarily. If you continue to exceed this limit, your access to the
Geocoding API may be blocked.
Note: the Geocoding API may only be used in conjunction with a Google map; geocoding results without displaying
them on a map is prohibited
Nell’ipotesi di avere un file Excel Data.xlsx con un Foglio1, contente le informazioni delle quantità di prodotto
venduto per cliente e città, è possibile usare le Google Geocoding API per costruire in QlikView una tabella Geocode
con le informazioni di latitudine e longitudine.
Pagina 2 di 14
Gestione delle mappe geografiche in QlikView
Sysadmin.it
LOAD Customer,
City,
Quantity
FROM
Data.xlsx
(ooxml, embedded labels, table is Foglio1);
let NoOfRow =NoOfRows('Foglio1');
for i=0 to $(NoOfRow)-1
let vCustomer=peek('Customer',$(i),'Foglio1');
let vCity=peek('City',$(i),'Foglio1');
Geocode:
LOAD '$(vCustomer)' as Customer,
'$(vCity)' as City,
Num#([result/geometry/location/lng],'#.#############', '.' , ',' ) as Longitude,
Num#([result/geometry/location/lat],'#.#############', '.' , ',' ) as Latitude,
Num#([result/geometry/location/lng]) & ',' & Num#([result/geometry/location/lat])
as coordinates
FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(vCity)&sensor=false]
(XmlSimple, Table is [GeocodeResponse]);
next;
Ricavare latitudine e longitudine tramite OpenStreetMap Nominatim service
In alternativa ai servizi di localizzazione offerti da Google è’ possibile ricavare latitudine e longitudine tramite il
servizio Nominatim offerto d OpenStreetMap usando una chiamata di questo tipo:
http://nominatim.openstreetmap.org/search?format=xml&q=Torino&limit=1
Riferendoci sempre all’ipotesi di avere un file Excel Data.xlsx con un Foglio1, contenente le informazioni delle
quantità di prodotto venduto per cliente e città, è possibile costruire in QlikView una tabella Nominatim con le
informazioni di latitudine e longitudine.
LOAD Customer,
City,
Quantity
FROM
Data.xlsx
(ooxml, embedded labels, table is Foglio1);
let NoOfRow =NoOfRows('Foglio1');
for i=0 to $(NoOfRow)-1
let vCustomer=peek('Customer',$(i),'Foglio1');
let vCity=peek('City',$(i),'Foglio1');
Nominatim:
LOAD '$(vCustomer)' as Customer,
'$(vCity)' as City,
Num#([place/lon],'#.#############', '.' , ',' ) as Longitude,
Num#([place/lat],'#.#############', '.' , ',' ) as Latitude,
Num#([place/lon]) & ',' & Num#([place/lat]) as coordinates
FROM [http://nominatim.openstreetmap.org/search?format=xml&q=$(vCity)&limit=1] (XmlSimple,
Table is [searchresults]);
next;
Pagina 3 di 14
Gestione delle mappe geografiche in QlikView
Sysadmin.it
OpenStreetMap Nominatim ha le limitazioni d’utilizzo descritte al seguente Nominatim usage policy in cui viene
specificato che è possibile eseguire una richiesta al secondo:
No heavy uses (an absolute maximum of 1 request/s).
MapQuest ha reso disponibile una versione del Nominatim Search Service in Beta senza limitazioni che consente di
ricavare latitudine e longitudine a partire da un indirizzo usando una chiamata di questo tipo:
http://open.mapquestapi.com/nominatim/v1/search.php?format=xml&q=Torino&limit=1
LOAD Customer,
City,
Quantity
FROM
Data.xlsx
(ooxml, embedded labels, table is Foglio1);
let NoOfRow =NoOfRows('Foglio1');
for i=0 to $(NoOfRow)-1
let vCustomer=peek('Customer',$(i),'Foglio1');
let vCity=peek('City',$(i),'Foglio1');
MapQuestNominatim:
LOAD '$(vCustomer)' as Customer,
'$(vCity)' as City,
Num#([place/lon],'#.#############', '.' , ',' ) as Longitude,
Num#([place/lat],'#.#############', '.' , ',' ) as Latitude,
Num#([place/lon]) & ',' & Num#([place/lat]) as coordinates
FROM [http://open.mapquestapi.com/nominatim/v1/search.php?format=xml&q=$(vCity)&limit=1]
(XmlSimple, Table is [searchresults]);
next;
Pagina 4 di 14
Gestione delle mappe geografiche in QlikView
Sysadmin.it
Visualizzazione di mappe tramite l’utilizzo dell’estensione free QlikView Qlik Map
Per quanto riguarda il rendering delle mappe in Qlik View è’ possibile utilizzare l’estensione gratuita QlikView Qlik
Map disponibile al seguente http://market.qlikview.com/qlikview-qlik-map.html basata su OpenStreetMap.
Attualmente è disponibile la versione 1.00 dell’estensione che supporta le seguenti versioni di QlikView Version:
10.0, 11.0, 11.0 SR1, 11.0 SR2, 11.2
Nelle note di rilascio viene indicato che tale estensione sarà inclusa nella versione 12 di QlikView (che dovrebbe
essere resa disponibile probabilmente entro la fine del 2013):
Will mapping be available in QlikView 12?
At the least, this extension and the SVG reader extension will be converted and updated to work with QlikView 12. In
the future, we will strive to make simple and basic mapping easier while continuing to look to our partners for
complex and heavy geospatial visualizations.
Per l’installazione dell’estensione è sufficiente eseguire un doppio click sul file qlikmap.qar, in questo modo
verranno copiati nel profilo dell’utente i file necessari all’interno della directory:
Windows XP:
%userprofile%\Impostazioni locali\Dati applicazioni\QlikTech\QlikView\Extensions\Objects\qlikmap
Windows 7 e Windows 8:
%localappdata%\QlikTech\QlikView\Extensions\Objects\qlikmap
Per poter utilizzare l’estensione è necessario abilitare la WebViev tramite il menù Visualizza:
Pagina 5 di 14
Gestione delle mappe geografiche in QlikView
Sysadmin.it
Dopo aver abilitato la WebView sarà possibile aggiungere gli oggetti Qlik Map tramite un click destro sul foglio:
La modalità WebView abilta il client AJAX modificando quindi il rendering, questo implica che alcune funzioni
potrebbero variare leggermente nei diversi client, mentre altre (per es. la modifica dei report e i comandi di menu
Taglia, Copia e Incolla, collegamenti rapidi CTRL+X, CTRL+C e CTRL+V) non sono disponibili.
A questo punto occorre configurare l’oggetto impostando almeno le seguenti proprietà:



Region Dimension: questa dimensione dovrebbe contenere la descrizione
Cordinates: questa dimesione deve contenere le coordinate nel formato longitudine,latitudine
Measurement: questa dimensione deve contenere il valore dell’entità che si vuole visualizzare sulla mappa
Talvolta il controllo ha qualche problema di resfresh che si risolve chiudendo e riaprendo il file Qlik View, ma occorre
tenere conto che questa è una versione Draft.
Pagina 6 di 14
Gestione delle mappe geografiche in QlikView
Sysadmin.it
Visualizzazione di mappe tramite una Google Static Maps V2
Per gestire la visualizzazione di analisi su mappe è possibile utilizzare le Google Static Maps API che permettono di
eseguire in modo gratuito 25.000 richieste di mappe statiche al giorno.
Per utilizzare le mappe creare un foglio Google Maps che conterrà le variabili necessarie all’utilizzo delle mappe nello
script d’importazione:
// Variabili di configurazione
max_zoom_level = 17; //maximum value 17
// Variabili per il calcolo della mappa
// (non è necessario apportare modifiche)
map_size_x=
'640';
map_size_y=
'400';
var_pi180=
'=pi()/180';
var_lat_offset=
'0';
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_level1)))+((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_level1)))+((0.5*log((1+(sin((Latitude)*pi()/180)))/(1-(sin((Latitude)*pi()/180)))))*((256*pow(2,_zoom_level))/(2*pi()))))<map_size_y,_zoom_level,1),_zoom_level))';
var_mc2=
'=256*pow(2,$(var_zoom))';
var_mc1=
'=256*pow(2,($(var_zoom)-1))';
var_maptype= '=if(isnull(only(maptype)),fieldvalue( '&chr(39)&'maptype'&chr(39)&', 1
),maptype)';
SET HidePrefix='_' ;
// Field required for calcualting best zoom level
_zoom_level:
Load RecNo( ) as _zoom_level autogenerate(max_zoom_level);
maptype:
LOAD * INLINE [
maptype, maptypedescription
roadmap, Stradale
satellite, Satellite
terrain, Rilievo
hybrid, Ibrida
];
Pagina 7 di 14
Gestione delle mappe geografiche in QlikView
Sysadmin.it
Passi per la creazione di un grafico a dispersione con una mappa di sfondo su cui visualizzare i dati
Passo1: aggiungere un Grafico a dispersione.
Passo2: selezionare la dimensione desiderata (per esempio il nome del cliente).
Pagina 8 di 14
Gestione delle mappe geografiche in QlikView
Sysadmin.it
Passo3: Impostare la Modalità avanzata per gestire le Espressioni.
Passo 4: Aggiungere le seguenti tre espressioni rispettivamente per l’asse X, l’asse Y, la dimensione della bolla e il
testo aggiuntivo da inserire nel popup.
Etichetta: Longitude
Definizione: round (256*pow(2,($(var_zoom)-1)))+(Longitude*((256*pow(2,$(var_zoom)))/360)) //Asse X
Etichetta: Latitude
Definizione: ((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())))) //Asse Y
Etichetta: Quantity
Definizione: Sum(Quantity) //Dimensione bolla
Etichetta: City
Definizione: City //Pop-up text
Pagina 9 di 14
Gestione delle mappe geografiche in QlikView
Sysadmin.it
Passo 5: Impostare la visualizzazione del testo come Popup sull’Espressione da usare come testo di pop up
Passo 6: Impostare le opzione del Grafico per la visualizzazione delle sole informazioni utili, tenendo conto che la
finalità sarà quella di avere i dati su di una mappa.
Pagina 10 di 14
Gestione delle mappe geografiche in QlikView
Sysadmin.it
Passo 7: Impostare le opzioni di visualizzazione degli Assi e in particolare impostare i valori massimi e minimi per
consentire il corretto resize della mappa.
Min Asse X: 256*pow(2,($(var_zoom)-1))+var_mid_long*((256*pow(2,$(var_zoom)))/360)-round(map_size_x/2)
Max Asse X: 256*pow(2,($(var_zoom)-1))+(var_mid_long*((256*pow(2,$(var_zoom)))/360))+round(map_size_x/2)
Min Asse Y: 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)
Max Asse Y: 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)
Pagina 11 di 14
Gestione delle mappe geografiche in QlikView
Sysadmin.it
Passo 8: Impostare come immagine di background del grafico una mappa statica di Google.
='http://maps.googleapis.com/maps/api/staticmap?center='
&
Replace(var_mid_lat,',','.')
&
','
&
Replace(var_mid_long,',','.')
&
'&zoom='
&
'$(var_zoom)'
&
'&maptype='
&
var_maptype
&
'&size='
&
map_size_x
&
'x'
&
map_size_y
&
'&sensor=false'
Pagina 12 di 14
Gestione delle mappe geografiche in QlikView
Sysadmin.it
Passo 9: Se necessario inserire una Casella di Elenco per la scelta del tipo di mappa che visualizza il campo
maptypedescription e uno Slider per modificare lo Zoom per la variabile var_zoom (con minimo=1, massimo=17 e
Passo statico 1)
Pagina 13 di 14
Gestione delle mappe geografiche in QlikView
Sysadmin.it
Visualizzazione di mappe tramite una Open StreetMap Static Map
Anche OpenStreetMap offre la possibilità di ottenere mappe statiche (l’estensione QlikView Map si basa su
OpenStreetMap) grazie ad alcuni servizi online. Quelli disponibili sono descritti nel seguente Static map images e al
momento sono:



staticMapLite
MapQuest/Open Static Maps API
MapBox Static API
Con riferimento all’esempio visto per l’utilizzo di una di mappa statica basata su Google Static Maps V2, per utilizzare
invece una mappa statica basata su OSM Static maps API v0.3 è sufficiente nel Passo 8 impostare il seguente link per
ottenere l’immagine di background del grafico:
='http://pafciu17.dev.openstreetmap.org/?module=map&lat='
&
Replace(var_mid_lat,',','.')
&
'&lon='
&
Replace(var_mid_long,',','.')
&
'&zoom='
&
'$(var_zoom)'
&
'&width='
&
map_size_x
&
'&height='
&
map_size_y
&
'&type=mapnik'
&
'&imgType=png'
Tramite questa espressione si otterrà dal servizio OSM Static maps API v0.3 una mappa statica di tipo mapnik in
formato PNG. In modo analogo possono essere ottenute mappe statiche anche dagli altri servizi disponibili di
OpenStreetMap.
Volendo è possibile gestire in modo dinamico anche il parametro type (come fatto per Google Static Maps V2)
andando ad modificare la definizione della Tabella maptype.
Conclusioni
La gestione delle mappe geografiche in Qlik View risulta tutto sommato abbastanza semplice, la soluzione basata
sull’estensione QlikView Qlik Map permette, differenza dell’approccio mediante mappa statica, di avere
un’interazione più dinamica con la mappa. Il vantaggio della soluzione basata sull’utilizzo di una mappa statica evita
il deploy dell’estensione sui client e l’utilizzo della modalità WebView.
Per ulteriori informazioni si veda la White Paper How QlikTech uses QlikView – Google Maps (Best Practices for
integrating Google Maps into Applications).
Pagina 14 di 14