Introduzione ai Database relazionali e ai GeoDatabase In collaborazione con Marco Ciolli Dipartimento di Ingegneria Civile ed Ambientale Università degli Studi di Trento I database Scopo di un database è raccogliere una consistente mole di dati in maniera sicura e ordinata. Un significativo antenato dei database relazionali odierni per caratteristiche e struttura è la vecchia agenda telefonica. Infatti è organizzata tramite un indice (la serie di linguette sul fianco che ci permette di accedere più rapidamente a tutti i nominativi che iniziano con una certa lettera) che gestisce una tabella composta da colonne che identificano il tipo di dato sotto riportato (nome, numero di telefono, a volte indirizzo). I dati registrati al suo interno, per quanto differiscano l'uno dall'altro hanno tutti la stessa “struttura", cioè riportano le stesse informazioni nella medesima maniera. Dall'agenda al database (1) Il primo tentativo per trasferire questo insieme di dati in un aggeggio gestibile dalle calcolatricitato il CSV (Comma Separated Value), file di testo tuttora utilizzato come file di scambio dove ogni informazione (numero di telefono di Gino, nome di Fausto, indirizzo di Franco) è separata dalle altre tramite un carattere (in genere una virgola) ed ogni record (cioè la riga della nostra agenda) è separato dagli altri tramite un altro carattere (in genere il carattere di "a capo"). Tale sistema era abbastanza scomodo, per trovare quanto si ricercava all'interno di un file così fatto un'informazione specifica era spesso necessario scorrerselo quasi tutto ed in modo poco pratico. Gino,Bartali,Via Panicale,565446<-| Fausto,Coppi,Via Panicale,<-| Franco,Bitossi,Via Tornabuoni,465453<-|. Dall'agenda al database (2) Dal CSV nacque l'ISAM (Indexed Sequential Access Method), che differiva dal CSV per il fatto che i record non erano messi dentro secondo l'ordine di inserimento e quindi potenzialmente a caso,bensì veniva definito un ordinamento (nel caso dell'agenda telefonica sarebbe l' ordine alfabetico dei cognomi). Gino,Bartali,Via Panicale,565446<-| Gino,Bartali,Via Panicale,565446<-| Fausto,Coppi,Via Panicale,<-| Franco,Bitossi,Via Tornabuoni,465453<-| Franco,Bitossi,Via Tornabuoni,465453<-| Fausto,Coppi,Via Panicale,<-| Tale ordinamento veniva sfruttato sia in scrittura, ma soprattutto in lettura così da abbreviare significativamente i tempi di ricerca. Per riuscire a gestire più velocemente la fase di ricerca si crearono anche degli indici, in cui veniva registrato solo l' ordine dei vari record senza tutte le altre informazioni, il che permetteva di andare a svolgere le proprie ricerche in questo "riassunto" in modo molto più veloce. Dall'agenda al database (3) Occorre a questo punto fare una precisazione... Molte di queste caratteristiche vengono ad oggi anche risolte utilizzando i cosiddetti fogli di calcolo o spreadsheet (excel, openoffice, libreoffice, google document etc.) Tuttavia un foglio di calcolo non è un identificabile come un database, e a differenza dei file csv non è nemmeno consigliato come file di scambio dati, anche se alcuni sistemi permettono un passaggio piuttosto agevole di dati da tabelle di un foglio di calcolo a database. (es. importare in Microsoft Access® una tabella redatta con Microsoft Excel®) Dall'agenda al database (4) Ridondanza dei dati: ma è possibile evitare di rimettere nel mio database della contabilità duemila volte l'indirizzo del mio cliente principale? Uniformità dei dati: con che nome ho inserito la Cozza Vongola e Molluschi SRL l'ultima volta? CVM? C.V.M. ? C.V.M. SRL.? C.V. & M. SRL?" Indipendenza dalla piattaforma: sul sistema di Pino per vedere il contenuto di una tabella faccio così su quello di Mino e Lino cosà, su quello di Addolorato e sul mio in un altro modo completamente diverso Sicurezza delle transazioni: DISASTRO! stavo mettendo dentro al database tutte le fatture dell'ultimo semestre quando è andata via la corrente ed adesso non so se l'ultima me l'ha presa o no! Ed ora cosa faccio, devo ricostruire l'intera tabella delle fatture per essere certo che non ci siano valori doppi o inseriti a metà? La possibilità di gestire correttamente un ambiente multiutente: sono certo di avere inserito l'ordine per le ultime tre scatole di Mandingo a nome del mio miglior cliente e quelle sono finite invece al peggior cliente di un altro commerciale? Per risolvere tutti questi problemi in maniera soddisfacente si è dovuto cambiare il modo di strutturare i database, portando così alla nascita dei database relazionali come sono strutturati oggi. I database relazionali (1) Un database è una raccolta di dati organizzata in modo tale che il suo contenuto possa essere facilmente consultato, gestito e aggiornato. Il tipo prevalente di database è quello relazionale, ossia un database tabellare in cui il dato è definito così da poter essere riorganizzato e consultato in molti modi differenti. Un DB può avere una struttura di organizzazione dei dati elementare o molto complicata, in funzione dell’ambito applicativo e della quantità e tipologia delle informazioni da memorizzare. E’ necessario introdurre alcuni concetti di base: – tabella – chiavi – record – query – campi – SQL – DBMS I database relazionali (2) tabella é una raccolta di dati organizzati in righe e colonne ogni tabella, in genere, rappresenta un raccolta di informazioni su uno specifico argomento o tematica ad esempio possiamo avere una tabella per gli studenti, una per i corsi e una per i docenti record (tupla) è una singola riga di una tabella ci consente di identificare un determinato insieme di dati, all’interno di tutti quelli che sono contenuti nella tabella campi sono le colonne che compongono la tabella e ne definiscono la struttura; si può specificare il tipo di ciascun campo (testo, numeri, interi e floating ecc.) e si può imporre una certa serie di regole p.e. che un campo non può essere vuoto o deve avere un massimo numero di caratteri I database relazionali (3) Riassumendo... Una tabella è composta da record omogenei Un record è composto dalle voci dei campi Tabella studenti record Tabella docenti campo Tabella corsi I database relazionali (4) chiave è l’insieme dei campi che permette di identificare in modo univoco ed inequivocabile un singolo record all’interno della tabella ogni tabella può avere una o più chiavi non si possono avere in tabella due record distinti con lo stesso valore del campo chiave non è possibile avere chiavi contenenti valore NULL una chiave può essere composta da uno o più campi è un elemento fondamentale per il collegamento con i GIS es. chiavi: - Matricola - ID - Codice - etc. valori NULL possibilità stesso numero telefono → NO chiave I database relazionali (5) query è un’interrogazione sul Database utile per per estrarre, riorganizzare, riclassificare i dati fornisce come risultato un insieme di dati che soddisfano le condizioni imposte in essa normalmente negli strumenti per la gestione dei DB si ha la possibilità di creare la query sia con apposite interfacce facilitate, sia scrivendola direttamente in un linguaggio apposito SQL è il linguaggio utilizzato per accedere e gestire i dati di un (R)DBMS, per creare e modificare la struttura del database e per gestire l'accesso agli oggetti contenuti. Sebbene SQL sia uno standard ANSI e ISO, molti database estendono supportano estensioni al linguaggio (dialetti). La prima versione dell'SQL fu sviluppata da IBM nei primi anni '70 da Donald D. Chamberlin e Raymond F. Boyce col nome di SEQUEL. IBM brevettò questa versione del linguaggio nel 1985 chiamandolo SQL, nel 1986 fu formalmente standardizzato dall'American Nationl Standards Institute. Le versioni successive (dal 1986) vennero rilascate come standard dall International Standardization Organization (ISO). Attualmente lo standard è SQL:2008 I database relazionali (6) Query & SQL Una query scritta in SQL può avere questa forma: Select-From-Where – SELECT: per indicare i campi richiesti (* per tutti) – FROM: per indicare su quali tabelle si deve effettuare la query – WHERE: per indicare i vincoli imposti Ad esempio se volessimo estrarre il nome ed il numero di telefono dello studente la cui matricola è 26741, potremmo scrivere una query in SQL: SELECT Nome, Cognome, Telefono FROM studenti WHERE Matricola = 26741 Tabella studenti I database relazionali (7) DBMS Software che controllano l'organizzazione, l'immagazzinamento, il caricamento, la sicurezza e l'integrità di un database. Accettano richieste dall'applicazione e istruiscono il sistema operativo per trasferire i dati appropriati DBMS (Data Base Management System) sono cioè i software in grado di gestire i DB consentendo - creare DB (strutture di tabelle, vincoli d’integrità di tupla o di dominio, …) - gestire dati (inserirli, effettuare query, definire trigger, …) - gestire accessi concorrenti, back up, roll back, … Alcuni DBMS: PostgreSQL, MySQL Oracle DBMS MicroSoft SQLServer MicroSoft Access / LibreOffice BASE PostgreSQL http://www.postgresql.org PostGIS Estensione spaziale per Postgresql: Postgresql immagazina e manipola oggetti, segue le specifiche OGC per l'SQL MySQL Oracle Microsoft SQL Server LibreOffice BASE Grass e i DB (1) La gestione dei DB può avvenire in GRASS seguendo differenti procedure a seconda delle versioni di GRASS utilizzate. In particolare i dati alfanumerici collegati ai dati geografici possono essere gestiti: Direttamente dal motore di GRASS: la gestione diretta dei dati da parte di GRASS non garantisce il rispetto delle principali funzioni dei DB (coerenza, ecc.) il formato dei file è il dbf Per mezzo di DataBase esterni interfacciati con GRASS in modo più o meno diretto: la gestione dei dati tramite Data Base esterno è più affidabile ed aumenta significativamente le capacità di analisi e trattamento dei dati (es. PostgreSQL – PostGIS, etc.) GRASS e i DB (2) In tabella sono riportati i DBMS che possono essere collegati in vario modo a GRASS, almeno nella versione attualmente utilizzata (6.4). Il set di comandi db.* fornisce un supporto di base SQL per la gestione degli attributi, mentre il set di comandi v.db.* opera sulle mappe vettoriali. Categorie & Layer Il numero di categoria è la chiave dell'elemento vettoriale (vector ID). E' usato per collegare la tabella/e degli attributi a ciascun oggetto vettoriale. E' possibile collegare gli oggetti geografici in una mappa vettoriale a una o più tabelle. Ciascun collegamento (link) a un tabella degli attributi diversa è chiamato layer. Un link definisce quale database driver, quale database e quale tabella deve essere usata. Un oggetto vettoriale può avere zero, una, due o più categorie. I numeri di Categoria sono immagazzinati entrambi dentro il file della geometria ed all'interno della tavola degli attributi per ciascun oggetto vettoriale. Ciascun numero di categoria in un file della geometria corrisponde ad una riga nella tabella degli attributi. Per fare in modo di collegare un oggetto vettoriale a parecchie tavole diverse, sono necessari diversi numeri di categoria per ciascun oggetto vettoriale. Usando v.db.connect i layers possono essere listati o mantenuti. Le tabelle associate ai vettori di default in GRASS sono in formato DBF e sono contenute nella cartella dbf → Grass-location folder Si tratta di tabelle molto elementari che possono essere aperte / create / modificate con LibreOffice Calc o con un semplice editor di testo Location Mapset dbf http://grass.fbk.eu/gdp/html_grass64/grass-dbf.html db.* Si tratta di comandi di interazione con le sole tabelle degli attibuti. Non coinvolgono la geometria. - comandi per connessione dei DB: db.connect - Permette la connessione ad un database tramite un interfaccia dbm db.test - Effettua un test del driver db per verificarne l'operatività db.drivers - Mostra la lista dei driver db disponibili db.login - Setta user e password di un certo driver db etc. etc. db.* - comandi per tabelle: db.columns - Permette di visualizzare le colonne di una data tabella contenuta in un database db.copy - Permette all'utente di copiare una tabella fra due database che possono anche essere connessi attraverso drivers differenti db.describe - Permette di visualizzare le informazioni inerenti una tabella. (utilizzando il flag -c si ottengono solo i nomi delle colonne invece della descrizione completa) db.tables – permette di ottenere una lista delle tabelle di attributi collegate in un dato mapset db.execute - Esegue delle stringhe SQL scritte direttamente oppure contenute in file di testo db.select - Stampa il risultato della selezione effettuata su un database a partire da una stringa SQL letta da un file di input oppure scritta nell'interfaccia db.dropcol - Cancella una colonna dalla tabella degli attributi scelta db.droptable – Cancella una tabella degli attributi db.in.ogr – importa una tabella degli attributi in vari formati OGC db.out.ogr - importa una tabella degli attributi in vari formati OGC v.db.* Si tratta di comandi di interazione con le tabelle degli attibuti associate alle geometrie vettoriali selezionate. v.db.addcol - Aggiunge una o più colonne a una tabella associata ad una mappa. v.db.addtable - Crea e aggiunge una nuova tabella degli attributi ad un layer dato di una mappa vettoriale esistente v.db.connect - Stampa o setta la connessione database per un determinato vettoriale v.db.dropcol - Elimina una colonna dalla tabella degli attributi connessa a una data mappa v.db.droptable - Rimuove la tabella degli attributi di una mappa vettoriale esistente v.db.join - Permette di fare operazioni di join (joining) fra una tabella ed una tabella di una mappa v.db.reconnect.all - Riconnette i file vettoriali a un nuovo database v.db.renamecol - Rinomina una colonna nella tabella degli attributi connessa a una data mappa v.db.select - Prints vector map attributes. v.db.univar - Calcola statistiche univariate sulla colonna selezionata di una tabella di una mappa v.db.update - Permette di assegnare un nuovo valore ad una colonna connessa ad una certa mappa v. in.ogr & v.in.db E' possibile ad esempio creare un generico file di punti con associate alcune informazioni, salvarlo come DBF, DBF importarlo in GRASS attraverso il comando v.in.ogr e infine creare la geometria vettoriale con v.in.db Visualizzazione di una tabella dati dal layer manager E' possibile visualizzare la tabella o le tabelle associate a una mappa vettoriale selezionata tra quelle caricate su un diaplay sul layer manager Table manager (1) Oltre a visualizzare la tabella è anche possibile realizzare delle query utilizzando il riquadro in basso. Table manager (2) Visualizzare dettagli circa le colonne di una data tabella, aggiungere / rimuovere / rinominare colonne Table manager (3) E infine gestire le connessioni alle diverse tabelle (layers) GRASS – PostgreSQL / PostGIS Come detto, l'utilizzo di DataBase esterni è più affidabile ed aumenta significativamente le capacità di analisi e trattamento dei dati. Uno dei DB più affidabile e facilmente interfacciabile con GRASS è sicuramente PostgreSQL associato alla sua estensione spaziale PostGIS. PostgreSQL / PostGIS possono essere gestiti da terminale, tuttavia esistono interfacce grafiche come PgAccess o ancor meglio PgAdmin III lato desktop o PhPPgAdmin per database caricati su server. PgAdmin III Creare un nuovo DB PgAdmin III Creare nuove tabelle PgAdmin III Con pgAdmin III ci si può connettere intuitivamente al database GRASS – PostgreSQL / PostGIS E' possibile esportare un vettoriale di GRASS in un database PostGIS/PostgreSQL utilizzando il comando v.out.ogr specificando opportunatamente i parametri di connessione al database (host nome del database - user password) e il formato di esportazione (PostgreSQL) v.out.ogr input=vettoriale_da_esportare dsn='PG:host=localhost dbname=nome_database user=nome_user password=password_scelta' format=PostgreSQL PhPPgAdmin Intuitiva interfaccia grafica per database PostgreSQL / PostGIS su server in remoto. Deve essere installato sul server e può essere lanciato da un browser web permettendo, previa connessione, la gestione in locale del DB. Questa presentazione è © 2012 Bianca Federici, Roberto Marzocchi, Domenico Sguerso, disponibile come http://www.creativecommons.it/Licenze