[Gfoss] problemi con indici spaziali sqlite

[a.furieri a lqt.it]

On Thu, 20 Feb 2014 22:09:42 +0100, Andrea Peri wrote:
> Infatti anche io credo che sia un problema di triggers.
>

"mistero nisterioso" completamente svelato.
*** leggete con attenzione le note finali in fondo ***

cominciamo mettendo in piedi un piccolo banale test:

CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT);
SELECT AddGeometryColumn('test', 'geom3003', 3003, 'POINT', 'XY');
SELECT CreateSpatialIndex('test', 'geom3003');
SELECT AddGeometryColumn('test', 'geom4326', 4326, 'POINT', 'XY');
SELECT CreateSpatialIndex('test', 'geom4326');

DROP TRIGGER gii_test_geom3003;

CREATE TRIGGER ins_geom4326 AFTER INSERT ON test
FOR EACH ROW BEGIN
UPDATE test SET geom3003 = ST_Transform(NEW.geom4326, 3003)
   WHERE rowid = NEW.ROWID;
END;

CREATE TRIGGER upd_geom4326 AFTER UPDATE OF geom4326 ON test
FOR EACH ROW BEGIN
UPDATE test SET geom3003 = ST_Transform(NEW.geom4326, 3003)
   WHERE rowid = NEW.ROWID;
END;

INSERT INTO test (id, geom4326) VALUES (NULL, MakePoint(42.11, 11.41, 
4326));
INSERT INTO test (id, geom4326) VALUES (NULL, MakePoint(42.12, 11.42, 
4326));
INSERT INTO test (id, geom4326) VALUES (NULL, MakePoint(42.13, 11.43, 
4326));
INSERT INTO test (id, geom4326) VALUES (NULL, MakePoint(42.14, 11.44, 
4326));
INSERT INTO test (id, geom4326) VALUES (NULL, MakePoint(42.15, 11.45, 
4326));

UPDATE test SET geom4326 = MakePoint(42.25, 10.45, 4326) WHERE id = 1;
UPDATE test SET geom4326 = MakePoint(42.26, 10.46, 4326) WHERE id = 3;
UPDATE test SET geom4326 = MakePoint(42.27, 10.47, 4326) WHERE id = 5;

SELECT CheckSpatialIndex('test', 'geom4326');
SELECT CheckSpatialIndex('test', 'geom3003');

SELECT id, ST_X(geom4326), ST_Y(geom4326), ST_X(geom3003), 
ST_Y(geom3003)
FROM test;
SELECT * FROM idx_test_geom4326;
SELECT * FROM idx_test_geom3003;

micro-spiegazione:
- creaiamo una tavola che ha solo la Primary Key
- aggiungiamo una geometria POINT srid=4326 con SpatialIndex
- aggiungiamo una seconda geometria POINT srid=3003
   (sempre con SpatialIndex)
- definiamo una coppia di triggers per assicurare che tutte le
   volte che si inserisce una geometria WGS84 venga immediatamente
   aggiornata anche la corrispondente GaussBoaga
- inseriamo e modifichiamo qualche riga
- ed alla fine andiamo a verificare se entrambi gli Spatial Index
   sono validi oppure se presentano problemi di out-of-sync.


******* NOTE FINALI ***********

scritto cosi' funziona perfettamente.
anche senza attivare "PRAGMA recursive_triggers=1".

in effetti, anche da un punto di vista del purismo lessicale, non 
stiamo
utilizzando nessun trigger ricursivo (che cioe' richiama se stesso piu'
e piu' volte).
questi sono tutti banali triggers nidificati (un trigger che ne chiama
un altro and so on, ma senza mai tornare su se stesso)

il silver bullet che fa la differenza e che evita la corruzione degli
Spatial Index e' semplicemente questo qua:

DROP TRIGGER gii_test_geom3003;

veloce analisi tecnica:
-----------------------
questo trigger (standard splite) salvaguarda tutte le INSERT per la 
colonna
"geom3003" (quella che invece noi lasciamo sempre NULL, dato che il 
valore
lo inserisce sempre l'altro trigger "ins_geom4326").
se lasciamo dentro questo trigger finisce che viene sempre eseguito per
ultimo nella catena nidificata; (verificato con una lunga e pallosa 
sessione
di debugging ad hoc: fidatevi).
ma a questo livello NEW.geom3003 vale sempre NULL (valore iniziale 
assegnato
esplicitamente dalla INSERT), e quindi il valore correttamente 
assegnato
precedentemente da "ins_geom4326" viene sovrascritto e finisce che 
comunque
qua ci trovremo un bel NULL, che finisce definitivamente nello Spatial 
Index

il problema non esiste invece per l'UPDATE, dato che non adremo mai ad
aggiornare direttamente la colonna "geom3003" (verra' sempre aggiornata
indirettamente via Trigger).


valutazione finale:
-------------------
i triggers di SQLite certamente consentono di implementare "effetti 
speciali"
assolutamente favolosi.
ma e' un po' come scalare ad alta quota un'altissima parete rocciosa 
verticale;
ci si muove  in zona ad altissimo rischio, il minimo errore e la piu' 
microscopica
disattenzione saranno certamente fatali :-D

ciao Sandro