Sistemi elaborazione informazioni - Progetto esame - Realizzazione di un database

CREATE OR REPLACE VIEW public.intestazione AS

SELECT r.num AS numero_rilievo,

s.cod AS codice_stazione,

r.data,

r.ora,

s.loc AS "località",

s.alt AS altitudine,

s.exp AS esposizione,

s.pend AS pendenza,

st_astext(s.geom) AS coordinate,

r.ww AS precipitazioni,

r.n AS nubi,

r.v AS vento,

r.q AS quota_vento,

ri.nome AS nome_rilevatore,

ri.cognome AS cognome_rilevatore,

s.comp AS stazione_forestale_di_competenza,

r.ta AS temperatura_aria,

r.hs AS altezza_manto_nevoso,

r.sf AS "rugosità_superficiale", 29

r.note

FROM stazione s

JOIN rilievo r ON s.id = r.staz

FULL JOIN (rilevazione ril

JOIN rilevatore ri ON ril.rilevatore = ri.id) ON ril.rilievo = r.num

ORDER BY r.data, r.ora;

Questa vista riflette precisamente l’intestazione dei modelli 2 e 3 AINEVA che si può vedere in Figura 1 e

può facilitare la consultazione dei rilievi senza dover ogni volta ricorrere ad operazioni di join per, ad

esempio, ricavare i nomi dei rilevatori oppure la località in cui è stato eseguito un rilievo.

Vengono aggiunte delle etichette ai nomi degli attributi per una comprensione immediata degli stessi.

5. POPOLAMENTO DELLA BASE DI DATI

Per popolare il database creato sono stati usati i dati dei rilievi nivologici di dicembre 2014 e gennaio 2015

forniti dalla Struttura stabile centrale per l'attività di prevenzione del rischio da valanga (Servizio foreste e

corpo forestale) della Regione Autonoma Friuli Venezia Giulia 30

a. Dati grezzi

I dati grezzi erano dei fogli di calcolo estratti da un database Microsoft Access in cui erano memorizzate le

informazioni sui rilievi. I dati delle stazioni itineranti erano incorporati a quelli dei rilievi, motivo per cui

sono stati scorporate le informazioni proprie delle stazioni, creando un foglio di calcolo ad hoc.

Le coordinate delle varie stazioni si trovavano sparse su più fogli di calcolo, spesso memorizzate in sistemi

di riferimento differenti (EPSG:3004, EPSG:4326, EPSG:23033, …) ed in ogni caso come semplice testo: le

coordinate sono state così uniformate al SR EPSG:3004 (Gauss-Boaga fuso est) tramite un tool convertitore

online [5] .

I fogli di calcolo dalla prova penetrometrica col test della sonda e dell’analisi stratigrafica riflettevano la

sonda strato

struttura delle tabelle e della base di dati, rispettivamente.

Nomi e cognomi dei rilevatori erano memorizzati in un’unica colonna del foglio di calcolo “Rilievi.xlsx”: è

stato dunque creato un foglio ad hoc con nome e cognome dei rilevatori, aggiungendo un id progressivo ed

il tipo di figura professionale. È stato poi creato un foglio che associa il numero di un rilievo ai rilevatori che

l’hanno eseguito.

Sono stati usati gli shapefile dei Comuni FVG 2016 [6] e delle Valanghe Rilevate [7], entrambi scaricati nel

SR di progetto (EPSG:3004), ed importati nella base di dati grazie al DB Manager di QGIS 2.12.2-Lyon. Lo

shapefile delle valanghe servirà unicamente per una selezione spaziale descritta nel prossimo paragrafo.

Eseguito qualche adattamento nella formattazione e nell’ortografia, i fogli di calcolo sono stati convertiti in

file Comma Separated Values (*.csv), uno per ognuna delle tabelle presenti nel database, ed importati nella

base di dati tramite pgAdmin.

b. Raffinamento della base di dati geom stazione.

Le coordinate delle stazioni sono state inserite nell’attributo della tabella Ad esempio,

per inserire la geometria della stazione con id 29 l’istruzione è la seguente:

update stazione

set geom = st_geomfromtext

('POINT(2403168 5136014)',3004)

where id = 29;

L’appartenenza di una stazione ad un comune non era nota nei dati grezzi: per inserire il codice ISTAT di un

comune stazione

comune su cui una stazione insiste nell’attributo di è stata formulata l’interrogazione

update stazione

set comune = ( select c.cod

from comune c

where st_contains (c.geom, stazione.geom));

In questa maniera il vincolo per cui la geometria di una stazione dev’essere contenuta all’interno del 31

comune di appartenenza viene automaticamente rispettato.

Per le stazioni di cui non erano note le geometrie e di cui si poteva risalire con certezza al comune su cui

insistevano, i codici ISTAT sono stati inseriti manualmente con comandi del tipo

update stazione

set comune = '030117'

where id = 26;

Per consentire operazioni computazionalmente più efficienti, si è scelto di eliminare dallo shapefile dei

comuni le geometrie su cui non sono presenti stazioni di rilievo o valanghe. Per far ciò è stata formulata la

seguente interrogazione ottenendo uno shapefile dei comuni “montani” (Figura 3).

delete from comune

where cod not in

(select distinct c.cod

from comune c, stazione s

where st_within (s.geom, c.geom)

union

select distinct c.cod

from comune c, valanga v

where st_intersects (v.geom, c.geom));

Figura 3: comuni "montani" in verde scuro; in chiaro il vecchio shapefile

rilievo

Durante il popolamento della tabella ci si accorge che per alcuni rilievi mancano i dati relativi al

v q).

vento ed alla quota del vento (attributi e Per non perdere le altre informazioni che quei rilievi

forniscono, si decide di eliminare il vincolo di valor non nullo per quei due attributi.

ALTER TABLE rilievo ALTER TABLE rilievo

ALTER v DROP NOT NULL; ALTER q DROP NOT NULL; 32

strato e1

Similmente, durante la popolazione di si vede che alcuni attributi sono nulli. Si decide e si

opera come nel caso precedente, consentendo il valor nullo.

strato, strato_ff1ff2_check,

Sempre in grazie alla verifica operata dal vincolo ci si accorge di

dati ridondanti: si sceglie di eliminarli coi comandi

UPDATE strato UPDATE strato

SET ff2 = NULL SET ff2 = NULL

WHERE ff1 = ff2 WHERE ff1 = ff2

AND e1=e2; AND e2 is NULL;

stazione

In si evince come rilievi molto vicini tra di loro presentino nomi di località differenti (cfr. Figura

4). Si sceglie di uniformare i nomi delle località di queste ed altre stazioni itineranti e spostare le altre

note rilievo.

informazioni nell’attributo di

Prima di tutto vediamo quali sono i nomi delle località di stazioni itineranti

select distinct loc

from stazione

where cod = 'ITIN' or cod = 'ITGA'

group by loc

order by loc

Successivamente si può procedere all’aggiornamento

update stazione set loc = 'Golovec' where loc = 'Golovec - Alpi Giulie'

or loc = 'Golovec-Alpi Giulie Gruppo Canin';

update rilievo set note = 'Canalone nord Bivera' where staz = 36;

update stazione set loc = 'Bivera' where loc = 'Canalone nord bivera';

update rilievo set note = 'Vallone del Ploto' where staz = 28;

update stazione set loc = 'Coglians' where loc = 'Coglians Vallone del

Ploto';

update rilievo set note = 'Rilievo effettuato presso la centralina' where

staz = 38;

update stazione set loc = 'Forcella Baldass' where loc = 'Forcella

Baldass - Centralina';

update stazione set loc = 'Matajur' where loc = 'Itin M.Matajur';

update rilievo set note = 'Forcella Tiarfin' where staz = 32; 33

update stazione set loc = 'Tiarfin' where loc = 'Forcella Tiarfin';

update rilievo set note = 'Tiarfin versante NE' where staz = 33;

update stazione set loc = 'Tiarfin' where loc = 'Tiarfin versante N-E';

Verifica di vincoli d’integrità

c. sonda(), densita(), t_neve()

Per come sono stati definiti i trigger che invocano le funzioni e

strato(), l’inserimento di rilievi carenti delle prove relative al Modello 3 AINEVA o di rilievi fissi senza

Modello 2 è permesso, limitandosi il sistema ad una notifica. Verifichiamo questi vincoli con interrogazioni

booleane:

select not exists

(select * from rilievo, stazione

where num not in (select rilievo from sonda)

and rilievo.staz = stazione.id

and stazione.cod <> 'ITGA'

and stazione.cod <> 'ITIN');

true,

Il risultato è dunque tutti i rilievi in stazione fissa sono provvisti del test della sonda. La RV1 di

Tabella 2 è rispettata.

Verifichiamo che in ogni rilievo sia stata misurata la densità della neve:

select not exists

(select * from rilievo

where num not in (select distinct rilievo from densita));

false:

Il risultato è eseguendo la query interna si scopre che il rilievo numero 20 non ha densità della

neve. Facendo uso delle viste create si può scoprire il motivo.

select * from intestazione

where numero_rilievo not in

(select distinct rilievo from densita);

select * from analisi_strati where numero_rilievo = 20;

L’altezza del manto nevoso di quel rilievo era di soli 16 cm e lo strato centrale era composto da una crosta

da fusione e rigelo, rendendo difficoltosa l’operazione di carotaggio per misurare la densità. Inoltre gli altri

strati sono eterogenei, dunque una misura della densità non avrebbe fornito informazioni significative sullo

stato dell’intero manto nevoso.

Il vincolo non è rispettato, ma per questo rilievo si tollera la mancanza di misure di densità per non perdere

le altre informazioni.

Con query analoghe si può verificare infatti che tutti i rilievi dispongono di analisi stratigrafica e di

temperature della neve. 34

stazione

Verifichiamo con un’interrogazione booleana che tutte le geometrie di (quando non nulle)

comune

7

siano contenute all’interno delle geometrie di

select not exists (

select distinct s.loc, s.cod, s.geom

from stazione s

where s.geom is not null

except

select distinct s.loc, s.cod, s.geom

from stazione s, comune c

where st_within (s.geom, c.geom)

); false:

Il risultato della query è ciò vuol dire che alcune geometrie non si trovano all’interno delle

comune.

geometrie di Eseguendo la query interna si può verificare che tre rilievi itineranti sono stati

comune.

eseguiti fuori dalle geometrie di

Visualizzandole in QGIS (Figura 4) ci si accorge della posizione: due rilievi sono stati eseguiti nei pressi del

Monte Tiarfin, ma in territorio veneto, essendo presumibilmente i rilevatori saliti da Sella Razzo. Un altro

rilievo è stato eseguito a Sella Prevala, dunque è facile aver sconfinato in territorio sloveno. Questo è

perfettamente comprensibile e casi come questi vengono tollerati; diverso sarebbe stato avere stazioni

lontane diverse decine di chilometri dai confini regionali, segno di un errore nelle coordinate.

7 stazione

Si noti che aver inserito in il codice ISTAT dei comuni su cui le stazioni insistono tramite una query

spa