Sistemi Informativi
e Basi di Dati
Laurea Specialistica in Tecnologie di
Analisi degli Impatti Ecotossicologici
Docente: Francesco Geri
Dipartimento di Scienze Ambientali “G. Sarfatti”
Via P.A. Mattioli 4 – 53100 Siena
Tel. 0577235408
[email protected]
FareDATABASE
clic per modificare
lo stile del
GEOGRAFICI
sottotitolo dello schema
Sistemi Informativi e Basi di Dati – I Database
Introduzione
I GIS per definizione sono basati su database di tipo geografico.
La visione classica delle applicazioni GIS ci ha abituati ad
avere in un unico dato o gruppo di dati le informazioni
riguardanti la geometria e gli attributi degli oggetti (es.
shapefile di Arcview).
Può essere corretto quando la gestione è stand-alone (quindi
desktop-gis) ma quando un sistema prevede l'accesso multiutente (ad esempio da diverse aree geografiche
contemporaneamente), questo approccio è inutilizzabile.
Sistemi Informativi e Basi di Dati – I Database
Introduzione
Abbiamo visto che un DBMS come PostgreSQL o MySQL permette
accessi multipli contemporanei garantendo l'integrità e la
sicurezza dei dati.
Può gestire una grande quantità di tipi di dati (varchar, tect, float,
double precision ecc.) ma (per adesso) non può gestire dati
geografici (quindi non “capisce” i dati spaziali e i sistemi di
riferimento).
Sistemi Informativi e Basi di Dati – I Database
Introduzione
DATABASE SPATIALLY – ENABLED
●
POSTGIS (POSTGRESQL >7.0)
●
SQLITE (SPATIALITE)
●
MYSQL (WITH SPATIAL EXTENSION > 5.0)
●
ORACLE SPATIAL
Sistemi Informativi e Basi di Dati – I Database
Introduzione
Nel 2001 l'azienda canadese Refractions (www. refractions.net,
Udig, GeoTools) ebbe l'esigenza di fare uso di un DBMS con
estensioni geografiche.
Dopo un indagine di mercato decise di creare un software ex-novo
(PostGIS) utilizzando come base PostgreSQL. Questa scelta è
giustificata dal fatto che PSQL permette di:
Supportare un numero molto grande di tipi di dati
●
Gestire DB molto grandi su sistemi con capacità limitate
●
Avere un grande controllo sulla sicurezza dei dati
●
E' open-source.
●
Sistemi Informativi e Basi di Dati – I Database
Introduzione
Costruire un estensione geografica per un DBMS come PSQL
significa costruire una libreria che:
Definisca la struttura del dato geografico (geometria, coordinate,
●
sistema di riferimento).
Implementi le funzione native di PSQL con nuove funzioni come
●
capacità di calcolare l'area o il perimetro o ancora la distanza
tra punti proiettati.
Sistemi Informativi e Basi di Dati – I Database
Installazione
Per installare PostGIS su Ubuntu utilizziamo ancora una volta Synaptic:
sudo apt-cache search postgis
sudo apt-get install postgis postgresql-8.4-postgis
PostGIS adesso è installato ma per funzionare è necessario:
1)deve essere creato il linguaggio PL/pgSQL
2)deve essere caricato il file postgis.sql
3)deve essere caricata la tabelle dei sistemi di riferimento
Sistemi Informativi e Basi di Dati – I Database
Installazione
sudo updatedb #costruisco l'archivio del contenuto del pc
locate postgis.sql
#/usr/share/postgresql/8.4/contrib/postgis.sql
locate spatial_ref_sys.sql #/usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql
Linguaggio procedurale PL/pgSQL, file postgis.sql e spatial_ref_sys.sql possono
essere caricati ogni volta che si vuole creare un database postgis oppure è possibile
farlo una volta sola, su un database di template, in modo tale che poi si possano
creare database postgis semplicemente a partire da quel template
Sistemi Informativi e Basi di Dati – I Database
Installazione
Scegliamo la seconda ipotesi: creiamo un template GIS con cui poter creare i
database con estensione geografica.
Per fare questo dobbiamo creare un template GIS che chiameremo template_gis
createdb –template template0 template_gis
#significa crea un database chiamato template_gis sulla base del template0
che è il template standard di PSQL
createlang plpgsql template_gis
#si abilità il template_gis al linguaggio
plpgsql
psql -f /usr/share/postgresql/8.4/contrib/postgis.sql -d template_gis
#aggiunge le funzioni postgis al database
psql -f /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql -d template_gis
# aggiunge i sistemi di riferimento al database
Sistemi Informativi e Basi di Dati – I Database
Installazione
psql postgres
#si entra nel db postgres (quello che viene creato all'avvio e contiene i dati di
sistema di PSQL)
UPDATE pg_database SET datistemplate='t' WHERE datname ='template_gis';
#aggiorna la tabella pg_database (che contiene i nomi di tutti i database
esistenti, specificando che template_gis è un template
A questo punto PostGIS è installato e perfettamente funzionante all'interno di PSQL.
Abbiamo creato un template GIS chiamato template_gis che ci rervirà tutte le volte
che dovremmo creare un nuovo geo-database. Adesso usciamo da PSQL e creiamo
un nuovo geodatabase chiamato database_gis
\q
createdb --template template_gis database_gis
Sistemi Informativi e Basi di Dati – I Database
Installazione
psql database_gis
\dt
#lista delle tabelle
Vediamo il nostro geodatabase database_gis non è vuoto ma contiene 2 tabelle:
geometry_columns
●
spatial_ref_sys
●
Le 2 tabelle rappresentano la lista delle geometrie presenti nel database
database_gis (che adesso è vuota \d geometry_columns), e spatial_ref_sys che
invece è piena. Per visualizzare il contenuto di spatial_ref_sys attiviamo la modalità
di visualizzazione estesa.
\x
\d spatial_ref_sys
select * from spatial_ref_sys;
Sistemi Informativi e Basi di Dati – I Database
Installazione
select * from spatial_ref_sys where srid='32632';
SRID EPSG relativo al sistema di riferimento UTM Wgs84 fuso 32N
Per popolare un geodatabase è possibile procedere in 2 modi:
1)Importare dati dall'esterno attraverso software specifici
2)Utilizzare il linguaggio SQL per inserire dati secondo la sintassi Simple Feautures.
Cos'è la sintassi Simple features?
E' una sintassi specifica di PostGIS che permette l'archiviazione di 7 tipi diversi di
dato: POINT (punto), LINESTRING (linee), POLYGON (poligoni), MULYPOINT
(collezioni di punti), MULTILINESTRING (collezioni di linee), MULTIPOLYGON (),
GEOMETRY COLLECTIONS ().
Sistemi Informativi e Basi di Dati – I Database
Installazione
Vedremo la prima possibilità più avanti. Adesso vediamo come aggiungere dati in
una tabella di un geodatabase da linguaggio SQL.
Per prima cosa dobbiamo creare una tabella con la sintassi classica.
create table punti (id int4, descrizione varchar(40));
\dt
\d punti #controlliamo se tutto è a posto
Questa non è una tabella geografica. Per farla diventare una tabella geografica
dobbiamo aggiungere il campo relativo alla geometria di tipo point secondo questa
sintassi:
Sistemi Informativi e Basi di Dati – I Database
Installazione
select addgeometrycolumn('public','punti','pt',32632,'POINT',2);
dove la sintassi è:
select addgeometrycolumn #è una funziona e in SQL le funzioni si chiamano con il
comando SELECT
Nomeschema = nome dello schema dove è memorizzata la tabella
Nometabella= nome della tabella
Nomecolonna= nome del campo geometrico, in questo caso pt
Srid= sistema di riferimento, in questo caso 32632 (Wgs84 32N)
Tipogeometria= tipo di geometria in base alle Simple Features e quindi puntuale
dimensione= può essere 2 o 3, ovvero numero delle dimensioni dello spazio.
Controlliamo la tabella geometry_column
select * from geometry_columns;
Sistemi Informativi e Basi di Dati – I Database
Installazione
Adesso controlliamo la tabella appena creata.
\d punti
A questo punto la tabella è diventata “geometrica” e l'inserimento avviene secondo
le classiche modalità SQL. Quindi
insert into punti (id, descrizione, pt) values
(1,'origine',ST_GeomFromText('POINT(621115 4879123)',32632));
insert into punti (id, descrizione, pt) values
(2,'punto_1',ST_GeomFromText('POINT(621565 4879683)',32632));
insert into punti (id, descrizione, pt) values
(3,'punto_2',ST_GeomFromText('POINT(621890 4879001)',32632));
Sistemi Informativi e Basi di Dati – I Database
Installazione
Adesso controlliamo la tabella appena creata.
select * from punti;
I punti sono stati inseriti correttamente, sia per quanto riguarda l'attribuzione sia per
quanto riguarda l'informazione geometrica. Il passo successivo è importare questo
shape dentro un GIS e vedere cosa succede.
Prima proviamo a vedere se PgAdminIII vede correttamente i dati.
Apriamo QuantumGIS e connettiamoci al database.
Sistemi Informativi e Basi di Dati – I Database
PgAdmin III
Qgis ha la capacità di default di
connettersi a database spaziali di
PostgreSQL/PostGIS.
Cliccando su “Aggiongi vettore
PostGIS” si apre la finestra della
connessione. Cliccare su nuovo e
inserire un nome a piacere per la
connessione, localhost su nome
server, databasegis su Database,
datauser su Utente e cliccare su Test
connessione.
Se il test è positivo cliccare su Ok.
Cliccare su Connetti.
Sistemi Informativi e Basi di Dati – I Database
PgAdmin III
Cliccare su Aggiungi e nella tab della
Legenda dovrebbe comparire il layer
Punti e sullo schermo dovrebbero
comparire i punti visualizzati.
Sistemi Informativi e Basi di Dati – I Database
PgAdmin III
QGIS è connesso con il database “databasegis” e ha caricato il layer postgis “punti”.
Questo layer può essere esportato come shapefile oppure può essere modificato
direttamente dal GIS. Proviamo ad aggiungere 2 punti con id=4 e id=5 e
descrizione=prova. Salvare il risultato.
Tornare su SQL Shell e controllare la tabella
select * from punti;
Andare su PgAdminIII e rifare la query.
La tabella è stata aggiornata direttamente sul server e quindi ad ogni accesso
vedremo la tabella aggiornata.
delete from punti where id=2;
Provare a ricaricare il layer postgis.
Sistemi Informativi e Basi di Dati – I Database
PgAdmin III
Da QGIS è possibile popolare il database non solo inserendo nuovi record nella
tabella ma importando nuove tabelle da shapefile.
È possibile farlo attraverso un plugin di default di QGIS chiamato SPIT.
Sistemi Informativi e Basi di Dati – I Database
PgAdmin III
Provare ad aggiungere lo shapefile relativo ai SIC
della Provincia di Siena. Caricare lo shape su QGIS
e impostare il CRS 3003 (Monte Mario Zone1).
Aprire SPIT e selezionare lo shapefile.
È possibile scegliere lo schema, il SRID, e il nome
della colonna geometrica (default the_geom).
Lasciare tutto com'è e cliccare su aggiungi e
successivamente su ok.
Rimuovere lo shapefile e provare a ricaricare il layer
PostGIS.
Adesso sono presenti 2 tabelle: punti e sic_si_gb.
Sistemi Informativi e Basi di Dati – I Database
PgAdmin III
È possibile importare solo una
parte della tabella utilizzando lo
sturmento query builder in entrata.
È possibile utilizzare gli operatori
logici AND OR NOT per eseguire
la query e i gli operatori LIKE e
ILIKE.
LIKE accetta i caratteri speciali %:
1. %se tutto quello che finisce con
la stringa 'se'.
2. se% tutto quello che inizia con la
stringa 'se'
3. %se& tutto quello che contiene
la stringa 'se'
