[Gfoss] VirtualKNN in SpatiaLite 5

Lun 19 Apr 2021 15:04:55 CEST

Grazie per la rapida risposta,
il mio laptop è del 2015 (Processore AMD FX-7500 Radeon R7, 10 Compute
Cores 4C+6G, 2100 Mhz, 4 core, 4 processori logici; 8 GB RAM e SSD) con win
10.

Non trovo quasi nessuna differenza in termini di tempo tra le due query
soluzioni proposte,  strano!

Grazie per avermi dato fiducia sull'uso dei VirtualKNN e per la nota
metodologia.

saluti

Il giorno lun 19 apr 2021 alle ore 14:04 <a.furieri a lqt.it> ha scritto:

> 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
> _______________________________________________
> Gfoss a lists.gfoss.it
> http://lists.gfoss.it/cgi-bin/mailman/listinfo/gfoss
> Questa e' una lista di discussione pubblica aperta a tutti.
> I messaggi di questa lista non hanno relazione diretta con le posizioni
> dell'Associazione GFOSS.it.
> 764 iscritti al 23/08/2019

-- 
*Ing. Salvatore Fiandaca*
*mobile*.:+39 327.493.8955
*m*: *pigrecoinfinito a gmail.com <pigrecoinfinito a gmail.com>*
*C.F*.: FNDSVT71E29Z103G
*P.IVA*: 06597870820
*membro QGIS Italia - http://qgis.it/ <http://qgis.it/>*
*socio GFOSS.it - *http://gfoss.it/
*blog:*
* https://pigrecoinfinito.com/ <https://pigrecoinfinito.com/> FB: Co-admin
- https://www.facebook.com/qgis.it/ <https://www.facebook.com/qgis.it/>**
<https://www.facebook.com/qgis.it/> *
*TW:  <http://goog_95411464>**https://twitter.com/totofiandaca
<https://twitter.com/totofiandaca>*

43°51'0.54"N  10°34'27.62"E - EPSG:4326

“Se la conoscenza deve essere aperta a tutti,
perchè mai limitarne l’accesso?”
R. Stallman

Questo documento, allegati inclusi, contiene informazioni di proprietà di
FIANDACA SALVATORE e deve essere utilizzato esclusivamente dal destinatario
in relazione alle finalità per le quali è stato ricevuto. E' vietata
qualsiasi forma di riproduzione o divulgazione senza l'esplicito consenso
di FIANDACA SALVATORE. Qualora fosse stato ricevuto per errore si prega di
informare tempestivamente il mittente e distruggere la copia in proprio
possesso.