Finalmente SpatiaLite 5 è implementato dentro QGIS!!!

Spatialite 5 è stato annunciato da Alessandro Furieri nell’agosto del 2018 e diventata versione stabile a fine agosto 2020. Ad ottobre 2020 apro un ticket (in OSGeo4W Trac) dove chiedo di implementare SpatiaLite 5 in QGIS: dopo 6 mesi il ticket viene chiuso, bingo!!!

SpatiaLite 5 è dentro QGIS ma solo nelle versioni a 64bit (e solo da win 10 in su): nella versione OSGeo4W Network Installer (1) e nelle standalone (2) di testing scaricabili dal sito ufficiale:

Quale problema risolvono i VirtualKNN  (K-Nearest Neighbors)

Immaginiamo di avere due insiemi di oggetti, il primo rappresentato da punti (esempio: punti luce pubblica illuminazione) e il secondo da linee (per esempio assi stradali): a partire da un punto, quali e quanti (per K=1 prende un solo asse, il più vicino) sono gli assi stradali più prossimi (più vicini)? Ecco, questo è il problema che risolve brillantemente (è straordinariamente veloce) il VirtualKNN di SpatiaLite. (per maggiori info leggi qui)

Fatta questa doverosa introduzione, vediamo ora come usare il VirtualKNN (presente in SpatiaLite a partire dalla versione 4.4, QGIS era ferma da anni alla 4.3a) in QGIS e in particolare usando il plugin DB Manager.

Esempio pratico

Utilizziamo la rete stradale di Palermo (da OpenStreetMap) e dei punti (incidenti stradali Palermo 2018, dal portale Opendata di Palermo): per ogni punto, tracciare il segmento di minima distanza tra punto e rete stradale.

Come procedere usando solo QGIS

Creare database, tasto destro su Provider SpatiaLite:

Importare, nel database appena creato, i due dataset, basta un semplice DragAndDrop

da DBManager, crea indici spaziali (passo indispensabile!) cliccando su crealo

Scrivere la query ed eseguirla:

CREATE TABLE segmenti_min_dist AS
SELECT d.pk_punti, d.fid as pk_strade, d.distance as distanza,
	 ST_shortestline(d.geom, s.geom) as geom 
FROM
(SELECT a.fid as fid, a.distance as distance, zz.pk as pk_punti,zz.geom
	FROM knn as a 
	JOIN 
	inc2k18Palermo as zz 
	WHERE f_table_name = 'strade_palermo' 
	AND f_geometry_column = 'geom' 
	AND ref_geometry = zz.geom 
	AND max_items = 1) as d 
JOIN
	strade_palermo s ON (pk_strade = s.pk)
ORDER BY d.pk_punti

dove:

• ST_shortestline è la funzione che traccia il segmento minimo;
• knn è la tabella virtuale creata automaticamente da spatialite (>=4.4) e che permette di calcolare velocemente i K elementi più prossimi;

la tabella creata non avrà ancora la geometria riconoscibile da QGIS, quindi occorre lanciare anche:

SELECT RecoverGeometryColumn('segmenti_min_dist', 'geom',4326, 'LINESTRING', 'XY')

Caricare il vettore in QGIS

Visualizzo

APPROFONDIMENTI

Per chi non fosse interessata/o puo’ saltare questo paragrafo.

Vediamo di analizzare meglio ogni parte della query utilizzata.

Il cuore della query, cioè la parte che fa il gran lavoro di ricerca è questa:

SELECT a.fid as pk_strade, a.distance as distance, 
    zz.pk as pk_punti
FROM knn as a
JOIN inc2k18Palermo as zz
WHERE f_table_name = 'strade_palermo'
AND f_geometry_column = 'geom'
AND ref_geometry = zz.geom
AND max_items = 1

la query crea una tabella di output con i campi: pk_strade, distance e pk_punti, ovvero genera per ogni punto (pk_punti) una riga (max_items = 1) con i dati della strada più vicina e la relativa distanza:

Per ottenere anche il segmento di minima distanza, occorre utilizzare la funzione ST_Shortestline() e quindi viene fuori la query descritta all’inizio.

ESEMPIO 2

Stesso database, ma questa volta utilizzo solo il layer dei punti inc2k18Palermo per fare un esempio che non ha un senso pratico, ma concettuale: per ogni punto, tracciare i segmenti di minima distanza verso i tre punti più vicini:

la query utilizzata è:

CREATE TABLE segmenti_min_dist_point AS 
SELECT d.pk_punti, d.fid as pk_pti, d.distance as distanza,
      ST_shortestline(d.geom, s.geom) as geom  
FROM (SELECT a.fid as fid, a.distance as distance,
      zz.pk as pk_punti,zz.geom     
      FROM knn as a
      JOIN inc2k18Palermo as zz
      WHERE f_table_name = 'inc2k18Palermo'
	  AND f_geometry_column = 'geom'     
	  AND ref_geometry = zz.geom      
	  AND max_items = 4) as d  
JOIN inc2k18Palermo s ON (pk_pti = s.pk) 
ORDER BY d.pk_punti

Osservazione: per cercare i tre punti più vicini ho usato max_items = 4 perché uno sarebbe se stesso (3+1).

NOTE FINALI

Il layer dei punti contiene 3.342 elementi, mentre il layer degli assi stradali contiene 9.410 elementi; la creazione della tabella finale segmenti_min_dist impiega circa 186 sec, ovvero 0.055 sec (186/3342) per ogni punto (i tempi dipendono molto dalle caratteristiche hardware del PC, il mio è del 2015, un po’ vecchiotto). Come si nota è estremamente veloce la ricerca dei K elementi più vicini e tale prestazioni sono molto utili nei trigger (qui un esempio).

RIFERIMENTI

• SpatiaLite : https://www.gaia-gis.it/fossil/libspatialite/index
• QGIS : https://qgis.org/it/site/
• Download spatialite 5 : http://www.gaia-gis.it/gaia-sins/windows-bin-amd64-prev/spatialite_gui-NG-5.0.0-win-amd64.7z
• VirtualKNN : https://www.gaia-gis.it/fossil/libspatialite/wiki?name=KNN
• GFOSS : http://lists.gfoss.it/pipermail/gfoss/2021-April/044036.html
• database : https://drive.google.com/file/d/1wi1VJgzgB3tKSePyRkaJc45fdAw6d2wM/view?usp=sharing
• Portable QGIS 3.18.2 Zürich con SpatiaLite 5 : https://github.com/pigreco/QGIS_portable_3x#con-grass-gis-78-saga-gis-782-spatialite-5-pdal-22—solo-per-win-10-64-bit

Se il blog post vi è piaciuto cliccate su ‘Mi piace’, grazie!!!
if you liked the blog post click on ‘Like’, thank you !!!

SE IL POST VI È STATO UTILE CONTRIBUITE A MANTENERLO AGGIORNATO PAYPAL