popola alcuni campi in funzione della vicinanza

trigger_with_KNN.sql

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;

Furieri scrive: http://lists.gfoss.it/pipermail/gfoss/2018-January/042438.html
ho testato il tuo trigger (quello di sopra), e funziona correttamente.
ci trovo un unico difettuccio: utilizza ben tre UPDATE per
ciascuna INSERT, e ciascuna delle UPDATE lancia una subquery
KNN che e' un'operazione computazionalmente pesantuccia.

(testata su 10.000 punti: tempo esecuzione circa 176 sec)

Soluzione proposta da Furieri:

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;

testata su 10.000 punti, tempo esecuzione: circa 83 sec;