Post gis base_postgresql_tools_sql_base

Trento, maggio 2014

POSTGRESQL E POSTGISPostGIS base

Trento, maggio 2014 – Basi di PostGIS2

PostGIS e' un'estensione per la base di dati relazionale ad oggetti PostgreSQL che consente di gestire nella base di dati oggetti territoriali.

PostGIS permette di create delle “colonne” con tipo geometrico e include il supporto per gli indici spaziali di tipo R-Tree basati sulle geometrie e le funzioni per l'analisi e il trattamento degli oggetti territoriali.

COS’È POSTGIS


TIPI GEOMETRICI PRINCIPALI

Implementa tutti i tipi base previsti dal Simple FeatureModel:

� POINT – punto � LINESTRING – linea � POLYGON – poligono � GEOMETRY – geometria generica� MULTIPOINT – collezione di punti� MULTILINESTRING – collezione di linee� MULTIPOLYGON – collezione di poligoni� GEOMETRYCOLLECTION – collezione generica


Per aggiungere un tipo geometrico ad una tabella ènecessario eseguire il seguente comando

AddGeometryColumn(nome_schema, nome_tabella, nome_colonna,sistema_di_riferimento, tipo_geometrico,dimensione_coordinate)

Esempio: select addgeometrycolumn ('public', 'un_vol', 'un_vol_sup', '25832', 'POLYGONZ', 3);

AGGIUNTA DI UN TIPO GEOMETRICO


Gli indici spaziali sono uno elemento chiave, infatti permettono di gestire ai database spaziali un grande insieme di dati senza sacrificare le performance.

L’indicizzazione velocizza la ricerca organizzando i dati secondo un albero di ricerca che può essere percorso velocemente per l’individuazione di un record specifico.

L'indicizzazione spaziale calcola per ogni record il reativoenvelope in modo da velocizzare non solo la ricerca ma anche la valutazione delle proprietà poichè alcune proprietà, se non valgono per l'envelope, automaticamente non varranno sulle geometrie che hanno generato quell'envelope

INDICI SPAZIALI


Per creare un indice spaziale si utilizza il comando:

CREATE INDEX nome_indice ON nome_tablella USING GIST (colonna_geometrica);

Esempio: CREATE INDEX un_vol_S1 ON un_vol USING GIST (un_vol_sup)

È inoltre consigliato creare indici anche per i dati non spaziali, tali indici possono essere creati con il comando:

CREATE INDEX nome_indice ON tablella (colonna);

CREAZIONE DI UN INDICE


La semplice creazione di un indice non automaticamente implica un uso efficiente dello stesso da parte di PostgreSQL. Ogni volta che si crea un nuovo indice o dopo molte operazioni di UPDATE, INSERT o DELETE è consigliabile lanciare il comando VACUUM per chiedere a PostgreSQL di utilizzare ogni spazio resosi libero dopo le operazioni citate.

Il pianificatore di query di PostgreSQL sceglie intelligentemente se usare o meno un indice per valutare una query. è necessaeio tenere presente che la ricerca con indice non sempre la più veloce quindi ènecessario utilizzare il comando ANALYZE per aggiornare le statistiche

Attenzione: VACUUM non aggiorna le statistiche, cosìcome ANALYZE non recupera lo spazio libero.

VACUUM E ANALYZE


Entrambi i comandi possono essere lanciati, anche in modo contestuale, su una singola tabella, una singola colonna o un intero database.

VACUUM ANALYZE sul database corrente:VACUUM ANALYZE

VACUUM ANALYZE sul una singola tabella:VACUUM ANALYZE nome_tabella

VACUUM ANALYZE sul una singola colonna:VACUUM ANALYZE nome_tabella (nome_colonna)

VACUUM E ANALYZE / 2


GEOMETRY ACCESSORS

Di seguito sono elencati i metodi per recuperare alcune proprietà di una geometria generica

� ST_GeometryType - Ritorna il tipo geometrico � ST_Boundary - Ritorna il boundary� ST_CoordDim - Ritorna la dimensione delle coordinate� ST_Dimension - Ritorna la dimensione della geometria� ST_Envelope - Ritorna l'envelope contenente la geometria� ST_SRID - Ritorna il sistema di riferimento utilizzato� ST_IsSimple - Ritorna TRUE se la geometria è semplice� ST_IsValid - Ritorna TRUE se la geometria è ben formata� ST_IsEmpty - Ritorna TRUE se la geometria è vuota


GEOMETRY ACCESSORS TIPIZZATI

Metodi definiti solo per le geometrie lineari

� ST_IsClosed - Ritorna TRUE se la linea è chiusa

� ST_IsRing - Ritorna TRUE se la linea è chiusa e semplice

� ST_StartPoint - Ritorna il punto iniziale della linea

� ST_EndPoint - Ritorna il punto finale della linea

Metodi definiti solo per le geometrie poligonali

� ST_ExteriorRing - Ritorna la frontiera esterna del poligono

� ST_NumInteriorRings - Ritorna il numero di ring interni

� ST_InteriorRingN(geom, n) - Ritorna l’ennesimo ring interno

Metodi definiti solo per le collezioni geometriche

• ST_NumGeometries - Ritorna il numero di geometrie della collezione

• ST_GeometryN(geom, n) - Ritorna l’ennesima geometria della collezione


RELAZIONI TOPOLOGICHE - FUNZIONI PRINCIPALI

� ST_Contains(A,B) - TRUE se nessun punto di B cade nell'exterior di A, e almeno un punto di B cade nell'interior di A.

� ST_ContainsProperly(A,B) - TRUE se tutti i punti di B cadono nell'interior di A

� ST_Covers(A,B) - TRUE se nessun punto di B cade nell'exterior di A� ST_CoveredBy(A,B) - TRUE se nessun punto di A cade nell'exterior di B� ST_Crosses(A,B) - TRUE se A e B hanno alcuni punti di interior in

comune.� ST_Disjoint(A,B) - TRUE se le due geometrie non hanno punti di

interazione� ST_Equals(A,B) - TRUE se A e B rappresentano la stessa geometria.� ST_Intersects(A,B) - TRUE se le due geometrie hanno punti di

interazione� ST_Overlaps(A,B) - TRUE se A e B si sovrappongono parzialmente� ST_Touches(A,B) - TRUE se le geometrie hanno almeno un punto di

contatto ma non condividono alcun punto di interior.� ST_Within(A,B) - TRUE se A è completamente contenuta B


FUNZIONI GEOMETRICHE PRINCIPALI

� ST_Buffer(A,m) – Calcola il buffer di m metri applicato alla geometria A

� ST_ConvexHull(A) – Calcola il convex hull (che è la minima geometria convessa che contiene la geometria A)

� ST_Difference(A,B) – Calcola la geometria che rappresenta le parti di A non coperte da B

� ST_Intersection(A,B) – Calcola l'intersezione tra A e B

� ST_SymDifference(A,B) – Calcola la geometria che rappresenta le parti di A non coperte B e le parti di B non coperte A

� ST_Union(geoms) – Calcola la geometria che rappresenta l'unione point set delle geometrie passate come argomento


ALTRE FUNZIONI INTERESSANTI� ST_Area(A) - Ritorna l'area (in metri quadrati) del (multi) polygon

� ST_Centroid(A) - Ritorna il centroide della geometria.

� ST_Distance(A,B) – La distanza minima tra le geometrie A e B

� ST_MaxDistance(A,B) – La distanza massima tra le geometrie A e B

� ST_Length(A) - La lunghezza 2D di una curva o multicurva

� ST_3DLength(A) - La lunghezza 3D di una curva o multicurva

� ST_Perimeter(A) – La lunghezza 2D del perimetro del (multi) polygon

� ST_3DPerimeter (A) - La lunghezza 3D del perimetro del (multi) polygon

� ST_PointOnSurface(A) - Ritorna un punto della superfice del poligono

� ST_LineMerge(multilinestring) - Ritorna un insieme di linenestringriordinando e se possibile fondendo le parti della multilinestring di ingresso

� ST_AsEWKT (A) – Ritorna la geometria nel formato EWKT

� ST_GeomFromEWKT(A) – Legge una geometria dal formato EWKT

� ST_Force2D(A) – Elimina la coordinata Z (funzione planar del GeoUML)

� ST_Force3D(A) – Aggiunge la coordinata Z settata a 0


POSTGRESQL E POSTGISTools SQL


OpenOffice può essere utilizzato come interfaccia per l’interazione con un database generico permettendo la creazione, modifica e cancellazione del contenuto.

OpenOffice permette di gestire diverse basi di dati attraverso l’utilizzo di driver di connessione; uno dei metodi per far interagire OpenOffice

È possibile scaricare gratuitamente il driver JDBC da http://jdbc.postgresql.org/

OPENOFFICE


Aprire OpenOffice

CONFIGURAZIONE DI OPENOFFICE

Selezionare il menùStrumenti -> Opzioni -> Java


Cliccare su Classpath… e aggiungere la libreria JDBC di PostgreSQL

CONFIGURAZIONE DI OPENOFFICE / 2

Chiudere OpeOffice per rendere permanenti e modifiche


Aprire OpenOffice Base e selezionare “Collega a un database esistente” -> JDBC

ISTAURARE LA CONNESSIONE A POSTGRESQL

Compilare i parametri di connessione e digitare Avanti >>


Inserire username e password e testare la connessione.

Salvare il database ed aprirlo

ISTAURARE LA CONNESSIONE A POSTGRESQL / 2

Ricapitoliamo i parametri di connessioneFormato URL del database

jdbc:postgresql://host:port/database

Classe del driver di PostgreSQLorg.postgresql.Driver


INTERFACCIA DI VISUALIZZAZIONE DATI


INTERFACCIA DI VISUALIZZAZIONE DATI / 2


POSTGRESQL E POSTGISBasi del linguaggio SQL


� SQL (“Structured Query Language”)linguaggio che permette il dialogo con il DBMS

� DDL (“Data Definition Language”)creazione modifica e cancellazione di database, schemi,

tabelle e proprietà correlate

� DCL (“Data Control Language”)controllo degli utenti e delle autorizzazioni

� DML (“Data Manipulation Language”)inserimento, modifica, selezione e cancellazione dei

dati

DEFINIZIONI


CREATE TABLE table_name ( column_name data_type [ column_constraint [ ... ] ][, ... ]

)

Tra i vincoli (column_constraint ) si ricordano:� PRIMARY KEY� NOT NULL � DEFAULT default_value� UNIQUE index_parameters� REFERENCES referenceTable ( referenceColumn )

CREAZIONE DI UNA TABELLA


TIPI DI DATI NUMERICI

Alcuni tipi di dato numerici disponibili in PostgreSQL

Nome Descrizione Range

smallint small-range integer -32768 to +32767

integer typical choice for integer-2147483648 to+2147483647

bigint large-range integer-9223372036854775808 to9223372036854775807

decimaluser-specified precision, exact

no limit

numericuser-specified precision, exact

no limit

real variable-precision, inexact 6 decimal digits precision

double precision variable-precision, inexact15 decimal digitsprecision


TIPI DI DATI TEMPORALI

Tipi di dato per la gestione temporale disponibili in PostgreSQL

Nome Descrizione Minimo Massimo

timestamp date and time (no time zone) 4713 BC 294276 AD

timestamp with time zone

date and time, with time zone

4713 BC 294276 AD

date date (no time) 4713 BC 5874897 AD

time time (no date) 00:00:00 24:00:00

Time with time zone times, with time zone 00:00:00 24:00:00


ALTRI TIPI DI DATO

Tipi di dato per la gestione dei caratteri disponibili in PostgreSQL

Nome Descrizione

character varying(n), varchar(n) variable-length with limit

character(n), char(n) fixed-length, blank padded

text variable unlimited length

Tipo Nome Descrizione

Boolean booleanCan have one of only two states: "true" or "false". A third state, "unknown", is represented by the SQL null value.

Binary blob Binary Large Object can store binary data of any kind

Da ricordare anche…


Per creare una tabella:CREATE TABLE un_vol (

classid varchar(70) PRIMARY KEY,datafine date ,dataini date NOT NULL,un_vol_ex varchar(80) NOT NULL,un_vol_qe double precision NOT NULL

);

Quando si vorrà eliminarla sarà sufficiente eseguire:DROP TABLE un_vol

ESEMPIO DI CREAZIONE E CANCELLAZIONE TABELLA


La query di selezione più basilare senza clausole di filtro sui valori è la seguente:

SELECT colonna [, ...] FROM tabella

Nel caso in sui si voglia selezionare tutte le colonne si può usare: SELECT * FROM tabella

Esempi:� SELECT classid, datafine, dataini, un_vol_qe FROM un_vol

� SELECT * FROM un_vol

QUERY DI SELEZIONE BASE


La query di inserimento permette di inserire una tupla (riga) in una tabella definita nel database:

INSERT INTO tabella ( colonna [, ...] ) VALUES ( colonna [, ...] )

Una query alternativa:INSERT INTO tabella ( colonna [, ...] )

SELECT colonna [, ...] FROM altra_tabella

Esempio: INSERT INTO un_vol (classid, dataini, un_vol_ex, un_vol_qe) VALUES ('1‘, DATE '2014-05-08','altezza', 6.0)

QUERY DI INSERIMENTO


La query di aggiornamento permette di aggiornare una o più tuple (rige) in una tabella del database:

UPDATE tabella SET colonna = valore [, ...]Esempio: UPDATE un_vol SET un_vol_qe = 6.5

La query di cancelazione permette di cancellare una o piùtuple (rige) in una tabella del database:

DELETE FROM tabellaEsempio: DELETE FROM un_vol

ATTENZIONE: La richiesta di aggiornamento e cancellazione è applicata a tutte le tuple presenti nella tabella della base dati

QUERY DI AGGIORNAMENTO E CANCELLAZIONE


La clausola where ci permette di “filtrare” le tuple a cui applicare le query di selezione, modifica e canellazione

WHERE nomeColonna operatore valore

Operatori:� = uguale � <> diverso� > maggiore, >= maggiore uguale� < minore, <= minore uguale� IS NULL filtra il contenuto uguale a null� IS NOT NULL filtra il contenuto diverso a null

CLAUSOLA WHERE


L’applicazione delle precedenti query è limitata alla sola tupla con identificativo (classid) uguale a 1

� SELECT classid, datafine, dataini, un_vol_qe FROM

un_vol WHERE classid = ‘1’

� SELECT * FROM un_vol WHERE classid = ‘1’

� UPDATE un_vol SET un_vol_qe = 6.5 WHERE classid =

‘1’

� DELETE FROM un_vol WHERE classid = ‘1’

È possibile applicare più di una clausola utilizzando gli operatori AND e/o OR

ESEMPI DI WHERE


La clausola order by ordina presentazione risultato all’utente su una o più colonne in modo ascendente (ASC) o discendente (DESC)

ORDER BY nomeColonna1 DESC, nomeColonna2 ASC

Esempio:SELECT classid, datafine, dataini, un_vol_qe FROM

un_vol ORDER BY un_vol_qe DESC, classid ASC

CLAUSOLA ORDER BY


Un Join serve a combinare (unire) le tuple di due o piùtabelle di un database tramite un’espressione booleana.

Sintassi:Tablella1 [tipoDiJoin] Tabella2 ON espressione

L’espressione più classica è colonnaTab1 = colonnaTab2

Tipologie di Join:� INNER JOIN (solitamente il default)� LEFT JOIN � RIGHT JOIN� FULL JOIN

INTERROGAZIONE MULTI TABELLA (JOIN)

Post gis base_postgresql_tools_sql_base

Technology

Transcript of Post gis base_postgresql_tools_sql_base