PostGIS

PostGIS (URL storico: PostGIS) e' l'estensione geografica al DBMS object-relational PostgreSQL che consente di memorizzare sulla base dati oggetti di tipo GIS (Geographic Information System) e di interrogarli. PostGIS logo (trac)

PostGIS implementa ed estende le funzioni definite dallo standard OGC. PostGIS supporta oggetti geometrici e geografici, indici spaziali GiST ed un numero molto elevato di funzioni per l'analisi e il processing di oggetti GIS. Le caratteristiche object-oriented di PostgreSQL consentono di estenderlo in modo semplice, per questo e' stato scelto come ospite per PostGIS. Molte funzioni PostGIS sono implementate in plpgsql.

Questo documento ha un approccio pratico con molti esempi di comandi e query SQL. Questo documento presenta diversi aspetti del PostGIS: Installazione, Primi passi, OpenGis Consortium (OGC), Creazione oggetti geometrici e geografici, Query geografiche, Cenni sulle prestazioni, Versioni ed aggiornamenti, ...
La pagina e' stata scritta utilizzando le versioni 1.5 e 2.0 di PostGIS con le versioni 9.x di PostgreSQL su Linux (CentOS 5.x) per x86_64 ma e', mutatis mutandis, valido anche per le altre versioni [NdE 1Q2024: la versione di produzione piu' recente di PostGIS e' la 3.2].

Un documento introduttivo su PostgreSQL e' Introduzione a PostgreSQL, maggiori dettagli sono nel documento Qualcosa in piu' su PostgreSQL, dettagli sulle statistiche prestazionali sono presentati in Statistiche prestazionali su PostgreSQL, infine sul partizionamento e' presente un capitolo in DIVIDE ET IMPERA !

Installazione

L'installazione di PostGIS ora e' semplice... soprattutto con gli RPM!

rpm -il postgis90-9.0.7-1PGDG.rhel5.x86_64.rpm su - postgres cd /usr/pgsql-9.0/share/contrib/postgis-1.5 createdb PGIS_01 # e' facile farlo anche con pg-Admin createlang plpgsql PGIS_01 # di default e' gia' installato nella 9.x psql -d PGIS_01 -f postgis.sql psql -d PGIS_01 -f spatial_ref_sys.sql # Per abilitare tutti gli utenti a creare oggetti GIS e' inoltre necessario: psql -d PGIS_01 GRANT select, insert, update, delete ON TABLE public.geometry_columns TO public; GRANT select ON TABLE public.spatial_ref_sys to public; ^D

Ancora piu' semplice la configurazione con PostgreSQL 9.1 o superiore e PostGIS 2.0. Nessun lancio manuale di script SQL e' sufficiente il comando
  create extension postgis;

Tipicamente vengono anche installate le topologie con:
  create extension postgis_topology;

Come verificare la versione di PostGIS installata? Semplice:

SELECT PostGIS_Version();

Un tempo si partiva dai sorgenti disponibili sul sito ufficiale eseguendo la compilazione con i passi ./configure; make; make install ma la difficolta' era sopratutto raccogliere i pacchetti o i sorgenti dei diversi componenti prerequisiti: Proj, GEOS, GDAL, ... e risolvere tutti gli errori di compilazione.
Utilizzando repository ed extension e' tutto molto piu' semplice!

Primi passi

PostGIS introduce alcuni data type (eg. geometry e geography), due meta-tabelle e le funzioni (oltre 500) per gestire gli oggetti GIS.
L'utilizzo di PostGIS e' semplicemente... SQL:

CREATE TABLE testgg(gid serial PRIMARY KEY, name varchar(16), location geography(POINT,4326) ); INSERT INTO testgg (name, location) VALUES ('TORINO', ST_GeographyFromText('SRID=4326;POINT(45.07055 7.686653)') ); INSERT INTO testgg (name, location) VALUES ('MILANO', ST_GeographyFromText('SRID=4326;POINT(45.631 8.724)') ); SELECT trunc(ST_Distance(c1.location, c2.location)/1000) km FROM testgg c1, testgg c2 WHERE c1.name='TORINO' and c2.name='MILANO';

Per rispettare lo standard OGC il datatype geometry deve essere aggiunto in modo esplicito ad una tabella:

CREATE TABLE testgm (ID int4, NAME varchar(64) ); SELECT AddGeometryColumn('', 'testgm', 'geom', -1, 'LINESTRING',2); INSERT INTO testgm (id, name, geom) VALUES ( 1, 'Esempio geometrico', ST_GeomFromText('LINESTRING(2 2, 4 2, 4 4, 2 4, 2 2)',-1)); SELECT ST_Length(geom) FROM testgm;

Nei prossimi paragrafi approfondiremo con maggior dettaglio quanto visto con questi semplici esempi.

OGC

L'OGC (OpenGis Consortium) e' un organismo che defnisce gli standard per i sistemi GIS. Le specifche OpenGIS prevedono due modi standard per definire spatial objects: Well-Known Text (WKT) e Well-Known Binary (WKB).

PostGIS implementa tutte le Simple Features for SQL di OGC. PostGIS implementa tutti i tipi di oggetti, tutte le funzioni e le due meta-tabelle standard (SPATIAL_REF_SYS, GEOMETRY_COLUMNS) previste dallo standard. PostGIS estende lo standard per gestire coordinate 3DZ, 3DM, 4D e trattare come embedded l'SRID (spatial referencing system identifer).

Esempi di GEOMETRY in WKT:

POINT(0 0) LINESTRING(0 0, 1 1, 1 2) LINEARRING(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))

Esistono parecchi sistemi di coordinate differenti... Sono necessari due elementi per definire un sistema completo: il riferimento (datum) ed il sistema di coordinate. Il sistema di coordinate piu' diffuso e' l'UTM: Universal Transverse Mercator. Per i poli si utilizza l'UPS: Universal Polar Stereographic, ... Tra i datum piu' utilizzati vi sono il WGS84 (World Geodedic System 1984, utilizzato dai GPS), NAD83, NAD27, ED50, ... PostGIS consente la conversione tra tutti i sistemi di coordinate descritti nella meta-tabella SPATIAL_REF_SYS (circa 3000). Il sistema di coordinate utilizzato per gli oggetti geografici e' WGS84 (con SRID=4326).
L'evoluzione di PostGIS e dei sistemi geografici aperti e' continua e su molteplici fronti. Sia come precisione dei dati sia come disponibilita' di sorgenti pubbliche di dati geografici.

Creazione di oggetti geometrici ed oggetti geografici

Gli oggetti geometrici e geografici vengono creati con normali comandi DDL e DML dell'SQL:

CREATE TABLE testgm (ID int4, NAME varchar(64) ); SELECT AddGeometryColumn('', 'testgm', 'geom', -1, 'LINESTRING',2); CREATE INDEX testgm_gix ON testgm USING GIST ( geom ); INSERT INTO testgm (ID, NAME, GEOM) VALUES ( 1, 'Esempio geometrico', ST_GeomFromText('LINESTRING(2 3,4 5,6 5,7 8)',-1)); CREATE TABLE citta ( id SERIAL PRIMARY KEY, name VARCHAR(64), location GEOGRAPHY(POINT,4326) ); CREATE INDEX citta_gix ON citta USING GIST ( location ); INSERT INTO citta (name, location) VALUES ('TORINO', ST_GeographyFromText('SRID=4326;POINT(45.07055 7.686653)') ); INSERT INTO citta (name, location) VALUES ('PARIGI', ST_GeographyFromText('SRID=4326;POINT(48.856 2.352)') ); -- Distanza tra Parigi e l'ortodromica Milano-New York: non banale come calcolo! SELECT ST_Distance('LINESTRING(45.631 8.724, 40.644 -73.782)' ::geography, location)/1000 km FROM citta WHERE name='PARIGI';

Query geografiche

Questi sono esempi classici con calcolo di distanze, di aree e l'uso di funzioni di gruppo:

SELECT sum(ST_Length(the_geom))/1000 AS km_roads FROM bc_roads; SELECT ST_Area(the_geom)/10000 AS hectares FROM bc_municipality WHERE name = 'ACQUI TERME'; SELECT name, ST_Area(the_geom)/10000 AS hectares FROM bc_municipality ORDER BY hectares DESC LIMIT 5; SELECT sum(ST_Length(r.the_geom))/1000 AS kilometers FROM bc_roads r, bc_municipality m WHERE r.name = 'STRADA MONGRENO' AND m.name = 'TORINO' AND ST_Contains(m.the_geom, r.the_geom);

Cenni sulle prestazioni

Una volta installato PostGIS puo' essere utilizzato immediatamente. Rispetto ad una configurazione standard PostgreSQL non vi sono parametri specifici per PostGIS su cui effettuare il tuning. Naturalmente un processore veloce ed una grande quantita' di memoria consentono buone prestazioni anche con DB di grandi dimensioni e query complesse.

I principali parametri di tuning di PostgreSQL sono riportati in questa pagina.
Per un sistema Linux con 4GB di RAM dedicato a PostgreSQL una configurazione iniziale (da impostare nel file postgresql.conf) e' la seguente: 


listen_addresses = '*'   	
max_connections = 100                   
shared_buffers = 1024MB      
work_mem = 8MB
maintainence_work_mem = 64MB
wal_buffers = 16MB                     
checkpoint_segments = 10           
checkpoint_completion_target = 0.9   
effective_cache_size = 2048MB

PostgreSQL ha un supporto molto completo per le diverse tecnologie di indicizzazione. Gli indici maggiormente utilizzati sono i B-tree (indici bilanciati), comuni in effetti su tutte le basi dati relazionali. Sono ovviamente supportati in PostgreSQL indici univoci e gli indici concatenati su piu' chiavi. Oltre agli indici B-tree in PostgreSQL sono disponibili anche gli indici Hash, GiST (Generalized Search Tree), GIN (Generalized Inverted Index), BRIN, ...

Gli indici tradizionali (B-tree) non sono adatti per le ricerche spaziali poiche' sono basati su una sola dimensione. PostGIS utilizza gli indici R-tree GIN o GiST.
Per rendere efficienti le ricerche e le funzioni si utilizza la tecnica di selezionare gli oggetti interessati mediante un bounding box. Nelle versioni piu' recenti di PostGIS tale tecnica viene applicata automaticamente. E' comunque importante provvedere ad indicizzare correttamente le colonne su cui avvengono le interrogazioni. Riassumendo... bisogna creare un indice in questo modo:
  CREATE INDEX citta_gix ON citta USING GIST (location);

Durante il caricamento i dati introdotti non vengono validati per default poiche' e' computazionalmente pesante, e' pero' possibile farlo esplicitamente con la ST_validate() lanciando uno statement SQL o con un trigger.

Nelle vecchie versioni (eg. 7.x) di PostgreSQL era necessario far ricalcolare le statistiche per gli indici geografici con:
  SELECT UPDATE_GEOMETRY_STATS();
Dalla 8.x e' sufficiente un VACUUM ANALYZE. Dalla 9.x tutto avviene automaticamente (ma si puo' sempre lanciare il comando di VACUUM se si e' impazienti di aggiornare le statistiche)!

Mantenersi aggiornati con le versioni e' sempre importante... [NdE: serve a rimanere giovani e tutto e' piu' veloce :-]

Versioni ed upgrade

Mantenersi aggiornati con le versioni e' sempre importante... [NdE: serve a rimanere giovani e tutto e' piu' veloce :-]
Questo vale anche per PostGIS e per il suo ospite: il database PostgreSQL.

Un elenco completo delle versioni di PostGIS ed i riferimenti a tutta la documentazione e' mantenuto nella pagina Your server stinks!. Una sezione apposita e' relativa a PostgreSQL. Ecco una delle tabelle contenute nel documento:

(Source: Your server stinks)

PostGIS
PostgreSQL 1.3 1.4 1.5 2.0 2.1 2.2 2.3 2.4 2.5 3.0 3.1 3.2
7.2-7.4 Yes No No No No No No No No No No No
8.0 Yes No No No No No No No No No No No
8.1 Yes No No No No No No No No No No No
8.2 Yes Yes No No No No No No No No No No
8.3 Yes Yes Yes No No No No No No No No No
8.4 Yes Yes Yes Yes No No No No No No No No
9.0 No No Yes Yes Yes No No No No No No No
9.1 No No Yes Yes Yes Yes No No No No No No
9.2 No No Yes Yes Yes Yes No No No No No No
9.3 No No No No Yes Yes Yes Yes No No No No
9.4 No No No No Yes Yes Yes Yes Yes No No No
9.5 No No No No No Yes Yes Yes Yes Yes No No
9.6 No No No No No Yes Yes Yes Yes Yes Yes Yes
10 No No No No No No Yes Yes Yes Yes Yes Yes
11 No No No No No No No Yes Yes Yes Yes Yes
12 No No No No No No No No Yes Yes Yes Yes
13 No No No No No No No No No Yes Yes Yes
14 No No No No No No No No No No Yes Yes

La versione 9.1 di PostgreSQL introduce ottimizzazioni specifiche per query KNN-GiST e la configurazione tramite extension. La versione di PostgreSQL (9.2) desupporta PostGIS 1.5 e quindi e' compatibile solo con la versione 2.0 di PostGIS [NdA anche se successivamente e' stata resa disponibile anche la 1.5]. PostGIS 2.0 a sua volta richiede un'ampia serie di pacchetti sul sistema operativo (eg GDAL: Geospatial Data Abstraction Library, libreria che consente la gestione dei formati raster). Ma sono molte le ottimizzazioni e le novita': 3D e raster!
La versione 2.1 di PostGIS e' stata rilasciata con rilascio con la versione 9.3 di PostgreSQL. Oltre ad alcune nuove funzioni sono state realizzate molte ottimizzazioni del codice che promette di essere significativamente piu' veloce nelle ricerche di tipo geografico.

Gli upgrade, tra versioni recenti e vicine tra loro, sono tipicamente semplici (Soft Upgrade) e non richiedono altro che un'upgrade dell'EXTENSION (eg. ALTER EXTENSION postgis UPDATE; ALTER EXTENSION postgis_topology UPDATE;). In particolare questo vale per tutte le versioni 2.x; e' anche possibile indicare una versione specifica con ALTER EXTENSION postgis UPDATE TO '2.5.5' Dalla 2.5 e' passare all'ultima versione disponibile anche con una funzione SQL: SELECT postgis_extensions_upgrade(); .

Con le vecchie versioni? Dovevano essere lanciati appositi script di upgrade...
Piu' complessi sono gli Hard Upgrade tra major version differenti di PostGIS che richiedono un dump specifico (eg. pg_dump -Fc -b -v -f "/backup/bck_cust_pgis.bck" OLD_DB), un'installazione ex-novo, l'adeguamento del backup con lo script postgis_restore.pl ed il restore (eg. perl /usr/pgsql-9.2/share/contrib/postgis-2.0/postgis_restore.pl "/backup/bck_cust_pgis.bck" | psql NEW_DB 2&> err.txt).

Il futuro? Per 2Q2024 attendiamo la versione 3.5 di PostGIS e la 17 di PostgreSQL!

Titolo: PostGIS
Livello: Medio (2/5)
Data: 1 Aprile 2008
Versione: 1.0.6 - 14 Febbraio 2024 ❤️
Autore: mail [AT] meo.bogliolo.name