Scrivo questo articolo per due motivi: uno legato ad un caso pratico che sto cercando di risolvere, l’altro perché voglio portare all’attenzione di molti il fatto che questo meraviglioso modulo (VirtualKNN) non è supportato da QGIS e neanche dall’ultima versione QGIS 3.0 che sarà disponibile nei primi mesi del 2018. Questa nuova caratteristica è stata introdotta in spatialite 4.4 (nel 2015) e ancora non presente in QGIS in quanto supporta solo spatialite 4.3 che è l’ultima versione stabile.
Vediamo un pò qual è il problema che risolve questo fantastico modulo KNN ( K-Nearest Neighbors):
Immagina un insieme di geometrie arbitrarie, che potrebbe essere un enorme set di dati contenente milioni di funzionalità. Ora immagina che per qualsiasi motivo sei interessato a identificare rapidamente tutte le caratteristiche entro una stretta vicinanza spaziale a una posizione arbitraria. (maggiori dettagli qui)
Esempio pratico:
Supponiamo di dover mappare i punti luce di un Comune e di realizzare un geo-database caratterizzato da due vettori: il primo è lo stradario del comune (un vettore lineare con i nomi degli assi stradali – vettore noto) e un secondo vettore puntuale che rappresenta i lampioni che dobbiamo mappare.
Un classico problema da risolvere è quello di associare ad ogni lampione il nome della strada più vicina, questo si può risolvere in vari modi:
- manualmente, procedura lunga e soggetta ad errori;
- usando un gis desktop e tanta fantasia;
- usare un database server/client PostgreSQL/PostGIS, ma è come sparare ad una mosca con un Bazooka;
- usare spatialite 4.3 e un codice SQL corposo e poco prestante;
- oppure usare il database spatialite 4.4 dove sono state implementate le VirtualKNN.
Sfruttando queste tabelle virtuali è possibile creare, per esempio, dei trigger che ‘scattino‘ ogni volta che inseriamo un punto nel database e quindi popolino, automaticamente, il campo ‘nome strada‘ del lampione.
ecco un esempio di trigger:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TRIGGER ins_punti AFTER INSERT ON punti | |
| BEGIN | |
| UPDATE punti SET data_ins = DATETIME ('NOW') | |
| WHERE rowid = new.rowid ; | |
| UPDATE punti SET distanza = | |
| ( | |
| SELECT k.distance | |
| FROM knn k, punti p | |
| WHERE f_table_name = 'strade' | |
| AND ref_geometry = p.geom | |
| AND max_items = 1 | |
| AND p.ROWID=NEW.ROWID | |
| ) | |
| WHERE punti.ROWID=NEW.ROWID; | |
| UPDATE punti SET nome_strada = | |
| ( | |
| SELECT s.nome_strada | |
| FROM | |
| ( | |
| SELECT k.fid | |
| FROM knn k, punti p | |
| WHERE f_table_name = 'strade' | |
| AND ref_geometry = p.geom | |
| AND max_items = 1 | |
| AND p.ROWID=NEW.ROWID | |
| ) t left join strade s ON t.fid = s.pk_l | |
| ) | |
| WHERE punti.ROWID=NEW.ROWID; | |
| END; |
è stata effettuata una prova con 10.000 punti (inseriti utilizzando spatialite_gui), 590 assi stradali e utilizzando il codice di sopra:
tempo di esecuzione 2’54”
da notare che il codice effettua tre INSERT:
- la data di inserimento ;
- la distanza del lampione dall’ asse stradale più vicino ;
- il nome della strada più vicina ;
Stessa prova è stata effettuata con 100 punti e 590 assi stradali (uguali a quelli di prima) utilizzando spatialite 4.3 e un codice SQL senza possibilità di usare i virtualKNN, tempo di esecuzione 2′ 47″ !!! – cioè, quasi stesso tempo di esecuzione, ma due ordini di grandezza di differenza tra i punti!!!!
Note finali: Perché è importante che tale caratteristica sia supportata da QGIS e quindi aggiornino la versione di spatialite da 4.3 almeno alla 4.4 (perchè esiste già una versione di prova della 4.5)? il motivo è QField, app Android di QGIS.
QField monta lo stesso motore di QGIS e riproduce fedelmente un progetto qgs nell’app Android, quindi l’aggiornamento di QGIS desktop si ripercuoterebbe anche in QField con enormi vantaggi durante la fase di rilievo dei dati.
Allego screenshots per gli scettici:
Spatialite 4.4 utilizzando VirtualKNN – tempo esecuzione 1″ e 703 ms – neanche due secondi!!!
Spatialite 4.4 SENZA utilizzare VirtualKNN – tempo esecuzione 1′ 17″ e 506 ms – quasi 78 secondi!!! – con un errore concettuale ma che spatialite tollera!!! (group by)
Spatialite 4.4 SENZA utilizzare VirtualKNN – tempo esecuzione 1′ 10″ e 736 ms – quasi 71 secondi!!!
Buon lavoro!!!
download spatialite_gui 4.4 RC0
EDIT:
NB: estratto da una e_mail di Furieri:
prima implementazione della KNN che trovi nella 4.4.0 era affetta da diversi problemi anche gravi che sono stati risolti in seguito.
Ti consiglio caldamente di utilizzare i sorgenti trunk che trovi sul repository Fossil (4.5.0-devel) se vuoi essere sicuro di avere un KNN che funzioni correttamente.
TRIGGER suggerito da Furieri:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TRIGGER ins_punti AFTER INSERT ON punti | |
| BEGIN | |
| INSERT OR REPLACE INTO punti (pk_p, nome_strada, data_ins, distanza, geom) | |
| SELECT NEW.ROWID, s.nome_strada, DateTime('now'), k.distance, NEW.geom | |
| FROM knn AS k | |
| LEFT JOIN strade AS s ON (k.fid = s.pk_l) | |
| WHERE k.f_table_name = 'strade' | |
| AND ref_geometry = NEW.geom | |
| AND k.max_items = 1; | |
| END; |
trigger ottimizzato per evitare TRE insert differenti con miglioramento notevole delle prestazioni: tempo esecuzione 82 sec. wow!!!
pigrecoinfinito 
2 pensieri su “SpatiaLite e VirtualKNN”