INTRODUZIONE AI DATABASE GEOSPAZIALI

https://enricofer.github.io/spatial_sql_workshop/

INCONTRI ESTIVI GFOSS

Esercitazione guidata

SINTASSI SQL

In linguaggio SQL sono possibili parecchi tipi di Query:

query di creazione tabella:

CREATE TABLE tab(id INT PRIMARY KEY NOT NULL, campo1 CHAR(50), campo2 INT);

query di inserimento dati:

INSERT INTO tab (id, campo1, campo2) VALUES (1, 'valore 1', 120);

query di cancellazione tabella:

DROP TABLE tab;

query di aggiornamento:

UPDATE tab SET campo1 = 'valore2' WHERE id = 1;

SQL SELECT

ma quello che ci interessa oggi è la query di ricerca

        SELECT id, campo1   -- prendi i campi id e campo1
        FROM tabella        -- da tabella
        WHERE campo2 > 100; -- dalle righe che soddisfano una condizione

        SELECT * 
        FROM tabella -- elenca tutti i campi e tutte le righe di una tabella

I dialetti SQL sono molti e ci sono grandi differenze di dettaglio. Ma la struttura della sintassi principale per l'estrazione dei dati rimane praticamente la stessa per tutti i RDBMS.
Oggi lavoreremo con SQlite, nella sua versione con estensioni spaziali Spatialite il cui sviluppo è open source ed è curato da Alessandro Furieri

L'ambiente di lavoro del Workshop

Per facilitare e velocizzare il workshop le esercitazioni sono completamente basate su un una versione speciale di Spatialite compilata per l'utilizzo da browser. Il database è residente nella memoria del browser, ma è facilmente scaricabile per l'utilizzo con QGIS per chi lo desidera:veneto.sqlite

E' possibile una visualizzazione tabellare:

      SELECT * FROM comuni

o una visualizzazione su mappa: Le tabelle contengono un campo *geom* con le informazioni spaziali memorizzate in latitudine e longitudine (ETRS89 epsg:4258) e visualizzabili producendo una geometria geojson per mezzo della funzione AsGeoJSON()

      SELECT *, AsGeoJSON(geom) AS geojson
      FROM comuni

Il formato geojson è un formato testuale facilmente interpretabile da javascript come un'oggetto (dati serializzati) contentente dati geografici e relativi attributi. Il formato è uno standard codificato di internet e costituisce una specie di lingua universale per l'interscambio di dati geografici nel web

query di ricerca SELECT

Disponiamo delle seguenti tabelle, visualizzabili eseguendo le query:

SELECT * FROM comuni     -- Confini comunali del Veneto (geometrie poligonali)

SELECT * FROM strade     -- Rete stradale (geometrie lineari)

SELECT * FROM ferrovie   -- Rete ferroviaria (geometrie lineari)

SELECT * FROM fiumi      -- Idrografia (geometrie lineari)

SELECT * FROM bacini     -- Bacini idrografici (geometrie poligonali)

SELECT * FROM centri     -- Centri abitati (geometrie puntuali)

SELECT * FROM pop        -- Popolazione italiana per comune (senza geometria)

L'elenco delle tabelle e dei relativi campi disponibili può essere visualizzato in ogni momento premendo il bottone "elenco tabelle"

E' buona prassi limitare il numero di record che una query può restituire, soprattutto in fase di sviluppo e test della query complesse, in modo da velocizzare i tempi di ricerca e non impattare sulle performance del database od ottenere risultati più significativi

SELECT * FROM pop
ORDER BY pop_totale DESC
LIMIT 10

Campi, Subquery e Accodamenti

Alias:

SELECT comune,provincia, istat AS codice
FROM comuni

Query nidificate:

SELECT subquery.comune
FROM (
    SELECT * FROM comuni
) AS subquery

Accodamenti:

SELECT istat as id,comune AS nome FROM comuni
UNION
SELECT OBJECTID as id, NAMN1 AS nome FROM strade
UNION
SELECT OBJECTID as id, NAMN1 AS nome FROM centri

Clausola WHERE

SELECT * FROM comuni
WHERE provincia = 'PD'

SELECT * FROM comuni
WHERE istat BETWEEN 28021 AND 28044

SELECT * FROM comuni
WHERE comune in ('Padova','Venezia','Treviso')

SELECT * FROM comuni
WHERE comune LIKE 'Ve%'

le condizioni in WHERE possono essere assemblate con AND, OR e negate con NOT attenzione alla precedenza degli operatori

SELECT * FROM centri
WHERE NAMN1 = (SELECT comune FROM comuni WHERE istat = 28021)

le query nidificate possono essere utilizzate per ottenere un valore interrogando un'altra tabella

CAMPI CALCOLATI E ORDINAMENTO

campi calcolati ed alias

SELECT comune, pop_totale, famiglie + convivenze as nuclei, pop_totale/(famiglie + convivenze) as componenti_medi
FROM pop

ordinamento crescente

SELECT * FROM pop
ORDER BY famiglie

ordinamento decrescente

SELECT * FROM pop
ORDER BY pop_totale DESC

ordinamento alfabetico multiplo

SELECT * FROM comuni
ORDER BY provincia,comune

JOIN - relazioni tra tabelle

La funzione fondamentale dei database relazionali è consentire l'aggregazione di dati tra due tabelle stabilendo una relazione tra campi

SELECT
  comuni.comune, pop.pop_totale,
  pop.pop_totale/comuni.area_ha AS densita
FROM comuni JOIN pop ON comuni.istat=pop.cod
ORDER BY densita DESC

JOIN associa a tutte le righe di una tabella le righe di un'altra solo se soddisfano una condizione, altrimenti NULL

Aggregazione e raggruppamento

Un'altra caratteristica delle query SELECT è la capacità di creare delle tabelle di sintesi da altre più complesse

SELECT
	provincia,
  COUNT(comune) as num_comuni,
  SUM(area_ha) as area_ha,
  AVG(area_ha) as area_media
FROM comuni
GROUP BY provincia

SELECT
	provincia,
  COUNT(comuni.comune) as num_comuni, SUM(pop.pop_totale) as pop
FROM comuni JOIN pop ON comuni.istat = pop.cod
GROUP BY provincia

Funzione di aggregazione disponibili in SQlite

Altri oggetti di un Database

VIEW: una vista permette di memorizzare una query di selezione assegnandole un nome. La view può essere richiamata secondo necessità e trattata come una tabella (in sola lettura)
SEQUENCE: una sequenza permette di popolare un campo con valori progressivi ad ogni chiamata. tipicamente vengono usate per i campi autonumeranti
INDEX: gli indici permettono di organizzare i dati in modo da facilitarne il reperimento durante la ricerca. La configurazione opportuna e l'aggiornamento degli indici è fondamentale per il miglioramento delle prestazioni di un DataBase
TRIGGERS: sono delle query, tipicamente di aggiornamento o di creazione di tabelle scatenate dall'accadere di qualche evento programmato in anticipo

CREATE VIEW prov AS
  SELECT provincia, COUNT(comuni.comune) as num_comuni, SUM(pop.pop_totale) as pop
  FROM comuni JOIN pop ON comuni.istat = pop.cod
  GROUP BY provincia;
SELECT * from prov
ORDER BY num_comuni;

FUNZIONI GEOSPAZIALI

Funzioni geometriche di Spatialite

L'estensione spaziale di SQlite è Spatialite, e dispone di una serie molto completa di funzioni per la manipolazione di informazioni geografiche: Reference List ed è compatibile con lo standard OGC per le funzioni geospaziali nei database

Le funzioni basilari che utilizzeremo nel workshop sono le seguenti

condizioni topologiche (condizioni spaziali): dentro, fuori, contenuto da, coincidente etc...
manipolazione geometrica (aggregazione): unione, differenza, intersezione, buffer, generalizzazione
misurazione geometrica: lunghezza, area, distanza
estrazione geometrica: centroidi, linee di unione
trasformazioni: riproiezioni, conversioni di formati geometrici

Il campo geometria

I tipi geometrici possono essere i seguenti:

geometria puntuale (POINT e MULTIPOINT)
geometria lineare (LINESTRING e MULTILINESTRING)
geometria poligonale (POLYGON e MULTIPOLYGON)

L'informazione spaziale è contenuta in almeno un campo geometrico. Nelle tabelle del workshop il campo geografico è per convenzione il campo "geom", ma nulla vieta che possa avere un altro nome.
Il formato di memorizzazione è binario, e quindi non visualizzabile "per gli umani" ma disponiamo di funzioni che permettono di trasformarlo in un formato più amichevole:

SELECT
	geom,
  ST_AsText(geom) AS wkt,
  Hex(ST_AsBinary(geom)) AS wkb
FROM centri

Tipi geometrici

Visualizzazione in mappa

Il risultato della query può essere visualizzato in mappa quando la tabella di output contiene un campo "geojson" contente le geometrie da visualizzare in formato geojson:

Il formato geojson è l'espressione testuale di un'oggetto javascript prodotta secondo lo standard "json" ed è facilmente "digeribile" dalle applicazioni web

L'altra condizione perchè la geometria possa essere visualizzata correttamente nel visualizzatore è che il dato geografico deve essere espresso in gradi di latitudine e longitudine. Le tabelle predefinite sono nel formato ETRS89-epsg4258, sono quindi compatibili con il visualizzatore

SELECT
  AsGeoJSON(
    ST_GeomFromText('LINESTRING(11 45,11 46,12 46,12 45, 11 45)')
  ) AS geojson

Condizioni topologiche

SELECT AsGeoJSON(geom) as geojson
FROM comuni
WHERE ST_Intersects(geom,ST_GeomFromText('POLYGON((11 45,11 46,12 46,12 45, 11 45))'))

Spatial JOIN

L'unione di due tabelle è possibile quando una condizione di JOIN è soddisfatta. Se la condizione è basata su una relazione spaziale tra geometrie, si stabilisce un Join spaziale tra dati

SELECT comuni.provincia, centri.*
FROM centri JOIN comuni ON ST_Within(centri.geom, comuni.geom)

Dall'unione delle tabelle potrebbe risultare dei risultati non univoci:

SELECT fiumi.NAMN1, comuni.*
FROM comuni JOIN fiumi ON ST_Intersects(fiumi.geom, comuni.geom)

Sarà quindi opportuno raggruppare i risultati

SELECT fiumi.NAMN1, comuni.*
FROM comuni JOIN fiumi ON ST_Intersects(fiumi.geom, comuni.geom)
GROUP BY comuni.comune

E' da notare come in altri DBMS la calusola GROUP BY pretende che tutti i campi siano esplicitamente aggregati. SQlite implicitamente mette il primo risultato

AGGREGAZIONE SPAZIALE

Le funzioni spaziali possono essere usate per collezionare le geometrie aggregate da una query di raggruppamento

SELECT
	provincia,
  COUNT(comune) as num_comuni,
  SUM(area_ha) as area_ha,
  AVG(area_ha) as area_media,
  AsGeoJSON(ST_union(geom)) as geojson
FROM comuni
GROUP BY provincia

L'intersezione tra geometrie lineari genera geometrie puntuali:

SELECT
  asgeojson(st_intersection(fiumi.geom,ferrovie.geom)) as geojson,
  fiumi.NAMN1,
  ferrovie.NAMN1
FROM fiumi, ferrovie
WHERE st_intersects(fiumi.geom,ferrovie.geom)

MISURE, TRASFORMAZIONI E BUFFER

E' possibile acquisire dati dimensionali delle geometrie inserite lunghezze, aree, distanze

SELECT comune,St_area(geom) AS superficie, St_length(geom) AS perimetro FROM comuni

SELECT NAMN1,St_length(geom) AS lunghezza FROM fiumi

SELECT NAMN1,St_distance(geom,ST_GeomFromText('POINT(11.89 45.4)')) AS dist_pd FROM centri

Essendo le geometrie espresse in latitudine e longitudine, I dati geometrici sono espressi in gradi. E' quindi necessario trasformare le coordinate in un sistema di riferimento proiettato per ottenere le corrette dimensioni in metri

SELECT NAMN1,St_length(st_transform(geom,3003)) AS lunghezza FROM fiumi

La funzione di buffer genera un poligono i cui vertici distano ad una distanza costante da una geometria generatrice

SELECT  asgeojson(st_buffer(fiumi.geom,0.03)) as geojson FROM fiumi

ELABORAZIONI

Quali sono i centri serviti dalla ferrovia?

Individuazione di un buffer di 10 km dalla ferrovia

SELECT AsGeoJson(st_buffer(st_union(geom), 0.03)) AS geojson
FROM ferrovie

individuazione dei centri ricadenti nel buffer appena individuato

SELECT *,ASgeoJSON(geom) as geojson
FROM centri
WHERE st_intersects(geom, (
    SELECT st_buffer(st_union(geom), 0.03)
    FROM ferrovie
))

metodo alternativo meno performante ma che recupera i dati

SELECT centri.NAMN1,st_distance(centri.geom,ferrovie.geom),ASgeoJSON(centri.geom) as geojson
FROM centri JOIN ferrovie 
	ON st_intersects(centri.geom, st_buffer(ferrovie.geom, 0.03))
GROUP BY centri.NAMN1

NB: i dati geografici sono espressi in latitudine e longitudine quindi la distanza di buffer va espressa in gradi (0.01 gradi corrispondono circa a 950 m)

Qual'è la popolazione dell'area metropolitana di Verona?

Individuazione dei comuni limitrofi

SELECT comune, asgeojson(geom) as geojson
FROM comuni 
WHERE st_touches(geom, (
  SELECT geom
  FROM comuni
  WHERE comune = 'Verona'
))

Usando st_intersects al posto di st_touches si include anche il comune stesso di Verona

Si può quindi raggruppare la tabella sommando la lunghezza di ogni arco

SELECT SUM(pop.pop_totale), asgeojson(st_union(geom)) as geojson
FROM comuni JOIN pop on comuni.istat=pop.cod
WHERE st_Touches(geom, (
  SELECT geom
  FROM comuni
  WHERE comune = 'Verona'
))
GROUP BY provincia

Nota bene: Questa query non funzionerebbe in postgis poichè nel caso di raggruppamento Postgresql richiede di specificare una funzione di aggregazione per ogni campo

Quanto è lungo un dato fiume?

Individuazione del fiume: Trattandosi di un grafo idrologico, il fiume è individuato da una serie di archi:

SELECT  NAMN1,asgeojson(geom) as geojson 
FROM fiumi 
WHERE NAMN1 = 'Fiume Bacchiglione'

Metodo 1: quindi si può raggruppare sommando la lunghezza di ugni arco

SELECT  
	NAMN1, 		
    st_length(st_transform(st_union(geom),3003)),
    asgeojson(st_union(geom)) as geojson 
FROM fiumi 
WHERE NAMN1 = 'Fiume Bacchiglione'
GROUP BY NAMN1

Nota bene: Questa query non funzionerebbe in postgis poichè nel caso di raggruppamento Postgresql richiede di specificare una funzione di aggregazione per ogni campo

Quanti e Quali comuni sono attraversati da un dato fiume?

Metodo 1: con subquery

SELECT  *,asgeojson(geom) as geojson 
FROM comuni 
WHERE st_intersects(geom, (
  SELECT st_union(geom)
  FROM fiumi 
  WHERE NAMN1 = 'Fiume Brenta'
  GROUP BY NAMN1
))

Metodo 2: con join

SELECT  comuni.comune,asgeojson(comuni.geom) as geojson 
FROM comuni
JOIN fiumi ON st_intersects(comuni.geom, fiumi.geom)
WHERE fiumi.NAMN1 = 'Fiume Brenta'
GROUP BY comune

Nota bene: Questa query non funzionerebbe in postgis poichè nel caso di raggruppamento Postgresql richiede di specificare una funzione di aggregazione per ogni campo