Ottenere informazioni su un itinerario con Excel “Power Query” e Bing Maps REST Services*

(*Bing™ Maps REST Services Application Programming Interface (API))

“Power Query” è un add-in di Excel che ci permette di comunicare con altri linguaggi, con il risultato di ottenere informazioni dal loro contenuto. Ad esempio è possibile calcolare la distanza tra due città “comunicando” con le informazioni fornite dalle API (cioè una altra applicazione che contiene info e comunica con altre appl.). “Bing™ Maps REST Services” è il servizio API di Bing Maps.

Conoscere la distanza tra due punti di un percorso è un fatto di grande importanza. Per un tecnico gestionale che deve calcolare la lunghezza del percorso che ottimizza i tempi di lavoro è (una) condizione necessaria per la progettazione dei servizi.

Un ottimo blog su come fare questo con le API di Google è:
http://www.excel2013.info/power-query/connection-api/

Riprendendo quanto dimostrato nel blog, vogliamo ottenere la distanza tra due punti di un itinerario con Bing Maps REST Services e Excel “Power Query”.

Procedere nel modo seguente:

1.       Ottenere una BingMapskey

2.       Scaricare l’add-in “Power Query” (possibile per vers. 2010 e 2013 di Excel).

3.       Utilizzare la documentazione API di Bing e cercare la URL e i parametri che interessano. La guida delle API di Bing si trova on-line. Cercare nel capitolo “Routes Api” per trovare quello che interessa. Nel nostro caso possiamo scrivere:

http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=london&wp.1=leeds&avoid=minimizeTolls&key=inserire la bing maps key.

Se inseriamo l’indirizzo API nella url di un browser (Explorer ad es.) otteniamo come risposta un file xml (nel ns.caso “o=xml”, oppure json se “o=json”) che contiene molteplici informazioni sugli indirizzi “londra” e “leeds” come lat, lon, distanza tra le città, ecc.

Da tutti i parametri noi vogliamo la distanza e ottenere essa da excel (per adesso la riposta è un file)

4.       Scrivere il codice in Power Query. Nella barra multifunzione → Power Query → Recupera dati esterni → Da altre origini → Query Vuota → editor di query → Visualizzazione → Editor avanzato.

All’interno dell’editor avanzato digitare il frammento di codice in linguaggio M#:

Let

Source=Xml.Document(Web.Contents("http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=london&wp.1=leeds&avoid=minimizeTolls&key=insertbingmapskey"))

In

Source

Al post di insertbingmapskey è chiaro che va inserita la stringa di caratteri della chiave di prova. “o=” è il file di output che è xml, “wp.0” è l’indirizzo di partenza (londra) e “wp.1” è l’indirizzo di arrivo (leeds).

5.       Risposta di Power Query: Il frammento di codice restituisce la seguente risposta:

 

 

L’informazione attesa “distanza tra 2 città” non è visibile: dobbiamo continuare a esplorare il contenuto delle API. Nella colonna “Value” fare “clic” una volta sulla parola Table.

6.     Esplora il file xml: Il risultato è la tabella seguente dove il risultato non è ancora visibile: continuiamo a esplorare il file xml facendo “clic” sulla parola Table della colonna Value nella riga ResourceSets.

7.       Si ottiene la tabella seguente.

8.      Procedendo ancora come al passo precedente più volte si ottengono le seguenti tabelle in successione

La distanza fra londra e leeds è quindi di 310 km circa.

Il codice in linguaggio M# associato risulta

let

   Source = Xml.Document(Web.Contents ("http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=london&wp.1=leeds&avoid=minimizeTolls&key=insertyourbingmapskey")),

    Value = Source{0}[Value],

    Value1 = Value{6}[Value],

    Value2 = Value1{0}[Value],

    Value3 = Value2{1}[Value],

    Value4 = Value3{0}[Value],

in

    Value4

In un prossimo blog vedremo come scrivendo le città (o le strade) su un foglio excel, sfruttando questo metodo, riesco ad ottenere velocemente le distanze.

Buon Lavoro. Daniele

Ottenere una mappa interattiva con Bing Maps e Excel

 

 

A differenza della mappa statica (blog precedente), la mappa interattiva consente spostamenti, zoom, visualizzazione della mappa a schermo intero, condivisione della mappa via mail e stampa (ed altro ancora). Queste funzioni sono particolarmente utili ad esempio per visualizzare parti in dettaglio o per fornire immediatamente una stampa cartacea.

Come nel caso della mappa statica, in sostanza è necessario costruire un URL personalizzato.  

Per costruire l’url personalizzato leggere l’articolo http://msdn.microsoft.com/it-it/library/dn217138.aspx

La stringa Url finale si ottiene concatenando le righe della tabella successiva ed è del tipo:

Dove:

“http://www.bing.com/maps/default.aspx?” è l’url base

“&rtp=adr.” “inserire il primo indirizzo in codifica url”

“&~adr.” “inserire il secondo indirizzo in codifica url” *

“&rtop=” “1~1~0”

“&mode=” “D”

 (*L’indirizzo codificato si ottiene come già visto con la funzione codifica.url () ).

La stringa finale della precedente tabella si ottiene dunque concatenando le stringhe contenute nelle celle delle colonne “A” e “I” della seguente tabella Excel:

Inseriamo infine il solito pulsante con associato il solito codice di chiamata dell’url attraverso “WebBrowser2.Navigate”. Il risultato è quello riportato nell’immagine.

La modifica dell’elenco è, anche in questo caso, molto veloce. Questo metodo non utilizza i servizi “Bing Maps REST Services”: non si utilizza la Bing Maps key ma non è possibile ottenere informazioni dal servizio come la distanza tra due indirizzi. Bisogna infine verificare che tutte le tappe dell’itinerario ottenuto corrispondano a quelle richieste perché le mappe di Bing non sono ancora precise come quelle di Google.

Buon Lavoro. Daniele.

 

Ottenere una mappa con un itinerario con Bing Maps

Spesso, nel lavoro, è necessario visualizzare su di una mappa un elenco di strade e modificare essa velocemente. Si pensi ad es. ad una Azienda di Igiene Urbana che si occupa dello spazzamento delle strade di una città: all’operatore ecologico vengono assegnate delle disposizioni di servizio con un elenco di strade da spazzare e la mappa per localizzare esse; all’autista addetto al vuotamento dei cassonetti stradali viene fornito un elenco di contenitori e la mappa con l’itinerario da seguire.

Un’altra cosa importante è come questo elenco e la mappa possano essere modificate velocemente dal tecnico gestionale. Per es. per inserire una serie di nuove strade che necessitano di uno spazzamento urgente.

Lavorare con BI permette soprattutto di acquisire tutti i dati necessari per il dimensionamento tecnico del servizio, ad es. calcolare la lunghezza totale delle strade assegnate ad un certo spazzamento (vedremo questo in un prossimo blog).

Un esempio di quello che può essere fatto è riportato qui sotto.

In sostanza, per visualizzare la mappa, abbiamo bisogno di un indirizzo url. L’indirizzo è costruito come richiesto da Bing Maps REST Services. Per visualizzare la mappa in una finestra Web sul foglio Excel procedere nel modo seguente:

1.       Dotarsi di una versione di Excel 2013 o 2010. Io utilizzo la versione di prova di Excel 2013.

2.       Ottenere una chiave di prova di Bing Maps. Il codice della chiave andrà poi inserito nella stringa finale dell’indirizzo url.

 
Inserire nella colonna verde un elenco di indirizzi, dove “wp.0” è l’indirizzo di partenza, “wp.6” indirizzo di arrivo (nel ns. es.) e “to” indirizzi di fermate intermedie (max 10). Nella colonna “J” troviamo la codifica URL dell’indirizzo, ottenuto con la funzione CODIFICA.URL (). Nella colonna “P” troviamo la lat e long dell’indirizzo ottenute con una funzione.

4.      Ottenere le stringhe della colonna “M”, necessarie perché parti della stringa finale. Io ho fatto questo in una seconda tabella concatenando le colonne “I” e “B” (che utilizzano dati della prima tabella).

 

5.       In una terza tabella, definire le dimensioni della mappa, il livello di zoom e l’area della mappa.

 

6.       Concatenare http://dev.virtualearth.net/REST/v1/Imagery/Map/Road/Routes? Con le stringhe della colonna “M” della seconda tabella e con i dati della terza tabella.

La stringa finale è tipo:

http://dev.virtualearth.net/REST/v1/Imagery/Map/Road/Routes?&wp.0=via%20duilio%2026%2C%20viareggio%2C%20italy&vwp.1=via%20palestrina%2088%2C%20viareggio%2C%20italy&vwp.2=via%20prato%2015%2C%20viareggio%2C%20italy&vwp.3=via%20fratti%20350%2C%20viareggio%2C%20italy&vwp.4=via%20palestrina%2068%2C%20viareggio%2C%20italy&vwp.5=via%20venezia%2069%2C%20viareggio%2C%20italy&wp.6=via%20baracca%2028%2C%20viareggio%2C%20italy&ms=700,700&key=InserisciBingMapsKey.

Nella stringa io non ho inserito il livello di zoom e la map area (opzionali).

Cliccando sull’indirizzo url trasformato in link otteniamo il file xml di risposta. Se però vogliamo visualizzare la mappa in una finestra Web sul foglio Excel continuare con la procedura:

7.      Attivare il controllo “Microsoft WebBrowser”. In Excel 2013, il controllo “Microsoft WebBrowser”, che permette di inserire una finestra Web direttamente nel foglio Excel, è disattivato. Seguire le istruzioni http://support.microsoft.com/kb/2793374 per attivarlo.

8.      Per inserire una finestra Web nel foglio Excel, dalla barra multifunzione: Sviluppo →  Inserisci → Controlli ActiveX → Altri controlli → WebBrowser.

9.       Scrivere un modulo VBA con un frammento di codice tipo

'visualizza un itinerario sulla mappa

'con webbrowser1.navigate

Option Explicit

Public Sub bingroutecode()

Dim myurladdr As String

myurladdr = Sheets("bingroute").Range("bingrouteurl")

Sheets("bingroute").WebBrowser1.Navigate (myurladdr)

Sheets("bingroute").WebBrowser1.Height = 700

Sheets("bingroute").WebBrowser1.Width = 700

End Sub

10.  Associare infine un pulsante al codice. Sviluppo →  Inserisci → Controlli ActiveX → Pulsante di Comando. Premendo il pulsante si visualizza la mappa di inizio pagina.

Questo esempio permette di visualizzare solo mappe statiche (Imagery, mappe non interattive, non hanno le funzioni zoom, visualizza schermo intero, pan, ecc.). Visualizzare mappe interattive sarà oggetto di un altro blog.

Tutto (quel poco) che conosco lo devo al blog di Chris Webb e al suo libro “Power Query for Power BI and Excel“. Un ringraziamento speciale va all’Ing. Stefano Pocci e a Claudio della Team Computer, per i preziosi consigli. Buon Lavoro a tutti. Daniele.