rigacci.org

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== PostGIS su Debian ======

===== PostGIS 2.3 =====

Distribuzione di riferimento: **Debian GNU/Linux** Stretch con **PostgreSQL 9.6** e **PostGIS 2.3.1**.

Pacchetti installati:

  * **postgresql-9.6**
  * **postgresql-9.6-postgis-2.3**
  * **postgresql-9.6-postgis-2.3-scripts**
  * **postgresql-contrib-9.6**
  * **postgresql-client-9.6**

==== Creazione di un database geografico ====

È necessario collegarsi al database con i privilegi di database administrator:

<code>
# su - postgres
> psql
postgres=#
</code>

quindi si crea un **utente** e un **database** su cui l'utente ha pieni poteri. Dopo essersi connessi al database si crea al suo interno le estensioni **PostGIS**:

<code>
CREATE USER "strade" PASSWORD 'MySecret';
CREATE DATABASE strade OWNER strade
    TEMPLATE template0 LC_COLLATE = 'it_IT.UTF-8' LC_CTYPE = 'it_IT.UTF-8'
    ENCODING 'UTF8';
\connect strade
CREATE EXTENSION postgis;
</code>

A differenza delle vecchie versioni di PostGIS (es. la 1.3), non c'è da impazzire con i permessi da assegnare alle tabelle speciali. La **''spatial_ref_sys''** (che contiene le definizioni dei sistemi di riferimento) va bene che sia in sola lettura, la **''geometry_columns''** e la **''geography_columns''** sono adesso delle semplici VIEW.
==== Migrazione da PostgreSQL 9.4/PostGIS 2.1 ====

La **migrazione** di un database dalla vecchia versione **postgresql-9.4-postgis-2.1** alla nuova **postgresql-9.6-postgis-2.3** è relativamente semplice, il tutto si risolve nell'esecuzione di **pg_dump** sulla vecchia versione e **pg_restore** sulla nuova.

Nelle versioni precedenti di PostGIS (es. la 1.3) c'era da impazzire con gli **oggetti creati dall'estensione PostGIS**; in pratica le funzioni, gli operatori, i tipi dati, ecc. venivano regolarmente salvati nel dump, ma al momento del restore **bisognava escluderli** perché altrimenti andavano in conflitto con gli analoghi già presenti nel nuovo database, in cui era stata creata l'estensione del nuovo PostGIS.

Con la versione 2.x di PostGIS il dump conterrà solo i dati utente e il restore è molto semplificato. Sulla vecchia installazione si esegue il **dump** del database:

<code>
su - postgres
pg_dump -Fc strade > strade.dump
</code>

Sulla nuova installazione si esegue il **restore** a nome dell'utente proprietario del nuovo database (utente non privilegiato). Nel nostro esempio il vecchio database e il nuovo coesistono sullo stesso host, la nuova istanza quindi gira sulla porta **5433/TCP**:

<code>
pg_restore --port 5433 -U strade -W -h 127.0.0.1 -d strade strade.dump
</code>

Poiché l'estensione PostGIS è stata attivata dal database administrator, il restore effettuato come utente non privilegiato genera degli errori:

<code>
pg_restore: [archiver (db)] Error from TOC entry 3407; 0 0 COMMENT EXTENSION plpgsql 
pg_restore: [archiver (db)] Error from TOC entry 3408; 0 0 COMMENT EXTENSION postgis 
pg_restore: [archiver (db)] Error from TOC entry 3199; 0 88843 TABLE DATA spatial_ref_sys postgres
</code>

sono errori che possono essere ignorati, poiché le estensioni 'plpgsql' e 'postgis' e la tabella ''spatial_ref_sys'' non devono essere in realtà recuperati dal vecchio dump.
===== PostGIS 1.3 =====

Distribuzione di riferimento: **Debian GNU/Linux** Lenny con **PostgreSQL 8.3** e **PostGIS 1.3.3**.

Pacchetti installati:

  * **postgresql-8.3**
  * **postgresql-client-8.3**
  * **postgis**
  * **postgresql-8.3-postgis**

**NOTA**: Alcune operazioni eseguite su PostgreSQL (creare un database, attivare il linguaggio PL/pgSQL, creare le estensioni PostGIS) richiedono l'account di amministratore del database, generalmente l'account //postgres//. Con l'installazione predefinita di Debian è sufficiente operare come utente di sistema //postgres// (da ''root'' effetturare ''su - postgres''), in quanto il database effettua l'autenticazione ''ident sameuser''.

Per gli esempi che seguono si presuppone che PostgreSQL accetti anche l'autenticazione con password (md5) per chi si connette via TCP/IP (host), aggiungere al file ''/etc/postgresql/pg_hba.conf'' una riga del tipo:

<file>
host all all 127.0.0.1 255.255.255.255 md5
</file>

Se si desidera connettersi da altri host sulla rete locale specificare l'indirizzo network e relativa netamask oltre all'indirizzo localhost 127.0.0.1/255.255.255.255.

===== Riferimenti web =====

  * [[http://postgis.refractions.net/docs/|Manuale PostGIS]]
  * [[http://postgis.refractions.net/docs/reference.html|PostGIS functions reference]]
  * {{postgis_quickguide.pdf|PostGIS 1.3 Cheat Sheet}} from [[http://www.bostongis.com/|Boston GIS]]
  * [[http://www.postgresql.org/docs/current/static/index.html|Manuale PostgreSQL]]

===== Creazione di un database geografico e di un template_gis =====

Queste le istruzioni per **PostgreSQL 8.4** e **PostGIS 1.5** su una Debian Squeeze. Dal prompt dei comandi SQL, come utente //postgres// (amministratore del database):

<code>
CREATE DATABASE template_gis TEMPLATE template0 LC_COLLATE = 'it_IT.UTF-8' LC_CTYPE = 'it_IT.UTF-8';
\connect template_gis
CREATE LANGUAGE plpgsql;
\i /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
\i /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
UPDATE pg_database SET datistemplate='t' WHERE datname = 'template_gis';
</code>

Con **PostgreSQL 9.1** di su Debian Wheezy c'è qualche differenza:

<code>
CREATE DATABASE template_gis TEMPLATE template0 LC_COLLATE = 'it_IT.UTF-8' LC_CTYPE = 'it_IT.UTF-8';
\connect template_gis
\i /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
\i /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
UPDATE pg_database SET datistemplate='t' WHERE datname = 'template_gis';
</code>

In **alternativa**, alcune operazioni possono essere eseguite da **shell Unix**:

<code>
su - postgres
createdb --template template0 template_gis
createlang plpgsql template_gis
psql -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql -d template_gis
psql -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql -d template_gis
</code>

Per trasformare il database in un template ci si collega all'SQL (eseguendo **''psql''**) e si esegue:

<code sql>
UPDATE pg_database SET datistemplate='t' WHERE datname = 'template_gis';
</code>

Con **PostgreSQL 9.4** e **PostGIS 2.1** (Debian Jessie) diventa tutto più semplice, al punto che creare un template_gis è sostanzialmente inutile perchè per abilitare spazialmente un database è sufficiente il comando ''CREATE EXTENSION'':

<code sql>
CREATE DATABASE strade OWNER strade TEMPLATE template0 
    LC_COLLATE = 'it_IT.UTF-8' LC_CTYPE = 'it_IT.UTF-8';
\connect strade
CREATE EXTENSION postgis;
</code>

La tabella **''spatial_ref_sys''** viene creata come in precedenza, mentre la **''geometry_columns''** e la **''geography_columns''** sono adesso delle semplici VIEW.

Se si intende effettuare il restore di vecchi dump, potrebbe essere necessario creare i nomi di alcune funzioni ormai obsolete. Ad esempio il restore di questa tabella fallisce

<code sql>
CREATE TABLE luoghi (
    id integer NOT NULL,
    wpt geometry,
    CONSTRAINT enforce_dims_wpt CHECK ((ndims(wpt) = 2))
);
</code>

perché la funzione **''ndims()''** non esiste più, essendo stata sostituita **''ST_ndims()''**. In questo caso si può definire le funzioni //legacy// eseguendo l'apposito script:

<code>
\connect strade
\i /usr/share/postgresql/9.4/contrib/postgis-2.1/legacy.sql
</code>

Esiste anche lo script **''uninstall_legacy.sql''**, ma per poterlo eseguire con successo si deve ovviamente aggiornare tutte le occorrenze degli oggetti legacy.
==== Creazione del linguaggio ====

Creare un linguaggio significa che sarà possibile aggiungere a quel database funzioni o trigger scritti in quel linguaggio. Questa operazione è necessaria perché PostGIS comprende funzioni scritte in PL/pgSQL.
In generale aggiungere un linguaggio ad un database richiede privilegi di superutente (//postgres//) e va fatto per ogni database che lo necessita.

Per default PostgreSQL aggiunge il linguaggio con la clausola ''TRUSTED'', ciò significa che il linguaggio è considerato sicuro (non consente di aggirare i meccanismi di protezione) e quindi potrà essere utilizzato anche dagli utenti non privilegiati. Controllare lo stato di trust del linguaggio con il il comando ''createlang -l dbname''. Vedere il manuale SQL a proposito di ''CREATE LANGUAGE''.

Anche lo script ''lwpostgis.sql'' deve essere eseguito con i privilegi di superutente (//postgres//). Il motivo è che vengono definite diverse funzioni di tipo ''LANGUAGE 'C''' fornite dalla shared library **''/usr/lib/postgresql/8.3/lib/liblwgeom.so.1.3''**; agganciare un eseguibile esterno ad una funzione PostgreSQL è consentito al solo superutente.

==== Le tabelle geometry_columns e spatial_ref_sys ====

Oltre alle funzioni vengono create anche le due tabelle: ''**geometry_columns**'' e ''**spatial_ref_sys**'' (e due relativi indici ''geometry_columns_pk'' e ''spatial_ref_sys_pkey''). Questi oggetti appena creati appartengono all'utente //postgres// e quindi è un problema far usare il database o farne fare un dump all'utente non privilegiato. Si cambia proprietario alle tabelle (e contestualmente agli indici) con le istruzioni ''ALTER TABLE'' viste sopra.

Per consentire all'utente non privilegiato di lavorare, sarebbe sufficiente garantire la ''SELECT'' sulla tabella ''spatial_ref_sys'' e garantire tutti i privilegi (''ALL'') sulla ''geometry_columns''. Vedere il manuale SQL a proposito di ''GRANT''.

Nella tabella ''spatial_ref_sys'' sono memorizzate le caratteristiche dei vari sistemi di riferimento spaziale (usata ad esempio per fare le conversioni) e in generale verrà usata in sola lettura. I dati vengono caricati dallo script ''spatial_ref_sys.sql'' fornito con PostGIS.

La tabella ''geometry_columns'' contiene informazioni sui campi geometrici creati nel database. Le funzioni PostGIS, ad esempio la ''AddGeometryColumn()'', modificano i record in questa tabella. Le funzioni rese disponibili da PostGIS si elencano dalla shell ''psql'' con il comando ''\df'', ma sono mescolate a quelle predefinite. L'unica differenza è che appartengono allo schema ''public'' invece che allo schema ''pg_catalog''.

==== I nomi delle funzioni PostGIS ====

Le funzioni fornite da PostGIS hanno in genere il nome che inizia con il prefisso **ST_** (spatial type). Per compatibilità con le vecchie versioni di PostGIS alcune funzioni sono disponibili anche con il vecchio nome, senza prefisso. Tuttavia il vecchio nome viene considerato deprecato e potrebbe essere rimosso nelle future versioni.

===== Creazione di un database geografico =====

A partire dal template creato al paragrafo precedente si crea un database geografico. Per semplicità di amministrazione **si crea un utente** che sarà **proprietario del database**:

<code sql>
CREATE USER dbuser PASSWORD 'secret';
CREATE DATABASE dbname OWNER dbuser TEMPLATE template_gis;
\connect dbname
GRANT ALL ON geometry_columns TO dbuser;
GRANT SELECT ON spatial_ref_sys TO dbuser;
GRANT SELECT ON geography_columns TO dbuser;
</code>

===== Creazione di una tabella con campo geografico =====

==== Primo esempio ====

Creazione di una tabella per memorizzare waypoint e inserimento di un record:

<code sql>
psql -h 127.0.0.1 -U strade -W strade
strade=> CREATE TABLE waypoints (
           id          SERIAL,
           elevation   float,
           name        varchar(6),
           comment     varchar(20),
           description varchar(50),
           idsymbol    integer
         );
strade=> SELECT AddGeometryColumn('waypoints','wpt',4326,'POINT',2);
strade=> INSERT INTO waypoints (elevation, name, idsymbol, wpt)
         VALUES (46.749512, 'NICCO', 2, ST_GeomFromText('POINT(11.097390857 43.858187562)',4326));
</code>

Nella funzione ''AddGeometryColumn()'' il parametro 4326 è lo SRID (spatial referencing system identifier), in questo caso corrisponde al datum [[wp>WGS84|WGS84]]) utilizzato dal GPS. Deve corrispondere allo ''srid'' di un record in ''spatial_ref_sys''. L'ultimo parametro è la dimensione, in questo caso si lavora sul piano in due dimensioni. Una colonna di tipo geometrico deve essere aggiunta con la funzione ''AddGeometryColumn()'', perché essa provvede a memorizzare informazioni supplementari in ''geometry_columns''.

Per visualizzare il contenuto di un campo geometrico conviene usare la funzione **''ST_AsText()''** (altrimenti si ottiene la rappresentazione binaria dell'oggetto, di scarsa comprensibilità per gli esseri umani):

<code sql>
SELECT id, name, ST_AsText(wpt) FROM waypoints;
</code>

Per rimuovere da una tabella una colonna di tipo geometry:

<code sql>
strade=> SELECT DropGeometryColumn('', 'waypoint','wpt');
</code>

==== Secondo esempio ====

Creare una tabella con il vettoriale delle strade, ogni record di questa tabella contiene un //track segment// in un campo di tipo ''**LINESTRING**'':

<code sql>
CREATE TABLE tracksegments (id SERIAL);
SELECT AddGeometryColumn('tracksegments','trkseg',4326,'LINESTRING',3);
INSERT INTO tracksegments (trkseg) VALUES (
    ST_GeomFromEWKT('SRID=4326;LINESTRING(
        11.132305 43.820643 45.44,
        11.131639 43.820643 48.23,
        11.132927 43.819184 49.22,
        11.134493 43.819592 44.85,
        11.136060 43.819249 40.01)'
    )
);
</code>

Notare che in questo caso la funzione **''ST_GeomFromText()''** (conforme alle specifiche //Simple Features for SQL// di OGC) non è sufficiente a causa della presenza della terza dimensione (altezza). Siamo costretti ad utilizzare la funzione non standard **''ST_GeomFromEWKT()''**. Tale funzione riceve nell'unico parametro stringa sia il sistema di riferimento che l'oggetto geometrico.

Per stampare come testo un oggetto geometrico a tre dimensioni si utilizza la funzione **''ST_AsEWKT()''**.

Problema: come fare ''INSERT'' di una ''LINESTRING'' con magari qualche migliaio di punti? Sicuramente con una sola INSERT (specialmente da PHP) si potrebbero avere dei problemi sulla eccessiva lunghezza della istruzione SQL.

==== Terzo esempio ====

**Nota:** a differenza dell'esempio precedente, qui si utilizzano solo le due dimensioni.

Per risolvere il problema di cui sopra si può creare una tabella in cui inserire i singoli trackpoint e poi usare la funzione di aggregazione **''ST_MakeLine()''** per generare un record nella tabella ''tracksegments''.

<code sql>
CREATE TABLE trackpoints (
    id         serial,
    idtrkseg   integer,
    trkpt_time timestamp without time zone,
    elevation  double precision
);
SELECT AddGeometryColumn('trackpoints','trkpt',4326,'POINT',2);

INSERT INTO tracksegments (trkseg) VALUES (NULL);
SELECT CURRVAL('tracksegments_id_seq'); -- Supponiamo che il valore corrente sia 43

INSERT INTO trackpoints (idtrkseg, trkpt_time, elevation, trkpt) VALUES
    (43, '2005-04-25 16:23:09', 418.791504,
    ST_GeomFromText('POINT(10.987358 43.786654)', 4326));
INSERT INTO trackpoints (idtrkseg, trkpt_time, elevation, trkpt) VALUES
    (43, '2005-04-25 16:23:28', 424.559448,
    ST_GeomFromText('POINT(10.987401 43.786612)', 4326));
INSERT INTO trackpoints (idtrkseg, trkpt_time, elevation, trkpt) VALUES
    (43, '2005-04-25 16:23:32', 426.001343,
    ST_GeomFromText('POINT(10.987508 43.786805)', 4326));
...
...

UPDATE tracksegments
    SET trkseg = (
        SELECT ST_MakeLine(trkpt) FROM (
            SELECT trkpt FROM trackpoints WHERE idtrkseg = 43 ORDER BY trkpt_time
        ) AS trkseg
    ) WHERE id = 43;
</code>

L'istruzione ''UPDATE'' è quella che provvede ad aggregare i trackpoint e ad assegnare il valore al campo geometrico di tipo ''LINESTRING'' nella tabella ''tracksegments''. Contiene due ''SELECT'' annidate, questo è il significato di ciascuna parte:

<code sql>
-- La seguente istruzione restituisce un geometry set:
SELECT trkpt FROM trackpoints WHERE idtrkseg = 43 ORDER BY trkpt_time;

-- La seguente istruzione trasforma un geometry set in una LINESTRING:
SELECT ST_MakeLine(trkpt) FROM <geometry set> AS trkseg;

-- Con questa istruzione si aggiorna il campo trkseg del record:
UPDATE tracksegments SET trkseg = <geometry> WHERE id = 43;
</code>

Per il momento non si utilizza la terza e la quarta dimensione (altezza e tempo) offerta dalle entità geometriche di PostGIS >= 1.0. Sarebbe ovviamente più sensato rispetto all'uso dei campi //elevation// e //time//. Però sembra che QGIS - almeno fino alla versione 0.6 - possa lavorare solo con layer PostGIS a due dimensioni.

==== Quarto esempio ====

Creare una tabella con i contorni di un paese, il campo che memorizza il dato sarà di tipo ''**POLYGON**'', ovviamente ogni paese può essere costituito da più poligoni (isole, ecc.). Ogni poligono viene creato da una o più ''LINESTRING'', dove la prima definisce il contorno del poligono e le successive definiscono gli eventuali //buchi// del poligono stesso (ad esempio un lago, oppure lo stato di San Marino all'interno dell'Italia). Per unire le ''LINESTRING'' in un ''POLYGON'' si usa la funzione di aggregazione **''ST_MakePolygon()''**.

Nell'esempio che segue i poligoni non hanno buchi, ma solo il contorno. La tabella ''borders'' deve contenere i contorni come oggetti di tipo ''LINESTRING'' chiusi, cioè il primo e l'ultimo punto devono coincidere:

<code sql>
CREATE TABLE countries (
  idcountry integer NOT NULL,
  idpolygon integer NOT NULL
);
SELECT AddGeometryColumn('countries', 'poly', 4008, 'POLYGON', 2);

INSERT INTO countries (idcountry, idpolygon) VALUES (39, 133);

UPDATE countries SET poly = (
  SELECT ST_MakePolygon(the_geom) FROM (
    SELECT the_geom FROM borders WHERE idtrkseg = 133
  ) AS the_geom
) WHERE idcountry = 39 AND idpolygon = 133;
</code>

==== Quinto esempio ====

A volte è più comodo creare una ''**VIEW**'' e prendere il dato geometrico da questa invece che lavorare direttamente con le tabelle, ad esempio quando si visualizzano i dati in QGIS. Ecco un esempio di come creare la VIEW e aggiornare la tabella ''geometry_columns'' in modo appropriato. Ovviamente il nome del campo contenente il dato geometrico, la dimensione, lo SRID e il tipo devono essere conosciuti.

<code sql>
CREATE VIEW my_view AS SELECT oid, nam, na2, the_geom FROM vmap0 WHERE nam LIKE 'SICILIA';
INSERT INTO geometry_columns VALUES('', 'public', 'my_view', 'the_geom', 2, 4326, 'MULTIPOLYGON');
</code>

**Nota** E' necessario che compaia il campo ''**oid**'' altrimenti QGIS non riesce a fare la query. Per poter fare il ''**DROP**'' oppure il ''**CREATE OR REPLACE**'' della query è necessario chiudere QGIS, altrimenti la ''VIEW'' risulta in uso e non può essere modificata.

Utilissima questa mail: [[using_views_as_a_source_of_geometric_data|Using Views as a source of geometric data]] sull'uso di una VIEW come sorgente dei dati per MapServer.

===== Importazione dati da shepfile =====

Supponiamo di avere uno shapefile con i confini regionali (**''MULTIPOLYGON''**) espressi nel **sistema di riferimento WGS84** (EPSG:4326), possiamo convertire lo shape in un listato SQL:

<code>
shp2pgsql -s 4326 vmap0_polbnda.shp vmap0_polbnda > vmap0_polbnda.sql
</code>

Il listato contiene le istruzioni per creare la tabella **''vmap0_polbnda''** e la popolarla. Dopo essersi connessi ad un database è possibile eseguire lo script SQL:

<code>
psql
postgres=# \i vmap0_polbnda.sql
</code>

===== Esempi =====

==== Aggiungere una geometria ad una tabella ====

Supponiamo che una tabella abbia i campi **x** e **y** con le coordinate geografiche espresse come **float**, vogliamo aggiungere una colonna di tipo geografico (POINT a due dimensioni, nel sistema di rifermento WGS84 EPSG:4326):

<code sql>
SELECT AddGeometryColumn('centri_abitati', 'wpt', 4326, 'POINT', 2);
UPDATE centri_abitati SET wpt = ST_SetSRID(ST_MakePoint(x, y), 4326);
</code>

==== Calcolo distanza ====

Elenco dei centri abitati in **ordine di distanza** da un centro abitato prescelto.

Anzitutto si cercano le coordinate del centro abitato:

<code sql>
SELECT toponimo, ST_AsText(wpt) FROM centri_abitati WHERE toponimo = 'Firenze';
</code>

Avendo trovato le coordinate di Firenze **''POINT(11.255279 43.769715)''** si costruisce la query:

<code sql>
SELECT 
    toponimo,
    ST_Distance(wpt, ST_GeomFromText('POINT(11.255279 43.769715)', 4326)) AS dist
  FROM centri_abitati
  WHERE toponimo != 'Firenze'
  ORDER BY dist;
</code>

Se si vuole la distanza in metri si deve trasformare la geometria da un sistema di coordinate longitudine e latitudine ad un sistema proiettato in metri, ad esempio **Monte Mario / Italy zone 1** (EPSG:3003):

<code sql>
SELECT
    toponimo,
    ST_Distance(
      ST_Transform(wpt, 3003),
      ST_Transform(ST_GeomFromText('POINT(11.255279 43.769715)', 4326), 3003)
    ) AS dist
  FROM centri_abitati ORDER BY dist;
</code>

==== Punti contenuti in un multipoligono ====

Elenco dei **comuni compresi in una regione**:

<code sql>
SELECT toponimo, idprovincia FROM centri_abitati
  WHERE ST_Contains(
    (SELECT ST_Union(the_geom) FROM vmap0_polbnda WHERE nam = 'TOSCANA'),
    wpt
  );
</code>

===== Ottimizzazione con gli indici =====

Se si eseguono query su oggetti geografici conviene creare un indice sulla colonna geografica. PostGIS utilizza in questo caso indici di tipo GiST. Ecco come creare l'indice (**tracksegments** è il nome della tabella e **trkseg** è il campo con la geometria):

<code sql>
CREATE INDEX tracksegments_trkseg_idx ON tracksegments USING GIST (trkseg GIST_GEOMETRY_OPS);
VACUUM ANALYZE tracksegments (trkseg);
</code>

Probabilmente l'operatore ''GIST_GEOMETRY_OPS'' [[http://postgis.refractions.net/pipermail/postgis-users/2005-May/007914.html|può essere omesso]], essendo l'unico disponibile.

Importante il comando VACUUM ANALYZE, altrimenti i successivi query plans non saranno ottimizzati. Su un campione di 1061 record di tipo LINESTRING (per un totale di circa 315000 punti) una query senza indice impiega circa 29 ms:

<code>
EXPLAIN ANALYZE SELECT count(trkseg) FROM tracksegments
WHERE trkseg && SetSRID(MakeBox2D(MakePoint(10.588, 43.026), MakePoint(11.661, 43.771)), 4326);
 Aggregate  (cost=52.00..52.01 rows=1 width=4548) (actual time=29.292..29.293 rows=1 loops=1)
   ->  Seq Scan on tracksegments
   ...
   ...
 Total runtime: 29.343 ms
</code>

dopo la creazione dell'indice impiega un terzo del tempo:

<code>
 Aggregate  (cost=51.26..51.27 rows=1 width=4794) (actual time=9.720..9.722 rows=1 loops=1)
   ->  Bitmap Heap Scan on tracksegments  (cost=9.96..50.71 rows=220 width=4794)
   ...
   ...
 Total runtime: 9.783 ms
</code>

===== Problemi di performance =====

Può capitare che una query effettuata su PostGIS da QGIS oppure da MapServer risulti estremamente lenta rispetto ad una analoga ''SELECT'' effettuata da riga di comando. Tenere presente che QGIS e MapServer attingono dati dichiarando dei ''**BINARY CURSOR**'' invece di utilizzare delle semplici ''**SELECT**'' e quindi il planner di Postgres si comporta in modo diverso. Vedere in proposito questa [[slow_queries_from_mapserver_and_qgis|mail]]. Purtroppo il comando ''EXPLAIN ANALYZE'' per valutare le performance della query non funziona sui BINARY CURSOR.

===== Entità geometriche supportate da PostGIS =====

Le entità geometriche supportate sono quegli oggetti descritti dalle specifiche //{{99-049.pdf|Simple Features for SQL}}// del consorzio [[http://www.opengeospatial.org/|OpenGIS Consortium (OGC)]]. Le specifiche OGC prevedono solo due dimensioni, mentre PostGIS supporta anche la terza (altezza). La quarta dimensione (tempo) pare ancora non supportata (versione PostGIS versione 0.9.1). Attenzione comunque anche ad usare la terza dimensione, pare che QGIS 0.6 non riesca ad usare layer PostGIS con tre dimensioni.

**%%POINT(X Y)%%**\\
A Point is a 0-dimensional geometry and represents a single location in coordinate space. A Point has a x-coordinate value and a y-coordinate value.

**%%MULTIPOINT(X Y, X Y, ...)%%**\\
A MultiPoint is a 0 dimensional geometric collection. The elements of a MultiPoint are restricted to Points. The points are not connected or ordered.

**%%LINESTRING(X Y, X Y, ...)%%**\\
A LineString is a Curve with linear interpolation between points. Each consecutive pair of points defines a line segment.

**%%MULTILINESTRING((X Y, X Y, ...), ...)%%**\\
A MultiLineString is a collection whose elements are LineStrings.

**%%POLYGON((X Y, X Y, X Y, ...), (X Y, X Y, X Y, ...), ...))%%**\\
A Polygon is a planar Surface, defined by 1 exterior boundary and 0 or more interior boundaries. Each interior boundary defines a hole in the Polygon. Polygons are topologically closed. The boundary of a Polygon consists of a set of LinearRings (simple and closed Linestrings) that make up its exterior and interior boundaries. No two rings in the boundary cross, the rings in the boundary of a Polygon may intersect at a Point but only as a tangent...

**%%MULTIPOLYGON(...)%%**\\
A MultiPoligon is a collection whose elements are Polygon.

**%%GEOMETRYCOLLECTION(POINT(X Y),LINESTRING(X Y, X Y, ...), ...)%%**\\
A GeometryCollection is a collection whose elements others Geometry objects.

===== Dump e restore =====

In linea generale si ottiene un dump di tutto il database con un comando del tipo:

<code>
$ pg_dump -h 127.0.0.1 --create -U strade -W strade > strade_plpgsql_postgis.dump
</code>

Dentro il dump ci saranno ovviamente le tabelle con i dati dell'utente, ma anche oggetti che devono essere ricreati con i privilegi di superutente (il linguaggio PL/pgSQL, le funzioni di PostGIS). Inoltre se si vuole effettuare l'upgrade ad una versione più recente di PostGIS il passaggio potrebbe non essere così semplice. Vedere in proposito le note che seguono e la pagina [[postgis_dump_restore|dump e restore di un database PostGIS]].

==== Problemi con il restore ====

**ATTENZIONE:** per motivi ignoti a volte il restore eseguito con **''psql''** fallisce, sia che per leggere il file dump si usi l'opzione **-f**, oppure la ridirezione da shell **<**, oppure ancora il comando interno **\i**.

Sembra che il problema stia nel comando COPY su record molto grossi o che contengono caratteri particolari. Alcuni errori possibili sono:

<code>
ERROR:  syntax error at or near "5" at character 1
Invalid command \..
Invalid command \N.
</code>

La soluzione è utilizzare **pg_dump -Fc** (dump in formato custom invece che plain-text) e quindi effettuare il restore con **pg_restore** invece di **psql**. Anche l'opzione **%%--inserts%%** di ''pg_dump'' risolve il problema, ma è sconsigliata per l'estrema lentezza del restore.

Quando si aggiorna la versione di PostGIS il database geografico deve essere ricostruito. Mentre i dati utente possono essere salvati con un dump e poi recuperati con un restore, gli oggetti PostGIS (**funzioni**, **tipi**, ecc.) devono essere aggiornati alla nuova versione e quindi **non vanno recuperati dal dump**. Anche la tabella **spatial_ref_sys** dovrebbe essere ricostruita con l'ultima versione del file sql fornito con PostGIS.

Quella che segue è una ricetta per effettuare un upgrade, è stata sperimentata nel passaggio:

PostgreSQL 8.1, PostGIS 1.1.6 => PostgreSQL 8.2, PostGIS 1.2.1

Nel nostro caso il vecchio database sta in ascolto sulla porta TCP 5432, mentre quello nuovo è sulla porta 5433. Si consiglia di eseguire i vari comandi PostgreSQL (**psql**, **pg_dump**, **pg_restore**) richiamandoli con il percorso completo (**''/usr/lib/postgresql/<ver>/bin/''**) rispettando la versione di database con il quale di volta in volta si interagisce.

Anzitutto si esegue il dump del vecchio database (notare l'opzione **-Fc**):

<code>
su - postgres
pg_dump --port 5432 -Fc dbname > dbname.dump
</code>

Poi da utente privilegiato si crea il nuovo database a partire dal nuovo **template_gis**, in modo da avere l'ultima versione dell'estensione PostGIS:

<code>
su - postgres
psql --port 5433
</code>

<code sql>
CREATE DATABASE dbname OWNER dbuser TEMPLATE template_gis;
\connect dbname
GRANT ALL ON geometry_columns TO dbuser;
GRANT SELECT ON spatial_ref_sys TO dbuser;
</code>

Poi si deve fare il restore solo dei dati utente. Un trucco potrebbe essere quello di effettuare il restore con un utente che non ha i privilegi per sovrascrivere gli oggetti PostGIS; vengono visualizzati molti errori durante il restore, ma il risultato finale dovrebbe essere corretto.

Una soluzione più pulita è quella di effettuare un **restore selettivo** con **pg_restore**.

Si estrae la lista degli oggetti contenuti nel dump:

<code>
pg_restore --list dbname.dump > dbname_list
</code>

Si edita la lista degli oggetti (''dbname_list'') togliendo quelli generati da PostGIS. In pratica si tolgono tutti i riferimenti alla tabella ''spatial_ref_sys'' e le righe che contengono:

<code>
ACL
AGGREGATE
CAST
COMMENT
FUNCTION
OPERATOR
PROCEDURAL LANGUAGE
SCHEMA
TYPE
</code>

Quindi si effettua il restore selettivo da utente non privilegiato:

<code>
pg_restore --port 5433 -U dbuser -W -h localhost -L dbname_list -d dbname dbname.dump
</code>