Chapter 4. Data Management

Table of Contents

4.1. Carregando dados GIS (Vector)

4.1.1. OGC Geometry

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.

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 Section 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, and polygonal types have dimension 2. 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 Section 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.

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 the 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 Section 4.4, “Geometry Validation” Ponto

A Point is a 0-dimensional geometry that represents a single location in coordinate space.

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

A LineString is a 1-dimensional line formed by a contiguous sequence of line segments. Each line segment is defined by two points, with the end point of one segment forming the start point of the next segment. An OGC-valid LineString has either zero or two or more points, but PostGIS also allows single-point LineStrings. LineStrings may cross themselves (self-intersect). A LineString is closed if the start and end points are the same. A LineString is simple if it does not self-intersect.

LINESTRING (1 2, 3 4, 5 6) LinearRing

A LinearRing is a LineString which is both closed and simple. The first and last points must be equal, and the line must not self-intersect.

LINEARRING (0 0 0, 4 0 0, 4 4 0, 0 4 0, 0 0 0) Polígono

A Polygon is a 2-dimensional planar region, delimited by an exterior boundary (the shell) and zero or more interior boundaries (holes). Each boundary is a LinearRing.

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

A MultiPoint is a collection of Points.

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

A MultiLineString is a collection of LineStrings. A MultiLineString is closed if each of its elements is closed.

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

A MultiPolygon is a collection of non-overlapping, non-adjacent Polygons. Polygons in the collection may touch only at a finite number of points.

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

A GeometryCollection is a heterogeneous (mixed) collection of geometries.

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

A PolyhedralSurface is a contiguous collection of patches or facets which share some edges. Each patch is a planar Polygon. If the Polygon coordinates have Z ordinates then the surface is 3-dimensional.

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

A Triangle is a polygon defined by three distinct non-collinear vertices. Because a Triangle is a polygon it is specified by four coordinates, with the first and fourth being equal.

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

A TIN is a collection of non-overlapping Triangles representing a Triangulated Irregular Network.

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

4.1.2. SQL-MM Part 3

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

CircularString is the basic curve type, similar to a LineString in the linear world. A single arc segment is specified by three points: the start and end points (first and third) and some other point on the arc. To specify a closed circle the start and end points are the same and the middle point is the opposite point on the circle diameter (which is the center of the arc). In a sequence of arcs the end point of the previous arc is the start point of the next arc, just like the segments of a LineString. This means that a CircularString must have an odd number of points greater than 1.

CIRCULARSTRING(0 0, 1 1, 1 0)

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

Uma curva composta é uma curva única e contínua que tem segmentos curvados (circulares) e lineares. Isto significa que, além de ter componentes bem formados, o ponto final de cada componente (exceto o último) deve ser coincidente com o ponto inicial do componente seguinte.

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

Um POLÍGONOCURVO é como um polígono, com um anel externo e zero ou mais anéis internos. A diferença é que um anel pode obter a forma de uma string circular, linear ou composta.

Assim como o PostGIS 1.4, o PostGIS suporta curvas compostas em um polígono curvo.

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

Example: A CurvePolygon with the shell defined by a CompoundCurve containing a CircularString and a LineString, and a hole defined by a CircularString

  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

A MULTICURVA é uma coleção de curvas, que podem incluir strings lineares, circulares e compostas.

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

Esta é uma coleção de superfícies, que podem ser polígonos (lineares) ou polígonos curvos.

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

4.1.3. OpenGIS WKB e WKT

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 é:

  • POINT(0 0)

  • POINT(0 0)

  • POINT(0 0)


  • LINESTRING(0 0,1 1,1 2)


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

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

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


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

  • MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))



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

text WKT = ST_AsText(geometry);
geometry = ST_GeomFromText(text WKT, 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_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:

  • POINT(0 0)

    WKB: 0101000000000000000000F03F000000000000F03

  • LINESTRING(0 0,1 1,1 2)

    WKB: 0102000000020000000000000000000040000000000000004000000000000022400000000000002240

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

4.2. Geometry Data Type

PostGIS implements the OGC Simple Features model by defining a PostgreSQL data type called geometry. It represents all of the geometry subtypes by using an internal type code (see GeometryType and ST_GeometryType). This allows modelling spatial features as rows of tables defined with a column of type geometry.

The geometry data type is opaque, which means that all access is done via invoking functions on geometry values. Functions allow creating geometry objects, accessing or updating all internal fields, and compute new geometry values. PostGIS supports all the functions specified in the OGC Simple feature access - Part 2: SQL option (SFS) specification, as well many others. See Chapter 7, Referência do PostGIS for the full list of functions.


PostGIS follows the SFA standard by prefixing spatial functions with "ST_". This was intended to stand for "Spatial and Temporal", but the temporal part of the standard was never developed. Instead it can be interpreted as "Spatial Type".

A especificação OpenGIS também requer que o formato do armazenamento interno dos objetos espacias incluam um identificador de sistema de referência espacial (SRID). O SRID é fundamental na criação de objetos espaciais para a inserção no banco de dados. Consulte ST_SRID e Section 4.5, “The SPATIAL_REF_SYS Table and Spatial Reference Systems”

To make querying geometry efficient PostGIS defines various kinds of spatial indexes, and spatial operators to use them. See Section 4.9, “Construindo índidces” and Section 5.2, “Using Spatial Indexes” for details.

4.2.1. OpenGIS WKB e WKT

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;

PostGIS EWKT output has a few differences to OGC WKT:

  • For 3DZ geometries the Z qualifier is omitted:

    POINT(0 0)

    POINT(0 0)

  • For 3DM geometries the M qualifier is included:

    POINT(0 0)

    POINT(0 0)

  • For 4D geometries the ZM qualifier is omitted:

    POINT(0 0)

    POINT(0 0)

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

  • POINT(0 0)

  • POINT(0 0)

  • POINT(0 0)


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:

  • POINT(0 0 0) -- XYZ

  • SRID=32632;POINT(0 0) -- XY with SRID

  • POINTM(0 0 0) -- XYM

  • POINT(0 0 0 0) -- XYZM

  • SRID=4326;MULTIPOINTM(0 0 0,1 2 1) -- XYM with SRID

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

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

  • MULTIPOLYGON(((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)),((-1 -1 0,-1 -2 0,-2 -2 0,-2 -1 0,-1 -1 0)))


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

  • POLYHEDRALSURFACE( ((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( ((0 0 0, 0 0 1, 0 1 0, 0 0 0)), ((0 0 0, 0 1 0, 1 1 0, 0 0 0)) )

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

bytea EWKB = ST_AsEWKB(geometry);
text EWKT = ST_AsEWKT(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' )

4.3. Tipo de geografia POstGIS

O tipo de dados PostGIS geography oferece suporte nativo para recursos espaciais representados em coordenadas "geográficas" (às vezes chamadas de coordenadas "geodésicas", "lat/lon" ou "lon/lat"). As coordenadas geográficas são coordenadas esféricas expressas em unidades angulares (graus).

A base para a geometria PostGIS é um plano. O menor caminho entre dois pontos no plano é uma linha. Isso quer dizer que cálculos em geometrias (áreas, distâncias, cumprimentos, interseções etc) podem ser feitos usando matemática cartesiana e vetores de linhas.

A base para a geometria PostGIS é um plano. O menor caminho entre dois pontos no plano é uma linha. Isso quer dizer que cálculos em geometrias (áreas, distâncias, cumprimentos, interseções etc) podem ser feitos usando matemática cartesiana e vetores de linhas.

Devido à matemática fundamental ser muito mais complicada, existem poucas funções definidas pela geografia em vez da geometria. Ao longo do tempo, à media que os algorítimos forem adicionados, as capacidades da geografia serão expandidas.

Like the geometry data type, geography data is associated with a spatial reference system via a spatial reference system identifier (SRID). Any geodetic (long/lat based) spatial reference system defined in the spatial_ref_sys table can be used. (Prior to PostGIS 2.2, the geography type supported only WGS 84 geodetic (SRID:4326)). You can add your own custom geodetic spatial reference system as described in Section 4.5.2, “The SPATIAL_REF_SYS Table and Spatial Reference Systems”.

For all spatial reference systems the units returned by measurement functions (e.g. ST_Distance, ST_Length, ST_Perimeter, ST_Area) and for the distance argument of ST_DWithin are in meters.

4.3.1. Criando uma Tabela Espacial

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 (
    name VARCHAR(64),
    location geography(POINT,4326)

The geography type supports two optional type modifiers:

  • Os valores permitidos para o modificador de tipo são: PONTO, LINESTRING, POLÍGONO, MULTIPONTO, MULTILINESTRING, MULTIPOLÍGONO. O modificador também suporta restrições de dimensionalidade através de sufixos: Z, M, e ZM. Então, por exemplo, um modificador de 'LINESTRINGM' só permitiria line strings com três dimensões, e trataria a terceira dimensão como uma medida. Da mesma forma, 'PONTOZM' esperaria dados de quatro dimensões.

  • the SRID modifier restricts the spatial reference system SRID to a particular number. If omitted, the SRID defaults to 4326 (WGS84 geodetic), and all calculations are performed using WGS84.

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.

-- Index the test table with a spherical index
CREATE INDEX global_points_gix ON global_points USING GIST ( location );

4.3.2. Tipo de geografia POstGIS

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.

-- Add some data into the test table
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.

-- NAD 83 lon/lat
SELECT 'SRID=4269;POINT(-123 34)'::geography;
-- NAD27 lon/lat
SELECT 'SRID=4267;POINT(-123 34)'::geography;
-- NAD83 UTM zone meters - gives an error since 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).

-- A distance query using a 1000km 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.

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

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.

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

4.3.3. Quando usar o tipo de dados Geografia sobre os dados Geometria

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?

  • Se seus dados estiverem contidos em uma pequena área, talvez perceba que escolher uma projeção apropriada e usar GEOMETRIA é a melhor solução, em termos de desempenho e funcionalidades disponíveis.

  • Se seus dados são globais ou cobrem uma região continental, você pode perceber que GEOGRAFIA permite que você construa uma sistema sem ter que se preocupar com detalhes de projeção. Você armazena seus dados em longitude/latitude, e usa as funções que foram definidas em GEOGRAFIA.

  • Se você não entende de projeções, não quer aprender sobre elas e está preparado para aceitar as limitações em funcionalidade disponíveis em GEOGRAFIA, então pode ser mais fácil se usar GEOGRAFIA em vez de GEOMETRIA. Simplesmente carregue seus dados como longitude/latitude e comece a partir daqui.

Recorra a Section 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 Section 13.4, “PostGIS Geography Support Functions”

4.3.4. FAQ de Geografia Avançada

Você calcula na esfera ou esferoide?

Por padrão, todos os cálculos de distância e área são feitos no esferoide. Você irá encontrar que os resultados dos cálculos nas áreas locais combinam com os resultados locais planares em boas projeções locais. Em grandes áreas, os cálculos esferoidais são mais precisos que os feitos em um plano projetado.

Todas as funções de geografia têm a opção de usar um cálculo esférico, configurando um parâmetro booleano final para 'FALSO'. isto irá acelerar os cálculos, particularmente para casos onde as geometrias são bem simples.

E a linha de data e os pólos?

Nenhum cálculo possui a compreensão de linha de data ou polos, as coordenadas são esféricas (longitude/latitude), então uma forma que cruza a linha de data não é, de um ponto de cálculo de view, diferente de nenhuma outra forma.

Qual é o maior arco que pode ser processado?

Nós usamos grandes arcos círculos como a "linha de interpolação" entre dois pontos. Isso significa que quaisquer dois pontos estão de fato juntaram-se de duas maneiras, depende qual direção você vá no grande círculo. Todo o nosso código assume que os pontos estão juntos pelo *menor* dos dois caminhos ao longo do grande círculo. Como consequência, formas que têm arcos de mais de 180 graus não serão modeladas corretamente.

Por que é tão lento para calcular a área da Europa / Rússia / insira uma grande região geográfica aqui ?

Porque o polígono é muito grande! Grandes áreas são ruins por duas razões: seus limites são grandes, logo o índice tende a puxar o traço, não importa qual consulta você execute; o número de vértices é enorme, e testes (distância, contenção) têm que atravessar a lista de vértices pelo meno uma vez e algumas vezes, N vezes (com N sendo o número de vértices em outra característica candidata).

As with GEOMETRY, we recommend that when you have very large polygons, but are doing queries in small areas, you "denormalize" your geometric data into smaller chunks so that the index can effectively subquery parts of the object and so queries don't have to pull out the whole object every time. Please consult ST_Subdivide function documentation. Just because you *can* store all of Europe in one polygon doesn't mean you *should*.

4.4. Geometry Validation

PostGIS is compliant with the Open Geospatial Consortium’s (OGC) Simple Features specification. That standard defines the concepts of geometry being simple and valid. These definitions allow the Simple Features geometry model to represent spatial objects in a consistent and unambiguous way that supports efficient computation. (Note: the OGC SF and SQL/MM have the same definitions for simple and valid.)

4.4.1. Simple Geometry

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.

(a) and (c) are simple LINESTRINGs. (b) and (d) are not simple. (c) is a closed Linear Ring.





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.

(e) and (f) are simple MULTILINESTRINGs. (g) is not simple.




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

To test if a geometry is simple use the ST_IsSimple function:

   ST_IsSimple('LINESTRING(0 0, 100 100)') AS straight,
   ST_IsSimple('LINESTRING(0 0, 100 100, 100 0, 0 100)') AS crossing;

 straight | crossing
 t        | f

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.

4.4.2. Valid Geometry

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:

  1. the polygon boundary rings (the exterior shell ring and interior hole rings) are simple (do not cross or self-touch). Because of this a polygon cannnot have cut lines, spikes or loops. This implies that polygon holes must be represented as interior rings, rather than by the exterior ring self-touching (a so-called "inverted hole").

  2. boundary rings do not cross

  3. boundary rings may touch at points but only as a tangent (i.e. not in a line)

  4. interior rings are contained in the exterior ring

  5. the polygon interior is simply connected (i.e. the rings must not touch in a way that splits the polygon into more than one part)

(h) and (i) are valid POLYGONs. (j-m) are invalid. (j) can be represented as a valid MULTIPOLYGON.







A MULTIPOLYGON is valid if:

  1. its element POLYGONs are valid

  2. elements do not overlap (i.e. their interiors must not intersect)

  3. elements touch only at points (i.e. not along a line)

(n) is a valid MULTIPOLYGON. (o) and (p) are invalid.




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.

   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

POINT and MULTIPOINT geometries have no validity rules.

4.4.3. Managing Validity

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

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

SELECT valid, reason, ST_AsText(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.56976744186045)

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:

  ADD CONSTRAINT geometry_valid_check
        CHECK (ST_IsValid(geom));

4.5. The SPATIAL_REF_SYS Table and Spatial Reference Systems

A Spatial Reference System (SRS) (also called a Coordinate Reference System (CRS)) defines how geometry is referenced to locations on the Earth's surface. There are three types of SRS:

  • A geodetic SRS uses angular coordinates (longitude and latitude) which map directly to the surface of the earth.

  • A projected SRS uses a mathematical projection transformation to "flatten" the surface of the spheroidal earth onto a plane. It assigns location coordinates in a way that allows direct measurement of quantities such as distance, area, and angle. The coordinate system is Cartesian, which means it has a defined origin point and two perpendicular axes (usually oriented North and East). Each projected SRS uses a stated length unit (usually metres or feet). A projected SRS may be limited in its area of applicability to avoid distortion and fit within the defined coordinate bounds.

  • A local SRS is a Cartesian coordinate system which is not referenced to the earth's surface. In PostGIS this is specified by a SRID value of 0.

There are many different spatial reference systems in use. Common SRSes are standardized in the European Petroleum Survey Group EPSG database. For convenience PostGIS (and many other spatial systems) refers to SRS definitions using an integer identifier called a SRID.

A geometry is associated with a Spatial Reference System by its SRID value, which is accessed by ST_SRID. The SRID for a geometry can be assigned using ST_SetSRID. Some geometry constructor functions allow supplying a SRID (such as ST_Point and ST_MakeEnvelope). The EWKT format supports SRIDs with the SRID=n; prefix.

Spatial functions processing pairs of geometries (such as overlay and relationship functions) require that the input geometries are in the same spatial reference system (have the same SRID). Geometry data can be transformed into a different spatial reference system using ST_Transform and ST_TransformPipeline. Geometry returned from functions has the same SRS as the input geometries.

4.5.1. SPATIAL_REF_SYS Table

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 (
  auth_name  VARCHAR(256),
  auth_srid  INTEGER,
  srtext     VARCHAR(2048),
  proj4text  VARCHAR(2048)

As opções da commandline são:


Um valor inteiro que só identifica o Sistema de Referenciação Espacial (SRS) dentro do banco de dados.


O nome do corpo padrão ou corpos padrẽos que estão sendo citados por este sistema de referência. Por exemplo, "EPSG" seria um AUTH_NAME válido.


The ID of the Spatial Reference System as defined by the Authority cited in the auth_name. In the case of EPSG, this is the EPSG code.


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

PROJCS["NAD83 / UTM Zone 10N",
          SPHEROID["GRS 1980",6378137,298.257222101]

For a discussion of SRS WKT, see the OGC standard Well-known text representation of coordinate reference systems.


O PostGIS usa a biblioteca Proj4 para fornecer capacidades de transformação de coordenada. A coluna PROJ4TEXT contém a string de definição da coordenada Proj4 para um SRID específico. Por exemplo:

+proj=utm +zone=10 +ellps=clrk66 +datum=NAD27 +units=m

Para maiores informações a respeito, veja o website do Proj4 O arquivo spatial_ref_sys.sql contém as definições SRTEXT e PROJ4TEXT para todas as projeções EPSG.

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

  • If auth_name and auth_srid are present (non-NULL) use the PROJ SRS based on those entries (if one exists).

  • If srtext is present create a SRS using it, if possible.

  • If proj4text is present create a SRS using it, if possible.

4.5.2. The SPATIAL_REF_SYS Table and Spatial Reference Systems

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 é

Alguns dos sistemas de referência espacial mais comumente usados são: 4326 - WGS 84 Long Lat, 4269 - NAD 83 Long Lat, 3395 - WGS 84 World Mercator, 2163 - US National Atlas Equal Area, Spatial reference systems para cadaNAD 83, WGS 84 UTM zona - zonas UTM são as mais ideais para medição, mas só cobrem 6-graus regiões.

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

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'

4.6. Criando uma Tabela Espacial

4.6.1. Criando uma Tabela Espacial

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

    name VARCHAR(64),
    geom geometry(LINESTRING,3005)

The geometry type supports two optional type modifiers:

  • O modificador de tipo espacial restringe o tipo de formas e dimensões permitidas na coluna. O valor pode ser qualquer um dos subtipos de geometria suportados (por exemplo, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION etc.). O modificador oferece suporte a restrições de dimensionalidade de coordenadas adicionando sufixos: Z, M e ZM. Por exemplo, um modificador de "LINESTRINGM" permite apenas linhas com três dimensões e trata a terceira dimensão como uma medida. Da mesma forma, 'POINTZM' requer dados de quatro dimensões (XYZM).

  • the SRID modifier restricts the spatial reference system SRID to a particular number. If omitted, the SRID defaults to 0.

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:

f_table_catalog, f_table_schema, f_table_name

O nome completo da tabela de característica que contém a coluna geométrica. Note que os termos "catálogo" e "esquema" são Oracle. Não existe um análogo do "catálogo" PostgreSQL, logo a coluna é deixada em branco -- para "esquema" o nome do esquema PostgreSQL é usado (public é o padrão).


O nome da coluna geométrica na tabela característica.


A dimensão espacial (2, 3 ou 4 dimensões) da coluna.


A ID do sistema de referência espacial usado para a geometria de coordenadas nessa tabela. É uma referência de chave estrangeira para a tabela spatial_ref_sys (consulte Section 4.5.1, “SPATIAL_REF_SYS Table”).


O tipo do objeto espacial. Para restringir a coluna espacial a um tipo só, use um dos: PONTO, LINESTRING, POLÍGONO, MULTIPONTO, MULTILINESTRING, MULTIPOLÍGONO, GEOMETRYCOLLECTION ou versões correspondentes XYM PONTOM, LINESTRINGM, POLÍGONOM, MULTIPOINTM, MULTILINESTRINGM, MULTIPOLÍGONOM, GEOMETRYCOLLECTIONM. Para coleções heterogêneas (do tipo mistas), você pode usar "GEOMETRIA" como o tipo.

4.6.3. Registrando manualmente as colunas geométricas em geometry_columns

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.

-- Lets say 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;

-- For it to register correctly
-- You need to cast the 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 you know the geometry type for sure is a 2D POLYGON then you could do
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;
--Lets say you created a derivative table by doing a bulk insert
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 2D index on new table
CREATE INDEX idx_myschema_myspecialpois_geom_gist
  ON myschema.my_special_pois USING gist(geom);

-- If your points are 3D points or 3M points,
-- then you might want to create an nd index instead of a 2D index
CREATE INDEX my_special_pois_geom_gist_nd
        ON my_special_pois USING gist(geom gist_geometry_ops_nd);

-- To manually register this new table's geometry column in geometry_columns.
-- Note it will also change the underlying structure of the table to
-- to make the column typmod based.
SELECT populate_geometry_columns('myschema.my_special_pois'::regclass);

-- If you are using PostGIS 2.0 and for whatever reason, you
-- you need the constraint based definition behavior
-- (such as case of inherited tables where all children do not have the same type and srid)
-- set 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, a constraint-based geometry column used directly in a view, will not register correctly in geometry_columns, as will a typmod one. 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);

Se executarmos em psql

\d pois_ny;

Observamos que elas são definidas de maneira diferente -- uma é typmod, outra é restrição

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              |
    "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
  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';

A coluna baseada em typmod registra corretamente, mas a baseada em restrições não.

f_table_name   | f_geometry_column | srid |   type
 vw_pois_ny_parks | geom              | 4326 | POINT
 vw_pois_ny_parks | geom_2160         |    0 | GEOMETRY

Isto pode modificar as versões futuras do PostGIS, mas por enquanto para forçar a restrição baseada em coluna view registrar corretamente, precisamos fazer isto:

DROP VIEW vw_pois_ny_parks;
CREATE VIEW vw_pois_ny_parks AS
SELECT gid, poi_name, cat,
  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

4.7. Carregando dados GIS (Vector)

Uma vez que tenha criado uma tabela espacial, você está pronto para atualizar os dados GIS no banco de dados. No momento, existe duas formas de colocar os dados no banco de dados PostGIS/PostgreSQL: usando as declarações SQL ou usando o shape file loader/dumper.

4.7.1. Usando SQL para recuperar dados

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:

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

O arquivo SQL pode ser carregado no PostgreSQL usando psql:

psql -d [database] -f roads.sql

4.7.2. shp2pgsql: Using the ESRI Shapefile Loader

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:

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.

(c|a|d|p) Essas são opções mutualmente exclusivas:


Cria uma tabela nova e popula do shapefile. Este é o modo padrão.


Anexa dados do shapefile dentro do banco de dados da tabela. Note que para usar esta opção para carregar vários arquivos, eles devem ter os mesmos atributos e tipos de dados.


Derruba a tabela do banco de dados, criando uma nova tabela com os dados do shapefile.


Produz somente a criação da tabela do código SQL, sem adicionar nenhum dado de fato. Isto pode ser usado se você precisar separar completamente a tabela de criação e os passos de carregamento de dados.


Exibir tela de ajuda.


Use o formato PostgreSQL "dump" para os dados de saída. Pode ser combinado com -a, -c e -d. É muito mais rápido para carregar que o formato padrão "insert" SQL. Use isto para dados muito grandes.


Cria e popula as tabelas de geometria com o SRID específico. Especifica, opcionalmente, que o shapefile de entrada usa o FROM_SRID dado, caso em que as geometrias serão reprojetadas para o SRID alvo. FROM_SRID não pode ser especificado com -D.


Mantém identificadores (coluna, esquema e atributos). Note que os atributos no shapefile estão todos em CAIXAALTA.


Coage todos os inteiros para 32-bit integers padrão, não cria 64-bit bigints, mesmo se a assinatura DBF parecer justificar ele.


Cria um índice GiST na coluna geométrica.


-m a_file_name Especifica um arquivo contendo um conjunto de mapas de nomes (longos) de colunas para nomes de colunas DBF com 10 caracteres. O conteúdo deste arquivo é uma ou mais linhas de dois nomes separados por um espaço branco e seguindo ou liderando espaço. Por exemplo:



Gera geometrias simples em vez de MULTI geometrias. Só irá ter sucesso se todas as geometrias forem de fato únicas (ex.: um MULTIPOLÍGONO com uma única shell, ou um MULTIPONTO com um único vértice).

-t <dimensionality>

Força a geometria de saída a ter dimensionalidade especificada. Use as strings seguintes para indicar a dimensionalidade: 2D, 3DZ, 3DM, 4D.

Se a entrada tiver poucas dimensões especificadas, a saída terá essas dimensões cheias com zeros. Se a entrada tiver mais dimensões especificadas, as que indesejadas serão tiradas.


Gera o formato WKT em vez do WKB. Note que isto pode introduzir impulsos de coordenadas para perda de precisão.


Execute cada declaração por si mesma, sem usar uma transação. Isto permite carregar a maioria dos dados bons quando existem geometrias ruins que geram erros. Note que não pode ser usado com a bandeira -D como o formato "dump" sempre usa a transação.

-W <encoding>

Especifica codificação dos dados de entrada (arquivo dbf). Quando usado, todos os atributos do dbf são convertidos da codificação especificada para UTF8. A saída SQL resultante conterá um comando SET CLIENT_ENCODING to UTF8, então o backend será capaz de reconverter do UTF8 para qualquer codificação que o banco de dados estiver configurado para usar internamente.

-N <policy>

Políticas para lidar com geometrias NULAS (insert*,skip,abort)


-n Só importa arquivo DBF. Se seus dados não possuem shapefile correspondente, ele irá trocar automaticamente para este modo e carregar só o dbf. Então, só é necessário configurar esta bandeira se você tiver um shapefile completo, e se quiser os dados atributos e nenhuma geometria.


Use geografia em vez de geometria (requer dados long/lat) em WGS84 long lat (SRID=4326)

-T <tablespace>

Especifica o espaço para a nova tabela. Os índices continuarão usando espaço padrão a menos que o parâmetro -X também seja usado. A documentação PostgreSQL tem uma boa descrição quando usa espaços personalizados.

-X <tablespace>

Especifica o espaço para os novos índices da tabela. Isto se aplica ao primeiro índice chave, e o índice GIST espacial, se -I também for usado.


When used, this flag will prevent the generation of ANALYZE statements. Without the -Z flag (default behavior), the ANALYZE statements will be generated.

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

4.8. Criando uma Tabela Espacial

Os dados podem ser extraídos do banco da dados usando o SQL ou o Shape file loader/dumper. Na seção do SQL discutiremos alguns dos operadores disponíveis para comparações e consultas em tabelas espaciais.

4.8.1. Usando SQL para recuperar dados

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:

db=# SELECT road_id, ST_AsText(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:


This function tells whether two geometries share any space.


Isto testa se duas geometrias são geometricamente iguais. Por exemplo, se 'POLYGON((0 0,1 1,1 0,0 0))' é o mesmo que 'POLYGON((0 0,1 1,1 0,0 0))' (é).

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 ST_AsText(roads_geom) AS geom
FROM roads
  roads_geom && ST_MakeEnvelope(191232, 243117,191232, 243119,312);

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

4.8.2. Usando o Dumper

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
pgsql2shp [<options
>] <database
> <query>

As opções da commandline são:

-f <filename>

Atribui a saída a um filename específico.

-h <host>

O hospedeiro do banco de dados para se conectar.

-p <port>

A porta para conectar no hospedeiro do banco de dados.

-P <password>

A senha para usar quando conectar ao banco de dados.

-u <user>

O nome de usuário para usar quando conectado ao banco de dados.

-g <geometry column>

No caso de tabelas com várias colunas geométricas, a coluna para usar quando atribuindo o shapefile.


Use um cursor binário. Isto tornará a operação mais rápida, mas não funcionará se qualquer atributo NÃO-geométrico na tabela necessitar de um cast para o texto.


Modo cru. Não derruba o campo gid, ou escapa o nome das colunas.

-m filename

Remapeia os identificadores para nomes com dez caracteres. O conteúdo do arquivo é linhas de dois símbolos separados por um único espaço branco e nenhum espaço seguindo ou à frente: VERYLONGSYMBOL SHORTONE ANOTHERVERYLONGSYMBOL SHORTER etc.

4.9. Construindo índidces

Spatial indexes make using a spatial database for large data sets possible. Without indexing, a search for features requires a sequential scan of every record in the database. Indexing speeds up searching by organizing the data into a structure which can be quickly traversed to find matching records.

The B-tree index method commonly used for attribute data is not very useful for spatial data, since it only supports storing and querying data in a single dimension. Data such as geometry (which has 2 or more dimensions) requires an index method that supports range query across all the data dimensions. One of the key advantages of PostgreSQL for spatial data handling is that it offers several kinds of index methods which work well for multi-dimensional data: GiST, BRIN and SP-GiST indexes.

  • Os índices GiST (Generalized Search Tree) dividem os dados em "coisas para um lado", "coisas que se sobrepõem", "coisas que estão dentro" e podem ser usados em uma ampla variedade de tipos de dados, inclusive dados GIS. O PostGIS usa um índice R-Tree implementado sobre o GiST para indexar dados espaciais. O GiST é o método de índice espacial mais comumente usado e versátil, e oferece um desempenho de consulta muito bom.

  • BRIN (Block Range Index) indexes operate by summarizing the spatial extent of ranges of table records. Search is done via a scan of the ranges. BRIN is only appropriate for use for some kinds of data (spatially sorted, with infrequent or no update). But it provides much faster index create time, and much smaller index size.

  • SP-GiST (Space-Partitioned Generalized Search Tree) is a generic index method that supports partitioned search trees such as quad-trees, k-d trees, and radix trees (tries).

Spatial indexes store only the bounding box of geometries. Spatial queries use the index as a primary filter to quickly determine a set of geometries potentially matching the query condition. Most spatial queries require a secondary filter that uses a spatial predicate function to test a more specific spatial condition. For more information on queying with spatial predicates see Section 5.2, “Using Spatial Indexes”.

See also the PostGIS Workshop section on spatial indexes, and the PostgreSQL manual.

4.9.1. Índices GiST

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

4.9.2. BRIN Indexes

BRIN stands for "Block Range Index". It is a general-purpose index method introduced in PostgreSQL 9.5. 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:

  • Index build time is very fast, and index size is very small.

  • Index query time is slower than GiST, but can still be very acceptable.

  • Requires table data to be sorted in a spatial ordering.

  • Requires manual index maintenance.

  • Most appropriate for very large tables, with low or no overlap (e.g. points), which are static or change infrequently.

  • More effective for queries which return relatively large numbers of data records.

4.9.3. SP-GiST Indexes

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:

  • <<, &<, &>, >>, <<|, &<|, |&>, |>>, &&, @>, <@, and ~=, for 2-dimensional indexes,

  • &/&, ~==, @>>, and <<@, for 3-dimensional indexes.

There is no support for kNN searches at the moment.

4.9.4. Construindo índidces

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:

  • Examine the query plan and check your query actually computes the thing you need. An erroneous JOIN, either forgotten or to the wrong table, can unexpectedly retrieve table records multiple times. To get the query plan, execute with EXPLAIN in front of the query.

  • Make sure statistics are gathered about the number and distributions of values in a table, to provide the query planner with better information to make decisions around index usage. VACUUM ANALYZE will compute both.

    You should regularly vacuum your databases anyways. Many PostgreSQL DBAs run VACUUM as an off-peak cron job on a regular basis.

  • If vacuuming does not help, you can temporarily force the planner to use the index information by using the command SET ENABLE_SEQSCAN TO OFF;. This way you can check whether the planner is at all able to generate an index-accelerated query plan for your query. You should only use this command for debugging; generally speaking, the planner knows better than you do about when to use indexes. Once you have run your query, do not forget to run SET ENABLE_SEQSCAN TO ON; so that the planner will operate normally for other queries.

  • If SET ENABLE_SEQSCAN TO OFF; helps your query to run faster, your Postgres is likely not tuned for your hardware. If you find the planner wrong about the cost of sequential versus index scans try reducing the value of RANDOM_PAGE_COST in postgresql.conf, or use SET RANDOM_PAGE_COST TO 1.1;. The default value for RANDOM_PAGE_COST is 4.0. Try setting it to 1.1 (for SSD) or 2.0 (for fast magnetic disks). Decreasing the value makes the planner more likely to use index scans.

  • If SET ENABLE_SEQSCAN TO OFF; does not help your query, the query may be using a SQL construct that the Postgres planner is not yet able to optimize. It may be possible to rewrite the query in a way that the planner is able to handle. For example, a subquery with an inline SELECT may not produce an efficient plan, but could possibly be rewritten using a LATERAL JOIN.

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