6 ottobre, 2015 | di

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%20asc per dichiarare la query;
  • key=1nS167pnytroD9SQWi0BUa_eFaeCwuWOk1_0GvsBFmsg per 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 :)

29 dicembre, 2014 | di

Diversi geoportali, piccoli e grandi, di Pubbliche Amministrazioni da ogni parte del mondo, sono basati su tecnologia ESRI ArcGIS. All’utente di solito viene esposta un’interfaccia di consultazione del catalogo dei dataset (come quella del geoportale della Regione Siciliana) e/o l’accesso diretto ai dati e alla loro rappresentazione tramite servizi OGC standard come WMS, WFS e WCS.

Ma ci sono diverse altre caratteristiche interessanti rese disponibili da queste tecnologie e che molto spesso ignoriamo, la cui conoscenza ci consente di accedere ad un numero molto più ampio di informazioni e dati. Tutto questo oggi è a maggior forza interessante, grazie alle politiche Open Data realizzate da molte Pubbliche Amministrazioni e anche perché dal 19 marzo 2013i ”tutti i dati e documenti che le pubbliche amministrazioni pubblicano con qualsiasi modalità, senza l’espressa adozione di una licenza d’uso, si intendono rilasciati come dati aperti (open data by default)” (cit. dati.gov.it, mentre dal punto di vista normativo si tratta dell’articolo 52, comma 2 del CAD).

In questo post sottolineerò alcuni elementi relativi all’accesso ai dati tramite query via interfaccia REST (più propriamente tramite API REST).

Introduzione

Le API REST di ArcGIS – REST è l’acronimo di Representational State Transfer - forniscono una semplice interfaccia di accesso web ai server cartografici di questa casa software, sia ai dati/servizi che ad alcuni processi. Il tutto è quindi accessibile tramite una serie di URL gerarchici, che identificano ciò a cui si vuole accedere. L’URL di default di accesso ha di _default _questa struttura:

http://<host>:/arcgis/rest/services

Ad esempio quello della Regione Siciliana è questo:

http://map.sitr.regione.sicilia.it/ArcGIS/rest/services

E aprendo l’indirizzo sono elencati dati e servizi disponibili in questo server.

sicilia_rest

La documentazione ufficiale generale è molto ricca e vasta e non aggiungerò altri dettagli generici.

A seguire invece alcuni esempi di query  via ArcGIS REST API, in modo da apprezzarne la ricchezza e le modalità di accesso.

Interroghiamo una risorsa

Per gli esempi di questo articolo farò riferimento alla Regione Umbria, che ha aperto da poco i suoi dati cartografici. Dal suo portale open data possiamo leggere l’URL pubblico del server ArcGIS.

url_umbria

Si legge

http://geo.umbriaterritorio.it/arcgis/

Bisogna aggiungere (vedi sopra) “rest/services/” e abbiamo

http://geo.umbriaterritorio.it/arcgis/rest/services/

A questo punto non resta che sfogliare un po’ la directory pubblica e scegliere un layer su cui eseguire una query. Userò il dataset dei “NUMERI CIVICI”, che si raggiunge facendo click su Public > ECOGRAFICO_CATASTALE_WGS84 (MapServer) > NUMERI CIVICI.

Scorrendo la pagina verso il basso è visibile il tasto “Query“, tramite il quale è possibile interrogare il dataset.

query

Per lanciare la prima interrogazione devo inserire almeno un criterio di ricerca. Ad esempio il numero civico con “ID=1″ (ogni elemento ha sempre un identificativo numerico associato). La pagina html con il risultato dell’interrogazione mi fornisce informazioni sia sugli attributi che sulla geometria dell’elemento.

attributi

Posso fare allora una query per attributo: tutti gli elementi che contengono nel campo “DESCR_S49″ la stringa “CORPOSANO”. Nell’interfaccia leggiamo che ci sono 47 record che soddisfano questa condizione e l’output è in HTML. Ci sono però altri formati di output tra cui KML e JSON; questo l’output in JSON della stessa query.

output

Questi due formati quindi mostrano nei fatti, come una query REST su server ArcGIS sia un’ulteriore modalità di accesso al dato grezzo.

Query spaziale

E’ possibile eseguire anche delle interrogazioni spaziali di complessità variabile. Ad esempio posso estrarre tutti i civici che ricadono tra queste coordinate 12.384356,43.108511,12.388869,43.110765 (<xmin>,<ymin>,<xmax>,<ymax>).

spatial_query

Posso farne il download con GDAL/OGR (vedi sotto) e ottenere rapidamente i dati in formato spaziale e al contempo visualizzarne rapidamente una loro rappresentazione.

spatial_query_map

Accesso tramite GDAL/OGR a una query

La libreria GDAL/OGR ha i driver di accesso in lettura e scrittura al formato GeoJSON. Uno degli output delle query di sopra è proprio JSON e questa libreria lo riesce a leggere nativamente proprio come GeoJSON.

L’ultima query si effettua chiamando questo lungo URL:

http://geo.umbriaterritorio.it/ArcGIS/rest/services/Public/ECOGRAFICO_CATASTALE1_WGS84/MapServer/0/query?geometry=&geometryType=esriGeometryPoint&inSR=&spatialRel=esriSpatialRelIntersects&relationParam=&objectIds=&where=DESCR_S49+LIKE+%27%25CORPOSANO%25%27&time=&returnCountOnly=false&returnIdsOnly=false&returnGeometry=true&maxAllowableOffset=&outSR=&outFields=*&f=pjson

Nell’indirizzo di sopra sono visibili tutti i parametri disponibili per effettuare un’interrogazione, ma sopra abbiamo valorizzato soltanto la stringa di testo da ricercare in un determinato campo (parametro “where”) e il formato di output (parametro “f”). Questo URL fa da source per GDAL/OGR. Se voglio avere tutte le informazioni su questa sorgente di dati vettoriali userò il classico ogrinfo:

ogrinfo -ro "http://geo.umbriaterritorio.it/ArcGIS/rest/services/Public/ECOGRAFICO_CATASTALE1_WGS84/MapServer/0/query?geometry=&geometryType=esriGeometryPoint&inSR=&spatialRel=esriSpatialRelIntersects&relationParam=&objectIds=&where=DESCR_S49+LIKE+%27%25CORPOSANO%25%27&time=&returnCountOnly=false&returnIdsOnly=false&returnGeometry=true&maxAllowableOffset=&outSR=&outFields=*&f=pjson" OGRGeoJSON

“OGRGeoJSON” è per GDAL/OGR il nome predefinito del layer di una sorgente GeoJSON. “-ro” perché imposto l’accesso in sola lettura.

Se voglio convertire in ArcView Shapefile l’output di questa query userò ogr2ogr:

ogr2ogr CORPOSANO.shp "http://geo.umbriaterritorio.it/ArcGIS/rest/services/Public/ECOGRAFICO_CATASTALE1_WGS84/MapServer/0/query?geometry=&geometryType=esriGeometryPoint&inSR=&spatialRel=esriSpatialRelIntersects&relationParam=&objectIds=&where=DESCR_S49+LIKE+%27%25CORPOSANO%25%27&time=&returnCountOnly=false&returnIdsOnly=false&returnGeometry=true&maxAllowableOffset=&outSR=&outFields=*&f=pjson" OGRGeoJSON

Come esempio, riporto qui il download fatto con GDAL/OGR nei formati ArcView Shapefile, GeoJSON e KML.

Web mapping a partire da query

Queste query possono essere visualizzate in maniera molto efficace anche in un’interfaccia di web mapping. Sia a partire dal salvataggio dell’output, che tramite chiamata diretta in realtime. La cosa è inoltre resa semplice da una bella libreria rilasciata in open source da ESRI e basata su Leaflet: http://esri.github.io/esri-leaflet.

La query di sopra (quella sulla stringa “CORPOSANO”) ad esempio si può rappresentare rapidamente come sotto (click qui per aprire a schermo intero).

leaflet

Il codice lo trovate qui: https://github.com/tanto/arcgisqueryrest/blob/gh-pages/index.html

Alcune note in conclusione

Sono rimasto molto in superficie, ma l’intento è quello di mostrare un punto di ingresso poco noto e utile, visto il sempre maggior numero di dati spaziali oggi disponibili e aperti. Di default l’output di queste _query _è limitato a 1000 risultati, quindi è necessario “ciclare” le chiamate. Ad esempio per ID a gruppi di 1000 record:

Il totale (190140 record), da conoscere prima di fare partire il ciclo, si ricava ad esempio con questa query. Ci sono altri modi per ciclare tra i record, questo è solo un esempio.

L’accesso REST è molto utile anche con sorgenti di tipo raster, ma magari lo scrive qualcun altro, in un altro post :) .

L’URL con struttura “http://<host>:/arcgis/rest/services” è quello di default. Chi gestisce il server potrebbe usare uno schema diverso.

Questo post è dedicato al giovane Padawan “Flavio“, che mi ha fatto scoprire questa pagina e ha dato la stura alla stesura di questo testo.

Non mi RESTa che augurarvi un buon 2015!


TANTO non rappresenta una testata giornalistica ai sensi della legge n. 62 del 7.03.2001, in quanto non viene aggiornato con una precisa e determinata periodicita'. Pertanto, in alcun modo puo' considerarsi un prodotto editoriale.