[Gfoss] Spatialite e matrici presenza/assenza(?)

[a.furieri a lqt.it]

On Tue, 16 Sep 2014 03:48:01 -0700 (PDT), Beppe wrote:
> Salve a tutti,
> vorrei costruire un DB su spatialite relativo ad un parco 
> naturalistico: in
> questa area umida, in ogni stagno sono presenti diverse specie 
> animali e
> vegetali e spesso alcune di queste specie sono presenti in uno o 
> pochi
> stagni e assenti in tutti gli altri. Agli scopi dello studio è 
> sufficiente
> avere un id per ogni stagno collegato ad una colonna geometry con le
> coordinate del suo centroide, e sin qui ci siamo: il problema è che 
> non so
> come fare per quelle che forse impropriamente ho chiamato matrici
> presenza/assenza.
> Potreste indicarmi qual'è il modo corretto per affrontare il problema 
> in
> teoria ed in pratica con Spatialite?
>

almeno a prima vista questo problema non presenta nessun aspetto
specificamente legato a SQLite oppure a Spatialite.
e' piuttosto un problema generico di disegno delle basi dati
relazionali.

io personalmente lo affronterei piu' o meno giostrando su tre
tavole:

a) "stagni"
    id_stagno INTEGER PRIMARY KEY,
    .... qualsiasi altro attributo relativo allo stagno, ivi
    compresa un'eventuale geometria ....

b) "specie"
    id_specie INTEGER PRIMARY KEY,
    ... nome generico e specifico (latino), nome volgare italiano etc 
...

c) "presenze"

CREATE TABLE presenze (
    id_stagno INTEGER NOT NULL,
    id_specie INTEGER NOT NULL,
    CONSTRAINT pk_pres PRIMARY KEY (id_stagno, id_specie),
    CONSTRAINT fk_pres_stagno FOREIGN KEY (id_stagno)
       REFERENCES stagni (id_stagno),
    CONSTRAINT fk_pres_specie FOREIGN KEY (id_specie)
       REFERENCES specie (id_specie));
CREATE INDEX idx_pres_rev (id_specie, id_stagno);

n.b.: vanno caricate esclusivamente le presenza confermate;
le assenza verranno totalmente ignorate.

a questo punto potrebbe essere utile definire qualche VIEW di supporto:

CREATE VIEW stagni_aux_1 AS
SELECT a.id_stagno AS id_stagno, ... altri attributi stagno ...,
    b.id_specie AS id_specie, ... altri attributi specie ...
FROM presenze AS x
JOIN stagni AS a ON (x.id_stagno = a.id_stagno)
JOIN specie AS b ON (x.id_specie = b.id_specie);

SELECT * FROM stagni_aux
WHERE id_stagno = 1
ORDER BY id_specie;

p.es. questa prima query ti torna l'elenco ordinato delle
specie presenti nello stagno 1.

SELECT * FROM stagni_aux
WHERE id_specie = 1
ORDER BY id_stagno;

questa seconda query ti torna invece l'elenco di tutti
gli stagni dove vive la specie 1.

N.B.: con questa VIEW di esempio non puoi pero' mai sapere
quali sono le specie che *non* vivono in un determinato stagno,
oppure gli stagni in cui una determinata specie e' assente.
se ti interessa anche rilevare esplicitamente le asssenze oltre
che le presenze puoi comunque giocare di LEFT JOIN ... non e'
particolarmente complesso.

auguri e buon divertimento ;-)

ciao Sandro