Database relazionali FOSS e GeoDatabasezatelli/cartografia_numerica/slides/... · 2016-09-08 ·...

Database relazionali FOSS e GeoDatabaseDatabase relazionali FOSS e GeoDatabase

dott. Marco Ciolli1

ing. Fabio Zottele2

1D.I.C.A. – Università degli Studi di Trento

2SIG – Fondazione Mach - FEM

GRASS, FREE ed OPEN SOURCE GIS e GEODATABASE

Trento, 20 – 24 giugno 2011

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) all'interno delle registrazioni

(se vogliamo chiamarle con il termine inglese le chiameremo "record") che , anche

se differiscono l'una dall'altra per i dati riportati al loro interno "hanno tutti la stessa

struttura", cioè riportano le stesse informazioni nella medesima maniera.

I data base: caratteristiche principali

Il primo tentativo per trasferire questo insieme di dati in un aggeggio gestibile dalle calcolatrici era

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<-|

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 (che nel caso dell'agenda telefonica sarebbe l' ordine

alfabetico dei cognomi)

Gino,Bartali,Via Panicale,565446<-|

Franco,Bitossi,Via Tornabuoni,465453<-|

Fausto,Coppi,Via Panicale,<-|

Tale ordinamento veniva sfruttato sia in scrittura ("dove scrivo il record del telefono di

Binda?" "lo inserisco tra il record di Bartali e Bitossi") sia in lettura ("dove vado a cercare il

numero di telefono di Coppi?" " lettera C, tra Cinelli e Cumini") così da abbreviare

significativamente i tempi di ricerca di una data informazione.

Per riuscire a gestire ancora meglio il tutto si crearono anche delle specie di "archivi

sussidiari", detti 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 (meno roba da leggere=ci metto di meno a leggerla)

e poi "puntare diritti" sul database completo per leggere tutto il record una volta che si

sapeva dov'era

Allora parecchi matematici si misero a cercare metodi per rendere più veloce l' accesso

alle informazioni sfornando sistemi di ricerca dai nomi fantasiosi come "ricerca

dicotomica" o " a farfalla" (di cui non ci occupiamo qui), sviluppando così tutti quei sistemi

oggi definiti "Database non relazionali" (in contrapposizione ai database relazionali)

con l'evoluzione dei DB relazionali i database ebbero una notevole diffusione e quindi iniziarono a nascere richieste di affidabilità e di prestazioni sempre maggiori, con uno sviluppo teorico

notevole dietro ad essi, che permetteva a questo punto di affrontare diversi problemi, di cui di seguito elenco quelli più conosciuti.

Ridondanza dei dati:ma è possibile evitare di rimettere nel mio database della contabilità duemila volte l'indirizzo del mio cliente principale?

Uniformità dei daticon 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 piattaformasul 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 transazioniDISASTRO! 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 multiutentesono 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? eppure Sono Sicuro di averle fermate al magazzino per primo.

Per risolvere tutti questi problemi in maniera soddisfacente si è dovuto cambiare il modo di "pensare" i database, portando così alla nascita dei database relazionali come sono strutturati

oggi

Un database è una raccolta di dati che è organizzata in modo tale che il suo contenuto possa essere facilmente consultato, gestito e aggiornato.

Il tipo prevalente di database è quello relazionale un database tabellare in cui il dato è definito così da poter essere riorganizzato e consultato in molti modi differenti. (Molti sostengono che non esista una definizione soddisfacente)

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

Degli esempi di data base sono un’agenda personale (nomi, calendario appuntamenti), un archivio universitario (studenti, matricole, esami, docenti, insegnamenti)

E’ necessario introdurre alcuni concetti di base:

– tabella

Cos’è un Database?

– record– campi –chiavi

– query– SQL

– DBMS

Marco Ciolli, Dipartimento di Ingegneria Civile e Ambientale

• 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– per esempio nella tabella Corsi degli studenti di ingegneria ci sarà il record relativo a “Pianificazione Ecologica” oppure “GIS e Cartografia Numerica”

CorsiCodice Nome Ore123E Ecologia 100125P Pianificazione ecologica 50127I GIS e cartografia numerica 100

Dunque un Database è costituito da una serie di tabelle differenti• Una tabella è composta da record omogenei• Un record è composto dalle voci dei campi

Database

Record

Tabella

Campi

DocentiCodiceProf Nome Cognome Indirizzo Telefono CodiceCorso

1012 Marco Bianchi Via Bella 365841 123E1025 Paolo Rossi Via Allegra 565862 125P1050 Alfonso Verdi Via Panoramica 965757 127I

StudentiMatricola Nome Cognome Indirizzo Telefono

1214 Gino Bartali Via Panicale 5654461300 Fausto Coppi Via Panicale

802 Franco Bitossi Via Tornabuoni 465453

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

• campi

• 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– esempi di chiave: Matricola per “Studenti”, Codice per “Corsi”, Codiceprof per “Docenti”– è un elemento fondamentale per il collegamento con i GIS

CorsiCodice Nome Ore123E Ecologia 100125P Pianificazione ecologica 50127I GIS e cartografia numerica 100

Non può essere una chiave

DocentiCodiceProf Nome Cognome Indirizzo Telefono CodiceCorso

1012 Marco Bianchi Via Bella 365841 123E1025 Paolo Rossi Via Allegra 565862 125P1050 Alfonso Verdi Via Panoramica 965757 127I

Può essere una chiave




è 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

• query

SQL (Structured Query Language) è un linguaggio per la formulazione di query• 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 è 1214, potremmo scrivere una query in SQL:SELECT Nome, Cognome, TelefonoFROM StudentiWHERE matricola = 1214

• Il risultato sarebbe:Gino Bartali 565446




• SQL


• 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 di– 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

• DBMS

Database Relazionali si chiamano così perché il loro elemento base è la:

relazione che è un sottoinsieme della relazione matematica tra due insiemi, detti domini della relazione, rappresentato da un insieme di tuple omogenee, fisicamente rappresentabile con una tabella costituita da righe e colonne.

Ci sono anche DB gerarchici, reticolari e NOSQL (2009) ma sono meno flessibili, più complessi e richiedono maggiori conoscenze anche sulla struttura dei dati da

parte dell’utenteMarco Ciolli, Dipartimento di Ingegneria Civile e Ambientale

Trento, venerdì 24 giugno 2011, CORSO GEODATABASE

Dal database al geodatabase

Fabio Zottele – SIG – Fondazione E. MachMarco Ciolli – DICA – Università degli Studi di Trento




Perchè inserire informazioni georiferite in un database?

Quando nasce l'esigenza di utilizzare il DataBaseManagementSystem con un'estensione geografica?

Che differenza c'è tra un GIS ed un GEODBMS?

È difficile utilizzare un GEODBMS?

Così, per iniziare...


I database e I DBMSs

Perchè inserire informazioni georiferite in un database?

L'informazione georiferita è un tipo di informazione complesso, ma può essere rappresentato partendo dalla definizione di un nuovo tipo: la geometria (Geometry)

Con questo approccio si utilizzeranno database di tipo relazionale ed a modello Object Oriented (ORDBMS). Ogni estensione spaziale dei DBMS sarà leggermente diversa...

DBMS Spatial Extension

SyBase ASE Boeing's SQS

SmallWorld SmallWorld VMDS

Sqlite SpatialLite

IBM DB2 spatial Extender

Oracle Oracle Spatial

Microsoft SQLServer Microsoft SQLServer

PostgreSQL PostGIS

MySQL MySQL (MySpatial)


I database e I DBMSs

E allora come scegliere?

1Fonte: Wikipedia 2Fonte:rivenditori, costi espressi in dollari, 2007

Per effettuare una scelta, la spesa non è il solo fattore da tenere in considerazione, ma è comunque importante ...



Tanto per complicare un pò le cose...

Dal database al geodatabaseDal database al geodatabase

Differentemente dai database relazionali, i database NoSQL gestiscono ed immagazzinano i dati attraverso grafi o array associativi. NoSQL viene usato quando si hanno piccole transazioni di lettura/scrittura o grandi transazioni di sola lettura.

Esempi di database NoSQL sono: il database di Facebook (50TB), di eBay (2PB): NoSQL è stato pensato per avere un'architettura distribuita quindi I dati sono estremamente ridondanti (in totale), ma polverizzati su un grande numero di server e sono alla base di moltissime applicazioni di Cloud Computing.

Esistono moltissimi DBMS NoSQL: BigTable di Google, MongoDb, Cassandra di Apache, Dynamo di Amazon, Project Voldemort di LinkedIn

DBMS NoSQL con estensione spaziale sono Neo4j, e AllegroGraph: quest'ultimo usa il Resource Description Framework (RDF) ed usa SPARQL (Sparql Protocol and RDF Query Language) diventando di fatto un GIS semantico

s


Ma cos'è l'estensione geografica...


Quando un database viene “spatially extended”, vengono creati dei tipi di dati nuovi (ad esempio il tipo geometry), vengono fornite le funzioni per operare su questi tipi di dati, vengono attivate delle procedure per verificare la consistenza di questi nuovi dati nel database, e, a volte, vengono forniti delle procedure che ottimizzino l'accesso alle informazioni registrate.

Ogni (R)DBMS implementerà differentemente gli oggetti di tipo spaziale. Per favorire l'interoperabilità, oggi quasi tutte le estensioni spaziali utilizzano delle definizioni comuni:Simple feature access (SFA; ISO 19125)

Il punto chiave di questo standard ISO è la rappresentazione delle entità vettoriali utilizzando un formato unico: il Well-known Text (WKT) sia per gli oggetti geografici, sia per il sistema di riferimento degli stessi.

Per migliorare l'esigenza di memorizzazione si utilizza anche il Well-known Binary (WKB).

POINT(0 0) è la descrizione WKT di un punto0101000020E61 è lo stesso punto in WKB



Che tipi di primitive sono previsti dall'ISO 19125?

Tipi base:

● POINT(0 0)● LINESTRING(0 0,1 1,1 2)● POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))● MULTIPOINT(0 0,1 2)● MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))● MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))● GEOMETRYCOLLECTION(POINT(2 3),LINESTRING((2 3,3 4)))

Inoltre, sono previsti I tipi TIN (Triangulated Irregular Network) e POLYHEDRALSURFACE,

Le coordinate possono essere 2D (x,y), 3D (x,y,z), 4D (x,y,z,m) con m che fa parte del linear referencing



E i sistemi di riferimento?

PARAM_MT["Mercator_2SP", PARAMETER["semi_major",6370997.0], PARAMETER["semi_minor",6370997.0], PARAMETER["central_meridian",180.0], PARAMETER["false_easting",-500000.0], PARAMETER["false_northing",-1000000.0], PARAMETER["standard parallel 1",60.0]]

PARAM_MT["Affine", PARAMETER["num_row",3], PARAMETER["num_col",3], PARAMETER["elt_0_1",1], PARAMETER["elt_0_2",2], PARAMETER["elt 1 2",3]]



...SQL-MM Part 3

CIRCULARSTRING(0 0, 1 1, 1 0) Simile alla LINESTRINGCOMPOUNDCURVE(CIRCULARSTRING(0 0, 1 1, 1 0),(1 0, 0 1))CURVEPOLYGON(CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 3

3, 3 1, 1 1))MULTICURVE((0 0, 5 5),CIRCULARSTRING(4 0, 4 4, 8 4))MULTISURFACE(CURVEPOLYGON(CIRCULARSTRING(0 0, 4 0, 4 4, 0 4,

0 0),(1 1, 3 3, 3 1, 1 1)),((10 10, 14 12, 11 10, 10 10),(11 11, 11.5 11, 11 11.5, 11 11)))

Il tipo COMPOUNDCURVE può contenere anche degli elementi LINESTRING.Il tipo MULTICURVE può essere un insieme di CURVE, LINESTRING, CIRCULARSTRING e COMPOUNDSTRING

SQL Multimedia Applicatio Spatial (ISO 13249-3:2006) estende le primitive descritte precedentemente con delle curve interpolate con archi di circonferneza (tolleranza: 1E-8)



...ma che me ne faccio dello STANDARD?

Uso gli standard quando voglio garantire l'interoperabiltà: voglio che i dati vengano visualizzati (e processati) da persone, uffici, enti (...) diversi che usano software diversi.

Lo standard diventa una LINGUA FRANCA per la comunicazione delle informazioni.

Se, in un database con estensione spaziale, ho dei dati in formato standard posso comunicare con questi altri geodatabase:

Postgres/PostGIS, Oracle Spatial (9i,10g,11g), MySQL (4.1+), Informix (9,10,11 con Spatial datablade), Microsoft SQL Server, SpatiaLite, Teradata (6.1, 6.2, 12, 13), Ingres GeoSpatial, altibase 5.x, AUTODESK MapGuide, GDAL



OK, ma perchè usare RDBMS?

Gli RDBMS sono nati per potere gestire ENORMI quantità di dati in maniera che si possa accedere o operare su queste informazioni in maniera veloce, e che molti utenti possano usufruire della banca dati in maniera contemporanea:

psqlmysqlclient

pg_dumpmysqldumpBackup/restore

54323306porta

postgresrootsuperuser

pg_ident.confmappatura utenti

pg_hba.confautenticazione e sicurezza

postgresql.confmy.cnfconfigurazione

postmastermysqlddemone

PostgreSQLMySQL

Struttura client/server



4 persone che lavorano sui MIEI dati CONTEMPORANEAMENTE?

Quasi tutti I database (anche alcuni NoSQL) permettono a più client di modificare il contenuto delle tabelle in contempranea attraverso il protocollo ACID: Atomicity, Consistency, Isolation e Durability:

atomicità: la transazione è indivisibile nella sua esecuzione e la sua esecuzione deve essere o totale o nulla, non sono ammesse esecuzioni intermedie;

consistenza: quando inizia una transazione il database si trova in uno stato consistente e quando la transazione termina il database deve essere in uno stato consistente, ovvero non deve violare eventuali vincoli di integrità, quindi non devono verificarsi contraddizioni (inconsistency) tra i dati archiviati nel DB;

isolamento: ogni transazione deve essere eseguita in modo isolato e indipendente dalle altre transazioni, l'eventuale fallimento di una transazione non deve interferire con le altre transazioni in esecuzione;

durabilità: detta anche persistenza, si riferisce al fatto che una volta che una transazione ha richiesto un commit work, i cambiamenti apportati non dovranno essere più persi. Per evitare che nel lasso di tempo fra il momento in cui la base di dati si impegna a scrivere le modifiche e quello in cui li scrive effettivamente si verifichino perdite di dati dovuti a malfunzionamenti, vengono tenuti dei registri di log, dove sono annotate tutte le operazioni sul DB..



…e le dimensioni contano...

Quando si lavora su grandi quantità di dati (layers > 1GB) i GIS tradizionali possono diventare molto lenti.Gli RDBMS utilizzano gli indici per velocizzare la ricerca dei dati.

Fondamentalmente un indice è la copia di una parte dei dati presenti in una relazione (tablle).

Alcuni database permettono un'indicizzazione creata per mezzo di funzioni od espressioni (ad esempio upper(cognome) immagazzina solamente l'iniziale maiuscola del campo cognome) o per mezzo di filtri, dove l'indice immagazzina solamente i valori che rispondono a qualche espressione condizionale.Come esistono molti modi di ordinare I dati, esistono molti indici diversi:

B-Trees: indici pensati per numeri, lettere (come nella rubrica telefonica), date...

R-Trees: ordinano i dati in rettangoli, sotto rettangoli, sotto-sotto rettangoli... Sono usati da alcuni GIS (GRASS) e da qualche estensione spaziale (ad esempio SpatiaLite) GIST (Generalized Search Tree): “cose che stanno da una parte”, “”cose che si sovrappongono”... Usato da PostGIS.


...ma quanto convengono gli indici?

.Esempio di ricerca di una particolare stringa all'interno di un campo di 490000 elementi circa...

SELECT fullname FROM utenti WHERE fullname LIKE '%Zottele%';

La query impiega 187ms e ritorna 8elementi: non c'è un indice sul campo fullname e quindi viene effettuata una full table scan.

CREATE INDEX idx_utenti_fullname_btree_ops ON utenti USING btree (fullname varchar_pattern_ops);SELECT fullname FROM utenti WHERE fullname LIKE '%Zottele%';

La query impiega ora 13ms (14x).

CREATE INDEX idx_utenti_email_btree_ops ON utenti USING btree email varchar_pattern_ops)SELECT email FROM utenti WHERE email LIKE '%@yahoo.com';

Questa query impiega 271 ms: il campo email è indicizzato, ma si effettua una full table scan poiché l'ordinamento della stringa avviene da sinistra verso destra ed il campo discriminante è “nascosto” dalla wildcard.

SELECT email FROM clienti WHERE reverse(email) LIKE reverse('%@yahoo.com');

Il campo di ricerca diviene moc.oohay@% e l'indicizzazione avviene efficientemente (9ms,30x)


mailto:'%[email protected]


...e per i dati geografici?


Lo standard ISO prevede che in un'estensione spaziale OGC compliant sia presente il dimensionally extended nine-intersection model (DE-9IM): un modello topologico che descrive il rapporto spaziale di due geometrie in 2 dimensioni.

Inoltre devono essere implementate ulteriori funzioni (buffer, distanza, lunghezza, area perimetro, linear referencing).

Queste funzionalità, rese veloci dalla presenza degli indici, appositamente pensati per I dati geometrici, rendono di fatto l'estensione spaziale un vero e proprio GIS.


… tutti i dati di tutti su un'unico DBMS?


Il pericolo di avere un'unico repository di dati è che, se questo repository non è più disponibile (salta la corrente) l'utente non può più accedere ai dati!

Gli RDBMS risolvono questo problema grazie alla replicazione: il database è replicato su molte macchine e le richieste possono essere ridirezionate su un'altra macchina in caso di manutenzione o rottura di un nodo.

Esistono molti tipi diversi di replicazione e la replicazione può essere costosa sia dal punto di vista dell'hardware (più computer) o del software (licenze aggiuntive).

I NoSQL DBMS utilizzano invece la distribuzione di porzioni ridondanti di database su molte macchine


… ma è difficile utilizzare un geoDBMS?


Dal punto di vista dell'utente che richiede i dati: NO se si usa un client GIS (qgis, GRASS, ArcGIS...). Basta connettersi alla base dati e analizzare le informazioni.Se però si dovesse operare direttamente sul DBMS (creare nuove tablle...) o interrogare il DBMS attraverso interfacce a basso livello (GDAL, R, MapServer) allora è necessario conoscere il linguaggio SQL.

SQL (Structured Query Language) è 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 col nome di SEQUEL. Dal 1986, SQL è uno standard ISO, la versione attuale è SQL:2008.

L'operazione più comune nell'SQL è la query: per mezzo dell'operatore SELECT vengono estratti i dati da una o più relazioni (JOIN). Il comando non ha effetti persistenti sui dati (sono persistenti INSERT,UPDATE,DELETE).

Le transazioni sono delle query successive che devono essere eseguite in gruppo.


… ma è difficile gestire un DBMS? (1/3)


NO, ma dipende dal grado di performance che si vuole fornire agli utenti. La performance può dipendere da quale DBMS si usa, da come si ottimizza il DBMS, da quanti/quali indici vengono applicati e da come gli indici vengano aggiornati.

PostgreSQL vs MySQL (MyISAM vs InnoDB) sottoposti a carichi variabili (processi concorrenziali vs richieste per secondo), con due processori differenti (SUN 1.0 GHz Niagara vs dual INTEL 3.0 GHz Woodcrest)




HW: HP ProLiant DL370 G6, Intel Xeon W5580-Nehalem [email protected] GHz cores.

OS: Red Hat Enterprise Linux 5.4 (2.6.18-164.eI5), Windows Server Enterprise Edition R2

DB: PostgreSQL 8.4.0, SQL Server 2008 R2

mailto:[email protected]


Marco Ciolli, Fabio ZotteleDipartimento di Ingegneria Civile e Ambientale

Universita' degli Studi di Trento

Database relazionali FOSS e GeoDatabase Database relazionali FOSS e GeoDatabase

PostgreSQL, Data Base Open Source e GRASSPostgreSQL, Data Base Open Source e GRASS

GRASS, FREE ed OPEN SOURCE GIS e GEODATABASE

Trento, 20 – 24 giugno 2011

Gestione dei Data Base in GRASS

• La gestione dei DB può avvenire in GRASS seguendo differenti procedure a seconda delle versioni di GRASS utilizzate.

• Le procedure non sono equivalenti e la scelta di una di esse fornisce strumenti differenti di analisi e trattamento dei dati

In particolare i dati alfanumerici collegati ai dati geografici possono essere gestiti:

• Direttamente dal motore di GRASSLa gestione diretta dei dati da parte di GRASS non garantisce il rispetto delle principali funzioni dei DB (coerenza, ecc.) il formato dei file è dbf

• Per mezzo di DataBase esterni interfacciati con GRASS in modo più o meno direttoLa gestione dei dati tramite Data Base esterno è più affidabile ed aumenta significativamente le capacità di analisi e trattamento dei dati

Qui si accenneranno varie procedure soffermandoci in particolare sulle versioni 6.2.x di GRASS interfacciate con PostgreSQL.


Perché conviene utilizzare un archivio esterno a quello di GRASS

La gestione diretta dei dati da parte di GRASS poteva portare a:

• ridondanza dei dati e inconsistenza;

• problemi di concorrenza per l’accesso ai dati contemporaneo da parte di più utenti;

• perdita di integrità dei dati;

• problemi di sicurezza;

• problemi di efficienza dal punto di vista dei tempi di:

- ricerca dei dati;

- aggiornamento dei dati.

Un DBMS, concepito appositamente per gestire archivi e quindi dotato di tutti gli strumenti necessari a questo scopo oltreché di caratteristiche di flessibilità ed espandibilità, permette di ottenere un approccio più mirato nella gestione dei dati.


Due tipi di interfaccia

L’interfaccia tra GRASS e PostgreSQL è stata realizzata seguendo due diversi approcci:

Interfaccia diretta Interfaccia mediante ODBC


GRASS

InterfacciaGRASS/Postgres

PostgreSQL

GRASS

InterfacciaGRASS/ODBC

PostgreSQLdriverODBC-

PostgreSQL

ODBC

http://grass.osgeo.org/grass64/manuals/html64_user/database.html

GRASS può essere collegato ad uno od a molti database management systems (DBMS). 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.

Alcune ulteriori funzioni sono state rese disponibili recentemente

* Categorie: Il numero di categoria è il vector ID. E' usato per collegare lo/gli

attributi a ciascun oggetto vettoriale. 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 (generalmente la colonna "cat"). Usando v.category, i numeri di categoria

possono essere stampati o mantenuti. Per fare in modo di collegare un oggetto

vettoriale a parecchie tavole diverse, sono necessari diversi numeri di categoria per

ciascun oggetto vettoriale.

* Layers: 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. Ciascun numero di categoria in un file della geometria

corrisponde ad una riga nella tabella degli attributi (generalmente la colonna "cat").

Usando v.db.connect i layers possono essere listati o mantenuti.

I layers di GRASS non contengono oggetti geografici, ma consistono in link alle

tavole degli attributi nei quali gli oggetti vettoriali possono avere zero, una o più

categorie.

Se un oggetto vettoriale ha zero categorie in un layer, allora non appare in quel layer.

In questa maniera alcuni oggetti vettoriali possono apparire in alcuni layer ma non in

altri. Il beneficio pratico di questo sistema è che permette di collocare oggetti

tematicamente distinti ma topologicamente correlati in una singola mappa (per

esempio foreste e laghi, o aree coltivate e bacini di raccolta idrica).

Questi layers virtuali sono anche utili per collegare serie temporali di dati ad una serie

di oggetti che non cambiano nel tempo.

Per convenzione il primo layer è quello attivo, per esempio la prima tabella

corrisponde al primo layer. Ulteriori tabelle sono collegate ai layer successivi.

* SQL support: Il driver DBF fornisce un supporto molto limitato all'SQL

mentre gli altri DBMS backends (come PostgreSQL, MySQL etc)

forniscono un pieno supporto SQL poiché i comandi SQL sono inviati

direttamente al DBMI.

I comandi SQL possono essere direttamente eseguiti con db.execute,

db.select e gli altri moduli db.*.

Quando si crea una tabella nuova, si deve creare una tabella degli attributi

e la tabella deve essere popolata con una riga per ogni categoria (usando

v.to.db). Questa operazione si può realizzare anche in un solo passaggio

usando v.db.addtable insieme con la definizione dei tipi di colonna della

tabella.

comandi db

comandi db e di interazione con i db: a cosa servono

1. db.connect2. db.test3. db.drivers4. db.login

• comandi per connessione db:1. db.columns2. db.copy3. db.describe4. db.tables

5. db.execute6. db.select

• comandi per tabelle:

• comandi che consentono esecuzione query SQL (operazioni su tabelle):

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

db.columns - Permette di visualizzare le colonne di una data tabella contenuta in un database

Quando il database è connesso e le tabelle sono visibili è possibile però usare anche l'interfaccia grafica, più user friendly, per 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. Se viene usato il parametro -c si ottengono solo i nomi delle colonne invece della descrizione completa

db.tables - Fa la lista di tutte le tabelle contenute in un database

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

I comandi db e di interazione vector-db: a cosa servono

comandi v.db

1. v.db.connect2. v.to.db

3. v.db.update4. v.db.addtable5. v.db.droptable6. v.db.reconnect.all

• comandi per connessione vector-db:

1. Display Manager2. d.vect3. v.extract4. v.reclass

• comandi che consentono esecuzione query SQL sui dati contenuti nelle tabelle:

v.db.connect - Stampa o setta la

connessione database per un

determinato vettoriale

v.to.db - Permette di inserire in un database i dati provenienti da un file vettoriale

v.db.update - Permette di assegnare un

nuovo valore ad una colonna connessa ad una

certa mappa

v.db.addtable - crea e aggiunge una nuova

tabella degli attributi ad un layer dato di una

mappa vettoriale esistente

v.db.droptable – rimuove la tabella degli attributi di una mappa

vettoriale esistente

v.db.reconnect.all – riconnette i file

vettoriali a un nuovo database

db.dropcol: Elimina una colonna da una tabella degli attributi selezionata

E ancora:

db.in.ogr: Importa tabelle degli attributi in vari formati

db.out.ogr: Exporta tabelle degli attributi in vari formati

Visualizzazione di una tabella dati dal layer manager

E' possibile visualizzare il

contenuto di una tabella collegata al

layer visualizzato

Realizzazione di una query dal table manager

E' possibile realizzare una query

direttamente dal table

manager, in questo caso

tutti I SIC del Trentino con

superficie maggiore

4000 ettari


E' possibile utilizzare il

query builder del table manager

Aggiungere layers di mappe vector dal Table Manager

Eliminare layers di mappe vector dal Table Manager

Modificare layers di mappe vector dal Table Manager

Gestire tabelle di mappe vector dal Table Manager

esecuzione di una query dal d.vect

si richiede la visualizzazione delle

zone di censimento aventi area minore di

500000

Funziona con i monitor lanciati da d.mon

esecuzione di una query dal layer manager

Qui si richiede la visualizzazione dei SIC del Trentino la cui superficie sia minore di 4000 ettari

esecuzione di una query dal gis.m (tcltk - old)

qui si richiede la visualizzazione degli edifici di Trento con quota del tetto minore di 10 m

E' possibile selezionare le modalità di visualizzazione degli oggetti, visualizzare il risultato di una query sul monitor attivo ed estrarre un nuovo vettoriale dal risultato della query

esecuzione di una query dal v.extract

si richiede che venga creata una nuova

mappa contenente solo le strade per cui l’emissione tra le ore 8 e le 9 sia maggiore

di 5000 (g CO/km)

d.what.vect -help

Description: Allows the user to interactively query a vector map layer at user-selected locations within the current geographic region.

Usage: d.what.vect [-1txdfe] [map=name[,name,...]]

Flags: -1 Identify just one location -t Terse output. For parsing by programs. -x Print information as plain text to terminal window. -d Print topological information (debugging). -f Enable flashing (slower). -e Open form in edit mode.

Da terminale: Vector -> Query with mouse (Form mode, editing enabled)

d.what.vect -e nomefile

La vecchia interfaccia d.m è ancora attiva, si richiama da console digitando: d.m& Essa utilizza i monitor x1, x2, x3

Solo il Postmaster può far partire questo script per creare un nuovo database

v.db.univar: Calcola statistica univariata sulla colonna selezionata di una tabella per una mappa vector

v.db.join: Permette di fare operazioni di join (joining) fra una tabella ed una tabella di una mappa vector

v.db.renamecol: Rinomina una colonna nella tabella degli attributi connessa a una data mappa vector

v.db.dropcol: Elimina una colonna dalla tabella degli attributi connessa a una data mappa

Confrontando l’ODBC con l’interfaccia diretta

Svantaggi interfaccia GRASS-ODBC:

• L’accesso è più lento, ci sono più strati software da attraversare.

• Maggior occupazione di memoria.

Vantaggi interfaccia GRASS-ODBC :

• Indipendenza dal software DBMS utilizzato, dato che con un’unica interfaccia per ODBC, vi è una maggiore facilità nelle operazioni di upgrade del sistema: esistendo i driver ODBC per molti gestori di basi di dati è possibile passare ad un altro gestore dell’archivio senza dover riscrivere i programmi di interfaccia realizzati che, comunicando con lo strato ODBC e non direttamente con il gestore della base di dati, sono sempre validi indipendentemente dal DBMS utilizzato.


http://pgdesigner.sourceforge.net/it/index.html

pgDesigner2

pgDesigner2

L’Open GIS Consortium, Inc. (OGC) è una organizzazione no-profit che tenta di stabilire dei criteri per favorire l’interoperabilità tra i software che trattano dati geografici.

http://www.opengis.org/index.htm

http://mapserver.gis.umn.edu/

MapServer

http://ems-hitech.com/pgmanager/?src=overtureAttenzione, questo però non è Open Source! Questa ditta rilascia il codice sorgente di alcuni suoi

prodotti (per esempio Mammoth PostgreSQL Replicator per PostgreSQL 7.3.6 e 7.4+) ma tale software non è distribuibile a meno che la ditta stessa non cessi l’attività

Creazione ed accesso all’archivio da console

Per iniziare si deve creare un archivio: all’interno di questo verranno poi definite le diverse tabelle di dati correlati che si vorranno gestire per mezzo di PostgreSQL.

% createdb nomedb

nomedb è il nome del database che si vuole creare.

Una volta creato il database vi si può accedere mediante il comando:

% psql nomedb

Esempio:

% createdb grass

% psql grass

grass =>

Ho creato e aperto un archivio chiamato grass; l’ultima riga è la riga di comando dalla quale lanciare il comando SQL per lavorare sull’archivio.

grass =>\q

%

Esco dall’archivio, ritorno alla linea di comando della shell di UNIX.


E’ possibile popolare il database anche creando delle tabelle vuote da riempire con dati in formato ASCII

L’utente deve essere già collegato (cioè deve eseguito psql) con l’archivio nel quale vuole lavorare.

Per creare una tabella è necessario definire nome e attributi che la comporranno:

CREATE TABLE nometab (nomeattr tipoattr [,elenco attr]);

Esempio:

grass => CREATE TABLE datamap (coord box, dg float8);

In questo caso creo, nella base di dati grass, una tabella chiamata datamap, con due attributi: uno nominato coord e di tipo box, l’altro nominato dg e di tipo float8.


La creazione di una tabella PostgreSQL si può ottenere anche tramite l’interfaccia PGAccess in maniera più

guidata e facilitata

Importare i dati nelle tabelle PostgreSQL

Definite le tabelle è possibile popolarle con i dati, caricandoli da un file ascii, con:

COPY nometab FROM input USING DELIMITERS ‘separatore’;

nometab è il nome della tabella in cui si vogliono copiare i dati; input è il nome del file con i dati da inserire;separatore è il carattere scelto per separare i diversi campi di un record (non usate il tab).

Es.: COPY datamap FROM ‘/home/alf/dati.csv’ USING DELIMITERS ‘;’;

In questo esempio copio nella tabella datamap, creata prima, i dati contenuti nel file dati.csv; in questo file gli attributi di ogni record saranno separati dal simbolo ;.

E’ indispensabile che i dati da importare abbiano la stessa struttura della tabella

Se il file ha più colonne della tabella nella quale lo si vuole copiare, quelle in eccedenza vengono ignorate da PostgreSQL e un messaggio ci avvisa di questo.


La stessa operazione ottenuta col comando COPY si può realizzare con PGAccess

Per inserire pochi valori si può usare il comando INSERT INTO nometabella VALUES (seriedivalori);

Formato dei file ascii

Nel file ascii che si vuole utilizzare come fonte per i dati da inserire nelle tabelle gestite da PosgreSQL è necessario:

• inserire un record per ogni riga;

• separare gli attributi che compongono un record con un carattere separatore a cui poi si dovrà fare riferimento quando si darà il comando copy oppure nel field delimiter della finestra di PGAccess;

• indicare il termine dell’elenco con la seguente sequenza di caratteri: \.

Esempio:

(6.000000,36.000000),(6.000000,36.000000)|10.3400

(6.050000,36.000000),(6.050000,36.000000)|11.3600

(6.100000,36.000000),(6.100000,36.000000)|12.3900

(6.150000,36.000000),(6.150000,36.000000)|12.0800

\.

Un file ascii costruito in questo modo permette di popolare la tabella datamap, definita in precedenza, con l’elenco dei dati posto nel file.


pgAdminIII

http://www.pgadmin.org/

http://www.pgadmin.org/

è un'interfaccia grafica per PostgreSQL scritta in Tcl/Tk, che permette di gestire facilmente l'archivio: operazioni sulle tabelle, interrogazioni,..., senza dover ricorrere ai comandi in linea e può essere visualizzata in contemporanea con GRASS.

Questo consente un rapido accesso alle tabelle e di poter effettuare al di fuori di GRASS tutte quelle elaborazioni che non sono direttamente vincolate alla geometria degli oggetti risparmiando tempo e risorse.

Purtroppo lo sviluppo è apparentemente fermo da qualche anno. Lo sforzo che era stato fatto era quello di trasformarlo in uno strumento per scrivere applicazioni che lavorano in una struttura distribuita client-server model (un database centrale PostgreSQL e dei remote clients). E’ disponibile per Linux,Unix, MacOS, Windows.


PgAccess

http://sourceforge.net/projects/pgaccess

http://rpmfind.net/linux/rpm2html/search.php?query=pgaccess

Tabella - apertura di tabelle multiple per la visualizzazione, con massimo numero di records (modificabile dal menù preferences) - ridimensionamento colonne, con il drag della linea di griglia verticale- text wrap in cells - layout salvato per ogni tabella - import/export a file esterni (SDF,CSV) - filter capabilities (enter filter like (price>3.14) - sort order capabilities (enter manually the sort field(s)) - editing in place - migliorato il table generator assistant - migliorato il field editing Queries - define, edit and stores "user defined queries" - salva le queries come views (viste) - esecuzione di queries con parametri opzionali dell’utente ( select * from invoices where year=[parameter "Year of selection"] ) - viewing of select type queries result - query deleting and renaming - visual query builder with drag & drop capabilities. For any of you who had installed the Tcl/Tk plugin for Netscape Nav. Sequences - defines sequences, delete them and inspect them Functions - define, inspect and delete functions in SQL, plpgsql and pgtcl languages Reports - design and display simple reports from tables - fields and labels, font changing, style and size - saves and loads report description from database - show report previews, sample postscript output file Forms - open user defined forms - form design module available - query widget available, controls bound to query results Scripts - define, modify and call user defined scripts Users - define and modify user information


CREATE VIEW [nome view] AS… SELECT * FROM [nome view] Quindi schiacciando il tasto “Esegui Query” appare la finestra :

Finestra che segnala che si sta eseguendo una query d’azione che porta alla creazione di una “View”, una tabella per la visualizzazione dei risultati della query stessa. Cliccando su “Yes”, PostgreSQL esegue le operazioni immesse nella finestra “Query Builder”, e se queste sono state scritte correttamente, apparirà un’altra finestra di dialogo.

Finestra di PGAccess per la creazione di una nuova query (Query Builder): si scrivono le operazioni in linguaggio SQL nell’apposito spazio (in cui è abilitato il copia ed incolla da clipboard) e successivamente si può salvare la query attribuendole un nome, in modo da poterla eseguire tutte le volte che è necessario.


La view crea una sorta di interrogazione permanente che

acquista la personalità di una tabella

normale.

Finestra in cui si visualizzano le view, richiamabile doppio-cliccando sul nome delle view nella finestra visualizzata sopra. La View visualizzata è quella denominata “coemissioni”, e dunque relativa alle emissioni totali di CO di ogni strada

Finestre in cui si visualizzano le query e le view salvate. La figura di sinistra mostra le query (in questo caso sono tutte query d’azione) e la figura di destra mostra le corrispondenti view. Per visualizzare una di queste, è sufficiente fare un doppio clic sul nome, o selezionare la view desiderata e premere il pulsante “Apri”.


DBdesigner4

DBdesigner4 un tool per progettare facilmente i data base (supporta MySQL ma, per ora, non PostgreSQL)

DB-Designer Fork

DB Designer Fork is a fork of the fabFORCE DBDesigner 4. It integrates entity relationship design,front-end (you can run queries) and SQL exporting. DB Designer Fork generates SQL scripts

for Oracle, SQL Server, MySQL, FireBird, SQLite and PostgreSQL.

phpPgAdmin

http://grass.osgeo.org/wiki/Openoffice.org_with_SQL_Databases

http://dba.openoffice.org/

http://mdbtools.sourceforge.net/

Bibliografia• AA.VV.: "PostgreSQL Programmer's Guide", Regent of the University of California, 1995.

• AA.VV.: "PostgreSQL User's Guide", Regent of the University of California, 1995.

• V.S. Subrahmanian: “Principles of multimedia database system”, Morgan Kaufmann, 1998

• M. A. Brovelli, M. Negretti, C. Saldarini: “GRASS interfacing with DBMSs”, Geomatics Workbooks

• Indexing tree methods and spatial ordering for maps and geographic data: an overview and application to the geodetic gis project, atti del congresso ISPRS - WG VI/3 "International cooperation and technology transfert", Parma, 15-19 febbraio 1999 L. Biagi, M. A. Brovelli, M. Negretti and C. Saldarini;• Nuove metodologie GIS per la stima e l’aggiornamento del geoide, pp.123-165, tesi di Laurea svolta presso il Politecnico di Milano, 1999, M. Negretti, C.Saldarini.• Ciolli M. Dispense del Corso GRASS e OPEN SOURCE GIS teoria ed applicazioni 1a edizione - anno 2003, 2a edizione - anno 2004, 3a edizione - anno 2005, 4a edizione - Roma anno 2006, 5a edizione Trento anno 2006, 6a edizione Trento anno 2007. • File sorgenti e manuali di PostgreSQL: http://www.postgresql.org e http://techdocs.postgresql.org/

• Interfaccia grafica PgAccess: http://www.flex.ro/pgaccess/ e www.pgaccess.org

• File sorgenti e manuali di GRASS: http://grass.itc.it/

• http://www.html.it/sql

•Suffritti P., Appunti sui DataBase Relazionali e sul linguaggio SQL. http://www.suffritti.it/SQLTutorial.htm

• L.Biagi, M.A.Brovelli, M.Negretti, Caratteristiche di PostgreSQL http://www.geo.unipr.it/~gis/TUTORIALS/POSTGRESINTRO/postgres.pdf

•M. Zanoni, Implementazione di un sistema integrato gis–database per la gestione dei dati di traffico e produzione di mappe delle emissioni. applicazione alla città di Trento. Tesi di Laurea Ingegneria Trento 2003

•C. Modena, Analisi e delimitazione delle aree forestali con particolare funzione protettiva in Trentino tramite tecniche GIS. Tesi di Laurea Ingegneria Trento 2003

•A. Daloli, Strutturazione e sviluppo di un Database in Postgresql per l’analisi dei dati di traffico Tesi di Laurea, Ingegneria Trento 2002

•Alla presente lista si aggiungano tutte le fonti web citate nei lucidi.


http://www.flex.ro/pgaccess/

http://www.pgaccess.org/

http://grass.itc.it/

http://www.html.it/sql

http://www.geo.unipr.it/~gis/TUTORIALS/POSTGRESINTRO/postgres.pdf

Database relazionali FOSS e GeoDatabasezatelli/cartografia_numerica/slides/... · 2016-09-08 ·...

Documents

Transcript of Database relazionali FOSS e GeoDatabasezatelli/cartografia_numerica/slides/... · 2016-09-08 ·...