UNIVERSITÀ DEGLI STUDI DI UDINE
Dipartimento di Scienze Agrarie
CORSO DI LAUREA IN SCIENZE E TECNOLOGIE DELL’AMBIENTE E DEL TERRITORIO
Caso di Studio:
ANAGRAFE AZIENDE AGRICOLE
1
Descrizione del dominio applicativo
Si vuole gestire l’anagrafica delle aziende agricole della regione Friuli Venezia Giulia.
Per ogni azienda si vuole mantener traccia del Codice Univoco di identificazione delle Aziende Agricole1 , della denominazione, dei proprietari (per
ognuno nome, cognome, codice fiscale), della sede legale (via, civico, comune
e provincia), di uno o più numeri di telefono e delle diverse sedi in cui viene
svolta l’attività. Per ogni sede di attività si vuole gestire i riferimenti della
particella catastale (con mappale catastale e superficie).
Per le particelle su cui viene svolta l’attività di coltivazione si vuole tener
traccia del tipo di irrigazione disponibile (nessuno, a pioggia o a scorrimento), delle colture coltivate e se la coltivazione è gestita in rotazione2 o meno.
Nel caso in cui la particella sia coltivata fuori rotazione si vuole l’indicazione
sulla percentuale di terreno coltivata per ogni coltura. Nel caso di particelle gestite in rotazione si vuole l’informazione relativa all’ordine di gestione
delle diverse colture.
Per le particelle su cui viene svolta l’attività di allevamento si vuole tener
traccia del numero di animali gestiti per specie (suini, bovini, equini, etc.)
1
Il CUAA, corrispondente al codice fiscale, è il numero identificativo da utilizzarsi in
tutti i rapporti con la pubblica amministrazione, ai sensi dell’art. 1, comma 2, del D.P.R.
503/99.
2
La rotazione delle colture o avvicendamento colturale è una tecnica adottata in agricoltura e giardinaggio che prevede la variazione, da un ciclo produttivo all’altro, della
specie agraria coltivata nello stesso appezzamento, al fine di migliorare o mantenere la
fertilità del terreno agrario e garantire, a parità di condizioni, una maggiore resa. Si
contrappone alla tecnica della monosuccessione, che consiste nella ripetizione sullo stesso
appezzamento della coltura effettuata nel ciclo precedente.
1
e tipo di stabulazione utilizzato (a terra, box su lattiera, recinti individuali,
etc.). Tenere presente che non tutti i tipi di stabulazione possono essere associati a tutte le specie animale. Inoltre, per ogni particella si vuole
memorizzare informazioni relative al tipo di stoccaggio dei residui prodotti
(vasca scoperta, platea per solidi, platea scoperta, platea coperta, etc.) con
il relativo volume.
2
Progettazione Concettuale
Lo schema risultante dalla progettazione concettuale rappresentato in Figura 1.
Figura 1: Schema CGG.
Note:
- entità ATTIVITA: l’attributo tipo indica il tipo di attività svolta (coltivazione o allevamento, supponendo che possa essere o l’una o l’altra,
non entrambe);
2
- relazione SU: l’attributo diritto indica la modalità con cui viene
svolta l’attività sulla particella (proprietà, affitto, ...);
3
Progettazione logica
Il primo passo della progettazione concettuale consiste nella ristrutturazione
dello schema concettuale che, a partire dallo schema rappresentato in Figura
1, considererà i seguenti punti:
1. attributo multivalore telefono dell’entità AZIENDA: si introduce un’entità TELEFONO che viene legata all’entità AZIENDA mediante una
relazione di tipo uno a molti;
2. attributo composto indirizzo dell’entità PARTICELLA: l’attributo composto mappale viene eliminato e sono mantenuti i singoli attributi
semplici che lo compongono;
3. attributo composto indirizzo della relazione SEDELEGALE: l’attributo composto indirizzo viene eliminato e sono mantenuti i singoli
attributi semplici che lo compongono;
4. specializzazione dell’entità ATTIVITÀ: supponendo che le attività (particelle su cui si svolge l’attività) vengano accedute per tipo di attività
si decide di eliminare le entità padre e mantenere solo le figlie della
specializzazione. Questa ristrutturazione porta a duplicare le relazioni
su e svolge con relativi attributi. L’attributo tipo dell’entità attività
perde poi di significato e può essere eliminato.
Note: si perdono alcuni vincoli 1) il fatto che ogni azienda deve avere almeno un’attività sia essa di coltivazione o di allevamento 2) ogni
particella ha associato un’unica attività (coltivazione o allevamento,
non entrambe);
5. specializzazione dell’entità COLTIVAZIONE: supponendo che si sia
interessati alle colture coltivate sulle diverse particelle, indipendentemente dal tipo di coltivazione (rotazione o fuori rotazione) si decide di
eliminare le entità figlie della specializzazione.
Note: si perde il vincolo secondo cui ogni attività di coltivazione deve
aver associato almeno una coltura;
6. accorpamento di relazioni: le due relazioni coltiva (nate dalla ristrutturazione della specializzazione dell’entità COLTIVAZIONE) sono stae
accorpate in un’unica relazione inglobando i due attributi che a questo
punto diventano opzionali.
Note: si riesce cosı̀ a vincolare ogni coltivazione ad essere associata
ad almeno una coltura ma bisogna tener presente che almeno uno due
3
attributi (non entrambi) deve essere popolato. La popolazione di tali
attributi deve essere anche uniforme rispetto al tipo di coltivazione
definito.
Lo schema CGG ristrutturato risulta quindi essere quello rappresentato
in Figura 2.
Figura 2: Schema CGG ristrutturato.
Si può quindi procedere con traduzione vera e propria dello schema CGG
ristrutturato sul modello logico relazionale esteso per il trattamento dei dati spaziali (gli attributi con nome the geom avranno infatti associato un
dominio di tipo spaziale).
1. Procedendo con ordine, andiamo prima a tradurre tutte le entità:
ST OCCAGGIO(codice, descrizione)
descrizione NOT NULL
ST ABU LAZION E(codice, descrizione)
descrizione NOT NULL
4
AN IM ALE(codice, specie)
specie NOT NULL
COLT U RA(codice, nome)
nome NOT NULL
IRRIGAZION E(codice, descrizione)
descrizione NOT NULL
P ROV IN CIA(codiceistat, nome, the geom)
nome NOT NULL
COM U N E(codiceistat, provincia, nome, the geom)
nome NOT NULL
P ART ICELLA(provincia, comune, sezione, f oglio, numero,
superf icie, the geom)
superficie NOT NULL
AZIEN DA(CU AA, denominazione, dataf ondazione, the geom)
denominazione NOT NULL
P ROP RIET ARIO(codicef iscale, nome, cognome, datanascita)
nome, cognome, datanascita NOT NULL
T ELEF ON O(azienda, numero)
T IP OALLEV AM EN T O(stabulazione, animale)
ALLEV AM EN T O(provincia, comune, sezione, f oglio, particella)
COLT IV AZION E(provincia, comune, sezione, f oglio, particella)
2. Quindi, consideriamo le relazioni di tipo uno a molti che apporteranno
delle modifiche ad alcune delle precedenti tabelle:
relazione sede legale:
AZIEN DA(CU AA, denominazione, dataf ondazione, the geom,
provincia, comune, via, civico, cap)
denominazione, provincia, comune, via, civico, cap NOT NULL
5
fk (provincia,comune) references COMUNE(provincia,codiceistat)
relazione ha:
T ELEF ON O(azienda, numero)
azienda NOT NULL
fk (azienda) references AZIENDA(CUAA)
relazioni svolgeA e suA
ALLEV AM EN T O(provincia, comune, sezione, f oglio, particella,
azienda, diritto)
azienda, diritto NOT NULL
fk (azienda) references AZIENDA(CUAA)
fk (provincia,comune,sezione,foglio,particella) references PARTICELLA(provincia,comune,sezione,foglio,numero)
relazione svolgeC e suC
COLT IV AZION E(provincia, comune, sezione, f oglio, particella,
azienda, diritto)
azienda, diritto NOT NULL
fk (azienda) references AZIENDA(CUAA)
fk (provincia,comune,sezione,foglio,particella) references PARTICELLA(provincia,comune,sezione,foglio,numero)
relazione irriga
COLT IV AZION E(provincia, comune, sezione, f oglio, particella,
azienda, diritto, irriga)
azienda, diritto, irriga NOT NULL
fk (azienda) references AZIENDA(CUAA)
fk (provincia,comune,sezione,foglio,particella) references PARTICELLA(provincia,comune,sezione,foglio,numero)
fk (irriga) references IRRIGAZIONE(codice)
3. Infine, introduciamo le tabelle necessarie per la traduzione delle relazioni molti a molti:
relazione proprieta:
P ROP RIET A(azienda, proprietario)
fk (azienda) references AZIENDA(CUAA)
fk (proprietario) references PROPRIETARIO(codicefiscale)
relazione dispone:
DISP ON E(provincia, comune, sezione, f oglio, particella, stoccaggio,
6
Figura 3: Schema logico relazionale.
volume)
volume NOT NULL
fk (provincia,comune,sezione,foglio,particella) references ALLEVAMENTO(provincia,comune,sezione,foglio,particella)
fk (stoccaggio) references STOCCAGGIO(codice)
relazione alleva:
ALLEV A(provincia, comune, sezione, f oglio, particella, stabulazione,
animale, quantita)
quiantita NOT NULL
fk (provincia,comune,sezione,foglio,particella) references ALLEVAMENTO(provincia,comune,sezione,foglio,particella)
fk (stabulazione, animale) references TIPOALLEVAMENTO(stabulazione,
animale)
relazione coltiva:
COLT IV A(provincia, comune, sezione, f oglio, particella, coltura,
ordine, percentuale)
check (ordine IS NOT NULL or percentuale IS NOT NULL)
check (ordine IS NULL or percentuale IS NULL)
fk (provincia,comune,sezione,foglio,particella) references COLTIVAZIONE(provincia,comune,sezione,foglio,particella)
fk (coltura) references COLTURA(codice)
7
Lo schema logico relazionale finale risulterà essere quindi quello rappresentato in Figura 3.
4
4.1
Linguaggio SQL
Creazione della base di dati
CREATE TABLE STOCCAGGIO (
codice
CHAR( 2 )
PRIMARY KEY
, d e s c r i z i o n e VARCHAR( 3 0 ) NOT NULL UNIQUE
);
CREATE TABLE STABULAZIONE (
codice
CHAR( 2 )
PRIMARY KEY
, d e s c r i z i o n e VARCHAR( 3 0 ) NOT NULL UNIQUE
);
CREATE TABLE ANIMALE (
codice
CHAR( 2 )
PRIMARY KEY
, s p e c i e VARCHAR( 3 0 ) NOT NULL UNIQUE
);
CREATE TABLE COLTURA (
codice
CHAR( 2 )
PRIMARY KEY
, nome
VARCHAR( 3 0 ) NOT NULL UNIQUE
);
CREATE TABLE IRRIGAZIONE (
codice
CHAR( 2 )
PRIMARY KEY
, d e s c r i z i o n e VARCHAR( 3 0 ) NOT NULL UNIQUE
);
CREATE TABLE PROVINCIA (
c o d i c e i s t a t CHAR( 3 )
PRIMARY KEY
, nome
VARCHAR( 3 0 ) NOT NULL
);
SELECT AddGeometryColumn ( ’ ’ , ’ p r o v i n c i a ’ , ’ the geom ’ , 4 3 2 6 , ’MULTIPOLYGON
’ ,2) ;
CREATE INDEX i d x g i s t p r o v i n c i a ON p r o v i n c i a USING g i s t ( the geom ) ;
CREATE TABLE COMUNE (
p r o v i n c i a CHAR( 3 ) REFERENCES PROVINCIA( c o d i c e i s t a t ) ON UPDATE
CASCADE
, c o d i c e i s t a t CHAR( 3 )
, nome
VARCHAR( 3 0 ) NOT NULL
, PRIMARY KEY ( p r o v i n c i a , c o d i c e i s t a t )
);
SELECT AddGeometryColumn ( ’ ’ , ’ comune ’ , ’ the geom ’ , 4 3 2 6 , ’MULTIPOLYGON ’
,2) ;
CREATE INDEX i d x g i s t c o m u n e ON comune USING g i s t ( the geom ) ;
CREATE TABLE PARTICELLA (
p r o v i n c i a CHAR( 3 )
, comune
CHAR( 3 )
, sezione
CHAR( 1 )
, foglio
INTEGER
, numero
INTEGER
, s u p e r f i c i e NUMERIC( 1 0 , 2 ) NOT NULL
, PRIMARY KEY ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , numero )
8
, FOREIGN KEY ( p r o v i n c i a , comune ) REFERENCES COMUNE( p r o v i n c i a ,
c o d i c e i s t a t ) ON UPDATE CASCADE
);
SELECT AddGeometryColumn ( ’ ’ , ’ p a r t i c e l l a ’ , ’ the geom ’ , 4 3 2 6 , ’POLYGON ’ , 2 )
;
CREATE INDEX i d x g i s t p a r t i c e l l a ON p a r t i c e l l a USING g i s t ( the geom )
;
CREATE TABLE AZIENDA (
CUAA
VARCHAR( 1 6 ) PRIMARY KEY
, d e n o m i n a z i o n e VARCHAR( 3 0 ) NOT NULL
,
d a t a f o n d a z i o n e DATE
, provincia
CHAR( 3 )
NOT NULL
, comune
CHAR( 3 )
NOT NULL
, via
VARCHAR( 3 0 ) NOT NULL
, civico
VARCHAR( 5 ) NOT NULL
, cap
NUMERIC( 5 , 0 ) NOT NULL
,
FOREIGN KEY ( p r o v i n c i a , comune ) REFERENCES COMUNE( p r o v i n c i a ,
c o d i c e i s t a t ) ON UPDATE CASCADE
);
SELECT AddGeometryColumn ( ’ ’ , ’ a z i e n d a ’ , ’ the geom ’ , 4 3 2 6 , ’POINT ’ , 2 ) ;
CREATE INDEX i d x g i s t a z i e n d a ON a z i e n d a USING g i s t ( the geom ) ;
CREATE TABLE PROPRIETARIO (
c o d i c e f i s c a l e CHAR( 1 6 ) PRIMARY KEY
,
nome
VARCHAR( 3 0 ) NOT NULL
,
cognome
VARCHAR( 3 0 ) NOT NULL
,
d a t a n a s c i t a DATE
NOT NULL
);
CREATE TABLE TELEFONO (
azienda
VARCHAR( 1 6 ) REFERENCES AZIENDA(CUAA)
ON DELETE CASCADE
, numero
VARCHAR( 1 0 )
, PRIMARY KEY ( a z i e n d a , numero )
);
ON UPDATE CASCADE
CREATE TABLE TIPOALLEVAMENTO (
s t a b u l a z i o n e CHAR( 2 ) REFERENCES STABULAZIONE( c o d i c e ) ON UPDATE
CASCADE
,
animale
CHAR( 2 ) REFERENCES ANIMALE( c o d i c e ) ON UPDATE CASCADE
, PRIMARY KEY ( s t a b u l a z i o n e , a n i m a l e )
);
CREATE TABLE ALLEVAMENTO (
p r o v i n c i a CHAR( 3 )
, comune
CHAR( 3 )
, sezione
CHAR( 1 )
, foglio
INTEGER
, p a r t i c e l l a INTEGER
, PRIMARY KEY ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a )
, azienda
VARCHAR( 1 6 ) REFERENCES AZIENDA(CUAA)
,
FOREIGN KEY ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a )
REFERENCES PARTICELLA( p r o v i n c i a , comune , s e z i o n e , f o g l i o , numero )
ON UPDATE CASCADE
);
CREATE TABLE COLTIVAZIONE (
p r o v i n c i a CHAR( 3 )
, comune
CHAR( 3 )
, sezione
CHAR( 1 )
, foglio
INTEGER
9
, p a r t i c e l l a INTEGER
, PRIMARY KEY ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a )
,
azienda
VARCHAR( 1 6 ) REFERENCES AZIENDA(CUAA) ON UPDATE
CASCADE
,
FOREIGN KEY ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a )
REFERENCES PARTICELLA( p r o v i n c i a , comune , s e z i o n e , f o g l i o , numero )
,
i r r i g a CHAR( 2 ) REFERENCES IRRIGAZIONE( c o d i c e ) ON UPDATE
CASCADE
);
CREATE TABLE PROPRIETA (
azienda
VARCHAR( 1 6 ) REFERENCES AZIENDA(CUAA) ON UPDATE CASCADE
,
p r o p r i e t a r i o CHAR( 1 6 )
REFERENCES PROPRIETARIO( c o d i c e f i s c a l e ) ON
UPDATE CASCADE
, PRIMARY KEY ( a z i e n d a , p r o p r i e t a r i o )
);
CREATE TABLE DISPONE (
p r o v i n c i a CHAR( 3 )
, comune
CHAR( 3 )
, sezione
CHAR( 1 )
, foglio
INTEGER
, p a r t i c e l l a INTEGER
,
FOREIGN KEY ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a )
REFERENCES PARTICELLA( p r o v i n c i a , comune , s e z i o n e , f o g l i o , numero )
ON UPDATE CASCADE
, s t o c c a g g i o CHAR( 2 )
REFERENCES STOCCAGGIO( c o d i c e ) ON UPDATE
CASCADE
,
volume
NUMERIC( 5 , 2 ) NOT NULL
, PRIMARY KEY ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
stoccaggio )
);
CREATE TABLE ALLEVA (
p r o v i n c i a CHAR( 3 )
, comune
CHAR( 3 )
, sezione
CHAR( 1 )
, foglio
INTEGER
, p a r t i c e l l a INTEGER
,
FOREIGN KEY ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a )
REFERENCES PARTICELLA( p r o v i n c i a , comune , s e z i o n e , f o g l i o , numero )
ON UPDATE CASCADE
, s t a b u l a z i o n e CHAR( 2 )
,
animale
CHAR( 2 )
,
FOREIGN KEY ( s t a b u l a z i o n e , a n i m a l e ) REFERENCES TIPOALLEVAMENTO(
s t a b u l a z i o n e , a n i m a l e ) ON UPDATE CASCADE
,
quantita
INTEGER NOT NULL
, PRIMARY KEY ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
stabulazione , animale )
);
CREATE TABLE COLTIVA (
provincia
CHAR( 3 )
, comune
CHAR( 3 )
, sezione
CHAR( 1 )
, foglio
INTEGER
, particella
INTEGER
,
FOREIGN KEY ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a )
REFERENCES PARTICELLA( p r o v i n c i a , comune , s e z i o n e , f o g l i o , numero )
ON UPDATE CASCADE
,
coltura
CHAR( 2 )
REFERENCES COLTURA( c o d i c e ) ON UPDATE
CASCADE
10
Figura 4: Interfaccia per l’importazione di file shape
,
,
,
percentuale
NUMERIC( 3 )
ordine
NUMERIC( 1 )
PRIMARY KEY ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
coltura )
, CHECK ( o r d i n e I S NOT NULL o r p e r c e n t u a l e I S NOT NULL)
, CHECK ( o r d i n e I S NULL o r p e r c e n t u a l e I S NULL)
);
4.2
Popolamento della base di dati
Si suppone di aver caricato lo shape file dei comuni in una tabella ausiliaria
COMUNI geoportalenazionale prima di procedere al caricamento delle geometrie relative alle province e ai comuni attraverso la funzionalità messa a
disposizione da PostGIS (Figura 4).
INSERT INTO STOCCAGGIO
COPERTA ’ ) ;
INSERT INTO STOCCAGGIO
SOLIDI ’ ) ;
INSERT INTO STOCCAGGIO
SCOPERTA ’ ) ;
INSERT INTO STOCCAGGIO
COPERTA ’ ) ;
( c o d i c e , d e s c r i z i o n e ) VALUES ( ’PC ’ , ’PLATEA
( c o d i c e , d e s c r i z i o n e ) VALUES ( ’PP ’ , ’PLATEA PER
( c o d i c e , d e s c r i z i o n e ) VALUES ( ’ PS ’ , ’PLATEA
( c o d i c e , d e s c r i z i o n e ) VALUES ( ’VC ’ , ’VASCA
INSERT INTO STABULAZIONE ( c o d i c e , d e s c r i z i o n e ) VALUES ( ’BL ’ , ’BOX SU
LATTIERA ’ ) ;
11
INSERT INTO STABULAZIONE ( c o d i c e , d e s c r i z i o n e ) VALUES ( ’ RI ’ , ’RECINTI
INDIVIDUALI ’ ) ;
INSERT INTO STABULAZIONE ( c o d i c e , d e s c r i z i o n e ) VALUES ( ’TE ’ , ’A TERRA ’
);
INSERT INTO ANIMALE ( c o d i c e , s p e c i e ) VALUES ( ’BO ’ , ’BOVINIO ’ ) ;
INSERT INTO ANIMALE ( c o d i c e , s p e c i e ) VALUES ( ’EQ ’ , ’ EQUINI ’ ) ;
INSERT INTO ANIMALE ( c o d i c e , s p e c i e ) VALUES ( ’SU ’ , ’SUINO ’ ) ;
INSERT
);
INSERT
);
INSERT
);
INSERT
);
INTO TIPOALLEVAMENTO ( s t a b u l a z i o n e , a n i m a l e ) VALUES ( ’ RI ’ , ’BO ’
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO TIPOALLEVAMENTO ( s t a b u l a z i o n e , a n i m a l e ) VALUES ( ’ RI ’ , ’EQ ’
INTO TIPOALLEVAMENTO ( s t a b u l a z i o n e , a n i m a l e ) VALUES ( ’TE ’ , ’BO ’
INTO TIPOALLEVAMENTO ( s t a b u l a z i o n e , a n i m a l e ) VALUES ( ’TE ’ , ’SU ’
COLTURA
COLTURA
COLTURA
COLTURA
COLTURA
( codice
( codice
( codice
( codice
( codice
, nome )
, nome )
, nome )
, nome )
, nome )
VALUES
VALUES
VALUES
VALUES
VALUES
(
(
(
(
(
’AV ’
’FA ’
’MA’
’ PI ’
’SO ’
,
,
,
,
,
’AVENA ’ ) ;
’FAGIOLO ’ ) ;
’MAIS ’ ) ;
’ PISELLO ’ ) ;
’ SOIA ’ ) ;
INSERT INTO IRRIGAZIONE ( c o d i c e , d e s c r i z i o n e ) VALUES ( ’NS ’ , ’NESSUNA ’ )
;
INSERT INTO IRRIGAZIONE ( c o d i c e , d e s c r i z i o n e ) VALUES ( ’PG ’ , ’PIOGGIA ’ )
;
INSERT INTO IRRIGAZIONE ( c o d i c e , d e s c r i z i o n e ) VALUES ( ’SC ’ , ’
SCORRIMENTO ’ ) ;
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
PROVINCIA
PROVINCIA
PROVINCIA
PROVINCIA
( codiceistat
( codiceistat
( codiceistat
( codiceistat
, nome )
, nome )
, nome )
, nome )
VALUES
VALUES
VALUES
VALUES
(
(
(
(
’ 030
’ 031
’ 032
’ 093
’
’
’
’
,
,
,
,
’UDINE ’ ) ;
’GORIZIA ’ ) ;
’TRIESTE ’ ) ;
’PORDENONE ’ ) ;
UPDATE PROVINCIA a s P
SET the geom = (SELECT ST Multi ( s t u n i o n ( the geom ) )
FROM ” COMUNI geoportalenazionale ” a s GP
WHERE GP. c o d p r o v i n=P . c o d i c e i s t a t ) ;
INSERT INTO COMUNE ( p r o v i n c i a , c o d i c e i s t a t , nome ) VALUES ( ’ 030 ’ , ’ 068 ’ ,
’PAGNACCO’ ) ;
INSERT INTO COMUNE ( p r o v i n c i a , c o d i c e i s t a t , nome ) VALUES ( ’ 030 ’ , ’ 118 ’ ,
’TAVAGNACCO’ ) ;
INSERT INTO COMUNE ( p r o v i n c i a , c o d i c e i s t a t , nome ) VALUES ( ’ 030 ’ , ’ 129 ’ ,
’UDINE ’ ) ;
UPDATE COMUNE a s C
SET the geom = (SELECT the geom
FROM ” COMUNI geoportalenazionale ” a s GP
WHERE GP. c o d p r o v i n=C . p r o v i n c i a and GP. cod comune=C .
codiceistat ) ;
INSERT INTO AZIENDA (CUAA, denominazione , d a t a f o n d a z i o n e , p r o v i n c i a ,
comune , v i a , c i v i c o , cap ) VALUES ( ’ 00111333555 ’ , ’TENUTA UDINESE ’ ,
NULL, ’ 030 ’ , ’ 068 ’ , ’VIA UDINE ’ , ’ 45/A ’ , 3 3 0 3 0 ) ;
INSERT INTO AZIENDA (CUAA, denominazione , p r o v i n c i a , comune , v i a , c i v i c o ,
cap ) VALUES ( ’ 00123456789 ’ , ’AZIENDA AGRICOLA UDINESE ’ , ’ 030 ’ , ’ 129 ’
, ’VIA ROMA’ , ’ 11/3 ’ , 3 3 1 0 0 ) ;
INSERT INTO AZIENDA (CUAA, denominazione , p r o v i n c i a , comune , v i a , c i v i c o ,
cap , d a t a f o n d a z i o n e ) VALUES ( ’ 00987654321 ’ , ’COLTIVAZIONI
12
BIOLOGICHE ’ , ’ 030 ’ , ’ 129 ’ , ’VIA DELLE SCIENZE ’ , ’ 100 ’ , 3 3 1 0 0 , ’
2000−01−01 ’ ) ;
UPDATE AZIENDA SET the geom=ST PointFromText ( ’POINT( 1 3 . 1 8 0 0 4 6 . 1 2 0 0 ) ’
, 4 3 2 6 ) WHERE CUAA= ’ 00111333555 ’ ; −− PAGNACCO
UPDATE AZIENDA SET the geom=ST PointFromText ( ’POINT( 1 3 . 2 4 0 0 4 6 . 0 6 0 0 ) ’
, 4 3 2 6 ) WHERE CUAA= ’ 00123456789 ’ ; −− UDINE
UPDATE AZIENDA SET the geom=ST PointFromText ( ’POINT( 1 3 . 2 5 7 4 4 6 . 0 8 3 2 ) ’
, 4 3 2 6 ) WHERE CUAA= ’ 00987654321 ’ ; −− UDINE
INSERT INTO TELEFONO ( a z i e n d a , numero ) VALUES ( ’ 00111333555 ’ , ’
0432767676 ’ ) ;
INSERT INTO TELEFONO ( a z i e n d a , numero ) VALUES ( ’ 00111333555 ’ , ’
3476767677 ’ ) ;
INSERT INTO TELEFONO ( a z i e n d a , numero ) VALUES ( ’ 00123456789 ’ , ’
0432666666 ’ ) ;
INSERT INTO
VALUES (
INSERT INTO
VALUES (
INSERT INTO
VALUES (
INSERT INTO
VALUES (
PROPRIETARIO ( c o d i c e f i s c a l e , nome , cognome , d a t a n a s c i t a )
’BNCMRA85Y66T656A ’ , ’MARIA ’ , ’BIANCHI ’ , ’ 1985−05−05 ’ ) ;
PROPRIETARIO ( c o d i c e f i s c a l e , nome , cognome , d a t a n a s c i t a )
’ RSSANT80X88R765X ’ , ’ANTONIO ’ , ’ ROSSI ’ , ’ 1980−10−05 ’ ) ;
PROPRIETARIO ( c o d i c e f i s c a l e , nome , cognome , d a t a n a s c i t a )
’RSSMRA85X56T789M ’ , ’MARIO ’ , ’ ROSSI ’ , ’ 1985−10−10 ’ ) ;
PROPRIETARIO ( c o d i c e f i s c a l e , nome , cognome , d a t a n a s c i t a )
’VRDMRA84X58T789M ’ , ’MARIO ’ , ’VERDI ’ , ’ 1984−10−10 ’ ) ;
INSERT INTO PROPRIETA
RSSANT80X88R765X ’ ) ;
INSERT INTO PROPRIETA
BNCMRA85Y66T656A ’ ) ;
INSERT INTO PROPRIETA
RSSMRA85X56T789M ’ ) ;
INSERT INTO PROPRIETA
RSSMRA85X56T789M ’ ) ;
INSERT INTO PROPRIETA
VRDMRA84X58T789M ’ ) ;
( a z i e n d a , p r o p r i e t a r i o ) VALUES ( ’ 00111333555 ’ , ’
( a z i e n d a , p r o p r i e t a r i o ) VALUES ( ’ 00123456789 ’ , ’
( a z i e n d a , p r o p r i e t a r i o ) VALUES ( ’ 00123456789 ’ , ’
( a z i e n d a , p r o p r i e t a r i o ) VALUES ( ’ 00987654321 ’ , ’
( a z i e n d a , p r o p r i e t a r i o ) VALUES ( ’ 00987654321 ’ , ’
INSERT INTO PARTICELLA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , numero ,
s u p e r f i c i e ) VALUES ( ’ 030 ’ , ’ 118 ’ , ’ ’ , ’ 10 ’ , ’ 1 ’ , 9 0 0 0 ) ;
INSERT INTO PARTICELLA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , numero ,
s u p e r f i c i e ) VALUES ( ’ 030 ’ , ’ 118 ’ , ’ ’ , ’ 10 ’ , ’ 2 ’ , 7 0 0 0 ) ;
INSERT INTO PARTICELLA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , numero ,
s u p e r f i c i e ) VALUES ( ’ 030 ’ , ’ 118 ’ , ’ ’ , ’ 11 ’ , ’ 1 ’ , 6 0 0 0 ) ;
INSERT INTO PARTICELLA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , numero ,
s u p e r f i c i e ) VALUES ( ’ 030 ’ , ’ 068 ’ , ’ ’ , ’ 12 ’ , ’ 1 ’ , 4 0 0 0 ) ;
INSERT INTO PARTICELLA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , numero ,
s u p e r f i c i e ) VALUES ( ’ 030 ’ , ’ 068 ’ , ’ ’ , ’ 12 ’ , ’ 2 ’ , 6 5 0 0 ) ;
INSERT INTO PARTICELLA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , numero ,
s u p e r f i c i e ) VALUES ( ’ 030 ’ , ’ 129 ’ , ’ ’ , ’ 55 ’ , ’ 1 ’ , 7 5 0 0 ) ;
INSERT INTO PARTICELLA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , numero ,
s u p e r f i c i e ) VALUES ( ’ 030 ’ , ’ 129 ’ , ’ ’ , ’ 55 ’ , ’ 3 ’ , 6 0 0 0 ) ;
UPDATE PARTICELLA
SET the geom=ST PolygonFromText ( ’POLYGON( ( 1 3 . 2 3 2 9 4 6 . 1 1 6 1 , 1 3 . 2 3 4 1
46.1154 ,13.2351 46.1161 ,13.2364 46.1184 ,13.2329 46.1161) ) ’ ,4326)
WHERE p r o v i n c i a= ’ 030 ’ AND comune= ’ 118 ’ AND s e z i o n e= ’ ’ AND f o g l i o= ’
10 ’ AND numero= ’ 1 ’ ; −− t a v a g n a c c o
UPDATE PARTICELLA
SET the geom=ST PolygonFromText ( ’POLYGON( ( 1 3 . 2 3 1 8 4 6 . 1 1 6 7 , 1 3 . 2 3 2 9
46.1161 ,13.2364 46.1184 ,13.2352 46.1191 ,13.2318 46.1167) ) ’ ,4326)
WHERE p r o v i n c i a= ’ 030 ’ AND comune= ’ 118 ’ AND s e z i o n e= ’ ’ AND f o g l i o= ’
10 ’ AND numero= ’ 2 ’ ; −− t a v a g n a c c o
UPDATE PARTICELLA
SET the geom=ST PolygonFromText ( ’POLYGON( ( 1 3 . 2 4 1 8 4 6 . 1 0 6 7 , 1 3 . 2 4 2 9
13
46.1061 ,13.2464 46.1084 ,13.2452 46.1091 ,13.2418 46.1067) ) ’ ,4326)
WHERE p r o v i n c i a= ’ 030 ’ AND comune= ’ 118 ’ AND s e z i o n e= ’ ’ AND f o g l i o= ’
11 ’ and numero= ’ 1 ’ ; −− t a v a g n a c c o
UPDATE PARTICELLA
SET the geom=ST PolygonFromText ( ’POLYGON( ( 1 3 . 1 6 5 8 4 6 . 1 2 7 5 , 1 3 . 1 6 5 8
46.1267 , 13.1675 46.1267 , 13.1675 46.1275 , 13.1658 46.1275) ) ’
,4326)
WHERE p r o v i n c i a= ’ 030 ’ AND comune= ’ 068 ’ AND s e z i o n e= ’ ’ AND f o g l i o= ’
12 ’ and numero= ’ 1 ’ ; −− p a g n a c c o
UPDATE PARTICELLA
SET the geom=ST PolygonFromText ( ’POLYGON( ( 1 3 . 1 6 5 8 4 6 . 1 2 6 7 , 1 3 . 1 6 5 8
46.1255 , 13.1675 46.1255 , 13.1675 46.1267 , 13.1658 46.1267) ) ’
,4326)
WHERE p r o v i n c i a= ’ 030 ’ AND comune= ’ 068 ’ AND s e z i o n e= ’ ’ AND f o g l i o= ’
12 ’ and numero= ’ 2 ’ ; −− p a g n a c c o
UPDATE PARTICELLA
SET the geom=ST PolygonFromText ( ’POLYGON( ( 1 3 . 2 6 2 6 4 6 . 1 1 0 0 , 1 3 . 2 6 2 6
46.1090 , 13.2690 46.1090 , 13.2690 46.1100 , 13.2626 46.1100) ) ’
,4326)
WHERE p r o v i n c i a= ’ 030 ’ AND comune= ’ 129 ’ AND s e z i o n e= ’ ’ AND f o g l i o= ’
55 ’ and numero= ’ 1 ’ ; −− u d i n e
UPDATE PARTICELLA
SET the geom=ST PolygonFromText ( ’POLYGON( ( 1 3 . 2 6 2 6 4 6 . 1 1 0 0 , 1 3 . 2 6 6 0
46.1100 , 13.2660 46.1115 , 13.2626 46.1115 , 13.2626 46.1100) ) ’
,4326)
WHERE p r o v i n c i a= ’ 030 ’ AND comune= ’ 129 ’ AND s e z i o n e= ’ ’ AND f o g l i o= ’
55 ’ AND numero= ’ 3 ’ ; −− u d i n e
INSERT INTO PARTICELLA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , numero ,
s u p e r f i c i e , the geom )
VALUES ( ’ 030 ’ , ’ 129 ’ , ’ ’ , ’ 10 ’ , ’ 1 ’ , 9 0 0 0 , ST PolygonFromText ( ’POLYGON
((13.2374 46.0260 , 13.2400 46.0260 , 13.2400 46.0280 , 13.2374
4 6 . 0 2 8 0 , 1 3 . 2 3 7 4 4 6 . 0 2 6 0 ) ) ’ , 4 3 2 6 ) ) ; −− u d i n e
−− d a t i a l l e v a m e n t i
INSERT INTO ALLEVAMENTO ( p r o v i n c i a
a z i e n d a ) VALUES ( ’ 030 ’ , ’ 118 ’ , ’
INSERT INTO ALLEVAMENTO ( p r o v i n c i a
a z i e n d a ) VALUES ( ’ 030 ’ , ’ 068 ’ , ’
INSERT INTO ALLEVAMENTO ( p r o v i n c i a
a z i e n d a ) VALUES ( ’ 030 ’ , ’ 129 ’ , ’
INSERT INTO ALLEVAMENTO ( p r o v i n c i a
a z i e n d a ) VALUES ( ’ 030 ’ , ’ 129 ’ , ’
, comune , s e z i o n e , f o g l i o , p a r t i c e l l a
’ , ’ 10 ’ , ’ 1 ’ , ’ 00111333555 ’ ) ;
, comune , s e z i o n e , f o g l i o , p a r t i c e l l a
’ , ’ 12 ’ , ’ 1 ’ , ’ 00111333555 ’ ) ;
, comune , s e z i o n e , f o g l i o , p a r t i c e l l a
’ , ’ 55 ’ , ’ 1 ’ , ’ 00111333555 ’ ) ;
, comune , s e z i o n e , f o g l i o , p a r t i c e l l a
’ , ’ 55 ’ , ’ 3 ’ , ’ 00123456789 ’ ) ;
,
,
,
,
INSERT INTO DISPONE ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
s t o c c a g g i o , volume ) VALUES ( ’ 030 ’ , ’ 118 ’ , ’ ’ , ’ 10 ’ , ’ 1 ’ , ’PC ’ , 5 0 0 ) ;
INSERT INTO DISPONE ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
s t o c c a g g i o , volume ) VALUES ( ’ 030 ’ , ’ 118 ’ , ’ ’ , ’ 10 ’ , ’ 1 ’ , ’PP ’ , 2 0 0 ) ;
INSERT INTO DISPONE ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
s t o c c a g g i o , volume ) VALUES ( ’ 030 ’ , ’ 129 ’ , ’ ’ , ’ 55 ’ , ’ 1 ’ , ’PC ’ , 3 0 0 ) ;
INSERT INTO DISPONE ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
s t o c c a g g i o , volume ) VALUES ( ’ 030 ’ , ’ 129 ’ , ’ ’ , ’ 55 ’ , ’ 3 ’ , ’VC ’ , 6 0 0 ) ;
INSERT INTO ALLEVA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
s t a b u l a z i o n e , animale , q u a n t i t a ) VALUES ( ’ 030 ’ , ’ 118 ’ , ’ ’ , ’ 10 ’ , ’ 1 ’ , ’
TE ’ , ’BO ’ , 1 0 0 0 ) ;
INSERT INTO ALLEVA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
s t a b u l a z i o n e , animale , q u a n t i t a ) VALUES ( ’ 030 ’ , ’ 118 ’ , ’ ’ , ’ 10 ’ , ’ 1 ’ , ’
TE ’ , ’SU ’ , 8 0 0 ) ;
INSERT INTO ALLEVA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
s t a b u l a z i o n e , animale , q u a n t i t a ) VALUES ( ’ 030 ’ , ’ 068 ’ , ’ ’ , ’ 12 ’ , ’ 1 ’ , ’
TE ’ , ’BO ’ , 2 0 0 ) ;
14
Figura 5: Rappresentazione dei dati spaziali inseriti (comuni, particelle e
aziende)
INSERT INTO ALLEVA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
s t a b u l a z i o n e , animale , q u a n t i t a ) VALUES ( ’ 030 ’ , ’ 068 ’ , ’ ’ , ’ 12 ’ , ’ 1 ’ , ’
RI ’ , ’BO ’ , 8 0 0 ) ;
INSERT INTO ALLEVA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
s t a b u l a z i o n e , animale , q u a n t i t a ) VALUES ( ’ 030 ’ , ’ 129 ’ , ’ ’ , ’ 55 ’ , ’ 1 ’ , ’
RI ’ , ’EQ ’ , 1 5 0 0 ) ;
INSERT INTO ALLEVA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
s t a b u l a z i o n e , animale , q u a n t i t a ) VALUES ( ’ 030 ’ , ’ 129 ’ , ’ ’ , ’ 55 ’ , ’ 3 ’ , ’
RI ’ , ’BO ’ , 1 8 0 0 ) ;
−− d a t i
coltivazioni
INSERT INTO COLTIVAZIONE ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
a z i e n d a , i r r i g a ) VALUES ( ’ 030 ’ , ’ 118 ’ , ’ ’ , ’ 10 ’ , ’ 2 ’ , ’ 00987654321 ’ , ’
NS ’ ) ;
INSERT INTO COLTIVAZIONE ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
a z i e n d a , i r r i g a ) VALUES ( ’ 030 ’ , ’ 118 ’ , ’ ’ , ’ 11 ’ , ’ 1 ’ , ’ 00987654321 ’ , ’
PG ’ ) ;
INSERT INTO COLTIVAZIONE ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
a z i e n d a , i r r i g a ) VALUES ( ’ 030 ’ , ’ 068 ’ , ’ ’ , ’ 12 ’ , ’ 2 ’ , ’ 00123456789 ’ , ’
PG ’ ) ;
INSERT INTO COLTIVA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
p e r c e n t u a l e , o r d i n e , c o l t u r a ) VALUES ( ’ 030 ’ , ’ 118 ’ , ’ ’ , ’ 10 ’ , ’ 2 ’ ,NULL
, 1 , ’MA’ ) ;
INSERT INTO COLTIVA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
c o l t u r a , o r d i n e ) VALUES ( ’ 030 ’ , ’ 118 ’ , ’ ’ , ’ 10 ’ , ’ 2 ’ , ’SO ’ , 2 ) ;
INSERT INTO COLTIVA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
c o l t u r a , p e r c e n t u a l e ) VALUES ( ’ 030 ’ , ’ 118 ’ , ’ ’ , ’ 11 ’ , ’ 1 ’ , ’SO ’ , 5 0 ) ;
INSERT INTO COLTIVA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
c o l t u r a , p e r c e n t u a l e ) VALUES ( ’ 030 ’ , ’ 118 ’ , ’ ’ , ’ 11 ’ , ’ 1 ’ , ’FA ’ , 5 0 ) ;
INSERT INTO COLTIVA ( p r o v i n c i a , comune , s e z i o n e , f o g l i o , p a r t i c e l l a ,
c o l t u r a , p e r c e n t u a l e ) VALUES ( ’ 030 ’ , ’ 068 ’ , ’ ’ , ’ 12 ’ , ’ 2 ’ , ’MA’ , 1 0 0 ) ;
I dati spaziali cosı̀ inseriti sono rappresentati in Figura 5).
15
4.3
Interrogazione dei dati
Determinare il codice e il nome di tutte le province:
SELECT c o d i c e i s t a t , nome
FROM PROVINCIA ;
Determinare i nomi (distinti) dei proprietari:
SELECT DISTINCT nome
FROM PROPRIETARIO ;
Determinare la denomione delle aziende di cui non si conosce la data di
fondazione:
SELECT d e n o m i n a z i o n e
FROM AZIENDA
WHERE a n n o f o n d a z i o n e I S NULL;
Determinare il nome dei comuni associati ai nomi delle corrispondenti
province:
SELECT C . nome a s comune , P . nome a s p r o v i n c i a
FROM COMUNE a s C, PROVINCIA a s P
WHERE p r o v i n c i a = P . c o d i c e i s t a t ;
SELECT C . nome a s comune , P . nome a s p r o v i n c i a
FROM COMUNE a s C JOIN PROVINCIA a s P ON p r o v i n c i a = P . c o d i c e i s t a t ;
Determinare per ogni azienda cuaa, denominazione, comune (nome), via,
civico e cap.
SELECT cuaa , denominazione , nome , v i a , c i v i c o , cap
FROM AZIENDA, COMUNE
WHERE AZIENDA . p r o v i n c i a=COMUNE. p r o v i n c i a AND comune=c o d i c e i s t a t ;
SELECT cuaa , denominazione , nome , v i a , c i v i c o , cap
FROM AZIENDA JOIN COMUNE ON AZIENDA . p r o v i n c i a=COMUNE. p r o v i n c i a AND
comune=c o d i c e i s t a t ;
Determinare per ogni azienda del comune di Udine cuaa, denominazione,
via, civico e cap.
SELECT cuaa , denominazione , v i a , c i v i c o , cap
FROM AZIENDA, COMUNE
WHERE AZIENDA . p r o v i n c i a=COMUNE. p r o v i n c i a AND comune=c o d i c e i s t a t AND
nome= ’UDINE ’ ;
SELECT cuaa , denominazione , v i a , c i v i c o , cap
FROM AZIENDA JOIN COMUNE ON AZIENDA . p r o v i n c i a=COMUNE. p r o v i n c i a AND
comune=c o d i c e i s t a t
AND nome= ’UDINE ’ ;
Determinare per ogni azienda cuaa, denominazione, comune (nome), via,
civico e cap.
16
SELECT cuaa , denominazione , PROVINCIA . nome , COMUNE. nome v i a , c i v i c o ,
cap
FROM AZIENDA, COMUNE, PROVINCIA
WHERE AZIENDA . p r o v i n c i a=COMUNE. p r o v i n c i a AND comune=COMUNE.
codiceistat
AND PROVINCIA . c o d i c e i s t a t=COMUNE. p r o v i n c i a ;
SELECT cuaa , denominazione , nome , v i a , c i v i c o , cap
FROM AZIENDA JOIN COMUNE ON AZIENDA . p r o v i n c i a=COMUNE. p r o v i n c i a AND
comune=c o d i c e i s t a t
JOIN PROVINCIA ON PROVINCIA . c o d i c e i s t a t=
COMUNE. p r o v i n c i a ;
Determinare il nome dei proprietari omonimi (stesso nome):
SELECT DISTINCT nome
FROM PROPRIETARIO AS P1 , PROPRIETARIO AS P2
WHERE P1 . nome=P2 . nome AND P1 . c o d i c e f i s c a l e <P2 . c o d i c e f i s c a l e ;
SELECT nome
FROM
PROPRIETARIO
GROUP BY nome
HAVING COUNT( ∗ ) >1;
Determinare l’insieme delle colture (nome) coltivate in rotazione:
SELECT DISTINCT nome
FROM
COLTIVA JOIN COLTURA ON c o l t u r a=c o d i c e
WHERE
o r d i n e I S NOT NULL;
Determinare le colture coltivate sia in rotazione che fuori rotazione:
SELECT DISTINCT nome
FROM
COLTIVA JOIN COLTURA ON c o l t u r a=c o d i c e
WHERE
o r d i n e I S NOT NULL
INTERSECT
SELECT DISTINCT nome
FROM
COLTIVA JOIN COLTURA ON c o l t u r a=c o d i c e
WHER
p e r c e n t u a l e I S NOT NULL;
Determinare le colture coltivate solo in rotazione, non fuori rotazione:
SELECT DISTINCT nome
FROM
COLTIVA JOIN COLTURA ON c o l t u r a=c o d i c e
WHERE
o r d i n e I S NOT NULL
EXCEPT
SELECT DISTINCT nome
FROM
COLTIVA JOIN COLTURA ON c o l t u r a=c o d i c e
WHER
p e r c e n t u a l e I S NOT NULL;
Determinare la denominazione delle aziende che allevano bovini e hanno
dispongono di vasche scoperte con un volume superiore di 200.
SELECT A. d e n o m i n a z i o n e
FROM AZIENDA AS A
JOIN ALLEVAMENTO AS AL
JOIN ALLEVA AS AE
ON A. cuaa=AL . a z i e n d a
ON AL . p r o v i n c i a=AE. p r o v i n c i a
AND AL . comune=AE.
comune
17
AND AL . s e z i o n e=AE. s e z i o n e
AND AL . f o g l i o=AE. f o g l i o
AND AL . p a r t i c e l l a=AE. p a r t i c e l l a
JOIN TIPOALLEVAMENTO AS TA ON AE. s t a b u l a z i o n e=TA. s t a b u l a z i o n e
AND AE. a n i m a l e=TA. a n i m a l e
JOIN ANIMALE AS AN
ON TA. a n i m a l e=AN. c o d i c e
JOIN DISPONE AS DI
ON AL . p r o v i n c i a=DI . p r o v i n c i a
AND AL . comune=DI .
comune
AND AL . s e z i o n e=DI . s e z i o n e
AND AL . f o g l i o=DI . f o g l i o
AND AL . p a r t i c e l l a=DI . p a r t i c e l l a
JOIN STOCCAGGIO AS ST
ON DI . s t o c c a g g i o=ST . c o d i c e
WHERE ST . d e s c r i z i o n e= ’VASCA COPERTA ’ AND DI . volume >200
AND AN. s p e c i e= ’BOVINO ’ ;
SELECT A. d e n o m i n a z i o n e
FROM AZIENDA AS A
JOIN ALLEVAMENTO AS AL
JOIN ALLEVA AS AE
ON A. cuaa=AL . a z i e n d a
ON AL . p r o v i n c i a=AE. p r o v i n c i a
AND AL . comune=AE.
comune
AND AL . s e z i o n e=AE. s e z i o n e
AND AL . f o g l i o=AE. f o g l i o
AND AL . p a r t i c e l l a=AE. p a r t i c e l l a
JOIN TIPOALLEVAMENTO AS TA ON AE. s t a b u l a z i o n e=TA. s t a b u l a z i o n e
AND AE. a n i m a l e=TA. a n i m a l e
JOIN ANIMALE AS AN
ON TA. a n i m a l e=AN. c o d i c e
WHERE AN. s p e c i e= ’BOVINO ’
INTERSECT
SELECT A. d e n o m i n a z i o n e
FROM AZIENDA AS A
JOIN ALLEVAMENTO AS AL
JOIN DISPONE AS DI
ON A. cuaa=AL . a z i e n d a
ON AL . p r o v i n c i a=DI . p r o v i n c i a
AND AL . comune=DI .
comune
AND AL . s e z i o n e=DI . s e z i o n e
AND AL . f o g l i o=DI . f o g l i o
AND AL . p a r t i c e l l a=DI . p a r t i c e l l a
JOIN STOCCAGGIO AS ST
ON DI . s t o c c a g g i o=ST . c o d i c e
WHERE ST . d e s c r i z i o n e= ’VASCA COPERTA ’ AND DI . volume >200;
SELECT A. d e n o m i n a z i o n e
FROM AZIENDA AS A
WHERE cuaa IN ( SELECT a z i e n d a
FROM ALLEVAMENTO AS AL
JOIN ALLEVA AS AE
ON AL . p r o v i n c i a=AE. p r o v i n c i a
AND AL . comune=AE.
comune
AND AL . s e z i o n e=AE. s e z i o n e
AND AL . f o g l i o=AE. f o g l i o
AND AL . p a r t i c e l l a=AE. p a r t i c e l l a
JOIN TIPOALLEVAMENTO AS TA ON AE. s t a b u l a z i o n e=TA. s t a b u l a z i o n e
AND AE. a n i m a l e=TA. a n i m a l e
JOIN ANIMALE AS AN
ON TA. a n i m a l e=AN. c o d i c e
WHERE AN. s p e c i e= ’BOVINO ’ )
AND cuaa IN (SELECT a z i e n d a
FROM ALLEVAMENTO AS AL
JOIN DISPONE AS DI
ON AL . p r o v i n c i a=DI . p r o v i n c i a
18
AND AL . comune=DI .
comune
AND AL . s e z i o n e=DI . s e z i o n e
AND AL . f o g l i o=DI . f o g l i o
AND AL . p a r t i c e l l a=DI . p a r t i c e l l a
JOIN STOCCAGGIO AS ST
ON DI . s t o c c a g g i o=ST . c o d i c e
WHERE ST . d e s c r i z i o n e= ’VASCA COPERTA ’ AND DI . volume >200) ;
SELECT A. d e n o m i n a z i o n e
FROM AZIENDA AS A
WHERE EXISTS ( SELECT ∗
FROM ALLEVAMENTO AS AL
JOIN ALLEVA AS AE
ON AL . p r o v i n c i a=AE. p r o v i n c i a
AND AL . comune=AE.
comune
AND AL . s e z i o n e=AE. s e z i o n e
AND AL . f o g l i o=AE. f o g l i o
AND AL . p a r t i c e l l a=AE. p a r t i c e l l a
JOIN TIPOALLEVAMENTO AS TA ON AE. s t a b u l a z i o n e=TA. s t a b u l a z i o n e
AND AE. a n i m a l e=TA. a n i m a l e
JOIN ANIMALE AS AN
ON TA. a n i m a l e=AN. c o d i c e
WHERE AN. s p e c i e= ’BOVINIO ’ AND A. cuaa=AL . a z i e n d a )
AND EXISTS (SELECT ∗
FROM ALLEVAMENTO AS AL
JOIN DISPONE AS DI
ON AL . p r o v i n c i a=DI . p r o v i n c i a
AND AL . comune=DI .
comune
AND AL . s e z i o n e=DI . s e z i o n e
AND AL . f o g l i o=DI . f o g l i o
AND AL . p a r t i c e l l a=DI . p a r t i c e l l a
JOIN STOCCAGGIO AS ST
ON DI . s t o c c a g g i o=ST . c o d i c e
WHERE ST . d e s c r i z i o n e= ’VASCA COPERTA ’ AND DI . volume >200 AND A. cuaa
=AL . a z i e n d a ) ;
Determinare il numero di particelle coltivate per azienda∗ :
SELECT a z i e n d a , COUNT( ∗ )
FROM
COLTIVAZIONE
GROUP BY a z i e n d a ;
Determinare il numero di animali complessivi allevati per azienda∗ :
SELECT a z i e n d a , SUM( q u a n t i t a )
FROM
ALLEVAMENTO a s A1 JOIN ALLEVA a s A2
ON A1 . p r o v i n c i a=A2 . p r o v i n c i a AND A1 . comune=A2 . comune AND A1 . s e z i o n e
=A2 . s e z i o n e AND A1 . f o g l i o=A2 . f o g l i o AND A1 . p a r t i c e l l a=A2 .
particella
GROUP BY a z i e n d a ;
Determinare l’area delle diverse particelle, e confrontarle con le superfici
precedentemente inserite:
SELECT comune , s e z i o n e , f o g l i o , p a r t i c e l l a , s t a r e a ( ST Transform (
the geom , 3 0 0 4 ) ) , s u p e r f i c i e
FROM PARTICELLA ;
Aggiornare quindi le superfici con i dati corretti:
UPDATE PARTICELLA
SET s u p e r f i c i e = s t a r e a ( ST Transform ( the geom , 3 0 0 4 ) ) ;
19
Determinare l’area complessiva delle particelle coltivate (a partire dalle
geometrie):
SELECT SUM( s t a r e a ( ST Transform ( the geom , 3 0 0 4 ) ) )
FROM PARTICELLA a s P JOIN ALLEVAMENTO AS A
ON P . p r o v i n c i a=A. p r o v i n c i a AND P . comune=A. comune AND P . s e z i o n e=A.
s e z i o n e AND P . f o g l i o=A. f o g l i o AND P . numero=A. p a r t i c e l l a ;
SELECT s t a r e a (ST UNION( ST Transform ( the geom , 3 0 0 4 ) ) )
FROM PARTICELLA a s P JOIN ALLEVAMENTO AS A
ON P . p r o v i n c i a=A. p r o v i n c i a AND P . comune=A. comune AND P . s e z i o n e=A.
s e z i o n e AND P . f o g l i o=A. f o g l i o AND P . numero=A. p a r t i c e l l a ;
Determinare per ogni comune il numero di aziende che hanno sede in
esso:
[ c a s o con i s o l i comuni che hanno a z i e n d e ]
SELECT C . p r o v i n c i a | | C . c o d i c e i s t a t a s c o d i c e , C . nome AS comune ,
COUNT( ∗ ) a s numeroSedi
FROM COMUNE AS C JOIN AZIENDA AS A ON C . p r o v i n c i a=A. p r o v i n c i a AND C
. c o d i c e i s t a t=A. comune
GROUP BY C . p r o v i n c i a , C . c o d i c e i s t a t , C . nome ;
[ c a s o con t u t t i i comuni ]
SELECT C . p r o v i n c i a | | C . c o d i c e i s t a t a s c o d i c e , C . nome AS comune ,
COUNT(A.CUAA) a s numeroSedi
FROM COMUNE AS C LEFT OUTER JOIN AZIENDA AS A ON C . p r o v i n c i a=A.
p r o v i n c i a AND C . c o d i c e i s t a t=A. comune
GROUP BY C . p r o v i n c i a , C . c o d i c e i s t a t , C . nome ;
Determinare la distanza fra le diverse aziende (sedi):
SELECT s 1 . cuaa , s 1 . denominazione , s 2 . cuaa , s 2 . denominazione ,
S T D i s t a n c e ( ST Transform ( s 1 . the geom , 3 0 0 4 ) , ST Transform ( s 2 .
the geom , 3 0 0 4 ) ) AS d i s t a n z a
FROM a z i e n d a a s s1 , a z i e n d a a s s 2
WHERE s 1 . cuaa<s 2 . cuaa ;
Determinare per ogni comune le particelle in esso coltivate (sfruttando
le geometrie):
SELECT c . nome , p . f o g l i o , p . numero
FROM comune a s c ,
p a r t i c e l l a a s p JOIN c o l t i v a z i o n e a s co ON p . p r o v i n c i a=co . p r o v i n c i a
AND p . comune=co . comune AND p . f o g l i o=co . f o g l i o AND p . s e z i o n e=co .
s e z i o n e AND p . numero=co . p a r t i c e l l a
WHERE s t c o n t a i n s ( c . the geom , p . the geom ) ;
Determinare per ogni comune il totale delle superfici delle particelle
coltivate (sfruttando le geometrie):
SELECT c . nome , SUM( s t a r e a ( s t t r a n s f o r m ( p . the geom , 3 0 0 4 ) ) )
areaColtivata
FROM comune a s c ,
p a r t i c e l l a a s p JOIN c o l t i v a z i o n e a s co ON p . p r o v i n c i a=co . p r o v i n c i a
AND p . comune=co . comune AND p . f o g l i o=co . f o g l i o AND p . s e z i o n e=co .
s e z i o n e AND p . numero=co . p a r t i c e l l a
WHERE s t c o n t a i n s ( c . the geom , p . the geom )
GROUP BY c . p r o v i n c i a , c . c o d i c e i s t a t , c . nome ;
20
Determinare il buffer di 1000 metri rispetto alle aziende e determinare
se le particelle su cui lavorano svolgono attività di allevamento sono in esso
incluse o meno:
SELECT a . denominazione , a l . p r o v i n c i a , a l . comune , a l . f o g l i o , a l .
p a r t i c e l l a , s t c o n t a i n s ( s t b u f f e r ( s t t r a n s f o r m ( a . the geom , 3 0 0 4 )
, 1 0 0 0 ) , s t t r a n s f o r m ( p . the geom , 3 0 0 4 ) ) a s i n c l u s a I n B u f f e r
FROM p a r t i c e l l a a s p JOIN a l l e v a m e n t o a s a l ON p . p r o v i n c i a=a l .
p r o v i n c i a AND p . comune=a l . comune AND p . f o g l i o=a l . f o g l i o AND p .
s e z i o n e=a l . s e z i o n e AND p . numero=a l . p a r t i c e l l a
JOIN a z i e n d a a ON a l . a z i e n d a=a . cuaa ;
Determinare per ogni azienda il numero di particelle su cui svolgono
coltivazione e il numero di quelle su cui svolgono allevamento:
SELECT a . cuaa , a . denominazione , COUNT( c . p a r t i c e l l a ) a s
n u m e r o P a r t i c e l l e C o l t i v a t e , COUNT( a l . p a r t i c e l l a ) a s
numeroParticelleAllevate
FROM a z i e n d a a s a LEFT OUTER JOIN c o l t i v a z i o n e a s c ON a . cuaa=c .
azienda
LEFT OUTER JOIN a l l e v a m e n t o a s a l ON a . cuaa=a l . a z i e n d a
GROUP BY a . cuaa , a . d e n o m i n a z i o n e ;
4.4
Trigger
In fase di progettazione sono stati indicati alcuni vincoli non gestibili attraverso il puro modello logico relazionale ma, come si era accennato, è
necessario l’inserimento di alcuni trigger. Si riportano di seguito alcuni
esempi.
Un primo trigger ha il compito di settare/modificare il valore dell’attributo area associata alla particella in base alla geometria della particella
stessa3 .
CREATE OR REPLACE FUNCTION f p a r t i c e l l a a r e a ( ) RETURNS t r i g g e r AS
$BODY$
BEGIN
new . s u p e r f i c i e = s t a r e a ( ST Transform ( new . the geom , 3 0 0 4 ) ) ;
RETURN new ;
END;
$BODY$ LANGUAGE ’ p l p g s q l ’ ;
CREATE TRIGGER t p a r t i c e l l a a r e a
BEFORE INSERT OR UPDATE ON p a r t i c e l l a
FOR EACH ROW EXECUTE PROCEDURE f p a r t i c e l l a a r e a ( ) ;
Per la verifica il precedente trigger è sufficiente riaggiornare le geometrie
associate alle particelle.
Sono necessari dei trigger per la gestione dei vincoli spaziali imposti dalla
relazione spaziale come ad esempio l’inclusione delle sedi delle aziende nei
comuni in cui stanno. Si riporta a scopo esemplificativo l’esempio relativo
3
Nota: tenere presente che nella realtà l’area da considerare è quella data dall’agenzia
del territorio.
21
al trigger che in fase di inserimento/modifica di un’azienda verifica che la
sua geometria sia contenuta nel rispettivo comune:
CREATE OR REPLACE FUNCTION a z i e n d a i n c o m u n e ( ) RETURNS t r i g g e r AS
$BODY$
DECLARE
num i n t e g e r ;
BEGIN
SELECT COUNT( ∗ ) INTO num
FROM COMUNE AS C
WHERE new . p r o v i n c i a=C . p r o v i n c i a
AND new . comune=C . c o d i c e i s t a t
AND s t c o n t a i n s (C . the geom , new . the geom ) ;
IF num=0 THEN
RAISE EXCEPTION ’ V i n c o l o s p a z i a l e non s o d d i s f a t t o ( s e d e non
i n c l u s a n e l comune ) ’ ;
ELSE
RETURN new ;
END IF ;
END;
$BODY$ LANGUAGE ’ p l p g s q l ’ ;
CREATE TRIGGER t a z i e n d a i n c o m u n e
BEFORE INSERT OR UPDATE ON a z i e n d a
FOR EACH ROW EXECUTE PROCEDURE a z i e n d a i n c o m u n e ( ) ;
Per verificare il precedente trigger è sufficiente eseguire i seguenti aggiornamenti. Nel primo caso i vincoli spaziali non sono soddisfatti ed il trigger
blocca l’aggiornamento, nel secondo caso l’inserimento viene eseguito vista
la soddisfazione del vincolo:
INSERT INTO AZIENDA (CUAA, denominazione , d a t a f o n d a z i o n e , p r o v i n c i a ,
comune , v i a , c i v i c o , cap , the geom ) VALUES ( ’ 00222333555 ’ , ’AZIENDA DI
PAGNACCO’ ,NULL, ’ 030 ’ , ’ 068 ’ , ’VIA ROMA’ , ’ 4/A ’ , 3 3 0 3 0 ,
ST PointFromText ( ’POINT( 1 3 . 2 4 0 0 4 6 . 0 6 0 0 ) ’ , 4 3 2 6 ) ) ; −− UDINE
INSERT INTO AZIENDA (CUAA, denominazione , d a t a f o n d a z i o n e , p r o v i n c i a ,
comune , v i a , c i v i c o , cap , the geom ) VALUES ( ’ 00222333555 ’ , ’AZIENDA DI
PAGNACCO’ ,NULL, ’ 030 ’ , ’ 068 ’ , ’VIA ROMA’ , ’ 4/A ’ , 3 3 0 3 0 ,
ST PointFromText ( ’POINT( 1 3 . 1 8 0 0 4 6 . 1 2 0 0 ) ’ , 4 3 2 6 ) ) ; −− PAGNACCO
4.5
Viste
Creare una vista che restituisce l’elenco delle aziende con tutte le informazioni ad essa associate, in particolare il nome del comune e della provincia
in cui si trovano:
c r e a t e v i e w AZIENDA INFO AS
SELECT A.CUAA a s CUAA, A. d e n o m i n a z i o n e AS denominazione , A.
d a t a f o n d a z i o n e , C . nome AS comune , P . nome a s p r o v i n c i a , A. the geom
a s the geom
FROM COMUNE AS C JOIN AZIENDA AS A ON C . p r o v i n c i a=A. p r o v i n c i a AND C
. c o d i c e i s t a t=A. comune
JOIN PROVINCIA AS P ON C . p r o v i n c i a=P . c o d i c e i s t a t ;
INSERT INTO g e o m e t r y c o l u m n s ( f t a b l e c a t a l o g , f t a b l e s c h e m a ,
f table name , f geometry column , coord dimension , s r i d , type )
VALUES ( ’ ’ , ’ p u b l i c ’ , ’ a z i e n d a i n f o ’ , ’ the geom ’ , 2 , 4 3 2 6 , ’POINT ’ ) ;
22
Creare una vista che associa ad ogni comune il numero di sedi di aziende
in esso contenute:
c r e a t e v i e w COMUNE SEDI AS
SELECT C . p r o v i n c i a | | C . c o d i c e i s t a t a s c o d i c e , C . nome AS comune , COUNT
(A.CUAA) a s numeroSedi , C . the geom a s the geom
FROM COMUNE AS C LEFT OUTER JOIN AZIENDA AS A ON C . p r o v i n c i a=A.
p r o v i n c i a AND C . c o d i c e i s t a t=A. comune
GROUP BY C . p r o v i n c i a , C . c o d i c e i s t a t , C . nome , C . the geom ;
INSERT INTO g e o m e t r y c o l u m n s ( f t a b l e c a t a l o g , f t a b l e s c h e m a ,
f table name , f geometry column , coord dimension , s r i d , type )
VALUES ( ’ ’ , ’ p u b l i c ’ , ’ c o m u n i s e d i ’ , ’ the geom ’ , 2 , 4 3 2 6 , ’
MULTIPOLYGON ’ ) ;
Nota: l’inserimento della riga nella tabella delle geometrie non è necessaria nella nuova versione di PostGIS in cui la tabella geometry columns è
diventata una vista.
Inoltre, per poter poi utilizzare la vista da vecchie versioni di qGIS è necessario avere un attributo numerico che possa essere utilizzato come identificatore. Bisogna quindi aggiungere un attributo quale:
• to number(A.CUAA,’99999999999’)::int as id nella prima vista e
• to number(C.provincia||C.codiceistat,’999999’)::int as id
per la seconda.
5
Interfacciamento con i GIS
Molti sistemi GIS utilizzano come sorgente dati le basi di dati spaziali e
permettono quindi la visualizzazione dei dati in esse contenuti, oltre che
tutta una serie di operazioni.
Sfruttando il sistema QGIS si possono creare delle viste analoghe a quelle
rappresentate in Figura 6 in cui è stato visualizzato un layer vettoriali per
ogni tabella spaziale della nostra base di dati.
Sfruttando le viste precedentemente create si possono aggiungere ulteriori e più completi strati informativi:
23
Figura 6: Visualizzazione dei dati contenuti nelle tabelle spaziali da qGIS.
Figura 7: Visualizzazione delle viste da qGIS.
24