Obtenha o fonte do PostGIS através da seção de downloads do website 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

Isto irá criar um diretório chamado postgis-3.7.0dev no diretório de trabalho atual.

Outra alternativa ,é o checkout da fonte do svn repository http://svn.osgeo.org/postgis/trunk/ .

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

Mude para o recém criado postgis-3.7.0dev diretório para continuar a instalação.

./configure

PostGIS tem os seguintes requisitos para a construção e uso:

Necessário

Opcional

Como a maior parte das instalações Linux, o primeiro passo é gerar o Makefile que será utilizado para construção do código fonte. Isto é feito utilizando o script shell

./configure

Sem parâmetros adicionais, este comando tentará automaticamente localizar os componentes necessários e bibliotecas para construção do fonte do PostGIS em seu sistema. Embora esta é a forma comum de uso do ./configure, o script aceita diversos parâmetros para aqueles que tem as bibliotecas e programas necessários em localizações do sistema operacional que não são padrão.

A lista a seguir mostra apenas os parâmetros comumente utilizados. Para uma lista completa, utilize os parâmetros --help ou --help=short.

	Se conseguiu o PostGIS do SVN depósito , o primeiro passo é fazer funcionar o script

./autogen.sh

Este script gera a configurar script que na volta é usada para personalizar a instalação do PostGIS.

Se em vez de conseguir o PostGIS como tarball, rodando ./autogen.sh não é necessariamente como configurar já foi gerado.

Uma vez que o Makefile tenha sido gerado, compilar o PostGIS é simples como rodar o comando

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

Você pode baixar alguns pre-construídos disponíveis em html e pdf de PostGIS / PostgreSQL Study Guides

make cheatsheets

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

Se você está compilando do repositório, você precisa de compilar a função de descrições primeiro. Estas são compiladas se você possui o docbook instalado. Você pode também construir manualmente com o comando:

make comments

Construir a documentação não é necessário se você está construindo de uma versão de lançamento no formato tar ball, já que estas são empacotadas pré-construídas com o tar ball.

Se você está construindo o PostGIS contra o PostgreSQL 9.1, as extensão devem ser automaticamente construídas como parte do processo de make. Você pode, contudo, se necessário, construir das pastas de extensões ou copiar os arquivos se você precisar dos mesmos em um servidor diferente.

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 uses psql to run tests and as such can use psql environment variables. Common ones useful to override are PGUSER,PGPORT, and PGHOST. Refer to psql environment variables

Os arquivos de extensões sempre serão os mesmos para a mesma versão do PostGIS, independente do Sistemas Operacional, então é fácil copiar os arquivos de extensão de um sistema operacional para outro, desde que você tenha os binários do PostGIS instalados em seus servidores.

Se você deseja instalar as extensões manualmente em um servidor diferente, do seu servidor de desenvolvimento, você precisará copiar os seguintes arquivos da pasta de extensões para a pastaPostgreSQL /share/extension da sua instalação do PostgreSQL, bem como os binários necessários para o PostGIS, se você não os tem ainda no servidor de destino.

Once you do that, you should see postgis, postgis_topology as available extensions in PgAdmin -> extensions.

Se você está utilizando psql, pode verificar quais estensões estão instaladas executando essa 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 você tem a extensão instalada no banco de dados de seu interesse, você a verá mencionada na coluna installed_version. Se você não receber nenhum registro de volta, significa que você não tem extensões do PostGIS instaladas no servidor. PgAdmin III 1.14+ também irá lhe dar esta informação na seção extensions do navegador de banco de dados e até permitirá o upgrade ou a desinstação utilizando o clique com o botão direito.

Se você tem extensões disponíveis, pode instalar a extensão postgis no seu database escolhido usando a interface da extensão pgAdmin ou rodando esses comandos sql:

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

No psql você pode ver quais versões foram instaladas e qual esquema eles estão instalando.

\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 você instalou 3.7.0dev sem usar nosso sistema de extensão maravilhoso, você pode mudar para uma extensão baseada em primeiro atualizando para a última micro versão rodando as scripts atualizadas: postgis_upgrade_22_minor.sql,raster_upgrade_22_minor.sql,topology_upgrade_22_minor.sql.

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

Se desejar testar o PostGIS, rode

make check

O comando acima irá rodar através de várias verificações e testes de regressão usando a biblioteca gerada contra o database do PostreSQL atual.

	Se você configurou o PostGIS usando o não padronizado PostgreSQL, GEOS, ou Proj4 localizações, talvez você precise adicionar a biblioteca de localizações deles à LD_LIBRARY_PATH variável de ambiente.

Atualmente, o faz verificação confia nas variáveis de ambiente PATH e PGPORT quando vai fazer as verificações - ele não usa a versão PostgreSQL que talvez tenha sido especificada utilizando o parâmetro de configuração --with-pgconfig. Portanto, certifique-se que para modificar seu PATH para ser compatível com a instalação do PostgreSQL detectada durante a configuração ou esteja preparado para iminentes aborrecimentos.

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.

If successful, make check will produce the output of almost 500 tests. The results will look similar to the following (numerous lines omitted below):

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

saída deve parecer com:

============== 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.
=====================

Para instalar o PostGIS, digite

make install

Isso irá copiar a instalação dos arquivos do PostGIS para suas subdireções específicas pelo --prefix parâmetro de configuração. Particularmente:

Se anteriormente você rodou o comando make comments para gerar o arquivo postgis_comments.sql, raster_comments.sql, instale o arquivo sql para executar

make comments-install

	postgis_comments.sql, raster_comments.sql, topology_comments.sql foi separado da construção típica e instalações alvo desde que como isso, veio a dependência extra do xsltproc.

These settings are configured 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 - sets the size of memory used for sort operations and complex queries

maintenance_work_mem - the memory size used for VACUUM, CREATE INDEX, etc.

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.

If you have compiled and installed the extensions/postgis modules, you can turn a database into a spatial one using the EXTENSION mechanism.

Core postgis extension includes geometry, geography, spatial_ref_sys and all the functions and comments. Raster and topology are packaged as a separate extension.

Run the following SQL snippet in the database you want to enable spatially:

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

	This is generally only needed if you cannot or don't want to get PostGIS installed in the PostgreSQL extension directory (for example during testing, development or in a restricted environment).

Adding PostGIS objects and function definitions into your database is done by loading the various sql files located in [prefix]/share/contrib as specified during the build phase.

The core PostGIS objects (geometry and geography types, and their support functions) are in the postgis.sql script. Raster objects are in the rtpostgis.sql script. Topology objects are in the topology.sql script.

For a complete set of EPSG coordinate system definition identifiers, you can also load the spatial_ref_sys.sql definitions file and populate the spatial_ref_sys table. This will permit you to perform ST_Transform() operations on geometries.

If you wish to add comments to the PostGIS functions, you can find them in the postgis_comments.sql script. Comments can be viewed by simply typing \dd [function_name] from a psql terminal window.

Run the following Shell commands in your terminal:

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

If you installed your database using extensions, you'll need to upgrade using the extension model as well. If you installed using the old sql script way, you are advised to switch your install to extensions because the script way is no longer supported.

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();

If you have multiple versions of PostGIS installed, and you don't want to upgrade to the latest, you can explicitly specify the version as follows:

Code

Copy

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

If you get an error notice something like:

Raster Outputs

No migration path defined for … to 3.7.0dev

Then you'll need to backup your database, create a fresh one as described in Seção 3.3.1, “Spatially enable database using EXTENSION” and then restore your backup on top of this new database.

If you get a notice message like:

Raster Outputs

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

Then everything is already up to date and you can safely ignore it. UNLESS you're attempting to upgrade from an development version to the next (which doesn't get a new version number); in that case you can append "next" to the version string, and next time you'll need to drop the "next" suffix again:

Code

Copy

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

	If you installed PostGIS originally without a version specified, you can often skip the reinstallation of postgis extension before restoring since the backup just has CREATE EXTENSION postgis and thus picks up the newest latest version during restore.

	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 Seção 4.5, “The SPATIAL_REF_SYS Table and 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 Seção 5.1.1, “Dimensionally Extended 9-Intersection Model”. 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 Seção 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.

	Todos as comparações de pontos flutuantes dentro da implementação SQL-MM são representadas com uma tolerância específica, atualmente 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)))

A especificação OpenGIS define dois caminhos padrão de expressar objetos espaciais: o Well-Known Text (WKT) e o Well-Known Binary (WKB). Ambos incluem informação sobre o tipo do objeto e as coordenadas que os formam.

A representação bem conhecida de texto do sistema de referência espacial. Um exemplo de uma representação WKT SRS é:

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);

Por exemplo, uma declaração inserida válida para criar e inserir um objeto espacial OGC seria:

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:

	Os formatos estendidos do PostGIS estão atualmente superset de OGC (cada WKB/WKT válido é um EWKB/EWKT válido), mas isto pode airar no futuro, especificamente se OGC sai com um novo formato conflitando com nossas extensões. Assim, você NÃO DEVE confiar neste aspecto!

A seguir, exemplos das representações de textos (WKT) dos objetos espaciais das características:

Entrada/Saída destes formatos estão disponíveis usando as seguintes interfaces:

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

Por exemplo, uma declaração inserida válida para criar e inserir um objeto espacial seria:

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;

Criar um índice funciona da mesma forma que uma GEOMETRIA. O PostGIS irá notar que o tipo de coluna é GEOGRAFIA e criará um índice baseado em esfera apropriado em vez do de costume usado para GEOMETRIA.

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.

As funções de consulta e medida usam unidades em metros. Então, os parâmetros de distância deveriam ser esperados em metros (ou metros quadrados para áreas).

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).

O tipo GEOGRAFIA calcula a verdadeira menor distância sobre a esfera entre Reykjavik e o grande caminho de voo circular entre Seattle e Londres.

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

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

Great Circle mapper A GEOMETRIA calcula a distância cartesiana insignificante entre Reykjavik e o caminho direto de Seattle para Londres marcado em um mapa. As unidades nominais do resultado podem ser chamadas de "graus", mas o resultado não corresponde a nenhuma diferença angular verdadeira entre os pontos, então, chamá-las de "graus" é incoerente.

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.

O tipo que você escolheu deveria ser condicionado da área de trabalho esperada da aplicação que você está construindo. Seus dados irão abranger o globo ou uma grande área continental, ou é local para um estado, condado ou município?

Recorra a Seção 13.11, “PostGIS Function Support Matrix” para uma comparação entre o que é suportado pela Geografia vs. Geometria. Para uma breve lista e descrição das funções da Geografia, recorra a Seção 13.4, “PostGIS Geography Support Functions”

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

Um POINT é herdado simple como um objeto geométrico 0-dimensional.

MULTIPOINTs são simple se nenhuma de duas coordenadas (POINTs) forem iguais (tenham o valor de coordenadas idêntico).

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.

A tabela SPATIAL_REF_SYS de definição está como segue:

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

As opções da commandline são:

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.

Uma ótima fonte para encontrar sistemas de referência espacial não definidos na configuração central é 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.

Vários estados dos EUA no sistema de referência espacial (em metros ou pés) - normalmente um ou 2 existem por estado. A maioria dos que estão em metros estão no centro, mas muitos dos que estão em pés ou foram criados por ESRI precisarão de 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);

A OGC Simple Features Specification for SQL define a tabela de metadados GEOMETRY_COLUMNS para descrever a estrutura da tabela de geometria. No PostGIS geometry_columns é uma visualização que lê as tabelas de catálogo do sistema de banco de dados. Isso garante que as informações de metadados espaciais sejam sempre consistentes com as tabelas e visualizações definidas atualmente. A estrutura da visualização é:

\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)  |

As opções da commandline são:

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)

Nas geometry_columns, elas registram corretamente

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

Entretanto -- se se quiséssemos criar uma view como essa

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

Se os dados espaciais puderem ser convertidos em uma representação de texto (como WKT ou WKB), o uso do SQL poderá ser a maneira mais fácil de inserir os dados no PostGIS. Os dados podem ser carregados em massa no PostGIS/PostgreSQL carregando um arquivo de texto de instruções SQL INSERT usando o utilitário SQL psql.

Um arquivo de atualização de dados (roads.sql por exemplo) deve se parecer com:

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;

O arquivo SQL pode ser carregado no PostgreSQL usando psql:

psql -d [database] -f roads.sql

O carregador de dados shp2pgsql converte ESRI Shape files em SQL adequado para inserção dentro de um banco de dados PostGIS/PostgreSQL, seja em formato de geometria ou geografia. O carregador possui vários modos de operação distinguidos pelas linhas de bandeiras de comando:

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.

Juntamente com o comando carregador shp2pgsql, existe uma interface shp2pgsql-gui gráfica com a maioria das opções como o carregador, mas pode ser mais fácil de usar para um carregamento único non-scripted ou se você é novo no PostGIS. Pode ser configurado como um plugin do PgAdminIII.

Uma seção exemplo usando o carregador para criar um arquivo de entrada e atualizando ele pode parecer com:

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

Uma conversão e um upload podem ser feitos em apenas um passo usando encadeamento UNIX:

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.

A maneira mais direta de extrair dados espaciais do banco de dados é usar uma consulta SQL SELECT para definir o conjunto de dados a ser extraído e despejar as colunas resultantes em um arquivo de texto analisável:

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)

Entretanto, às vezes algum tipo de restrição será necessária para cortar o número de campos retornados. No caso de restrições baseadas em atributos, só use a mesma sintaxe SQL como normal com uma tabela não espacial. No caso de restrições espaciais, os operadores seguintes são úteis/disponíveis:

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;

A consulta acima retornaria um único relato da tabela "ROADS_GEOM"na qual a geometria era igual ao valor.

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.

Usando o operador "&&" , você pode especificar uma CAIXA3D como uma caracetrística de comparação ou uma GEOMETRIA. Entretanto, quando você especifica uma GEOMETRIA, a caixa delimitadora dela será usada para a comparação.

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);

Observe o uso do SRID 312, para especificar a projeção do envelope.

A tabela dumper pgsql2shp conecta diretamente ao banco de dados e converte uma tabela (possivelmente definida por uma consulta) em um shapefile. A sintaxe básica é:

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

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

As opções da commandline são:

GiST significa "Árvores de Pesquisa Generalizada" e é uma forma genérica de classificar. Além disso, ele é usado para acelerar pesquisas em todos os tipos de estruturas de dados irregulares (arranjos inteiros, dados espectrais etc) que não são agradáveis à classificação normal B-Tree.

Uma vez que uma tabela de dados GIS excede pouco mais de mil filas, você irá querer construir um índice para acelerar pesquisas espaciais dos dados (a menos que suas pesquisas sejam baseadas em atributos, você vai querer construir um índice normal nos campos de atributo).

A sintaxe para construir um índice GiST em uma coluna "geométrica" é a seguinte:

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;

A sintaxe para criar um índice BRIN em uma coluna geometry é a seguinte:

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.

Se você achar que seus índices não estão sendo usados (ou seus atributos) há algumas coisas que pode fazer:

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

According to the OpenGIS Simple Features Implementation Specification for SQL, "the basic approach to comparing two geometries is to make pair-wise tests of the intersections between the Interiors, Boundaries and Exteriors of the two geometries and to classify the relationship between the two geometries based on the entries in the resulting 'intersection' matrix."

In the theory of point-set topology, the points in a geometry embedded in 2-dimensional space are categorized into three sets:

The Dimensionally Extended 9-Intersection Model (DE-9IM) describes the spatial relationship between two geometries by specifying the dimensions of the 9 intersections between the above sets for each geometry. The intersection dimensions can be formally represented in a 3x3 intersection matrix.

For a geometry g the Interior, Boundary, and Exterior are denoted using the notation I(g), B(g), and E(g). Also, dim(s) denotes the dimension of a set s with the domain of {0,1,2,F}:

Using this notation, the intersection matrix for two geometries a and b is:

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

Reading from left to right and top to bottom, the intersection matrix is represented as the text string '212101212'.

For more information, refer to:

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).

Este problema ocorre se você possui tabelas com geometrias grandes, mas não muitas linhas (uma tabela dos limites todos os países europeus em alta resolução). A tabela em si, é pequena, mas utiliza muito espaço TOAST. Em nosso exemplo, a tabela em si possuía apenas 80 linhas e utilizava apenas 3 páginas de dados, mas a tabela TOAST utilizava 8225 páginas de dados.

Emita uma pesquisa onde você utiliza o operador && para pesquisa por um retângulo envolvente que bate com poucas dessas linhas. O otimizador de pesquisas ve esta tabela contendo apenas 3 páginas e 80 linhas. Como a tabela é pequena, ele estima que um scan sequencial em uma tabela tão pequena será mais rápida do que utilizar um índice, ignorando o mesmo. Geralmente esta estimativa é correta, mas em nosso caso o operador && tem que buscar todas as geometrias em disco para comparação dos retângulos envolventes, lendo todas as páginas TOAST também.

Para visualizar se você sofre com este bug, utilize um "EXPLAIN ANALYZE" na pesquisa em questão. Para maiores informações e detalhes técnicos, você pode recorrer a lista do postgres sobre desempenho: http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php

and newer thread on PostGIS https://lists.osgeo.org/pipermail/postgis-devel/2017-June/026209.html

O pessoal responsável pelo PostgreSQL está tentando resolver esta questão por transformar o otimizador de pesquisas ciente das tabelas TOAST. Por enquanto, existem duas soluções:

A primeira solução é forçar o estimador de pesquisar a utilizar o índice. Emita um comando "SET enable_seqscan TO off" ao servidor antes de emitir a pesquisa. Isto força o estimador a evitar scans sequenciais sempre que possível, utilizando o índice GIST como de costume. Mas esta flag deve ser setada para cada conexão e causa o estimador a decidir mal em outros casos, portanto, você deve habilitar "SET enable_seqscan TO on;" após a pesquisa.

A segunda solução é fazer a pesquisa sequencial tão rápida quanto o estimador imagina. Isto pode ser feito criando uma coluna adicional que cacheia o retângulo envolvente e realizando as pesquisas em cima desta coluna. Em nosso exemplo, os comandos são:

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

Altere sua query para usar o operador && contra o retângulo envolvente ao invés da colunas geométrica, assim:

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

Claro, se você alterar ou adicionar colunas a mytable, você deve manter o retângulo envolvente em sincronia. A forma mais transparente de fazer isto seria através de triggers, mas você também querer modificar sua aplicação para manter a coluna do retângulo envolvente atualizada or executar a query de UPDATE após cada modificação.