Alcuni esempi di codice Python per creare, riempire ed interrogare una

PostGIS

Di seguito viene mostrato come creare, riempire e interrogare una tabella in un database PostgreSQL, utilizzando Python.

Per poter eseguire qualsiasi operazione all'interno di un database, i passi da seguire sono:

ˆ eettuare la connessione al database

ˆ richiamare i comandi SQL per creare, modicare o interrogare le tabelle ˆ confermare tutte le modiche apportate al database.

Connessione al database La connessione al database viene creata attraverso il metodo 'connect', contenuto nella libreria di Python psycopg2. Per poter utilizzare il contenuto di una libreria è necessario che essa venga importata nell'interprete Python con il seguente comando:

I parametri necessari per eseguire la connessione sono il nome del database ('My_data base'), l'utente ('utente') e la password ('pw') per l'accesso:

conn = psycopg2.connect("dbname=My_database user=utente password=pw")

Inne, per poter eseguire operazioni e ottenere dati dalle tabelle, dev'essere creato l'oggetto 'cursore':

cur = conn.cursor()

Creazione, modica e interrogazione di una tabella Qualsiasi operazione al- l'interno del database viene eettuata richiamando delle istruzioni SQL.

Come esempio, viene commentato il codice utilizzato per caricare all'interno di un database PostgreSQL i dataset dei punti dei GDEM.

I le dei punti contengono tre colonne: coordinata est, coordinata nord, quota.

Ogni tabella è stata strutturata in modo da associare a ciascun punto una chiave primaria, che lo identichi in maniera univoca e il campo contenente la geometria, così da denirlo come entità puntuale tridimensionale.

Per la creazione della tabella devono essere specicati: il nome della tabella (SRTM_v2 ) ed i nomi dei campi con il tipo di dati contenuto; in questo caso viene creato un solo campo id che deve contenere dati di tipo intero (INTEGER); il comando è contenuto nella variabile chiamata sql:

sql = "CREATE TABLE SRTM_v2 (id SERIAL PRIMARY KEY)"

Per eseguire il comando, viene richiamato il metodo execute:

cur.execute(sql)

Execute() è il metodo associato all'oggetto cur, che esegue una operazione sul database, in questo caso la creazione della tabella SRTM_v2 con la colonna id.

La creazione di una colonna Geometry non è equivalente ad una colonna normale, dal momento che deve contenere tutte le informazioni necessarie per denire gli oggetti come entità spaziali. Si deve utilizzare la funzione spaziale AddGeometryColumn(), specicando:

1. il nome della tabella: SRTM_v2

2. il nome della colonna di tipo geometrico: in questo caso, geom 3. il codice SRID da usare: 32632

CAPITOLO 6. GLI STRUMENTI UTILIZZATI 69 4. la classe di geometria voluta: POINT

5. la dimensione del modello: in questo caso, 3D

sql = "SELECT AddGeometryColumn(SRTM_v2, 'geom', 32632, 'POINT', 3)" cur execute(sql)

Ora è possibile inserire nella tabella i dati relativi ai punti. Per riempire il campo 'geom', dev'essere utilizzata la funzione 'GeomFromText()' che permette di creare l'en- tità punto denito dalla dimensione ('POINT(X Y Z)') e dal sistema di riferimento (32632). A titolo di esempio, per caricare il punto P(100, 100, 100), il comando è:

sql = "INSERT INTO SRTM_v2 (geom)"

sql +="VALUES (ST_GeomFromText('POINT(%f %f %f)', f))" %(100, 100, 100, 32632) cur.execute(sql)

La stringa che viene eseguita è:

INSERT INTO SRTM_v2(geom) VALUES (ST_GeomFromText('POINT(100 100 100)', 32632))

Per generalizzare ed inserire nella tabella SRTM_v2 un elenco di punti, contenuto ad esempio in un le .csv, come quello riportato in gura 6.3, le istruzioni necessarie sono:

f = open('SRTM_v2.csv', 'r') (apertura del le csv in lettura) while 1:

line = f.readline() (lettura riga per riga del le) if not line:

break

cols = line.split(';') (lettura delle coordinate di una riga)

if math.isnan(float(cols[0]))==True: (individuazione della presenza di valori NaN) pass

else:

sql="INSERT INTO SRTM_v2 (geom)" (riempimento della tabella)

sql+="VALUES (ST_GeomFromText('POINT(%f %f %f)', %f))" %(cols[0], cols[1], cols[2], 32632)

cur.execute(sql) f.close() (chiusura del le csv) cur.close()

L'istruzione INSERT INTO (...) VALUES (...) fa esattamente quello che dice il suo nome: la prima lista elenca le colonne per nome (geom), mentre la seconda lista con- tiene i valori da inserire; la corrispondenza fra colonne e valori è data dalla posizione. Questa tabella dichiara un SERIAL PRIMARY KEY per la colonna id: come crite- rio generale non viene passato nessun valore, in modo che sia PostgreSQL a generare automaticamente il valore corretto.

L'ultimo passaggio che deve essere eseguito, trattandosi di dati spaziali, è la creazione dello spatial index, indispensabile per eettuare qualunque interrogazione in modo ve- loce. I parametri che devono essere passati alla funzione sono il nome della tabella ed il nome della colonna contenente la geometria:

sql = "SELECT CreateSpatialIndex(SRTM_v2, 'geom')" cur.execute(sql)

Il risultato di quanto illustrato nora è rappresentato nell'immagine seguente. Nella parte sinistra è riportato un esempio di le di punti e sulla destra la tabella PostgreSQL corrispondente. Nel campo 'geom' l'entità punto è rappresentata nella forma di una BLOB geometry.

Figura 6.3: Esempio di tabella PostgreSQL contenente punti 3D. Nella prima colonna è contenuto l'id di ciascun punto, nella seconda colonna le coordinate, attraverso la denizione della geometria

La tabella può essere interrogata, ad esempio per estrarre le coordinate di un particolare punto, in questo caso il vertice con id=10:

sql = "SELECT ST_X(geom), ST_Y(geom), ST_Z(geom) FROM SRTM_v2 WHERE id = 10" cur.execute(sql)

Oppure per estrarre tutti i punti che cadono dentro un poligono, attraverso l'inter- sezione delle geometrie. Il poligono ha come vertici: [(499860, 4970635),(499960, 4970635),(499960, 4970735),(499860, 4970735)]

CAPITOLO 6. GLI STRUMENTI UTILIZZATI 71

sql="SELECT * FROM SRTM_v2 WHERE geom && 'POLYGON((499860 4970635, 499960 4970635, 499960 4970735, 499860 4970735, 499860 4970635))'"

sql+="AND ST_INTERSECTS(geom, ST_GeomFromText('POLYGON((499860 4970635, 499960 4970635, 499960 4970735, 499860 4970735, 499860 4970635))', 32632))

cur.execute(sql)'"

Per generalizzare ed utilizzare un poligono a partire dall'elenco dei suoi vertici, è neces- sario creare un array bidimensionale contenente le coppie di coordinate di ciascun pun- to (vertici_array). Quindi gli elementi dell'array devono essere passati alla geometria POLYGON nella forma [X1 Y1, X2 Y2, ...]:

nv=vertici_array.shape[0] (restituisce il numero di righe dell'array) sql="SELECT * FROM SRTM_v2 WHERE geom && 'POLYGON (("

for i in range(nv):

sql+="%.3f %.3f" % tuple(vertici_array[i,:]) if i< nv-1:

sql+="," sql+="))', 32632))"

sql+="AND ST_INTERSECTS(geom, ST_GeomFromText('POLYGON((" for i in range(nv): sql+="%.3f %.3f" % tuple(vertici_array[i,:]) if i< nv-1: sql+="," sql+="))', 32632))" cur.execute(sql)

Salvataggio delle modiche apportate al database Una volta eseguite tutte le operazioni su un database, per renderle eettive devono essere salvate, dopodiché può essere chiusa la connessione:

conn.commit() (salvataggio)

conn.close() (chiusura della connessione)