PostGIS, aggiungiamo dati geografici a PostgreSQL

Quando ci troviamo a dover salvare una grossa quantita’ di dati geografici abbiamo due opzioni: usare un database Oracle con il pacchetto aggiuntivo (entrambi a pagamento, molto a pagamento) oppure rimanere nel nostro mondo Open Source ed utilizzare PostgreSQL con PostGIS. E’ una soluzione comunque molto performante, tanto che e’ stata utilizzata anche da OpenStreetMaps, che oggi mantiene comunque l’uso di PostgreSQL. Viene utilizzato anche da PJ Hooker nel suo lavoro di Spatial Analysis. PJ ci ha scritto una guida introduttiva introduttiva all’uso dei database spaziali applicata a PostGIS. Come descritto in Wikipedia PostGIS, è un software open source che aggiunge il supporto per oggetti geografici al database PostgreSQL. PostGIS segue le caratteristiche semplici per specifiche SQL dal Open Geospatial Consortium (OGC). L’Open Source Geospatial Foundation (OSgeo), creata per sostenere lo sviluppo collaborativo di software open source geospaziale e promuovere la sua diffusione, inserisce PostGIS, tra le sue Geospatial Libraries. Una volta installato, secondo la comoda guida del sito ufficiale postgis.net con pochi semplici passaggi si trasforma un database in un geo-database:

createdb yourdatabase
createlang plpgsql yourdatabase
psql -d yourdatabase -f postgis.sql
psql -d yourdatabase -f spatial_ref_sys.sql

Viene riportato ad un esempio applicativo, che rende molto chiaro che si tratta di un software molto potente. Questo esempio si potrebbe intitolare “Street side generator” il tutto con sole due coppie di coordinate geografiche. Ecco l’esempio: Premessa: la logica dell’esempio funziona, ma bisogna intepretare i nomi con un po’ di sforzo, ovvero marcio, significa marciapiede… Premessa II: PostGIS non ha un vero e proprio viewer, ma si possono usare diversi software open source, per visualizzare le geometrie, come QGis, utilizzato in questo esempio. Lo schema della tabella che genera punti:

CREATE TABLE pt_prova (
gid serial NOT NULL, id INTEGER, seq_tratta INTEGER, id_tratta INTEGER,
buff INTEGER, marcio INTEGER, id_marcio INTEGER, buff_marci DOUBLE PRECISION,
side_marci CHARACTER VARYING(255), the_geom geometry,
CONSTRAINT pt_prova_pkey PRIMARY KEY (gid),
CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL),
CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = (-1))
)
WITH ( OIDS=FALSE );
ALTER TABLE pt_prova OWNER TO pjh;

La tabella (con informazioni geometriche) caricata in QGis, appare in questo modo. Questa geometria puntuale è l’unica cosa che devo disegnare! Deve essere creata una vista che converte i punti in linee: i punti hanno una colonna di sequenza e una colonna di riferimento alla tratta di appertenenza.

-- View: vpt_prova6
CREATE OR REPLACE VIEW vpt_prova6 AS
SELECT MIN(gps.gid) AS gid, MAX(gps.buff) AS buff, gps.id_tratta, st_makeline(gps.the_geom) AS the_geom
FROM ( SELECT pt_prova.gid, pt_prova.id, pt_prova.the_geom, pt_prova.seq_tratta, pt_prova.id_tratta, pt_prova.buff, pt_prova.marcio
FROM pt_prova
ORDER BY pt_prova.id_tratta, pt_prova.seq_tratta) gps
GROUP BY gps.id_tratta;
ALTER TABLE vpt_prova6 OWNER TO pjh;

Si crea un buffer attorno alle linee, ma prima bisogna preparare la linea in modo tale che contenga le informazioni necessarie per creare un buffer con ampiezza definita nella colonna (la massima espressa nei punti, ma si potrebbe fare una tabella a parte con l’elenco delle tratte e le varie proprietà) ed unico per ogni tratta in base al valore della tratta di ogni punto.

-- View: vpt_prova6
 
CREATE OR REPLACE VIEW vpt_prova6 AS
SELECT MIN(gps.gid) AS gid, MAX(gps.buff) AS buff, gps.id_tratta, st_makeline(gps.the_geom) AS the_geom
FROM ( SELECT pt_prova.gid, pt_prova.id, pt_prova.the_geom, pt_prova.seq_tratta, pt_prova.id_tratta, pt_prova.buff, pt_prova.marcio
FROM pt_prova
ORDER BY pt_prova.id_tratta, pt_prova.seq_tratta) gps
GROUP BY gps.id_tratta;
ALTER TABLE vpt_prova6 OWNER TO pjh;

Questo genera il buffer

-- View: vpt_prova_buff6
 
CREATE OR REPLACE VIEW vpt_prova_buff6 AS
SELECT a.gid, st_buffer(a.the_geom, a.buff::DOUBLE PRECISION) AS the_geom
FROM vpt_prova6 a;
ALTER TABLE vpt_prova_buff6 OWNER TO pjh;

Unisco i vari buffer, per creare poligoni consecutivi dissoluti, sia per una questione grafica, sia di calcolo dell’area, per evitare le sovrapposizioni.

-- View: vpt_prova_buff6u
 
CREATE OR REPLACE VIEW vpt_prova_buff6u AS
SELECT MIN(a.gid) AS gid, st_union(a.the_geom) AS the_geom
FROM vpt_prova_buff6 a;
ALTER TABLE vpt_prova_buff6u OWNER TO pjh;

Ora passiamo al marciapiede che può essere sul lato destro, su quello sinistro o su entrambe, per ora lo faccio solo da un lato, senza smussi; poi questo prevederà la creazione di una serie di viste, non solo di marciapiede o altro, ma suddivise in livelli, con specificato se marciapiede, pista, aiuola… Questo passaggio crea la linea solo per i punti che hanno l’attributo marciapiede.

-- View: vpt_prova6b
 
CREATE OR REPLACE VIEW vpt_prova6b AS
 
SELECT MIN(gps.gid) AS gid, MAX(gps.buff_marci) AS buff_marci, MAX(gps.buff) AS buff, gps.id_marcio, st_makeline(gps.the_geom) AS the_geom
 
FROM ( SELECT pt_prova.gid, pt_prova.id, pt_prova.seq_tratta, pt_prova.id_tratta, pt_prova.buff, pt_prova.marcio, pt_prova.id_marcio, pt_prova.buff_marci, pt_prova.side_marci, pt_prova.the_geom
FROM pt_prova
WHERE NOT (pt_prova.id_marcio = 0 AND pt_prova.side_marci::text = 'left'::text)
ORDER BY pt_prova.id_tratta, pt_prova.seq_tratta) gps
GROUP BY gps.id_marcio;
 
ALTER TABLE vpt_prova6b OWNER TO pjh;

Adesso provvedo a spostare la linea al di fuori del buffer della strada, più metà di quanto deve essere ampio questo marciapiede e uso finalmente ST_OffsetCurve

-- View: vpt_prova_off6b
 
CREATE OR REPLACE VIEW vpt_prova_off6b AS
SELECT f.gid, st_offsetcurve(f.the_geom, f.buff::DOUBLE PRECISION + f.buff_marci / 2::DOUBLE PRECISION, 'quad_segs=4 join=bevel'::text) AS the_geom, f.buff_marci
FROM vpt_prova6b f;
 
ALTER TABLE vpt_prova_off6b OWNER TO pjh;

Poi creo un “semplice” buffer

-- View: vpt_prova_buff6b
 
CREATE OR REPLACE VIEW vpt_prova_buff6b AS
SELECT a.gid, a.buff_marci, st_buffer(a.the_geom, a.buff_marci / 2::DOUBLE PRECISION, 'endcap=flat join=round'::text) AS the_geom
FROM vpt_prova_off6b a;
 
ALTER TABLE vpt_prova_buff6b OWNER TO pjh;

Questa immagine è solo per capire che il buffer del marciapiede non è coperto e risulta essere giusto solo graficamente, ma è proprio geometricamente quello che deve essere… Risultato: Conclusione: PostGIS, per la gestione dei dati geografici, ha delle potenzialità enormi e mette a disposizione circa 500 funzioni, che si applicano, come in questo caso, in modo immediato e con una certa semplicità. Si può usare PostGIS non solo per la costruzione grafica di cartografia, ma forse questa è soltanto la verifica della potenza del risultato; il risultato grafico è spesso considerato, l’output più difficile da rappresentare con query e tabelle, ma prima di arrivare a questo si passa da centinaia di analisi che costituiscono le basi per creare scenari territoriali/ambientali.

Info about author

Alessandro Lorenzi

RHCE Consultant at Extraordy