NdR: questo è stato pubblicato originariamente sul sito della dataninjaschool.
Introduzione
Tra gli strumenti esposti da Google Drive c’è Sheet, un foglio elettronico online ricco di funzioni, molto usato per le professioni e gli utilizzi più svariati.
I fogli creati fungono spesso da “contenitori” di dati, che fanno da sorgente a grafici, mappe, infografiche e applicazioni di svariata natura
L’accesso a Sheet si può eseguire tramite le API ufficiali di interrogazione. Nella documentazione ufficiale è indicato come generare un output, come generarne uno filtrato, come impostare i formati di output, come usarlo come un database relazionale su cui fare delle query SQL con aggregazione, calcoli, ecc.. Sul web, oltre alla guida dedicata, numerosi tutorial ed esempi.
Si tratta di operazioni semplici, con possibilità di uso molto ricche, per uno strumento che è comunque “per tutti“. Nella mia esperienza da formatore ho riscontrato però che per alcuni, la costruzione di una query non è di immediato apprendimento.
Questo post nasce proprio allo scopo di presentare uno strumento che consente di superare questi ostacoli: il buon vecchio Guardian Datastore Explorer di Tony Hirst.
Lo strumento
Il Guardian Datastore Explorer è un vecchietto arzillo: fa la sua prima apparizione nel lontanissimo 2009, un’altra era (non c’era ad esempio Instagram).
Sono due le caratteristiche principali che lo rendono molto interessante:
- consente facilmente di costruire in modo guidato un’interrogazione su uno Sheet di GDrive;
- è molto didattico. Utilizzandolo si impara ad usare il linguaggio di query e dopo poco tempo si impareranno a scrivere stringhe di interrogazione in autonomia con un semplice editor di testo.
E’ uno strumento web ed il suo URL è http://ouseful.open.ac.uk/datastore/gspreadsheetdb4.php.
Come si usa
Predisposizione del foglio Google Drive Sheet
E’ propedeutico avere un account su Google Drive. E poi è necessario avere un foglio elettronico con cui testare il Guardian Datastore Explorer: per le spiegazioni successive verrà utilizzato questo, con i musei del territorio comunale fiorentino.
Si tratta di dati aperti presenti sul portate dati.gov.it. Lo sottolineo perché un’altra cosa abilitata dagli open data è la didattica.
La prima cosa da fare con il foglio elettronico, è crearne una copia:
Fatta la “vostra” copia, è necessario impostarne la condivisione (tasto “Condividi” o “Share” in alto a destra), e fare in modo che (1) chiunque abbia l’URL del foglio, (2) possa visualizzarlo.
Poi si dovrà pubblicare sul web:
In questo video la sequenza della procedura sopra descritta:
La condivisione e la pubblicazione sul web sono necessarie perché il Guardian Datastore Explorer è utilizzabile soltanto con fogli pubblicamente accessibili.
Costruzione della query sul del foglio elettronico
La prima cosa da fare è ricavare il codice identificativo del foglio. Si trova all’interno dell’URL dello sheet ed è facilmente indetificabile.
Ad esempio nell’URL sottostante l’ID del foglio è quello in grassetto:
http://docs.google.com/spreadsheets/d/1nS167pnytroD9SQWi0BUa_eFaeCwuWOk1_0GvsBFmsg/edit#gid=108845820
Quindi in questo caso è “1nS167pnytroD9SQWi0BUa_eFaeCwuWOk1_0GvsBFmsg“.
Poi c’è aprire la pagina web del Guardian Datastore Explorer e (1) inserire l’ID del foglio e (2) fare click su “Preview table headings”. In questo modo l’interrogazione è stata attivata e come risultato vengono visualizzate le (3) intestazione delle colonne del foglio.
Subito dopo si può andare a pescare dati (“Go Fish” scrive l’autore) e iniziare a imparare come usare questo linguaggio di interrogazione, tramite la tabella di esempi riportata sul sito. Leggendola si vede che a tutti gli effetti si tratta un classico SQL (Structured Query Language). Qualche esempio:
| Obiettivo | Comando |
|---|---|
| Selezionare tutti i record | SELECT * |
| Selezionare le colonne A e B (ovvero la prima e la seconda) per tutti i record | SELECT A,B |
| Selezionare tutti record per le colonne A e B, dove il valore della colonna I è uguale a “3467″ | SELECT A,B WHERE I = 3467 |
| Selezionare tutti record delle colonne C e D, in cui la colonna F non assume il valore di 42043 | SELECT C,D WHERE F != 3467 |
Nella pagina trovate molti altri esempi.
Un’interrogazione che si potrebbe fare sul foglio dei musei di Firenze potrebbe essere quella per cui applichiamo questi filtri:
- Soltanto le colonne A, B, E, F, G, H, I e K;
- i soli musei a Est della “Cappella Brancacci”;
- solo quelli Statali;
- solo quelli che hanno un numero di telefono associato;
- ordinati da Nord verso Sud.
Prima di costruirla, un breve video che illustra una prima query più semplice, in modo da prendere confidenza con lo strumento: le sole colonne “latitude” e “longitude”, dove la “latitude” è maggiore di 43.77 e tutto ordinato per longitudine crescente.
Fatta la query, poco sopra i risultati di output, tre righe di testo molto interessanti:
La prima è proprio la query che abbiamo costruito, secondo il linguaggio delle API di Google Drive:
select%20A%2CB where B%20%3E%2043.77 order by A asc Nella stringa ci sono dei caratteri che ne rendono poco “leggibili” alcune parti: select A,B where B > 43.77 order by A asc si comprende meglio. In realtà la prima è il risultato dell’encoding dei caratteri della seconda. Questa è una procedura necessaria perché l’interrogazione viene lanciata tramite un URL, e in questo alcuni caratteri non sono consentiti. Nel nostro caso lo spazio deve essere codificato in “%20″, la “,” in “%2C”, il “<” in “%3E”.
La seconda contiene due hyperlink, a due dei formati di output possibili di una query fatta su Google Drive Sheet: l’HTML e il CSV. Il secondo è forse il formato più comodo per chi dovrà utilizzare i risultati di un’interrogazione per creare grafici, mappe e infografiche.
Questo ad esempio l’hyperlink per l’output in CSV:
http://spreadsheets.google.com/tq?tqx=out:csv&tq=select%20A%2CB%20where%20B%20%3E%2043.77%20order%20by%20A%20asc&key=1nS167pnytroD9SQWi0BUa_eFaeCwuWOk1_0GvsBFmsg
Se lo separiamo in blocchi, si evidenziano elementi interessanti:
tqx=out:csvserve per impostare il formato di output;tq=select%20A%2CB%20where%20B%20%3E%2043.77%20order%20by%20A%20ascper dichiarare la query;key=1nS167pnytroD9SQWi0BUa_eFaeCwuWOk1_0GvsBFmsgper dichiarare l’ID del foglio.
La terza riga un segnalibro che consente di aprire il Guardian Data Explorer con e fargli lanciare la query appena eseguita. E’ un modo per salvare l’interrogazione costruita.
Adesso siamo in grado di costruire la query indicata a inizio paragrafo:
- Soltanto le colonne A, B, E, F, G, H, I e K ->
select A,B,E,F,G,H,I,K - i soli musei a Est della “Cappella Brancacci” ->
where B > 11.2438292167895 - solo quelli Statali ->
AND I matches 'Statale' - solo quelli che hanno un numero di telefono associato ->
AND H !="" - ordinati da Nord verso Sud ->
order by A desc
select A,B,E,F,G,H,I,K where B > 11.2438292167895 AND I matches 'Statale' AND H !=\"\" order by B desc Per potere usare questa query è necessario eseguire la codifica dei caratteri in modo che possa essere inserita in un URL. Il risultato (mille strumenti online per farlo, uno è questo) dell’endoding è:
select%20A%2CB%2CE%2CF%2CG%2CH%2CI%2CK%20where%20B%20%3E%2011.2438292167895%20AND%20I%20matches%20%27Statale%27%20AND%20H%20!%3D%22%22%20order%20by%20B%20desc E’ possibile usare allora questa stringa per creare l’URL che esegue l’interrogazione di sopra e che produce come un risultato un file CSV:
http://spreadsheets.google.com/tq?tqx=out:csv&tq=select%20A%2CB%2CE%2CF%2CG%2CH%2CI%2CK%20where%20B%20%3E%2011.2438292167895%20AND%20I%20matches%20%27Statale%27%20AND%20H%20!%3D%22%22%20order%20by%20B%20desc&key=1nS167pnytroD9SQWi0BUa_eFaeCwuWOk1_0GvsBFmsg
Il file scaricato sarà apribile con qualsiasi editor di testo, e qualsiasi foglio elettronico.
Usare l’output di una query per costruire una mappa online
Come già scritto sopra, il risultato di una di queste query può essere usata per visualizzare i dati in differenti modi. Uno è una mappa online (deformazione professionale).
Il dataset di esempio si presta, perché contiene la latitudine e la longitudine di ogni museo. Uno strumento free e open-source molto comodo per generare mappe da output di questo tipo è uMap. Tra i formati di input supportati proprio il CSV; l’unico requisito è che nel file CSV siano presenti le colonne denominate “latitude” e “longitude“.
La cosa interessante è che la mappa online sarà live e ogni aggiornamento fatto nel foglio elettronico, produrrà un aggiornamento della mappa. Questo avviene perché tutte le volte che verrà visualizzata, verrà lanciata una nuova query.
Nel video sottostante è illustrato come creare una mappa online live a partire proprio dall’URL soprastante, che produce in output un CSV.
Considerazioni finali
Il Guardian Datastore Explorer non è un query builder particolarmente potente ed elegante, ma è sicuramente uno strumento che rende semplice l’avvicinamento al Query Language di Google Drive Sheet.
Sopratutto fa comprendere che si tratta di un linguaggio semplice, e dopo poco tempo anche i novizi scriveranno le query “a mano” senza più usarlo. Bastano concetti di base di SQL, sapere fare l’encoding dei caratteri e leggersi la documentazione 
Sono un geologo marino e nel 1999 ho iniziato con Antonio D'Argenio la vita da libero professionista nel mondo dei Sistemi Informativi Geografici. Nei primi anni - di tanto studio e di poche fatture - è stato fatale l’incontro con MapServer: il web-mapping e le applicazioni web sono diventati due pezzi importanti della mia professione.
Negli ultimi anni mi sono occupato anche di formazione e di recente sono entrato nello strano mondo del telerilevamento di prossimità.
Mi piace molto leggere di GIS, informazione geografica, e neogeography, e parlarne con gli amici che incontro spesso al bar dietro al router.
I contenuti potrebbero non essere più adeguati ai tempi!
