6 ottobre, 2015 | di Andrea Borruso

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

Posted in Didattica | No Comments »

29 dicembre, 2014 | di Andrea Borruso

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.

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.

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.

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.

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.

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>).

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.

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).

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:

• inizio con la condizione OBJECTID>=1 and OBJECTID<=1000;
• poi OBJECTID>=1001 and OBJECTID<=2000;
• e così via …

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!

Posted in Didattica | 9 Comments »

28 aprile, 2014 | di Andrea Borruso

Le relazioni in QGIS consentono di sviluppare dei casi d’uso applicativi molto interessanti; una delle ragioni del post è quella di contribuire alla diffusione della conoscenza del tema.

Il caso d’uso: le strade e la loro manutenzione

Immaginate di essere i gestori del network della rete stradale della vostra città e di volere utilizzare QGIS (insieme ad altri strumenti) per farlo. Avete quindi un layer cartografico con tutte le strade (e i relativi attributi) e la necessità di associare a questo il dataset delle manutenzioni che vengono realizzate nei vari componenti della rete.

A seguire vedremo come gestire la cosa con QGIS, le relazioni e con il comodo supporto delle maschere di input personalizzabili.

Chiavi e relazione

Per mettere in relazione il dataset delle manutenzioni con quello delle strade, potrete usare il meccanismo classico delle chiavi.

Il layer delle strade sarà caratterizzato da un campo con un codice identificativo numerico univoco per ogni strada – la chiave primaria - denominato nell’esempio di questo post “pkuid”.

Il database delle manutenzioni – che è una semplice tabella senza alcun attributo spaziale – è composto dai campi “PK_UID”, “data”, “responsabile”, “compagnia”, “note”; aggiungeremo un campo che contenga il codice identificativo della strada a cui la manutezione è riferita. Questo campo farà da chiave esterna e si chiamerà “ce_pkuid”.

Il nostro modello prevede, per semplicità, che ad ogni strada sia possibile associare una o più manutenzioni; una classica relazione 1:N impostata sulle due chiavi numeriche di cui sopra.

Layer

Il layer delle strade ha la struttura sottostante, in cui sono stati introdotti dei campi per raccogliere il nome della Via, il relativo tipo (Corso, Viale, Via, ecc.) e il codice identificativo comunale.

Quello delle manutenzioni conterrà un codice identificativo numerico univoco per ogni elemento, un campo in cui inserire la data dell’intervento di manutenzione, il nome del responsabile, il nome della compagnia incaricata e un campo per le note.

Definire la relazione

La relazione tra i due dataset si definisce a livello di “Proprietà di progetto”. Aperto il tab Relations si farà click su “Add Relation” e si imposteranno i seguenti parametri:

• Name, per dare un nome alla relazione;
• Referencing Layer (Child), per il nome del layer che contiene la chiave esterna;
• Referencing Field, per il nome della chiave esterna;
• Referencing Layer (Parent), per il nome del layer che contiene la chiave primaria;
• Referencing Field, per il nome della chiave primaria;
• Id, che viene usato internamente da QGIS, deve essere univoco e fa da indice della tabella di relazione tra chiave primaria e chiave esterna.

A relazione definita, questa apparirà nella finestra di dialogo:

Moduli

Una volta definita la relazione, se ne avrà evidenza anche nel modulo di inserimento/modifica degli attributi del dataset delle strade. La visualizzazione a moduli in QGIS è molto utile per alcuni task di verifica ed inserimento dei dati, ed è molto personalizzabile.

La tabella della manutenzioni sarà visibile in un widget del modulo dello strade, e sopra questa saranno visibili cinque pulsanti:

1. la matita, per attivare la modifica della tabella delle manutenzioni;
2. il “+” per aggiungere un nuovo record alla tabella, che di default verrà associato all’elemento attivo del layer “strade”;
3. il tasto “x” per cancellare il record selezionato della tabella “manutenzioni”;
4. il tasto “catenella” che aprirà una nuova finestra di dialogo con il quale associare qualsiasi manutenzione presente alla strada corrente;
5. il tasto “rompi catenella” che rimuoverà l’associazione tra la manutenzione selezionata e la strada corrente;
6. ed infine, a destra, ci sono due pulsanti per passare dalla vista “tabella” a quella modulo.

Anche per il dataset delle manutenzioni è possibile attivare il modulo di inserimento/modifica degli attributi, in modo che dia conto visivamente della relazione con il layer delle strade.

A partire dalle proprietà del layer manutenzioni, bisognerà modificare il widget del campo che fa da chiave esterna.

E scegliere come tipo di widget “Relation Reference”, utilizzando i parametri visibili nella figura sottostante:

Una volta scelto questo speciale widget, sarà possibile leggere la relazione tra i due layer anche nel modulo delle manutezioni:

Note finali (riferimenti, file esempio e video demo)

Questo articolo è quasi una traduzione di questo di Matthias Kuhn. Sono caduto nel suo bel blog, perché tra i tipi di widget accessibili nelle proprietà dei campi di un layer ho scoperto il tipo “Relation Reference”. A me era completamente ignoto; ho provato a cercare nella documentazione, senza però trovarne traccia. Il post di Matthias mi è sembrata una piccola perla e ho creduto utile clonarlo.

Da qui potrete scaricare un file di progetto d’esempio basato su QGIS 2.2 e come formato dati su SpatiaLite. Questo ultimo farà da contenitore unico sia del file spaziale (le strade), che della tabella delle manutenzioni.

Nel progetto, per i due moduli, ho impostato altri widget diversi da quello di default (“Line edit”); alcuni sono di grande comodità come quello per il campo “data” delle manutenzioni (widget di tipo “Calendar”), o l’elenco controllato del campo “Tipo” (widget di tipo “Value map”) del layer delle strade.

Per chi vuole vedere la cosa in azione ho preparato un piccolo video, che non ha alcuna pretesa didattica. Vuole essere soltanto una carrelata visuale rapida di alcune delle cose descritte in questo post.

Un’ultima annotazione finale: il caso d’uso descritto è molto semplificato e non tiene conto di tutte i requisiti di un’applicazione complessa come la gestione di un network stradale. Mi è stato utile come esempio per scrivere di QGIS, relazioni, moduli e widget.

NdR: Salvatore Fiandaca, prendendo spunto da questo post, ne ha creato una sorta di versione aggiornata che trovate qui. Ringraziamo Totò e consigliamo la lettura

Posted in Didattica | 20 Comments »

3 ottobre, 2013 | di Andrea Borruso

MySQL è uno dei più diffusi database server, e non deve stupire che abbia un’estensione spaziale (peraltro attiva di default). Mi stupisce però che le funzioni per testare le relazioni spaziali tra oggetti siano limitate.

Mi spiego con un esempio classico: selezionare tutti i punti di un layer che cadono all’interno del perimetro di un poligono presente in un altro layer. E’ un’operazione tipica dei GIS, e presente in tutti database con estensione spaziale.

In MySQL basta lanciare una query spaziale di questo tipo:

SELECT * FROM poligoni as g1, punti as g2 WHERE Contains(g1.geometry,g2.geometry) = 1

Purtroppo però l’output non è costituito da tutti i punti contenuti nel perimetro di mio interesse, ma da tutti quelli contenuti nel rettangolo che lo include. Quest’ultimo è il classico Bounding Box, o come si definisce in ambiente MySQL Minimal Bounding Rectangles (MBR). Nel manuale online è riportato:

The OpenGIS specification defines the following functions. They test the relationship between two geometry values g1 and g2.

Questo dovrebbe garantire che l’output della funzione Contains() sia relativo al perimetro dell’oggetto “target”, ma poco più avanti si legge:

Currently, MySQL does not implement these functions according to the specification. Those that are implemented return the same result as the corresponding MBR-based functions.

Immagino che i GeoSpatial Developers abbiano diverse frecce al loro arco per superare questo problema, così come ne esisteranno diverse lato utente. Una potrebbe essere quella di non usare questo database server e fare tutto con PostGIS, Oracle Spatial, Spatialite, ecc., ma è troppo facile e non sempre si può scegliere.

Io ho pensato a GDAL/OGR ed alle sue utility per oggetti vettoriali, ed alla possibilità (che esiste dalla versione 1.10 della libreria) di usare il dialetto SQLite/Spatialite. Questo dialetto estende di molto quello che queste utility fanno (egregiamente) di default, ovvero eseguire delle query sql all’interno di un comando; ad esempio:

ogrinfo province.shp province -sql "SELECT nome FROM province WHERE ID_PRO = 2"

Con il dialetto SQLite/Spatialite ho a disposizione anche le funzioni per verificare relazioni spaziali tra oggetti, anche per quelle basi dati che non prevedono intrinsecamente la possibilità di farlo, proprio come MySQL spatial.

Andando nel concreto dovrei scrivere una cosa di questo tipo:

ogrinfo MYSQL:"mydb,user=myuse,password=mypwd,port=3306" -dialect sqlite -sql "SELECT *FROM poligoni as g1, punti as g2 WHERE Contains(g1.geometry,g2.geometry) = 1"

La sorpresa è che, nonostante si dichiari nella stringa il “dialetto” sqlite, si ottiene sempre come output quello relativo ai punti contenuti nel bounding box del perimetro di interesse. E’ un baco? Nella documentazione di OGR si legge:

All OGR drivers for database systems: MySQL, PostgreSQL and PostGIS (PG), Oracle (OCI), SQLite, ODBC, ESRI Personal Geodatabase (PGeo) and MS SQL Spatial (MSSQLSpatial), override the OGRDataSource::ExecuteSQL() function with dedicated implementation and, by default, pass the SQL statements directly to the underlying RDBMS. In these cases the SQL syntax varies in some particulars from OGR SQL. Also, anything possible in SQL can then be accomplished for these particular databases. Only the result of SQL WHERE statements will be returned as layers.

Ma si legge anche:

The SQLite dialect may be used with any OGR datasource, like the OGR SQL dialect. It is available through the OGRDataSource::ExecuteSQL() method by specifying the pszDialect to “SQLITE”. For the ogrinfo or ogr2ogr utility, you must specify the “-dialect SQLITE” option.

La soluzione l’ho trovata in un test stupido che ho voluto fare: accedere alla fonte MySQL non direttamente, ma tramite il Virtual Format di OGR, che in qualche modo astrae l’accesso al formato di input.

A partire quindi da una fonte MySQL costruita secondo le specifiche del Virtual Format e salvata come “test.vrt”:

<ogrvrtdatasource>
<ogrvrtlayer name="poligoni">
<srcdatasource>MYSQL:mydb,user=myuser,
password=mypwd,port=3306,host=127.0.0.1
</srcdatasource>
<srclayer>poligoni</srclayer>
<geometrytype>wkbPolygon</geometrytype>
<layersrs>epsg:4326</layersrs>
</ogrvrtlayer>
<ogrvrtlayer name="punti">
<srcdatasource>MYSQL:mydb,user=myuser,
password=mypwd,port=3306,host=127.0.0.1
</srcdatasource>
<srclayer>punti</srclayer>
<geometrytype>wkbPoint</geometrytype>
<layersrs>epsg:4326</layersrs>
</ogrvrtlayer>
</ogrvrtdatasource>

Posso ad esempio lanciare:

ogrinfo test.vrt -dialect sqlite -sql "SELECT *FROM poligoni as g1, punti as g2 WHERE Contains(g1.geometry,g2.geometry) = 1"

Il risultato ottenuto sarà così quello desiderato e l’output corrisponderà a tutti i punti che ricadono nel perimetro di interesse. Nell’immagine di sotto, ho raccontato il tutto in modo visuale.

Concludo sottolineando  quanto siano interessanti, utili, belle e di alto livello professionale alcune delle dinamiche delle comunità open source. Ho segnalato quello che a me sembrava un baco alla lista GDAL – DEV. Non solo ho ottenuto subito delle risposte che mi hanno consentito di capirne molto di più, ma si sono messi già al lavoro sia in termini di codice che di documentazione. Un semplice “grazie” sembra veramente poco.

Questo post è dedicato a Flaviano, un mio amico che grazie anche ad alcuni tips & tricks su GDAL/OGR ha trovato un bel posto di lavoro in Qatar

Posted in Didattica | 1 Comment »

25 settembre, 2013 | di Andrea Borruso

Ieri io, Giuseppe Borruso, Fabio Malfatti, Lucio Beltrami e Gianluca Calabretta abbiamo fatto e  registrato l’hangout sul corso del Coursera dedicato alle mappe: “Maps and the Geospatial Revolution“.

E’ stata per me – che facevo purtroppo soltanto il bravo presentatore – un’esperienza entusiasmante. I “fantastici 4” di cui sopra sono riusciti a descrivere la loro esperienza di didattica in un MOOC (massive open online course) in modo interessante e molto variegato. La buona riuscita si deve a tre aspetti principali:

• la passione  (in questo caso per la geomatica)
• le storie professionali profondamente diverse di ognuno dei quattro
• la prorompente bella umanità dei protagonisti

E’ un video un po’ lungo (poco più di un’ora), ma è un crescendo che è una delle migliore testimonianze che io abbia visto su questo tipo di corsi online (lo so, il mio giudizio in questo caso vale poco). Averli ascoltati prima, ed avendo avuto in testa alcune delle loro parole dopo, mi è venuta una gran voglia di mettermi in gioco, di imparare, di condividere quanto appreso e di passare questo entusiasmo a qualcun altro.

Sembra sia stato un corso veramente ben pensato, e questo ovviamente è in qualche modo propedeutico. Ma le modalità di fruizione, l’interazione “massiva” con gli altri utenti da tutto il mondo, i criteri di valutazione e i tempi di fruizione sono altri aspetti importanti che rendono questo tipo di didattica molto soddisfacente.

Per preparare bene l’intervista è stata creata una piccola rassegna stampa, resa disponibile a tutti i partecipanti. Fornisce altri spunti di riflessione e dati sulla valutazione dei MOOC (non soltanto di natura geomatica). La trovate qui.

Oggi un amico – che l’ha visto in diretta – mi ha scritto che questo hangout “poteva tranquillamente essere preso per un servizio RAI (Storia)”. E’un giudizio molto di parte, ma più passano le ore, più sento di avere contribuito a creare qualcosa d’importante e non soltanto per questo settore.

In ultimo, un grosso grazie ai “fantastici 4”, ed al loro essere persone. E’ la cosa che personalmente mi ha arricchito di più.

Buona visione

Posted in Didattica | No Comments »