[Gfoss] VirtualKNN in SpatiaLite 5

[a.furieri a lqt.it]

On Mon, 19 Apr 2021 03:20:37 -0700 (MST), pigreco wrote:
> Buongiorno a tutte/i
> ho usato in passato i VirtualKNN di SpatiaLite soprattutto nei 
> trigger, ma
> ora volevo usarlo per risolvere il seguente quesito:
>
> dati due tabelle, una con circa 3000 punti e un'altra con circa 10000 
> linee
> (assi stradali); trovare, per ogni punto, l'asse stradale più vicino.
>
> Per risolvere questo problema ho pensato di usare spatialite 5 e la 
> tabella
> KNN.
>
> Ho importato i due vettori in un geodatabase sqlite (creato con QGIS 
> 3.19
> master, che ha implementato spatialite 5.0.1)  e ho lanciato la 
> seguente
> query:
>
> 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 restituisce un output e quindi creo la relativa tabella 
> (create
> table as ..), ma impiega circa 200 secondi (sia da db manager di QGIS 
> che da
> spatialite_gui 2.1.0 beta 1);
>

che ci impieghi sempre lo stesso tempo e' normale; il lavoro duro lo
fa esclusivamente libspatialite, che venga incapsulata dentro a QGIS
oppure dentro alla GUI e' assolutamente irrilevante.

i tempi che riporti non mi tornano con quanto verifico sul mio PC;
qua da me ci mette poco piu' di 1 minuto (60-70 secondi), che e'
un valore abbastanza differente dal tuo.

possibile spiegazione: il tuo HW e' molto piu' lento del mio.
giusto per curiosita', io uso una workstation con un Intel i7
che ha 8 cores fisici da 3.0 GHz, 32 GB RAM e un SSD.


> volevo chiedere se faccio un uso corretto dei virtualKNN oppure ho 
> scritto
> male la query?
>

si puo' scrivere meglio invertendo l'ordine delle tavole.

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

riscritta in questa seconda forma gira in 45-50 secondi;
la differenza non e' abissale, ma con un problema simile
ma con maggiori dimensioni potrebbe facilmente diventare
molto piu' consistente.

nota metodologica:
==================
il query oprimizer di SQLite non e' particolarmente sofisticato;
molto spesso indovina la strategia ottimale di accesso ai dati,
ma qualche volta imbocca la strada sbagliata.
capita soprattutto quando ci sono di mezzo le VirtualTables,
che per SQLite sono "oggetti buffi" dal comportamento non
predicibile, ed ai quali viene sempre assegnata la minima
priorita' possibile.

visto che sia lo SpatialIndex che il KNN sono proprio basati
sulle VirtualTable di tipo R*Tree, occorre sempre stare
attenti a come si scrivono le query SQL, perche' potrebbe
avere un notevole impatto sui tempi di esecuzione.

le seconda forma toglie il query optimizer dall'imbarazzo,
perche' diventa chiarissimo che la sequenza attesa e':
1. pescati una riga dagli incroci
2. e poi vatti a cercare via KNN la geometria piu' vicina.

regola empirica a braccio: ogni volta che hai il sospetto
che una query basata su R*Tree giri lenta prova sempre a
riscrivere la tua query "rovesciata".

ciao Sandro