Recuperare l'archivio sorgente di PostGIS dal sito web dei download https://postgis.net/stuff/postgis-3.7.0dev.tar.gz

wget https://postgis.net/stuff/postgis-3.7.0dev.tar.gz
tar -xvzf postgis-3.7.0dev.tar.gz
cd postgis-3.7.0dev

Questo passaggio creerà una cartella denominata postgis-3.7.0dev nella cartella di lavoro attuale.

In alternativa, potete ottenere una copia del sorgente dalla repository git https://git.osgeo.org/gitea/postgis/postgis/ .

git clone https://git.osgeo.org/gitea/postgis/postgis.git postgis
cd postgis
sh autogen.sh
    

Passare alla cartella postgis appena creata per continuare l'installazione.

./configure

PostGIS necessita dei seguenti requisiti per la compilazione e l'utilizzo:

Necessari

Opzionali

Come in molte installazioni Linux, il primo passo consiste nel generare il Makefile che sarà poi utilizzato che compilare il codice sorgente. Per questo si esegue lo script da shell

./configure

Se non si specificano parametri aggiuntivi, questo comando cercherà di individuare automaticamente le componenti e le librerie necessarie per compilare il codice sorgente PostGIS sul vostro sistema. Benché questa sia la modalità di utilizzo più comune per ./configure, lo script accetta vari parametri per coloro che tengono le librerie e i programmi necessari in percorsi non standard.

La lista seguente riporta solamente i parametri usati più di frequente. Per una lista completa, utilizzate i parametri --help o --help=short.

	Se avete ottenuto PostGIS dalla code repository , il primo passo consiste nell'eseguire lo script

./autogen.sh

Questo script genererà lo script configure, che a sua volta viente utilizzato per personalizzare l'installazione di PostGIS.

Se invece avete ottenuto PostGIS come file tar, non sarà necessario eseguire ./autogen.sh, dato che configure sarà già stato generato.

Una volta creato il Makefile, compilare PostGIS è semplice come eseguire

make

[last line of output]
PostGIS was built successfully. Ready to install.

All the functions have comments generated from the documentation. If you wish to install these comments into your spatial databases later, run the command which requires docbook. The postgis_comments.sql and other package comment files are also packaged in the tar.gz distribution in the doc folder so no need to make comments if installing from the tar ball. Comments are also included as part of the CREATE EXTENSION install.

make comments

The make cheatsheets target generates html cheat sheets suitable for quick reference or for student handouts. This requires xsltproc to build and will generate 4 files in doc folder topology_cheatsheet.html, raster_cheatsheet.html, postgis_cheatsheet.html

Ne potete scaricare alcuni già pronti sia in formato HTML che PDF dalla pagina PostGIS / PostgreSQL Study Guides

make cheatsheets

The PostGIS extensions are built and installed automatically when PostgreSQL extension support is available.

Se state compilando a partire dalla repository del sorgente, dovete prima compilare le descrizioni delle funzioni. Queste vengono create se avete docbook installato. Potete anche eseguire la compilazione manualmente tramite il comando:

make comments

Creare i file dei commenti non è necessario se state compilando a partire dal file tar, dato che questi file sono inclusi nel file tar stesso.

Le estensioni dovrebbero essere create automaticamente come parte del processo di installazione di make. Se necessario, si possono creare dalle cartelle delle estensioni o copiare i file se servono su un altro server.

Set PGUSER if you need to override the psql connection user, then test before installing. After installation, run the extension regression tests.

cd extensions
cd postgis
make clean
make
export PGUSER=postgres
make check
make install
make check RUNTESTFLAGS=--extension

	make check usa psql per eseguire i test e come tale può usare variabili d'ambiente psql. Quelle comuni utili da sovrascrivere sono PGUSER, PGPORT e PGHOST. Fare riferimento a variabili d'ambiente psql

I file di estensione saranno sempre gli stessi per la stessa versione di PostGIS e PostgreSQL, indipendentemente dal sistema operativo, quindi è possibile copiare i file di estensione da un sistema operativo all'altro, purché i binari di PostGIS siano già installati sui server.

Se volete installare le estensioni manualmente su un server separato, diverso da quello di sviluppo, dovete copiare i seguenti file dalla cartella delle estensioni nella cartella PostgreSQL / share / extension della vostra installazione PostgreSQL, così come i file binari per la versione normale di PostGIS, se questi già non sono presenti sul server.

Una volta fatto ciò, si dovrebbe vedere postgis, postgis_topology come estensioni disponibili in PgAdmin -> estensioni.

Se state utilizzando psql, potete verificare l'avvenuta installazione delle estensioni tramite questa query:

SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name LIKE 'postgis%'
ORDER BY name;

┌──────────────────┬─────────────────┬───────────────────┐
│ name             │ default_version │ installed_version │
├──────────────────┼─────────────────┼───────────────────┤
│ postgis          │ 3.7.0dev        │ 3.7.0dev        │
│ postgis_raster   │ 3.7.0dev        │ 3.7.0dev        │
│ postgis_sfcgal   │ 3.7.0dev        │                   │
│ postgis_topology │ 3.7.0dev        │                   │
└──────────────────┴─────────────────┴───────────────────┘

Se avete le estensioni installate nel database che state interrogando, vedrete questo fatto menzionato nella colonna installed_version. Se non vengono riportari record nel risultato, significa che non avete alcuna estensione installata sul server. PgAdmin III 1.14+ fornirà inoltre questa informazione nella sezione extensions dell'albero di navigazione del database e consentirà anche l'aggiornamento o la disinstallazione tramite clic del tasto destro del mouse.

Se le estensioni sono disponibili, potete installare l'estensione postgis nel database da voi scelto utilizzando l'interfaccia pgAdmin per le estensioni o eseguendo i seguenti comandi SQL:

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_raster;
CREATE EXTENSION postgis_sfcgal;
CREATE EXTENSION postgis_topology;

In psql è possibile vedere quali versioni sono installate e anche quali schemi sono installati.

\connect mygisdb
\x
\dx postgis*

List of installed extensions
┌─[ RECORD 1 ]────────────────────────────────────────────────┐
│ Name        │ postgis                                       │
│ Version     │ 3.7.0dev                                        │
│ Schema      │ public                                        │
│ Description │ PostGIS geometry, geography, and raster…      │
├─[ RECORD 2 ]────────────────────────────────────────────────┤
│ Name        │ postgis_raster                                │
│ Version     │ 3.7.0dev                                        │
│ Schema      │ public                                        │
│ Description │ PostGIS raster types and functions            │
├─[ RECORD 3 ]────────────────────────────────────────────────┤
│ Name        │ postgis_topology                              │
│ Version     │ 3.7.0dev                                        │
│ Schema      │ topology                                      │
│ Description │ PostGIS topology spatial types and functions  │
└─────────────────────────────────────────────────────────────┘

Extension tables spatial_ref_sys, layer, topology can not be explicitly backed up. They can only be backed up when the respective postgis or postgis_topology extension is backed up, which only happens when you back up the whole database. Only srid records not packaged with PostGIS are captured in backups, so don't change the entries we ship and expect the modifications to persist. Put in a ticket if you find an issue. The structures of extension tables are never backed up since they are created with CREATE EXTENSION and assumed to be the same for a given version of an extension. These behaviors are built into the current PostgreSQL extension model.

Se avete installato 3.7.0dev, senza usare il nostro meraviglioso sistema di estensioni, potete cambiarlo in un sistema basato sulle estensioni eseguendo i comandi seguenti per impacchettare le funzioni nelle rispettive estensioni. L'installazione con `unpackaged` è stata rimossa in PostgreSQL 13, quindi si consiglia di passare a una compilazione con estensione prima di aggiornare a PostgreSQL 13.

CREATE EXTENSION postgis FROM unpackaged;
CREATE EXTENSION postgis_raster FROM unpackaged;
CREATE EXTENSION postgis_topology FROM unpackaged;

Se volete eseguire un test sul PostGIS compilato, eseguite

make check

Il comando di cui sopra eseguirà vari controlli e collaudi di regressione, utilizzando la libreria generata per un database PostgreSQL effettivo.

	Se avete configurato PostGIS utilizzando percorsi non standard per PostgreSQL, GEOS o Proj, potreste dover aggiungere il percorso di queste librerie nella variabile di ambiente LD_LIBRARY_PATH.

Attualmente il comando make check fa riferimento alle variabili di ambiente PATH e PGPORT nello svolgimento dei controlli - e non utilizza la versione PostgreSQL che può essere stata specificata nella configurazione con il parametro --with-pgconfig. Perciò assicuratevi che la vostra variabile PATH corrisponda all'installazione di PostgreSQL rilevata durante al configurazione, o preparatevi ad affrontare una serie di grattacapi.

Sandboxed build accounts that are not PostgreSQL superusers can delegate database ownership during the regression cycle by exporting POSTGIS_REGRESS_DB_OWNER. The harness will create the temporary regression database owned by the nominated role while continuing to connect using the less privileged account. Combine this with POSTGIS_REGRESS_ROLE_EXT_CREATOR when the extension creation role must differ from the database owner. These variables allow automated environments to exercise the full upgrade and extension install paths without promoting the calling account to superuser, provided the target PostgreSQL instance permits extension installation by those delegate roles.

Se il test è positivo, l'uscita conterrà il risultato di molti test la schermata dovrebbe essere simile alla seguente (molte linee sono omesse):

CUnit - A unit testing framework for C - Version 2.1-3
     http://cunit.sourceforge.net/

  .
  .
  .

Run Summary:    Type  Total    Ran Passed Failed Inactive
              suites     44     44    n/a      0        0
               tests    300    300    300      0        0
             asserts   4215   4215   4215      0      n/a
Elapsed time =    0.229 seconds

  .
  .
  .

Running tests

  .
  .
  .

Run tests: 134
Failed: 0


-- if you build with SFCGAL

  .
  .
  .

Running tests

  .
  .
  .

Run tests: 13
Failed: 0

-- if you built with raster support

  .
  .
  .

Run Summary:    Type  Total    Ran Passed Failed Inactive
              suites     12     12    n/a      0        0
               tests     65     65     65      0        0
             asserts  45896  45896  45896      0      n/a


  .
  .
  .

Running tests

  .
  .
  .

Run tests: 101
Failed: 0

-- topology regress

.
.
.

Running tests

  .
  .
  .

Run tests: 51
Failed: 0

-- if you built --with-gui, you should see this too

     CUnit - A unit testing framework for C - Version 2.1-2
     http://cunit.sourceforge.net/

  .
  .
  .

Run Summary:    Type  Total    Ran Passed Failed Inactive
              suites      2      2    n/a      0        0
               tests      4      4      4      0        0
             asserts      4      4      4      0      n/a

L'output dovrebbe essere simile a:

============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== installing fuzzystrmatch               ==============
CREATE EXTENSION
============== installing postgis                     ==============
CREATE EXTENSION
============== running regression test queries        ==============
test test-normalize_address   ... ok

=====================
All 2 tests passed.
=====================

Per installare PostGIS, digitate

make install

Questo comando copierà i file per l'installazione di PostGIS nelle cartelle appropriate, specificate tramite il parametro di configurazione --prefix. In particolare:

Se avete eseguito il comando make comments per generare i file postgis_comments.sql e raster_comments.sql, installate i file SQL eseguendo

make comments-install

	postgis_comments.sql, raster_comments.sql, e topology_comments.sql sono separati dai tipici obiettivi per il build e per l'installazione, dato che hanno una ulteriore dipendenza da xsltproc.

Queste impostazioni sono configurate in postgresql.conf:

constraint_exclusion

shared_buffers

max_worker_processes controls the maximum number of background processes available to the server and therefore also bounds how many workers can participate in parallel queries.

work_mem - imposta la dimensione della memoria utilizzata per le operazioni di ordinamento e le query complesse

maintenance_work_mem - la dimensione della memoria utilizzata per VACUUM, CREATE INDEX, ecc.

max_parallel_workers_per_gather

Parallel query support in modern PostgreSQL and PostGIS uses this setting to decide how many workers a plan may request. If set to higher than 0, then some queries such as those involving relation functions like ST_Intersects can use multiple processes and can run more than twice as fast when doing so. If you have a lot of processors to spare, you should change the value of this to as many processors as you have. Also make sure to bump up max_worker_processes to at least as high as this number.

Se hai compilato ed installato il modulo extensions/postgis, puoi aggiungere il supporto spaziale in un database usando il meccanismo per le estensioni (EXTENSION)

L'estensione principale di postgis include geometria, geografia, spatial_ref_sys e tutte le funzioni e i commenti. Raster e topologia sono confezionati come estensione separata.

Lancia il seguente codice SQL nel database in cui vuoi abilitare il supporto spaziale:

CREATE EXTENSION IF NOT EXISTS plpgsql;
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_raster; -- OPTIONAL
CREATE EXTENSION postgis_topology; -- OPTIONAL

	Questo è generalmente necessario solo se non si può o non si vuole installare PostGIS nella directory delle estensioni di PostgreSQL (ad esempio durante i test, lo sviluppo o in un ambiente limitato).

L'aggiunta degli oggetti e delle definizioni delle funzioni di PostGIS nel database avviene caricando i vari file sql che si trovano in [prefisso]/share/contrib come specificato durante la fase di compilazione.

Gli oggetti PostGIS di base (tipi di geometria e geografia e relative funzioni di supporto) si trovano nello script postgis.sql. Gli oggetti raster si trovano nello script rtpostgis.sql. Gli oggetti topologici si trovano nello script topology.sql.

Per un insieme completo di identificatori di definizione di sistemi di coordinate EPSG, è possibile anche caricare il file di definizioni spatial_ref_sys.sql e popolare la tabella spatial_ref_sys. In questo modo è possibile eseguire operazioni ST_Transform() sulle geometrie.

Se si desidera aggiungere commenti alle funzioni di PostGIS, è possibile trovarli nello script postgis_comments.sql. I commenti possono essere visualizzati semplicemente digitando \dd [nome_funzione] da una finestra di terminale psql.

Eseguire i seguenti comandi di Shell nel terminale:

DB=[yourdatabase]
SCRIPTSDIR=`pg_config --sharedir`/contrib/postgis-3.6/

# Core objects
psql -d ${DB} -f ${SCRIPTSDIR}/postgis.sql
psql -d ${DB} -f ${SCRIPTSDIR}/spatial_ref_sys.sql
psql -d ${DB} -f ${SCRIPTSDIR}/postgis_comments.sql # OPTIONAL

# Raster support (OPTIONAL)
psql -d ${DB} -f ${SCRIPTSDIR}/rtpostgis.sql
psql -d ${DB} -f ${SCRIPTSDIR}/raster_comments.sql # OPTIONAL

# Topology support (OPTIONAL)
psql -d ${DB} -f ${SCRIPTSDIR}/topology.sql
psql -d ${DB} -f ${SCRIPTSDIR}/topology_comments.sql # OPTIONAL

Se il database è stato installato utilizzando le estensioni, è necessario effettuare anche l'aggiornamento nello stesso modo. Se l'installazione è stata eseguita con il vecchio metodo degli script sql, si consiglia di passare alle estensioni perché il metodo degli script non è più supportato.

3.4.1.1. Soft upgrade using extensions

If you originally installed PostGIS with extensions, then you need to upgrade using extensions as well. Doing a minor upgrade with extensions is fairly painless.

Use the PostGIS_Extensions_Upgrade function to upgrade to the latest version you have installed.

Code

Copy

SELECT postgis_extensions_upgrade();

If you are upgrading a database that still runs PostGIS 2.5, issue an ALTER EXTENSION followed by postgis_extensions_upgrade() to make sure the legacy postgis_raster objects are folded back into the main extension.

Code

Copy

ALTER EXTENSION postgis UPDATE;
SELECT postgis_extensions_upgrade();

This second call is needed to rebundle the postgis_raster extension.

Code

Copy

SELECT postgis_extensions_upgrade();

Se sono installate più versioni di PostGIS e non si vuole aggiornare alla più recente, è possibile specificare esplicitamente la versione come segue:

Code

Copy

ALTER EXTENSION postgis UPDATE TO "3.7.0dev";
ALTER EXTENSION postgis_topology UPDATE TO "3.7.0dev";

Se si ottiene un avviso di errore, qualcosa di simile a:

Output

No migration path defined for … to 3.7.0dev

È quindi necessario eseguire un backup del database, crearne uno nuovo come descritto in Sezione 3.3.1, «Abilitazione spaziale di database usando il metodo EXTENSION» e quindi ripristinare il backup su questo nuovo database.

Se viene visualizzato un messaggio di avviso del tipo:

Output

Version "3.7.0dev" of extension "postgis" is already installed

Allora tutto è già aggiornato e si può tranquillamente ignorare. A MENO CHE non si stia cercando di passare da una versione di sviluppo alla successiva (che non riceve un nuovo numero di versione); in questo caso si può aggiungere "next" alla stringa della versione e la prossima volta si dovrà eliminare il suffisso "next":

Code

Copy

ALTER EXTENSION postgis UPDATE TO "3.7.0devnext";
        ALTER EXTENSION postgis_topology UPDATE TO "3.7.0devnext";

	Se PostGIS è stato installato originariamente senza una versione specificata, spesso è possibile saltare la reinstallazione dell'estensione postgis prima del ripristino, poiché il backup ha solo CREATE EXTENSION postgis e quindi preleva la versione più recente durante il ripristino.

	If you are upgrading PostGIS extension from a version prior to 3.0.0, you will have a new extension postgis_raster which you can safely drop, if you don't need raster support.

Code

Copy

DROP EXTENSION postgis_raster;

The Open Geospatial Consortium (OGC) developed the Simple Features Access standard (SFA) to provide a model for geospatial data. It defines the fundamental spatial type of Geometry, along with operations which manipulate and transform geometry values to perform spatial analysis tasks. PostGIS implements the OGC Geometry model as the PostgreSQL data types geometry and geography.

Geometry is an abstract type. Geometry values belong to one of its concrete subtypes which represent various kinds and dimensions of geometric shapes. These include the atomic types Point, LineString, LinearRing and Polygon, and the collection types MultiPoint, MultiLineString, MultiPolygon and GeometryCollection. The Simple Features Access - Part 1: Common architecture v1.2.1 adds subtypes for the structures PolyhedralSurface, Triangle and TIN.

Geometry models shapes in the 2-dimensional Cartesian plane. The PolyhedralSurface, Triangle, and TIN types can also represent shapes in 3-dimensional space. The size and location of shapes are specified by their coordinates. Each coordinate has a X and Y ordinate value determining its location in the plane. Shapes are constructed from points or line segments, with points specified by a single coordinate, and line segments by two coordinates.

Coordinates may contain optional Z and M ordinate values. The Z ordinate is often used to represent elevation. The M ordinate contains a measure value, which may represent time or distance. If Z or M values are present in a geometry value, they must be defined for each point in the geometry. If a geometry has Z or M ordinates the coordinate dimension is 3D; if it has both Z and M the coordinate dimension is 4D. The coordinate dimension is at least 2, because every geometry has at least X and Y coordinates.

Geometry values are associated with a spatial reference system indicating the coordinate system in which it is embedded. The spatial reference system is identified by the geometry SRID number. The units of the X and Y axes are determined by the spatial reference system. In planar reference systems the X and Y coordinates typically represent easting and northing, while in geodetic systems they represent longitude and latitude. SRID 0 represents an infinite Cartesian plane with no units assigned to its axes. See Sezione 4.5, «Spatial Reference Systems».

The geometry dimension is a property of geometry types. Point types have dimension 0, linear types have dimension 1, polygonal types have dimension 2, and solid types have dimension 3. Collections have the dimension of the maximum element dimension.

A geometry value may be empty. Empty values contain no vertices (for atomic geometry types) or no elements (for collections).

An important property of geometry values is their spatial extent or bounding box, which the OGC model calls envelope. This is the 2 or 3-dimensional box which encloses the coordinates of a geometry. It is an efficient way to represent a geometry's extent in coordinate space and to check whether two geometries interact.

The geometry model allows evaluating topological spatial relationships as described in Sezione 5.1.1, «Modello dimensionale esteso 9-Intersection». To support this the concepts of interior, boundary and exterior are defined for each geometry type. Geometries are topologically closed, so they always contain their boundary. The boundary is a geometry of dimension one less than that of the geometry itself. For example, the boundary of a Polygon is its rings (LineStrings), the boundary of a LineString is its endpoints (Points), and the boundary of a Point is empty.

The OGC geometry model defines validity rules for each geometry type. These rules ensure that geometry values represents realistic situations (e.g. it is possible to specify a polygon with a hole lying outside its shell, but this makes no sense geometrically and is thus invalid). PostGIS also allows storing and manipulating invalid geometry values. This allows detecting and fixing them if needed. See Sezione 4.4, «Geometry Validation»

POINT (1 2)
POINT Z (1 2 3)
POINT ZM (1 2 3 4)

LINESTRING (1 2, 3 4, 5 6)

LINEARRING (0 0 0, 4 0 0, 4 4 0, 0 4 0, 0 0 0)

POLYGON ((0 0 0, 4 0 0, 4 4 0, 0 4 0, 0 0 0), (1 1 0, 2 1 0, 2 2 0, 1 2 0, 1 1 0))

MULTIPOINT ( (0 0), (1 2) )

MULTILINESTRING ( (0 0, 1 1, 1 2), (2 3, 3 2, 5 4) )

MULTIPOLYGON (((1 5, 5 5, 5 1, 1 1, 1 5)), ((6 5, 9 1, 6 1, 6 5)))

GEOMETRYCOLLECTION ( POINT(2 3), LINESTRING(2 3, 3 4))

POLYHEDRALSURFACE Z (
((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)),
((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),
((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)),
((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) )

TRIANGLE ((0 0, 0 9, 9 0, 0 0))

TIN Z ( ((0 0 0, 0 0 1, 0 1 0, 0 0 0)), ((0 0 0, 0 1 0, 1 1 0, 0 0 0)) )

The ISO/IEC 13249-3 SQL Multimedia - Spatial standard (SQL/MM) extends the OGC SFA to define Geometry subtypes containing curves with circular arcs. The SQL/MM types support 3DM, 3DZ and 4D coordinates.

	All floating point comparisons within the SQL-MM implementation are performed to a specified tolerance, currently 1E-8.

CIRCULARSTRING(0 0, 1 1, 1 0)

CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0)

COMPOUNDCURVE( CIRCULARSTRING(0 0, 1 1, 1 0), (1 0, 0 1))

CURVEPOLYGON(
CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),
(1 1, 3 3, 3 1, 1 1) )

CURVEPOLYGON(
COMPOUNDCURVE( CIRCULARSTRING(0 0, 2 0, 2 1, 2 3, 4 3),
               (4 3, 4 5, 1 4, 0 0)),
CIRCULARSTRING(1.7 1, 1.4 0.4, 1.6 0.4, 1.6 0.5, 1.7 1) )

MULTICURVE( (0 0, 5 5), CIRCULARSTRING(4 0, 4 4, 8 4))

MULTISURFACE(
CURVEPOLYGON(
  CIRCULARSTRING( 0 0, 4 0, 4 4, 0 4, 0 0),
  (1 1, 3 3, 3 1, 1 1)),
((10 10, 14 12, 11 10, 10 10), (11 11, 11.5 11, 11 11.5, 11 11)))

The OGC SFA specification defines two formats for representing geometry values for external use: Well-Known Text (WKT) and Well-Known Binary (WKB). Both WKT and WKB include information about the type of the object and the coordinates which define it.

Well-Known Text (WKT) provides a standard textual representation of spatial data. Examples of WKT representations of spatial objects are:

Input and output of WKT is provided by the functions ST_AsText and ST_GeomFromText:

text WKT = geometry;
geometry = ST_GeomFromText(text WKT, SRID);

For example, a statement to create and insert a spatial object from WKT with SRID 312 is:

INSERT INTO geotable ( geom, name )
VALUES ( ST_GeomFromText('POINT(-126.4 45.32)', 312), 'A Place');

Well-Known Binary (WKB) provides a portable, full-precision representation of spatial data as binary data (arrays of bytes). Examples of the WKB representations of spatial objects are:

Input and output of WKB is provided by the functions ST_AsBinary and ST_GeomFromWKB:

bytea WKB = ST_AsBinary(geometry);
geometry = ST_GeomFromWKB(bytea WKB, SRID);

For example, a statement to create and insert a spatial object from WKB is:

INSERT INTO geotable ( geom, name )
VALUES ( ST_GeomFromWKB('\x0101000000000000000000f03f000000000000f03f', 312), 'A Place');

OGC SFA specifications initially supported only 2D geometries, and the geometry SRID is not included in the input/output representations. The OGC SFA specification 1.2.1 (which aligns with the ISO 19125 standard) adds support for 3D (ZYZ) and measured (XYM and XYZM) coordinates, but still does not include the SRID value.

Because of these limitations PostGIS defined extended EWKB and EWKT formats. They provide 3D (XYZ and XYM) and 4D (XYZM) coordinate support and include SRID information. Including all geometry information allows PostGIS to use EWKB as the format of record (e.g. in DUMP files).

EWKB and EWKT are used for the "canonical forms" of PostGIS data objects. For input, the canonical form for binary data is EWKB, and for text data either EWKB or EWKT is accepted. This allows geometry values to be created by casting a text value in either HEXEWKB or EWKT to a geometry value using ::geometry. For output, the canonical form for binary is EWKB, and for text it is HEXEWKB (hex-encoded EWKB).

For example this statement creates a geometry by casting from an EWKT text value, and outputs it using the canonical form of HEXEWKB:

SELECT 'SRID=4;POINT(0 0)'::geometry;

geometry
----------------------------------------------------
01010000200400000000000000000000000000000000000000

PostGIS EWKT output has a few differences to OGC WKT:

EWKT avoids over-specifying dimensionality and the inconsistencies that can occur with the OGC/ISO format, such as:

	PostGIS extended formats are currently a superset of the OGC ones, so that every valid OGC WKB/WKT is also valid EWKB/EWKT. However, this might vary in the future, if the OGC extends a format in a way that conflicts with the PosGIS definition. Thus you SHOULD NOT rely on this compatibility!

Examples of the EWKT text representation of spatial objects are:

Input and output using these formats is available using the following functions:

bytea EWKB = ST_AsEWKB(geometry);
text EWKT = geometry;
geometry = ST_GeomFromEWKB(bytea EWKB);
geometry = ST_GeomFromEWKT(text EWKT);

For example, a statement to create and insert a PostGIS spatial object using EWKT is:

INSERT INTO geotable ( geom, name )
VALUES ( ST_GeomFromEWKT('SRID=312;POINTM(-126.4 45.32 15)'), 'A Place' )

You can create a table to store geography data using the CREATE TABLE SQL statement with a column of type geography. The following example creates a table with a geography column storing 2D LineStrings in the WGS84 geodetic coordinate system (SRID 4326):

CREATE TABLE global_points (
  id SERIAL PRIMARY KEY,
  name VARCHAR(64),
  location geography(POINT, 4326)
);

The geography type supports two optional type modifiers:

Examples of creating tables with geography columns:

Create a table with 2D POINT geography with the default SRID 4326 (WGS84 long/lat):

CREATE TABLE ptgeogwgs(gid serial PRIMARY KEY, geog geography(POINT));

Create a table with 2D POINT geography in NAD83 longlat:

CREATE TABLE ptgeognad83(gid serial PRIMARY KEY, geog geography(POINT, 4269));

Create a table with 3D (XYZ) POINTs and an explicit SRID of 4326:

CREATE TABLE ptzgeogwgs84(gid serial PRIMARY KEY, geog geography(POINTZ, 4326));

Create a table with 2D LINESTRING geography with the default SRID 4326:

CREATE TABLE lgeog(gid serial PRIMARY KEY, geog geography(LINESTRING));

Create a table with 2D POLYGON geography with the SRID 4267 (NAD 1927 long lat):

CREATE TABLE lgeognad27(gid serial PRIMARY KEY, geog geography(POLYGON, 4267));

Geography fields are registered in the geography_columns system view. You can query the geography_columns view and see that the table is listed:

SELECT * FROM geography_columns;

Creating a spatial index works the same as for geometry columns. PostGIS will note that the column type is GEOGRAPHY and create an appropriate sphere-based index instead of the usual planar index used for GEOMETRY.

CREATE INDEX global_points_gix ON global_points USING GIST ( location );

You can insert data into geography tables in the same way as geometry. Geometry data will autocast to the geography type if it has SRID 4326. The EWKT and EWKB formats can also be used to specify geography values.

INSERT INTO global_points (name, location) VALUES ('Town', 'SRID=4326;POINT(-110 30)');
INSERT INTO global_points (name, location) VALUES ('Forest', 'SRID=4326;POINT(-109 29)');
INSERT INTO global_points (name, location) VALUES ('London', 'SRID=4326;POINT(0 49)');

Any geodetic (long/lat) spatial reference system listed in spatial_ref_sys table may be specified as a geography SRID. Non-geodetic coordinate systems raise an error if used.

A NAD83 longitude/latitude geography value:

SELECT 'SRID=4269;POINT(-123 34)'::geography;

geography
----------------------------------------------------
 0101000020AD1000000000000000C05EC00000000000004140

A NAD27 longitude/latitude geography value:

SELECT 'SRID=4267;POINT(-123 34)'::geography;

geography
----------------------------------------------------
 0101000020AB1000000000000000C05EC00000000000004140

NAD83 UTM zone meters fails because it is a meter-based planar projection.

SELECT 'SRID=26910;POINT(-123 34)'::geography;

ERROR:  Only lon/lat coordinate systems are supported in geography.

Query and measurement functions use units of meters. So distance parameters should be expressed in meters, and return values should be expected in meters (or square meters for areas).

This query finds rows within a 1000 km tolerance:

SELECT name FROM global_points WHERE ST_DWithin(location, 'SRID=4326;POINT(-110 29)'::geography, 1000000);

You can see the power of geography in action by calculating how close a plane flying a great circle route from Seattle to London (LINESTRING(-122.33 47.606, 0.0 51.5)) comes to Reykjavik (POINT(-21.96 64.15)) (map the route).

The geography type calculates the true shortest distance of 122.235 km over the sphere between Reykjavik and the great circle flight path between Seattle and London.

SELECT ST_Distance('LINESTRING(-122.33 47.606,0.0 51.5)'::geography, 'POINT(-21.96 64.15)'::geography);

st_distance
-----------------
 122235.23815667

The geometry type calculates a meaningless cartesian distance between Reykjavik and the straight line path from Seattle to London plotted on a flat map of the world. The nominal units of the result is "degrees", but the result doesn't correspond to any true angular difference between the points, so even calling them "degrees" is inaccurate.

SELECT ST_Distance('LINESTRING(-122.33 47.606,0.0 51.5)'::geometry, 'POINT(-21.96 64.15)'::geometry);

st_distance
--------------------
 13.342271221453624

Figure

The geography data type allows you to store data in longitude/latitude coordinates, but at a cost: there are fewer functions defined on GEOGRAPHY than there are on GEOMETRY; those functions that are defined take more CPU time to execute.

The data type you choose should be determined by the expected working area of the application you are building. Will your data span the globe or a large continental area, or is it local to a state, county or municipality?

Refer to Sezione 13.11, «Matrice di supporto alle funzioni PostGIS» for compare between what is supported for Geography vs. Geometry. For a brief listing and description of Geography functions, refer to Sezione 13.4, «Funzioni di supporto alla geografia PostGIS»

A simple geometry is one that has no anomalous geometric points, such as self intersection or self tangency.

A POINT is inherently simple as a 0-dimensional geometry object.

MULTIPOINTs are simple if no two coordinates (POINTs) are equal (have identical coordinate values).

A LINESTRING is simple if it does not pass through the same point twice, except for the endpoints. If the endpoints of a simple LineString are identical it is called closed and referred to as a Linear Ring.

Use ST_IsSimple to classify linear geometries. Each labeled result below is returned only when the function agrees with the stated classification; example (c) also verifies that the simple LineString is closed.

WITH cases(label, geom) AS (VALUES
  ('a', 'LINESTRING (10 190, 140 130, 70 80, 190 10)'::geometry),
  ('b', 'LINESTRING (10 190, 130 40, 170 160, 10 10)'::geometry),
  ('c', 'LINESTRING (90 190, 120 190, 130 140, 190 50, 70 10, 10 70, 10 150, 90 190)'::geometry),
  ('d', 'LINESTRING (90 190, 120 190, 50 60, 130 10, 190 50, 160 90, 10 150, 90 190)'::geometry)
)
SELECT
  (SELECT geom FROM cases WHERE label = 'a' AND ST_IsSimple(geom))
    AS "input_(a) simple",
  (SELECT geom FROM cases WHERE label = 'b' AND NOT ST_IsSimple(geom))
    AS "input_(b) not simple",
  (SELECT geom FROM cases
             WHERE label = 'c' AND ST_IsSimple(geom) AND ST_IsClosed(geom))
    AS "input_(c) simple, closed",
  (SELECT geom FROM cases WHERE label = 'd' AND NOT ST_IsSimple(geom))
    AS "input_(d) not simple";

-[ RECORD 1 ]----------
input_(a) simple         | LINESTRING(10 190,140 130,70 80,190 10)
input_(b) not simple     | LINESTRING(10 190,130 40,170 160,10 10)
input_(c) simple, closed | LINESTRING(90 190,120 190,130 140,190 50,70 10,10 70,10 150,90 190)
input_(d) not simple     | LINESTRING(90 190,120 190,50 60,130 10,190 50,160 90,10 150,90 190)

01020000000400000000000000000024400000000000C0674000000000008061400000000000406040000000000080514000000000000054400000000000C067400000000000002440 | 01020000000400000000000000000024400000000000C06740000000000040604000000000000044400000000000406540000000000000644000000000000024400000000000002440 | 01020000000800000000000000008056400000000000C067400000000000005E400000000000C06740000000000040604000000000008061400000000000C067400000000000004940000000000080514000000000000024400000000000002440000000000080514000000000000024400000000000C0624000000000008056400000000000C06740 | 01020000000800000000000000008056400000000000C067400000000000005E400000000000C0674000000000000049400000000000004E40000000000040604000000000000024400000000000C0674000000000000049400000000000006440000000000080564000000000000024400000000000C0624000000000008056400000000000C06740

Figure

A MULTILINESTRING is simple only if all of its elements are simple and the only intersection between any two elements occurs at points that are on the boundaries of both elements.

The next example applies the same classification to the three MultiLineStrings. In (f) the component lines meet only at a shared endpoint, while in (g) they cross in their interiors.

WITH cases(label, geom) AS (VALUES
  ('e', 'MULTILINESTRING ((30 190, 60 60, 170 10), (100 190, 180 150, 160 70))'::geometry),
  ('f', 'MULTILINESTRING ((30 190, 60 60, 170 10), (30 190, 180 150, 160 70))'::geometry),
  ('g', 'MULTILINESTRING ((30 190, 60 60, 170 10), (100 190, 180 150, 80 10))'::geometry)
)
SELECT
  (SELECT geom FROM cases WHERE label = 'e' AND ST_IsSimple(geom))
    AS "input_(e) simple",
  (SELECT geom FROM cases WHERE label = 'f' AND ST_IsSimple(geom))
    AS "input_(f) simple",
  (SELECT geom FROM cases WHERE label = 'g' AND NOT ST_IsSimple(geom))
    AS "input_(g) not simple";

-[ RECORD 1 ]----------
input_(e) simple     | MULTILINESTRING((30 190,60 60,170 10),(100 190,180 150,160 70))
input_(f) simple     | MULTILINESTRING((30 190,60 60,170 10),(30 190,180 150,160 70))
input_(g) not simple | MULTILINESTRING((30 190,60 60,170 10),(100 190,180 150,80 10))

0105000000020000000102000000030000000000000000003E400000000000C067400000000000004E400000000000004E400000000000406540000000000000244001020000000300000000000000000059400000000000C0674000000000008066400000000000C0624000000000000064400000000000805140 | 0105000000020000000102000000030000000000000000003E400000000000C067400000000000004E400000000000004E40000000000040654000000000000024400102000000030000000000000000003E400000000000C0674000000000008066400000000000C0624000000000000064400000000000805140 | 0105000000020000000102000000030000000000000000003E400000000000C067400000000000004E400000000000004E400000000000406540000000000000244001020000000300000000000000000059400000000000C0674000000000008066400000000000C0624000000000000054400000000000002440

Figure

POLYGONs are formed from linear rings, so valid polygonal geometry is always simple.

Generally, PostGIS functions do not require geometric arguments to be simple. Simplicity is primarily used as a basis for defining geometric validity. It is also a requirement for some kinds of spatial data models (for example, linear networks often disallow lines that cross). Multipoint and linear geometry can be made simple using ST_UnaryUnion.

Geometry validity primarily applies to 2-dimensional geometries (POLYGONs and MULTIPOLYGONs) . Validity is defined by rules that allow polygonal geometry to model planar areas unambiguously.

A POLYGON is valid if:

Consecutive repeated points in a polygon boundary ring do not make the ring non-simple. Under the SFS curve model, a curve is interpreted by a continuous parameterization; consecutive equal coordinates are topologically indistinguishable from a single vertex. PostGIS therefore treats them as redundant vertices, so they do not make an otherwise valid polygon invalid.

The following examples use ST_IsValid to check the validity rules. Each labeled geometry is returned only if its computed validity agrees with the label.

WITH cases(label, geom) AS (VALUES
  ('h', 'POLYGON ((10 140, 90 190, 130 170, 190 60, 160 10, 50 20, 10 140),
                 (50 100, 70 80, 110 100, 110 140, 50 100))'::geometry),
  ('i', 'POLYGON ((10 140, 90 190, 130 170, 190 60, 160 10, 50 20, 10 140),
                 (190 60, 140 40, 110 60, 120 90, 190 60))'::geometry)
)
SELECT
  (SELECT geom FROM cases WHERE label = 'h' AND ST_IsValid(geom))
    AS "input_(h) valid",
  (SELECT geom FROM cases WHERE label = 'i' AND ST_IsValid(geom))
    AS "input_(i) valid, tangent hole";

-[ RECORD 1 ]----------
input_(h) valid               | POLYGON((10 140,90 190,130 170,190 60,160 10,50 20,10 140),(50 100,70 80,110 100,110 140,50 100))
input_(i) valid, tangent hole | POLYGON((10 140,90 190,130 170,190 60,160 10,50 20,10 140),(190 60,140 40,110 60,120 90,190 60))

010300000002000000070000000000000000002440000000000080614000000000008056400000000000C06740000000000040604000000000004065400000000000C067400000000000004E400000000000006440000000000000244000000000000049400000000000003440000000000000244000000000008061400500000000000000000049400000000000005940000000000080514000000000000054400000000000805B4000000000000059400000000000805B40000000000080614000000000000049400000000000005940 | 010300000002000000070000000000000000002440000000000080614000000000008056400000000000C06740000000000040604000000000004065400000000000C067400000000000004E40000000000000644000000000000024400000000000004940000000000000344000000000000024400000000000806140050000000000000000C067400000000000004E40000000000080614000000000000044400000000000805B400000000000004E400000000000005E4000000000008056400000000000C067400000000000004E40

Figure

Examples (j-m) are invalid. They show, respectively, a disconnected interior, crossing rings, a cut line, and a hole outside the shell. The invalidity reason is checked with ST_IsValidReason. The disconnected interior in (j) can instead be represented as a valid MULTIPOLYGON.

WITH cases(label, geom, reason_pattern) AS (VALUES
  ('(j) disconnected interior',
   'POLYGON ((10 140, 90 190, 130 170, 190 60, 160 10, 50 20, 10 140),
             (130 170, 10 140, 50 120, 110 110, 130 170))'::geometry,
   'Interior is disconnected%'),
  ('(k) crossing rings',
   'POLYGON ((10 140, 90 190, 130 170, 190 60, 160 10, 50 20, 10 140),
             (90 189, 10 139, 80 110, 110 130, 90 189))'::geometry,
   'Self-intersection%'),
  ('(l) cut line',
   'POLYGON ((10 100, 60 140, 60 190, 60 140, 130 170,
              190 60, 160 10, 50 20, 10 100))'::geometry,
   'Ring Self-intersection%'),
  ('(m) hole outside shell',
   'POLYGON ((10 40, 10 100, 130 130, 190 80, 140 20, 50 10, 10 40),
             (81 143, 103 143, 112 162, 107 175, 89 183, 71 169, 81 143))'::geometry,
   'Hole lies outside shell%')
)
SELECT label, geom
FROM cases
WHERE NOT ST_IsValid(geom)
  AND ST_IsValidReason(geom) LIKE reason_pattern
ORDER BY label;

label          | geom
-------------------------+----------------------------------------------------------------------------------------------------------------------------------------
 (j) disconnected interior | POLYGON((10 140,90 190,130 170,190 60,160 10,50 20,10 140),(130 170,10 140,50 120,110 110,130 170))
 (k) crossing rings       | POLYGON((10 140,90 190,130 170,190 60,160 10,50 20,10 140),(90 189,10 139,80 110,110 130,90 189))
 (l) cut line             | POLYGON((10 100,60 140,60 190,60 140,130 170,190 60,160 10,50 20,10 100))
 (m) hole outside shell   | POLYGON((10 40,10 100,130 130,190 80,140 20,50 10,10 40),(81 143,103 143,112 162,107 175,89 183,71 169,81 143))

(j) disconnected interior | 010300000002000000070000000000000000002440000000000080614000000000008056400000000000C06740000000000040604000000000004065400000000000C067400000000000004E4000000000000064400000000000002440000000000000494000000000000034400000000000002440000000000080614005000000000000000040604000000000004065400000000000002440000000000080614000000000000049400000000000005E400000000000805B400000000000805B4000000000004060400000000000406540
(k) crossing rings | 010300000002000000070000000000000000002440000000000080614000000000008056400000000000C06740000000000040604000000000004065400000000000C067400000000000004E400000000000006440000000000000244000000000000049400000000000003440000000000000244000000000008061400500000000000000008056400000000000A067400000000000002440000000000060614000000000000054400000000000805B400000000000805B40000000000040604000000000008056400000000000A06740
(l) cut line | 01030000000100000009000000000000000000244000000000000059400000000000004E4000000000008061400000000000004E400000000000C067400000000000004E400000000000806140000000000040604000000000004065400000000000C067400000000000004E40000000000000644000000000000024400000000000004940000000000000344000000000000024400000000000005940
(m) hole outside shell | 010300000002000000070000000000000000002440000000000000444000000000000024400000000000005940000000000040604000000000004060400000000000C0674000000000000054400000000000806140000000000000344000000000000049400000000000002440000000000000244000000000000044400700000000000000004054400000000000E061400000000000C059400000000000E061400000000000005C4000000000004064400000000000C05A400000000000E0654000000000004056400000000000E066400000000000C05140000000000020654000000000004054400000000000E06140

Figure

A MULTIPOLYGON is valid if:

The next example checks the MultiPolygon rules. In (n) the polygons touch at only one point. In (o) they share an edge, and in (p) their interiors overlap.

WITH cases(label, geom, valid, reason_pattern) AS (VALUES
  ('(n) point touch',
   'MULTIPOLYGON (((10 40, 29 118, 100 75, 180 88, 140 30, 70 10, 10 40)),
                  ((50 170, 100 190, 160 180, 180 140, 180 90, 120 120,
                    30 120, 50 170)))'::geometry,
   true, NULL),
  ('(o) shared edge',
   'MULTIPOLYGON (((10 40, 30 120, 120 120, 180 88, 140 30, 70 10, 10 40)),
                  ((50 170, 100 190, 160 180, 180 140, 180 90, 120 120,
                    30 120, 50 170)))'::geometry,
   false, 'Self-intersection%'),
  ('(p) overlap',
   'MULTIPOLYGON (((10 40, 42 93, 130 150, 180 86, 140 30, 70 10, 10 40)),
                  ((50 170, 100 190, 138 182, 149 156, 146 137, 100 100,
                    58 140, 50 170)))'::geometry,
   false, 'Self-intersection%')
)
SELECT label, geom
FROM cases
WHERE ST_IsValid(geom) = valid
  AND (valid OR ST_IsValidReason(geom) LIKE reason_pattern)
ORDER BY label;

label       | geom
------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 (n) point touch  | MULTIPOLYGON(((10 40,29 118,100 75,180 88,140 30,70 10,10 40)),((50 170,100 190,160 180,180 140,180 90,120 120,30 120,50 170)))
 (o) shared edge  | MULTIPOLYGON(((10 40,30 120,120 120,180 88,140 30,70 10,10 40)),((50 170,100 190,160 180,180 140,180 90,120 120,30 120,50 170)))
 (p) overlap      | MULTIPOLYGON(((10 40,42 93,130 150,180 86,140 30,70 10,10 40)),((50 170,100 190,138 182,149 156,146 137,100 100,58 140,50 170)))

(n) point touch | 01060000000200000001030000000100000007000000000000000000244000000000000044400000000000003D400000000000805D4000000000000059400000000000C052400000000000806640000000000000564000000000008061400000000000003E400000000000805140000000000000244000000000000024400000000000004440010300000001000000080000000000000000004940000000000040654000000000000059400000000000C067400000000000006440000000000080664000000000008066400000000000806140000000000080664000000000008056400000000000005E400000000000005E400000000000003E400000000000005E4000000000000049400000000000406540
(o) shared edge | 01060000000200000001030000000100000007000000000000000000244000000000000044400000000000003E400000000000005E400000000000005E400000000000005E400000000000806640000000000000564000000000008061400000000000003E400000000000805140000000000000244000000000000024400000000000004440010300000001000000080000000000000000004940000000000040654000000000000059400000000000C067400000000000006440000000000080664000000000008066400000000000806140000000000080664000000000008056400000000000005E400000000000005E400000000000003E400000000000005E4000000000000049400000000000406540
(p) overlap | 01060000000200000001030000000100000007000000000000000000244000000000000044400000000000004540000000000040574000000000004060400000000000C062400000000000806640000000000080554000000000008061400000000000003E400000000000805140000000000000244000000000000024400000000000004440010300000001000000080000000000000000004940000000000040654000000000000059400000000000C0674000000000004061400000000000C066400000000000A06240000000000080634000000000004062400000000000206140000000000000594000000000000059400000000000004D40000000000080614000000000000049400000000000406540

Figure

These rules mean that valid polygonal geometry is also simple.

For linear geometry the only validity rule is that LINESTRINGs must have at least two points and have non-zero length (or equivalently, have at least two distinct points.) Note that non-simple (self-intersecting) lines are valid.

SELECT
ST_IsValid('LINESTRING(0 0,1 1)') AS len_nonzero,
ST_IsValid('LINESTRING(0 0,0 0,0 0)') AS len_zero,
ST_IsValid('LINESTRING(10 10,150 150,180 50,20 130)') AS self_int;

len_nonzero | len_zero | self_int
-------------+----------+----------
 t           | f        | t

Figure

POINT and MULTIPOINT geometries have no validity rules.

PostGIS allows creating and storing both valid and invalid Geometry. This allows invalid geometry to be detected and flagged or fixed. There are also situations where the OGC validity rules are stricter than desired (examples of this are zero-length linestrings and polygons with inverted holes.)

Many of the functions provided by PostGIS rely on the assumption that geometry arguments are valid. For example, it does not make sense to calculate the area of a polygon that has a hole defined outside of the polygon, or to construct a polygon from a non-simple boundary line. Assuming valid geometric inputs allows functions to operate more efficiently, since they do not need to check for topological correctness. (Notable exceptions are that zero-length lines and polygons with inversions are generally handled correctly.) Also, most PostGIS functions produce valid geometry output if the inputs are valid. This allows PostGIS functions to be chained together safely.

If you encounter unexpected error messages when calling PostGIS functions (such as "GEOS Intersection() threw an error!"), you should first confirm that the function arguments are valid. If they are not, then consider using one of the techniques below to ensure the data you are processing is valid.

	If a function reports an error with valid inputs, then you may have found an error in either PostGIS or one of the libraries it uses, and you should report this to the PostGIS project. The same is true if a PostGIS function returns an invalid geometry for valid input.

To test if a geometry is valid use the ST_IsValid function:

SELECT ST_IsValid('POLYGON ((20 180,180 180,180 20,20 20,20 180))');

t

Figure

Information about the nature and location of an geometry invalidity are provided by the ST_IsValidDetail function:

SELECT valid, reason,location AS location
        FROM ST_IsValidDetail('POLYGON ((20 20,120 190,50 190,170 50,20 20))') AS t;

valid |      reason       |                  location
-------+-------------------+---------------------------------------------
 f     | Self-intersection | POINT(91.51162790697674 141.56976744186048)

f | Self-intersection | 0101000000E80BFA82BEE05640B923EE883BB26140

Figure

In some situations it is desirable to correct invalid geometry automatically. Use the ST_MakeValid function to do this. (ST_MakeValid is a case of a spatial function that does allow invalid input!)

By default, PostGIS does not check for validity when loading geometry, because validity testing can take a lot of CPU time for complex geometries. If you do not trust your data sources, you can enforce a validity check on your tables by adding a check constraint:

ALTER TABLE mytable
ADD CONSTRAINT geometry_valid_check
  CHECK (ST_IsValid(geom));

The SPATIAL_REF_SYS table used by PostGIS is an OGC-compliant database table that defines the available spatial reference systems. It holds the numeric SRIDs and textual descriptions of the coordinate systems.

The spatial_ref_sys table definition is:

CREATE TABLE spatial_ref_sys (
  srid       INTEGER NOT NULL PRIMARY KEY,
  auth_name  VARCHAR(256),
  auth_srid  INTEGER,
  srtext     VARCHAR(2048),
  proj4text  VARCHAR(2048)
)

The columns are:

When retrieving spatial reference system definitions for use in transformations, PostGIS uses fhe following strategy:

The PostGIS spatial_ref_sys table contains over 3000 of the most common spatial reference system definitions that are handled by the PROJ projection library. But there are many coordinate systems that it does not contain. You can add SRS definitions to the table if you have the required information about the spatial reference system. Or, you can define your own custom spatial reference system if you are familiar with PROJ constructs. Keep in mind that most spatial reference systems are regional and have no meaning when used outside of the bounds they were intended for.

A resource for finding spatial reference systems not defined in the core set is http://spatialreference.org/

Some commonly used spatial reference systems are: 4326 - WGS 84 Long Lat, 4269 - NAD 83 Long Lat, 3395 - WGS 84 World Mercator, 2163 - US National Atlas Equal Area, and the 60 WGS84 UTM zones. UTM zones are one of the most ideal for measurement, but only cover 6-degree regions. For WGS84 longitude/latitude data, the UTM zone is least(floor((longitude + 180) / 6) + 1, 60); the EPSG code is 32600 + zone in the northern hemisphere or 32700 + zone in the southern hemisphere.

US states use State Plane spatial reference systems (meter or feet based) - usually one or 2 exists per state. Most of the meter-based ones are in the core set, but many of the feet-based ones or ESRI-created ones will need to be copied from spatialreference.org.

You can even define non-Earth-based coordinate systems, such as Mars 2000 This Mars coordinate system is non-planar (it's in degrees spheroidal), but you can use it with the geography type to obtain length and proximity measurements in meters instead of degrees.

Here is an example of loading a custom coordinate system using an unassigned SRID and the PROJ definition for a US-centric Lambert Conformal projection:

INSERT INTO spatial_ref_sys (srid, proj4text)
VALUES ( 990000,
  '+proj=lcc  +lon_0=-95 +lat_0=25 +lat_1=25 +lat_2=25 +x_0=0 +y_0=0 +datum=WGS84 +units=m +no_defs'
);

You can create a table to store geometry data using the CREATE TABLE SQL statement with a column of type geometry. The following example creates a table with a geometry column storing 2D (XY) LineStrings in the BC-Albers coordinate system (SRID 3005):

CREATE TABLE roads (
  id SERIAL PRIMARY KEY,
  name VARCHAR(64),
  geom geometry(LINESTRING, 3005)
);

The geometry type supports two optional type modifiers:

Examples of creating tables with geometry columns:

Create a table holding any kind of geometry with the default SRID:

CREATE TABLE geoms(gid serial PRIMARY KEY, geom geometry);

Create a table with 2D POINT geometry with the default SRID:

CREATE TABLE pts(gid serial PRIMARY KEY, geom geometry(POINT));

Create a table with 3D (XYZ) POINTs and an explicit SRID of 3005:

CREATE TABLE pts(gid serial PRIMARY KEY, geom geometry(POINTZ, 3005));

Create a table with 4D (XYZM) LINESTRING geometry with the default SRID:

CREATE TABLE lines(gid serial PRIMARY KEY, geom geometry(LINESTRINGZM));

Create a table with 2D POLYGON geometry with the SRID 4267 (NAD 1927 long lat):

CREATE TABLE polys(gid serial PRIMARY KEY, geom geometry(POLYGON, 4267));

It is possible to have more than one geometry column in a table. This can be specified when the table is created, or a column can be added using the ALTER TABLE SQL statement. This example adds a column that can hold 3D LineStrings:

ALTER TABLE roads ADD COLUMN geom2 geometry(LINESTRINGZ, 4326);

The OGC Simple Features Specification for SQL defines the GEOMETRY_COLUMNS metadata table to describe geometry table structure. In PostGIS geometry_columns is a view reading from database system catalog tables. This ensures that the spatial metadata information is always consistent with the currently defined tables and views. The view structure is:

\d geometry_columns

View "public.geometry_columns"
      Column       |          Type          | Modifiers
-------------------+------------------------+-----------
 f_table_catalog   | character varying(256) |
 f_table_schema    | character varying(256) |
 f_table_name      | character varying(256) |
 f_geometry_column | character varying(256) |
 coord_dimension   | integer                |
 srid              | integer                |
 type              | character varying(30)  |

The columns are:

Two of the cases where you may need this are the case of SQL Views and bulk inserts. For bulk insert case, you can correct the registration in the geometry_columns table by constraining the column or doing an alter table. For views, you could expose using a CAST operation. Note, if your column is typmod based, the creation process would register it correctly, so no need to do anything. Also views that have no spatial function applied to the geometry will register the same as the underlying table geometry column.

For example, suppose you have a view created like this.

CREATE VIEW public.vwmytablemercator AS
SELECT gid, ST_Transform(geom, 3395) As geom, f_name
FROM public.mytable;

To register the geometry column correctly, cast the transformed geometry.

DROP VIEW public.vwmytablemercator;
CREATE VIEW  public.vwmytablemercator AS
    SELECT gid, ST_Transform(geom, 3395)::geometry(Geometry, 3395) As geom, f_name
    FROM public.mytable;

If the geometry type is known to be a 2D Polygon, specify it in the cast.

DROP VIEW public.vwmytablemercator;
CREATE VIEW  public.vwmytablemercator AS
    SELECT gid, ST_Transform(geom, 3395)::geometry(Polygon, 3395) As geom, f_name
    FROM public.mytable;

For a derivative table created by a bulk insert, create the table first.

SELECT poi.gid, poi.geom, citybounds.city_name
INTO myschema.my_special_pois
FROM poi INNER JOIN citybounds ON ST_Intersects(citybounds.geom, poi.geom);

Create a 2D spatial index on the new table.

CREATE INDEX idx_myschema_myspecialpois_geom_gist
ON myschema.my_special_pois USING gist(geom);

If the points are 3D or measured, an n-D index may be more appropriate.

CREATE INDEX my_special_pois_geom_gist_nd
ON my_special_pois USING gist(geom gist_geometry_ops_nd);

Manually register the new table's geometry column in geometry_columns. This also changes the underlying table structure to make the column typmod-based.

SELECT Populate_Geometry_Columns('myschema.my_special_pois'::regclass);

To retain constraint-based definition behavior, such as for inherited tables where all children do not have the same type and SRID, set the optional use_typmod argument to false.

SELECT Populate_Geometry_Columns('myschema.my_special_pois'::regclass, false);

Although the old constraint-based method is still supported, direct pass-through view columns register correctly in geometry_columns for both typmod-based and constraint-based source columns. In this example we define a column using typmod and another using constraints.

CREATE TABLE pois_ny(gid SERIAL PRIMARY KEY, poi_name text, cat text, geom geometry(POINT, 4326));
SELECT AddGeometryColumn('pois_ny', 'geom_2160', 2160, 'POINT', 2, false);

If we run \d pois_ny in psql, we observe they are defined differently -- one is typmod, one is constraint.

\d pois_ny

Table "public.pois_ny"
  Column   |         Type          |                       Modifiers

-----------+-----------------------+------------------------------------------------------
 gid       | integer               | not null default nextval('pois_ny_gid_seq'::regclass)
 poi_name  | text                  |
 cat       | character varying(20) |
 geom      | geometry(Point,4326)  |
 geom_2160 | geometry              |
Indexes:
    "pois_ny_pkey" PRIMARY KEY, btree (gid)
Check constraints:
    "enforce_dims_geom_2160" CHECK (st_ndims(geom_2160) = 2)
    "enforce_geotype_geom_2160" CHECK (geometrytype(geom_2160) = 'POINT'::text
        OR geom_2160 IS NULL)
    "enforce_srid_geom_2160" CHECK (st_srid(geom_2160) = 2160)

In geometry_columns, they both register correctly

SELECT f_table_name, f_geometry_column, srid, type
FROM geometry_columns
WHERE f_table_name = 'pois_ny';

f_table_name | f_geometry_column | srid | type
-------------+-------------------+------+-------
pois_ny      | geom              | 4326 | POINT
pois_ny      | geom_2160         | 2160 | POINT

However -- if we were to create a view like this

CREATE VIEW vw_pois_ny_parks AS
SELECT *
  FROM pois_ny
  WHERE cat='park';

SELECT f_table_name, f_geometry_column, srid, type
    FROM geometry_columns
    WHERE f_table_name = 'vw_pois_ny_parks';

Both the typmod based geom view column and the direct constraint based view column register correctly.

f_table_name   | f_geometry_column | srid |   type
------------------+-------------------+------+----------
 vw_pois_ny_parks | geom              | 4326 | POINT
 vw_pois_ny_parks | geom_2160         | 2160 | POINT

If the view applies a spatial function to the geometry, such as ST_Transform, you still need to cast the transformed column explicitly so the view can expose the correct type and SRID:

DROP VIEW vw_pois_ny_parks;
CREATE VIEW vw_pois_ny_parks AS
SELECT gid, poi_name, cat,
  geom,
  ST_Transform(geom, 2160)::geometry(POINT, 2160) As geom_2160
  FROM pois_ny
  WHERE cat = 'park';
SELECT f_table_name, f_geometry_column, srid, type
    FROM geometry_columns
    WHERE f_table_name = 'vw_pois_ny_parks';

f_table_name   | f_geometry_column | srid | type
------------------+-------------------+------+-------
 vw_pois_ny_parks | geom              | 4326 | POINT
 vw_pois_ny_parks | geom_2160         | 2160 | POINT

If spatial data can be converted to a text representation (as either WKT or WKB), then using SQL might be the easiest way to get data into PostGIS. Data can be bulk-loaded into PostGIS/PostgreSQL by loading a text file of SQL INSERT statements using the psql SQL utility.

A SQL load file (roads.sql for example) might look like this:

BEGIN;
INSERT INTO roads (road_id, roads_geom, road_name)
  VALUES (1, 'LINESTRING(191232 243118,191108 243242)', 'Jeff Rd');
INSERT INTO roads (road_id, roads_geom, road_name)
  VALUES (2, 'LINESTRING(189141 244158,189265 244817)', 'Geordie Rd');
INSERT INTO roads (road_id, roads_geom, road_name)
  VALUES (3, 'LINESTRING(192783 228138,192612 229814)', 'Paul St');
INSERT INTO roads (road_id, roads_geom, road_name)
  VALUES (4, 'LINESTRING(189412 252431,189631 259122)', 'Graeme Ave');
INSERT INTO roads (road_id, roads_geom, road_name)
  VALUES (5, 'LINESTRING(190131 224148,190871 228134)', 'Phil Tce');
INSERT INTO roads (road_id, roads_geom, road_name)
  VALUES (6, 'LINESTRING(198231 263418,198213 268322)', 'Dave Cres');
COMMIT;

The SQL file can be loaded into PostgreSQL using psql:

psql -d [database] -f roads.sql

The shp2pgsql data loader converts Shapefiles into SQL suitable for insertion into a PostGIS/PostgreSQL database either in geometry or geography format. The loader has several operating modes selected by command line flags.

The input must be an unpacked shapefile path. The loader does not read .zip archives directly; unpack the .shp, .shx, and .dbf files before loading.

There is also a shp2pgsql-gui graphical interface with most of the options as the command-line loader. This may be easier to use for one-off non-scripted loading or if you are new to PostGIS. It can also be configured as a plugin to PgAdminIII.

An example session using the loader to create an input file and loading it might look like this:

shp2pgsql -c -D -s 4269 -i -I shaperoads.shp myschema.roadstable > roads.sql
psql -d roadsdb -f roads.sql

A conversion and load can be done in one step using UNIX pipes:

shp2pgsql shaperoads.shp myschema.roadstable | psql -d roadsdb

To load a directory of Shapefiles, run the loader once per file and derive the table name from the file name. On Windows cmd.exe, use a single percent sign interactively and double percent signs in a batch file:

REM In a .bat file, use %%F. At an interactive cmd.exe prompt, use %F.
for %%F in ("C:\data\shapefiles\*.shp") do (
  shp2pgsql -d -D -s 4269 -I -W UTF-8 "%%~fF" "myschema.%%~nF" | psql -d roadsdb
)

If every Shapefile has the same attributes and data types and should be appended into one table, create the table from the first file and use -a for the remaining files.

The most straightforward way of extracting spatial data out of the database is to use a SQL SELECT query to define the data set to be extracted and dump the resulting columns into a parsable text file:

SELECT road_id,road_geom AS geom, road_name FROM roads;

road_id | geom                                    | road_name
--------+-----------------------------------------+-----------
      1 | LINESTRING(191232 243118,191108 243242) | Jeff Rd
      2 | LINESTRING(189141 244158,189265 244817) | Geordie Rd
      3 | LINESTRING(192783 228138,192612 229814) | Paul St
      4 | LINESTRING(189412 252431,189631 259122) | Graeme Ave
      5 | LINESTRING(190131 224148,190871 228134) | Phil Tce
      6 | LINESTRING(198231 263418,198213 268322) | Dave Cres
      7 | LINESTRING(218421 284121,224123 241231) | Chris Way
(6 rows)

There will be times when some kind of restriction is necessary to cut down the number of records returned. In the case of attribute-based restrictions, use the same SQL syntax as used with a non-spatial table. In the case of spatial restrictions, the following functions are useful:

Next, you can use these operators in queries. Note that when specifying geometries and boxes on the SQL command line, you must explicitly turn the string representations into geometries function. The 312 is a fictitious spatial reference system that matches our data. So, for example:

SELECT road_id, road_name
FROM roads
WHERE roads_geom='SRID=312;LINESTRING(191232 243118,191108 243242)'::geometry;

The above query would return the single record from the "ROADS_GEOM" table in which the geometry was equal to that value.

To check whether some of the roads passes in the area defined by a polygon:

SELECT road_id, road_name
FROM roads
WHERE ST_Intersects(roads_geom, 'SRID=312;POLYGON((...))');

The most common spatial query will probably be a "frame-based" query, used by client software, like data browsers and web mappers, to grab a "map frame" worth of data for display.

When using the "&&" operator, you can specify either a BOX3D as the comparison feature or a GEOMETRY. When you specify a GEOMETRY, however, its bounding box will be used for the comparison.

Using a "BOX3D" object for the frame, such a query looks like this:

SELECT roads_geom AS geom
FROM roads
WHERE
  roads_geom && ST_MakeEnvelope(191232, 243117,191232, 243119,312);

Note the use of the SRID 312, to specify the projection of the envelope.

The pgsql2shp table dumper connects to the database and converts a table (possibly defined by a query) into a shape file. The basic syntax is:

pgsql2shp [<options
>] <database
> [<schema
>.]<table>

pgsql2shp [<options
>] <database
> <query>

The commandline options are:

GiST stands for "Generalized Search Tree" and is a generic form of indexing for multi-dimensional data. PostGIS uses an R-Tree index implemented on top of GiST to index spatial data. GiST is the most commonly-used and versatile spatial index method, and offers very good query performance. Other implementations of GiST are used to speed up searches on all kinds of irregular data structures (integer arrays, spectral data, etc) which are not amenable to normal B-Tree indexing. For more information see the PostgreSQL manual.

Once a spatial data table exceeds a few thousand rows, you will want to build an index to speed up spatial searches of the data (unless all your searches are based on attributes, in which case you'll want to build a normal index on the attribute fields).

The syntax for building a GiST index on a "geometry" column is as follows:

CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield]);

The above syntax will always build a 2D-index. To get the an n-dimensional index for the geometry type, you can create one using this syntax:

CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] gist_geometry_ops_nd);

Building a spatial index is a computationally intensive exercise. It also blocks write access to your table for the time it creates, so on a production system you may want to do in in a slower CONCURRENTLY-aware way:

CREATE INDEX CONCURRENTLY [indexname] ON [tablename] USING GIST ( [geometryfield]);

After building an index, it is sometimes helpful to force PostgreSQL to collect table statistics, which are used to optimize query plans:

VACUUM ANALYZE [table_name] [(column_name)];

BRIN stands for "Block Range Index". It is a general-purpose index method provided by PostgreSQL. BRIN is a lossy index method, meaning that a secondary check is required to confirm that a record matches a given search condition (which is the case for all provided spatial indexes). It provides much faster index creation and much smaller index size, with reasonable read performance. Its primary purpose is to support indexing very large tables on columns which have a correlation with their physical location within the table. In addition to spatial indexing, BRIN can speed up searches on various kinds of attribute data structures (integer, arrays etc). For more information see the PostgreSQL manual.

Once a spatial table exceeds a few thousand rows, you will want to build an index to speed up spatial searches of the data. GiST indexes are very performant as long as their size doesn't exceed the amount of RAM available for the database, and as long as you can afford the index storage size, and the cost of index update on write. Otherwise, for very large tables BRIN index can be considered as an alternative.

A BRIN index stores the bounding box enclosing all the geometries contained in the rows in a contiguous set of table blocks, called a block range. When executing a query using the index the block ranges are scanned to find the ones that intersect the query extent. This is efficient only if the data is physically ordered so that the bounding boxes for block ranges have minimal overlap (and ideally are mutually exclusive). The resulting index is very small in size, but is typically less performant for read than a GiST index over the same data.

Building a BRIN index is much less CPU-intensive than building a GiST index. It's common to find that a BRIN index is ten times faster to build than a GiST index over the same data. And because a BRIN index stores only one bounding box for each range of table blocks, it's common to use up to a thousand times less disk space than a GiST index.

You can choose the number of blocks to summarize in a range. If you decrease this number, the index will be bigger but will probably provide better performance.

For BRIN to be effective, the table data should be stored in a physical order which minimizes the amount of block extent overlap. It may be that the data is already sorted appropriately (for instance, if it is loaded from another dataset that is already sorted in spatial order). Otherwise, this can be accomplished by sorting the data by a one-dimensional spatial key. One way to do this is to create a new table sorted by the geometry values (which in recent PostGIS versions uses an efficient Hilbert curve ordering):

CREATE TABLE table_sorted AS
SELECT * FROM table  ORDER BY geom;

Alternatively, data can be sorted in-place by using a GeoHash as a (temporary) index, and clustering on that index:

CREATE INDEX idx_temp_geohash ON table
    USING btree (ST_GeoHash(ST_Transform(geom, 4326 ), 20));
CLUSTER table USING idx_temp_geohash;

The syntax for building a BRIN index on a geometry column is:

CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geome_col]);

The above syntax builds a 2D index. To build a 3D-dimensional index, use this syntax:

CREATE INDEX [indexname] ON [tablename]
USING BRIN ([geome_col] brin_geometry_inclusion_ops_3d);

You can also get a 4D-dimensional index using the 4D operator class:

CREATE INDEX [indexname] ON [tablename]
USING BRIN ([geome_col] brin_geometry_inclusion_ops_4d);

The above commands use the default number of blocks in a range, which is 128. To specify the number of blocks to summarise in a range, use this syntax

CREATE INDEX [indexname] ON [tablename]
USING BRIN ( [geome_col] ) WITH (pages_per_range = [number]); 

Keep in mind that a BRIN index only stores one index entry for a large number of rows. If your table stores geometries with a mixed number of dimensions, it's likely that the resulting index will have poor performance. You can avoid this performance penalty by choosing the operator class with the least number of dimensions of the stored geometries

The geography datatype is supported for BRIN indexing. The syntax for building a BRIN index on a geography column is:

CREATE INDEX [indexname] ON [tablename] USING BRIN ( [geog_col]);

The above syntax builds a 2D-index for geospatial objects on the spheroid.

Currently, only "inclusion support" is provided, meaning that just the &&, ~ and @ operators can be used for the 2D cases (for both geometry and geography), and just the &&& operator for 3D geometries. There is currently no support for kNN searches.

An important difference between BRIN and other index types is that the database does not maintain the index dynamically. Changes to spatial data in the table are simply appended to the end of the index. This will cause index search performance to degrade over time. The index can be updated by performing a VACUUM, or by using a special function brin_summarize_new_values(regclass). For this reason BRIN may be most appropriate for use with data that is read-only, or only rarely changing. For more information refer to the manual.

To summarize using BRIN for spatial data:

SP-GiST stands for "Space-Partitioned Generalized Search Tree" and is a generic form of indexing for multi-dimensional data types that supports partitioned search trees, such as quad-trees, k-d trees, and radix trees (tries). The common feature of these data structures is that they repeatedly divide the search space into partitions that need not be of equal size. In addition to spatial indexing, SP-GiST is used to speed up searches on many kinds of data, such as phone routing, ip routing, substring search, etc. For more information see the PostgreSQL manual.

As it is the case for GiST indexes, SP-GiST indexes are lossy, in the sense that they store the bounding box enclosing spatial objects. SP-GiST indexes can be considered as an alternative to GiST indexes.

Once a GIS data table exceeds a few thousand rows, an SP-GiST index may be used to speed up spatial searches of the data. The syntax for building an SP-GiST index on a "geometry" column is as follows:

CREATE INDEX [indexname] ON [tablename] USING SPGIST ( [geometryfield]);

The above syntax will build a 2-dimensional index. A 3-dimensional index for the geometry type can be created using the 3D operator class:

CREATE INDEX [indexname] ON [tablename] USING SPGIST ([geometryfield] spgist_geometry_ops_3d);

Building a spatial index is a computationally intensive operation. It also blocks write access to your table for the time it creates, so on a production system you may want to do in in a slower CONCURRENTLY-aware way:

CREATE INDEX CONCURRENTLY [indexname] ON [tablename] USING SPGIST ( [geometryfield]);

After building an index, it is sometimes helpful to force PostgreSQL to collect table statistics, which are used to optimize query plans:

VACUUM ANALYZE [table_name] [(column_name)];

An SP-GiST index can accelerate queries involving the following operators:

There is no support for kNN searches at the moment.

Ordinarily, indexes invisibly speed up data access: once an index is built, the PostgreSQL query planner automatically decides when to use it to improve query performance. But there are some situations where the planner does not choose to use existing indexes, so queries end up using slow sequential scans instead of a spatial index.

If you find your spatial indexes are not being used, there are a few things you can do:

For more information see the Postgres manual section on Query Planning.

Secondo la specifica OpenGIS Simple Features Implementation Specification for SQL, "l'approccio di base per comparare due geometrie è quello di determinare l'interesezione tra gli interni, i confini e gli esterni di ognuna, classificando così la relazione tra le due geometrie attraverso la risultante matrice di 'intersezione'."

Nella teoria della topologia degli insiemi di punti, i punti di una geometria nello spazio bidimensionale sono categorizzate in 3 insiemi:

L'articolo Dimensionally Extended 9-Intersection Model (DE-9IM) descrive la relazione spatiale tra due geometrie specificando le dimensioni delle 9 intersezioni tra gli insiemi di punti sopracitati di ogni geometria. La dimensione dell'intersezione può essere formalmente rappresentata in una matrice d'intersezione 3x3.

Per una geometria g Interno, Confine e Esterno si denotano usando la notazione I(g), B(g) ed E(g). Inoltre, dim(s) denota la dimensione di un insieme s nel dominio {0,1,2,F}:

Usando questa notazione, la matrice d'intersezione per due geometrie a e b è:

The following overlapping polygons provide a concrete example. ST_Relate computes the matrix 212101212; it matches the overlap pattern T*T***T** used by ST_Overlaps.

WITH example AS (
  SELECT
    'POLYGON ((140 140,140 122,135 105,126 100,118 99,110 94,100 86,97 73,102 59,98 49,87 38,70 30,55 29,40 30,28 38,20 50,14 66,10 84,6 100,4 119,4 143,6 166,10 180,18 191,28 195,40 190,55 189,67 186,86 179,112 165,124 158,133 148,140 140),(48 177,40 177,30 174,24 166,22 159,25 155,30 153,40 154,45 157,52 162,53 169,51 174,48 177))'::geometry AS a,
    'POLYGON ((75 63,79 50,87 38,95 31,108 25,124 22,140 18,154 11,166 6,176 10,184 21,188 35,190 58,190 82,193 104,190 121,185 139,178 154,166 163,154 171,139 172,124 171,112 165,96 152,92 142,92 126,86 116,79 110,75 104,72 94,73 86,75 76,75 63))'::geometry AS b
), relation AS (
  SELECT a, b, ST_Relate(a, b) AS matrix
  FROM example
)
SELECT matrix,
       ST_RelateMatch(matrix, 'T*T***T**') AS matches_overlap_pattern,
       ST_Overlaps(a, b) AS overlaps
FROM relation;

matrix   | matches_overlap_pattern | overlaps
-----------+-------------------------+----------
 212101212 | t                       | t
(1 row)

Figure

The executable query and generated overlay above are the visual source for the relation. The query below renders the nine cells of the same kind of matrix as a 3-by-3 set of examples. The final exterior/exterior cell is clipped to a finite viewport, since the true exterior/exterior intersection is unbounded.

WITH example AS (
  SELECT
    ST_MakeEnvelope(0, 0, 6, 4) AS a,
    ST_MakeEnvelope(3, -1, 8, 3) AS b,
    ST_MakeEnvelope(-1, -2, 9, 5) AS viewport
), parts AS (
  SELECT
    a,
    b,
    ST_Boundary(a) AS ba,
    ST_Boundary(b) AS bb,
    viewport
  FROM example
)
SELECT
  ST_Relate(a, b) AS matrix,
  ST_AsText(ST_Normalize(a)) AS input_a,
  ST_AsText(ST_Normalize(b)) AS input_b,
  ST_AsText(ST_Normalize(ST_Intersection(a, b))) AS "2 I(a) ∩ I(b)",
  ST_AsText(ST_Normalize(ST_Intersection(a, bb))) AS "1 I(a) ∩ B(b)",
  ST_AsText(ST_Normalize(ST_Difference(a, b))) AS "2 I(a) ∩ E(b)",
  ST_AsText(ST_Normalize(ST_Intersection(ba, b))) AS "1 B(a) ∩ I(b)",
  ST_AsText(ST_Normalize(ST_Intersection(ba, bb))) AS "0 B(a) ∩ B(b)",
  ST_AsText(ST_Normalize(ST_Collect(
      'LINESTRING(0 0,0 4,6 4,6 3)'::geometry,
      'LINESTRING(0 0,3 0)'::geometry
  ))) AS "1 B(a) ∩ E(b)",
  ST_AsText(ST_Normalize(ST_Difference(b, a))) AS "2 E(a) ∩ I(b)",
  ST_AsText(ST_Normalize(ST_Collect(
      'LINESTRING(3 -1,8 -1,8 3,6 3)'::geometry,
      'LINESTRING(3 -1,3 0)'::geometry
  ))) AS "1 E(a) ∩ B(b)",
  ST_AsText(ST_Normalize(ST_Difference(viewport, ST_Union(a, b))))
    AS "2 E(a) ∩ E(b) clipped"
FROM parts;

-[ RECORD 1 ]----------+-----------------------------------------------------------------------------
matrix                 | 212101212
input_a                | POLYGON((0 0,0 4,6 4,6 0,0 0))
input_b                | POLYGON((3 -1,3 3,8 3,8 -1,3 -1))
2 I(a) ∩ I(b)          | POLYGON((3 0,3 3,6 3,6 0,3 0))
1 I(a) ∩ B(b)          | LINESTRING(3 0,3 3,6 3)
2 I(a) ∩ E(b)          | POLYGON((0 0,0 4,6 4,6 3,3 3,3 0,0 0))
1 B(a) ∩ I(b)          | LINESTRING(3 0,6 0,6 3)
0 B(a) ∩ B(b)          | MULTIPOINT((6 3),(3 0))
1 B(a) ∩ E(b)          | MULTILINESTRING((0 0,0 4,6 4,6 3),(0 0,3 0))
2 E(a) ∩ I(b)          | POLYGON((3 -1,3 0,6 0,6 3,8 3,8 -1,3 -1))
1 E(a) ∩ B(b)          | MULTILINESTRING((3 -1,8 -1,8 3,6 3),(3 -1,3 0))
2 E(a) ∩ E(b) clipped  | POLYGON((-1 -2,-1 5,9 5,9 -2,-1 -2),(0 0,3 0,3 -1,8 -1,8 3,6 3,6 4,0 4,0 0))

Figure

Leggendola da sinistra a destra e dall'alto verso il basso, la matrice d'intersezione viene rappresentata come la stringa di testo '212101212'.

Per maggiori informazioni si veda:

To make it easy to determine common spatial relationships, the OGC SFS defines a set of named spatial relationship predicates. PostGIS provides these as the functions ST_Contains, ST_Crosses, ST_Disjoint, ST_Equals, ST_Intersects, ST_Overlaps, ST_Touches, ST_Within. It also defines the non-standard relationship predicates ST_Covers, ST_CoveredBy, and ST_ContainsProperly.

Spatial predicates are usually used as conditions in SQL WHERE or JOIN clauses. The named spatial predicates automatically use a spatial index if one is available, so there is no need to use the bounding box operator && as well. For example:

SELECT city.name, state.name, city.geom
FROM city JOIN state ON ST_Intersects(city.geom, state.geom);

For more details and illustrations, see the PostGIS Workshop.

In some cases the named spatial relationships are insufficient to provide a desired spatial filter condition. These requirements can be expressed by computing the full DE-9IM intersection matrix with ST_Relate.

To test a particular spatial relationship, an intersection matrix pattern is used. This is the matrix representation augmented with the additional symbols {T,*}:

Using intersection matrix patterns, specific spatial relationships can be evaluated succinctly. The ST_Relate and ST_RelateMatch functions can both test these patterns.

These road segments share a line segment. ST_Crosses does not find this relationship for linear features, because it only returns true when their interiors intersect at a point. The pattern 1*1***1** tests for a one-dimensional intersection instead.

WITH roads AS (
  SELECT
    'LINESTRING(10 10,40 90,70 110,140 110,170 130,190 190)'::geometry AS "road A",
    'LINESTRING(10 190,50 130,90 110,130 110,160 70,180 10)'::geometry AS "road B"
), relation AS (
  SELECT "road A", "road B", ST_Relate("road A", "road B") AS matrix
  FROM roads
)
SELECT
  ST_Intersection("road A", "road B") AS overlap,
  matrix,
  ST_Crosses("road A", "road B") AS "ST_Crosses",
  ST_RelateMatch(matrix, '1*1***1**') AS "matches 1*1***1**"
FROM relation;

overlap           |  matrix   | ST_Crosses | matches 1*1***1**
----------------------------+-----------+------------+-------------------
 LINESTRING(90 110,130 110) | 1F1FF0102 | f          | t
(1 row)

01020000000200000000000000008056400000000000805B4000000000004060400000000000805B40 | 1F1FF0102 | f | t

Figure

The next example finds a wharf which runs from the lake interior onto its shoreline, with one endpoint on the shoreline. The other wharves provide nearby counterexamples in the figure. The pattern 102101FF2 expresses the complete required relationship in one test.

WITH data AS (
  SELECT
    'POLYGON((-20 10,30 30,80 70,110 80,145 85,190 110,300 220,230 220,-20 220,-20 10))'::geometry AS lake,
    'MULTILINESTRING((120 180,145 85,110 80),(10 130,30 70),(90 140,110 80,94.81118881118876 75.83496503496498),(150 160,180 80))'::geometry AS wharves
), wharf AS (
  SELECT lake, (item).path[1] AS id, (item).geom
  FROM data CROSS JOIN LATERAL ST_Dump(wharves) AS item
), relation AS (
  SELECT id, geom, ST_Relate(lake, geom) AS matrix
  FROM wharf
)
SELECT
  id AS wharf,
  geom AS "matching wharf",
  matrix
FROM relation
WHERE ST_RelateMatch(matrix, '102101FF2')
ORDER BY id;

wharf |           matching wharf           |  matrix
-------+-----------------------------------+-----------
     1 | LINESTRING(120 180,145 85,110 80) | 102101FF2
(1 row)

1 | 0102000000030000000000000000005E400000000000806640000000000020624000000000004055400000000000805B400000000000005440 | 102101FF2

Figure

Current PostgreSQL versions suffer from a query optimizer weakness regarding TOAST tables. TOAST tables are a kind of "extension room" used to store large (in the sense of data size) values that do not fit into normal data pages (like long texts, images or complex geometries with lots of vertices), see the PostgreSQL Documentation for TOAST for more information).

Il problema si presenta se si ha una tabella con geometrie piuttosto grandi, ma non troppe righe (come una tabella contenente i confini di tutti i paesi europei in alta risoluzione). In questo caso, la tabella stessa è piccola, ma utilizza molto spazio TOAST. Nel nostro caso di esempio, la tabella stessa aveva circa 80 righe e utilizzava solo 3 pagine di dati, ma la tabella TOAST utilizzava 8225 pagine.

Ora si lanci una query che usi l'operatore && e che trovi solo poche righe. L'ottimizzatore di query ora vede che la tabella ha solo 3 pagine e 80 record. Stima che una scansione sequenziale su una tabella cosi' piccola e' molto piu' veloce rispetto all'uso di un indice, e quindi decide di ignorare l'indice GiST. Normalmente questa stima e' corretta, ma nel nostro caso l'operatorore && deve estrarre ogni geometria dal disco per confrontare i bounding box finendo con il leggere anche tutte le pagine TOAST.

Per controllare se i propri dati sono interessati da questo bug, si può usare il comando PostgreSQL "EXPLAIN ANALYZE". Per maggiori informazioni e dettagli tecnici consultare il corrispondente thread sulla mailing list di PostgreSQL: http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php

e una discussione più recente su PostGIS https://lists.osgeo.org/pipermail/postgis-devel/2017-June/026209.html

Gli sviluppatori di PostgreSQL stanno cercando di risolvere il problema rendendo la valutazione della query indipendente dalla tabella TOAST. Per ora ci sono due possibili soluzioni alternative:

La è forzare il query planner ad usare l'indice spaziale usando il comando "SET enable_seqscan TO off;" prima di lanciare la query. Questo comando impedisce al query planner di usare lo scan sequenziale della tabella se possibile e lo forza quindi ad usare l'indice GIST. Tuttavia il comando deve essere lanciato ad ogni connessione e, per evitare di confondere il query planner in altri casi, il parametro deve essere resettato dopo l'esecuzione della query interessata con il comando "SET enable_seqscan TO on;" .

Il secondo metodo è rendere lo scan sequenziale così veloce come il query planner si aspetta che sia. Questo può essere raggiunto aggiungendo una colonna addizionale in cui salvare la bounding box di ogni geometria. Nel nostro esempio i comandi sarebbero:

SELECT AddGeometryColumn('myschema', 'mytable', 'bbox', '4326', 'GEOMETRY', '2');
UPDATE mytable SET bbox = ST_Envelope(ST_Force2D(geom));

Ora la query deve essere modificata in modo da usare l'operatore && con la colonna bbox piuttosto che con la colonna geom_column:

SELECT geom_column
FROM mytable
WHERE bbox && ST_SetSRID('BOX3D(0 0,1 1)'::box3d,4326);

Ovviamente la colonna bbox deve essere mantenuta attuale quando si modificano o si aggiungono geometrie. La via più semplice per fare questo sarebbe con un trigger, oppure l'applicazione può essere modificata in modo da attualizzare anche la colonna bbox oppure si può lanciare l'UPDATE precedente dopo ogni modifica.