In questo articolo voglio far vedere la bellezza dell’Open Source e della comunità che ci sta dietro.

Ecco una thread in ML GFOSS.it e la relativa risposta:

OGGETTO: Spatial Join con SpatiaLite

Buongiorno e buona domenica a tutti.
Sto provando uno spatial join sfruttando un buffer a 250 m da un punto, ma non riesco a capire perchè non funziona.

Come dato di base sto sfruttando le celle censuarie ISTAT che potete scaricare da qui
http://www.istat.it/storage/cartografia/basi_territoriali/WGS_84_UTM/2011/R15_11_WGS84.zip,
le ho chiamate test_celle.  Poi c’è un punto che ha come attributo il solo ID e di cui faccio (vorrei fare) un buffer a 250 m. Il tutto voglio farlo confluire in una view chiamata spatial_join.
Inserisco il codice che ho usato:

Dove sbaglio?

RISPOSTA:

(Alessandro Furieri papà di SpatiaLite)

ciao Massimiliano,

scusami per la risposta poco tempestiva ma nei giorni scorsi ero impegnato su tutt’altri versanti. Ho provato a replicare il tuo esempio a partire dalle Sezioni di Censimento ISTAT 2011 della Campania. Come punto di riferimento (visto che tu non hai fornito esempi) ho usato la posizione della Cappella San Severo nel cuore del centro storico di Napoli. Piu’ sotto troverai i miei commenti.

– primo errore: per arrivare a creare una View non si deve mai   partire creando direttamente la View stessa, perche’ in questo   modo se qualcosa va storto non capirai piu’ nulla e non sarai   in grado di identificare e correggere i tuoi errori. Si parte sempre definendo (e testando accuratamente) lo  statement SELECT, che una volta messo opportunamente a punto sara’ poi facilissimo trasformare in una View.

– secondo errore: mai usare “SELECT *” in una View; tu pensi in  questo modo di risparmiare tempo e fatica, ma finirari invece per perdere un sacco di tempo e per faticare un sacco rincorrendo errori “misteriosi”. Buona regola da usare _SEMPRE_ con le View: tutte le colonne vanno identificate esplicitamente con il proprio nome qualificato  (specificando cioe’ a quale tavola appartiene ciascuna colonna), ed e’ sempre opportuno specificare esplicitamente tutti gli alias names di colonna per evitare poi spiacevoli sorprese al momento dell’esecuzione.

– terzo errore: (vedo che lo segnali tu stesso nella tua mail successiva) ST_Contains() non e’ l’operatore spaziale che devi usare, perche’ richiede che la prima geometria copra interamente la seconda. Ma nel nostro caso e’ molto piu’ verosimile che le Sezioni di Censimento ricadano solo parzialmente all’interno del Buffer, ragion per cui e’ molto  piu’ opportuno usare la ST_Intersects()

– quarto errore (veniale): quando usi un alias-name per le tavole, cerca di usare una singola lettera, altrimenti poi ti troverai a dovere gestire nomi completi esageratamente  lunghi ed inutilmente complicati.

ed ecco qua la prima versione della nostra SELECT riscritta in modo piu’ ragionevole:

questa prima query identifica 46 sezioni di censimento in circa 1 secondo e 460 millisecondi; possiamo migliorare la velocita’ di esecuzione ?

basta evitare di usare la ST_Buffer + ST_Intersects, che nel caso di un singolo punto rappresenta un’inutile complicazione che impone un sacco di calcoli complessi di cui possiamo fare tranquillamente a meno. Basta usare semplicemente la ST_Distance per ottenere il medesimo risulato con tempi di esecuzione decisamente migliori. Questa seconda query impiega soli 600 millis, cioe’ meno della meta’ del tempo richiesto dalla precedente. Comunque possiamo arrivare ad una ottimizzazione ancora piu’ spinta.

SELECT CreateSpatialIndex(‘test_celle’, ‘geometry’);

per prima cosa andiamo a creare uno Spatial Index che supporti le sezioni di censimento. e poi riscriviamo la query precedente in modo tale che ora prenda in considerazione lo Spatial Index. Ora siamo scesi ad un tempo di esecuzione di soli 250 millis, che e’ decisamente soddisfacente.

Nota bene: il dataset sezioni di censimento campania contiene circa 25mila features, cioe’ un dataset MEDIO-PICCOLO; ed infatti i tempi di esecuzione sono abbastanza soddisfacenti anche quando non si usa lo Spatial Index. Ma se si fosse trattato di un dataset GRANDE (con milioni di features) l’uso dello Spatial Index o meno avrebbe
prodotto effetti decisamente critici.

a questo punto siamo finalmente pronti per andare a creare la nostra View:

SELECT * FROM spatial_join;

ok, ora abbiamo creato la View ed abbiamo verificato che funzioni correttamente.

Attenzione: non abbiamo ancora finito, perche’ per ora abbiamo semplicemente una View “non qualificata”, che il data provider di QGIS non sara’ in grado di visualizzare. Quello che dobbiamo fare a questo punto e’ registrare correttamente una Spatial View, che anche QGIS sapra’ riconoscere correttamente come un layer di tipo vettoriale.

in cui:
– ‘spatial_join‘ e’ il nome della View che intendi registrare per promuoverla a Spatial View;

– ‘geom‘ e’ l’alias-name della geometria presente nella View

– ‘rowid‘ identifica la Primary Key corrispondente alla geometria (a questo punto ti dovrebbe risultare assolutamente chiaro perche’ abbiamo dovuto usare un sacco di noiosi alias-names nella definizione della View);

– ‘test_celle‘ e’ il nome della tavola che fornisce le geometrie

– ‘geometry‘ e’ il nome della colonna geometria dentro a test_celle (specificare queste ultime tre informazioni e’ indispensabile per consentire al data provider di QGIS di accedere correttamente agli Spatial Index anche nel caso delle Spatial Views).

– infine 1 significa semplicemente che questa Spatial View e’ di tipo read-only (puoi consultare le features, ma non le puoi modificare in nessun caso).

ora abbiamo veramente finito; fai partire QGIS e vedrai che funziona tutto 😉

ciao Sandro

Note finali: questa non è una semplice risposta ad un quesito ma è una piccola lezione sull’uso dei DBMS e, più in generale, nell’ottimizzazione di uno script SQL. Non è la prima volta che Furieri, nel rispondere a dei quesiti, fa una bella lezione.

W Open Source, GFOSS.it e A. Furieri.

Iscrivetevi alla mailing list GFOSS.it  e all’associazione GFOSS.it