Use PostGIS

Eugenia Kuzina

Overview

PostGIS provides support for storing, indexing, and querying geospatial data to extend the capabilities of the PostgreSQL relational database.

PostGIS allows you to work with different types of spatial data, such as points, lines, polygons, and multi-geometries in both 2D and 3D data formats. PostGIS methods include filtering and analyzing spatial data, measuring distances and areas, intersecting geometries, buffering, etc. It also provides tools to process geometry and raster data and perform geocoding.

The package required for the installation of the postgis extension is shipped with ADP. To use postgis, execute the CREATE EXTENSION command:

CREATE EXTENSION postgis;
NOTE
If the postgis extension is created in the template1 database used as the default template, all subsequently created databases will have this extension installed.

ADP uses the 3.4.1 postgis version. To check it, execute the following query:

SELECT extversion FROM pg_extension
    WHERE extname = 'postgis';
 extversion
------------
3.4.1

Alternatively, you can use the postgis_full_version function, which reports the full PostGIS version and build configuration:

SELECT postgis_full_version();
 POSTGIS="3.4.1 16.3_arenadata3-14-g1364ce1" [EXTENSION] PGSQL="160" GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/var/lib/pgsql/.local/share/proj DATABASE_PATH=/usr/
share/proj/proj.db" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"

Geometry and geography types

PostGIS implements the OGC Geometry model as the PostgreSQL geometry and geography data types.

Geometry

Geometry is an abstract type. Values of the geometry type belong to one of its subtypes, which represent various kinds of geometric shapes. Subtypes include:

Geometry types model 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 an X and Y value, determining its location in the plane. Shapes are constructed from points specified by a single coordinate or line segments determined by two coordinates.

Coordinates may contain optional ordinate values — Z and M. Z is often used to represent elevation. The M ordinate contains a measure value, which can be, for example, time or distance. If Z or M values are set in a geometric value, they must be defined for each point in the geometric object.

If a geometric object has either a Z or M ordinate, the coordinate dimension is three-dimensional; if it has both a Z and M ordinate, the coordinate dimension is four-dimensional. So, there are four coordinate options:

  • XY

  • XYZ

  • XYM

  • XYZM

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

The SFA standard specifies that spatial objects include a Spatial Reference System Identifier (SRID). The SRID is required to insert spatial objects into a database.

A Spatial Reference System (SRS) defines how geometry is referenced to locations on the Earth’s surface. There are three types of SRS:

  • A geodetic SRS uses geographic coordinates (longitude and latitude), which map directly to the surface of the Earth.

  • A projected SRS uses a mathematical projection transformation to "flatten" the Earth’s spherical surface and make it a plane. It assigns location coordinates in a way that allows direct measurement of quantities such as distances, areas, and angles.

  • A local SRS is a Cartesian coordinate system, which is not referenced to the Earth’s surface. In PostGIS, set the SRID value to 0 to use it.

Geography

The PostGIS geography data type provides native support for spatial features represented on geographic coordinates (also called geodetic coordinates or latitude and longitude). Geographic coordinates are expressed in degrees. The geography type supports most geometry subtypes except curves, TIN, and POLYHEDRALSURFACE.

The basis for the PostGIS geometry data type is a plane. The shortest path between two points on the plane is a straight line. It means that functions calculating areas, distances, lengths, and intersections use straight-line vectors and Cartesian mathematics. It makes them simpler to implement and faster to execute, but also makes them inaccurate for points on the Earth’s surface.

The PostGIS geography data type is based on a spherical model. The shortest path between two points on the sphere is a circle arc. Functions calculating areas, distances, lengths, and intersections on geographic objects are computed using arcs on the sphere. These functions provide more accurate results, since they take into account the spheroidal shape of the Earth.

Since the mathematics underlying the geography type is more complex, fewer functions are defined for it than for the geometry type. As a workaround, you can convert objects back and forth between the geometry and geography types and use geometry functions.

Like the geometry data type, geography data is associated with a Spatial Reference System via SRID. You can use any geodetic SRS defined in the PostGIS spatial_ref_sys table. It is also possible to add your own custom geodetic SRS. See User-Defined Spatial Reference Systems.

For all SRS, measurement functions (ST_Distance, ST_Length, ST_Perimeter, ST_Area, and others) return values in meters.

TIP

The geography type is very convenient if your data is of global extent, and if you do not want to work with projected coordinate systems. However, calculations on the spheroid are very expensive, so many queries will be slower on geography types than on geometry types.

If you can fit your data into a planar coordinate system, consider using the geometry type rather than geography.

Create tables with geometry and geography columns

Use the standard CREATE TABLE SQL statement to create a table to store geometry and geography data. Both types support two optional modifiers in a column definition — [geometry]|[geography] (<type>, <SRID>):

  • <type> — restricts the kind of shapes and dimensions allowed in the column. The value can be any of the supported geometry or geography subtypes (for example, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION). Note that the geography type does not support curves, TIN, and POLYHEDRALSURFACE. The modifier allows you to set the restriction of coordinate dimensions via suffixes: Z, M, and ZM. For example, LINESTRINGM allows only linestrings with three dimensions and treats the third dimension as a measure; POINTZM requires four-dimensional data (XYZM).

  • <SRID> — restricts SRID to a particular number. If omitted, the SRID is set to 0 for geometry types and 4326 for geography types.

Examples of creating table columns to store geometry and geography objects

Any kind of geometry objects with the default SRID (0)

 

CREATE TABLE geom_table1(
                    id SERIAL PRIMARY KEY,
                    name VARCHAR(64),
                    geometry_column geometry);
3D POINT geometry objects with the default SRID (0)

 

CREATE TABLE geom_table2(
                    id SERIAL PRIMARY KEY,
                    geometry_column geometry(POINTZ));
2D POLYGON geometry objects with SRID 4326 (spatial data using geographic coordinates)

 

CREATE TABLE geom_table3(
                    id SERIAL PRIMARY KEY,
                    geometry_column geometry(POLYGON,4326));
3D POINT geography objects with the default SRID (4326)

 

CREATE TABLE geo_table1(
                    id SERIAL PRIMARY KEY,
                    geo_column geography(POINTZ));
2D POLYGON geography objects with SRID 4267

 

CREATE TABLE geo_table2(
                    id SERIAL PRIMARY KEY,
                    geo_column geography(POLYGON,4267));

It is possible to have more than one geometry or geography column in a table. You can specify multiple columns in the CREATE TABLE statement or add a column using ALTER TABLE:

ALTER TABLE geom_table3 ADD COLUMN geometry_column2 geometry(LINESTRING,4326);

ALTER TABLE geo_table1 ADD COLUMN geo_column2 geography(LINESTRING,4326);
NOTE
Geometry data can be autocasted to the geography type if it has SRID 4326.

Geometry columns are registered in the geometry_columns system view. You can display a list of columns using the query:

SELECT * FROM geometry_columns;
 f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid |    type
-----------------+----------------+--------------+-------------------+-----------------+------+------------
 postgres        | public         | geom_table1  | geometry_column   |               2 |    0 | GEOMETRY
 postgres        | public         | geom_table2  | geometry_column   |               3 |    0 | POINT
 postgres        | public         | geom_table3  | geometry_column   |               2 | 4326 | POLYGON
 postgres        | public         | geom_table3  | geometry_column2  |               2 | 4326 | LINESTRING

Geography columns are registered in the geography_columns system view. You can query the geography_columns view to see a list of the geography columns:

SELECT * FROM geography_columns;
 f_table_catalog | f_table_schema | f_table_name | f_geography_column | coord_dimension | srid |    type
-----------------+----------------+--------------+--------------------+-----------------+------+------------
 postgres        | public         | geo_table1   | geo_column         |               3 | 4326 | PointZ
 postgres        | public         | geo_table2   | geo_column         |               2 | 4267 | Polygon
 postgres        | public         | geo_table1   | geo_column2        |               2 | 4326 | LineString

Convert geometry and geography types for external use

The OGC SFA specification defines two formats for representing geometry and geography values for external use: Well-Known Text (WKT) and Well-Known Binary (WKB).

WKT is a standard textual representation of spatial data. Examples:

POINT(0 0)

POINT ZM (0 0 0 0)

LINESTRING(0 0,1 1,1 2)

Well-Known Binary (WKB) provides representation of spatial data as binary data (arrays of bytes).

In addition to supporting the SFA standard, PostGIS defines extended the EWKB and EWKT (Extended Well-Known Binary and Extended Well-Known Text) formats. They include the SRID information and allow explicit type casting (with the :: operator):

SELECT 'SRID=4326;POINT(0 0)'::geography;
                     geography
----------------------------------------------------
 0101000020E610000000000000000000000000000000000000

The table below lists functions that convert geometry and geography values to text and binary representation and perform inverse conversion (PostGIS uses the ST_ prefix for spatial functions to follow the SFA standard).

Name Supported type Description

ST_GeomFromText

geometry

Creates a PostGIS geometry object from a WKT representation

ST_AsText

geometry, geography

Returns a WKT representation of a PostGIS geometry or geography object

ST_GeomFromWKB

geometry

Creates a PostGIS geometry object from a WKB representation

ST_AsBinary

geometry, geography

Returns a WKB representation of a PostGIS geometry or geography object

ST_GeomFromEWKT

geometry

Creates a PostGIS geometry object from an EWKT representation that contains SRID

ST_AsEWKT

geometry, geography

Returns an EWKT representation (with SRID) of a PostGIS geometry or geography object

ST_GeomFromEWKB

geometry

Creates a PostGIS geometry object from EWKB that contains SRID

ST_AsEWKB

geometry

Returns an EWKB representation (with SRID) of a PostGIS geometry object

Examples of using functions

ST_GeomFromText and ST_AsText

 

The following code builds a geometry object from WKT and inserts it to the geom_table1 table created above:

INSERT INTO geom_table1 (name, geometry_column)
  VALUES ( 'point1', ST_GeomFromText('POINT(15 25)', 0));

SELECT ST_AsText(geometry_column) from geom_table1 WHERE name = 'point1';
 st_astext
--------------
 POINT(15 25)
ST_GeomFromWKB and ST_AsBinary

 

The following code builds a geometry object from WKB and inserts it to the geom_table1 table created above:

INSERT INTO geom_table1 (name, geometry_column)
  VALUES (
        'point2',
        ST_GeomFromWKB('\x0101000000000000000000f03f000000000000f03f',0));

SELECT ST_AsText(geometry_column), ST_AsBinary(geometry_column) from geom_table1 WHERE name = 'point2';
 st_astext  |                 st_asbinary
------------+----------------------------------------------
 POINT(1 1) | \x0101000000000000000000f03f000000000000f03f
ST_GeomFromEWKT and ST_AsEWKT

 

The following code builds a geometry object from EWKT and inserts it to the geom_table1 table:

INSERT INTO geom_table1 (name, geometry_column)
  VALUES ( 'point3',ST_GeomFromEWKT('SRID=4326;POINTM(-138 54 12)'));

SELECT ST_AsEWKT(geometry_column) from geom_table1 WHERE name = 'point3';
          st_asewkt
------------------------------
 SRID=4326;POINTM(-138 54 12)
ST_GeomFromEWKB and ST_AsEWKB

 

The following code builds a geometry object from EWKB and inserts it to the geom_table1 table:

INSERT INTO geom_table1 (name, geometry_column)
  VALUES (
        'point4',
        ST_GeomFromEWKB(E'\\001\\002\\000\\000 \\255\\020\\000\\000\\003\\000\\000\\000\\344J=\\013B\\312Q\\300n\\303(\\010\\036!E@''\\277E''K\\312Q\\300\\366{b\\235*!E@\\225|\\354.P\\312Q\\300p\\231\\323e1!E@'));

SELECT ST_AsEWKT(geometry_column), ST_AsEWKB(geometry_column) from geom_table1 WHERE name = 'point4';
                                      st_asewkt                                      |                                                          st_asewkb
-------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------
 SRID=4269;LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932) | \x0102000020ad10000003000000e44a3d0b42ca51c06ec328081e21454027bf45274bca51c0f67b629d2a214540957cec2e50ca51c07099d36531214540

PostGIS methods for the geometry and geography types

This section contains examples of some popular methods. For a complete list of methods, refer to the PostGIS documentation.

Note that some methods working with the geography type have an additional use_spheroid parameter that determines whether calculations should be performed on a spheroid. If use_spheroid is set to true (the default value), a method calculates a distance or area on a spheroid specified by SRID (for example, WGS 84). If use_spheroid is false, calculations are performed on a spherical model, which is faster but less accurate.

ST_Distance

For geometry type, ST_Distance returns the minimum 2D Cartesian distance between two objects, in projected units. In this example, measurement units are degrees, because SRID 4326 is a geographic coordinate system that uses degrees.

SELECT ST_Distance(
    'SRID=4326;POINT(10 10)'::geometry,
    'SRID=4326;POINT(10 20)'::geometry );
 st_distance
-------------
    10

For geography type, ST_Distance returns the minimum geodesic distance between two objects, in meters, calculated on the spheroid defined by the SRID.

SELECT ST_Distance(
    'SRID=4326;POINT(10 10)'::geography,
    'SRID=4326;POINT(10 20)'::geography );
        st_distance
-----------------------------
     1106511.42093726

ST_Intersects

ST_Intersects returns true if two geometries intersect. Geometries are considered intersecting if they have a common point.

SELECT ST_Intersects(
    'SRID=4326;LINESTRING(-22.21212 12.4447,-22.21212 12.4448)'::geometry,
    'SRID=4326;POINT(-22.21212 12.4447)'::geometry);
 st_intersects
---------------
t
SELECT ST_Intersects(
    'SRID=4326;LINESTRING(-22.21212 12.4447,-22.21212 12.4448)'::geometry,
    'SRID=4326;POINT(-22.21212 12.444699999)'::geometry );
 st_intersects
---------------
f

For geography, a distance tolerance of 0.00001 meters is used — points that are very close are considered to intersect.

SELECT ST_Intersects(
    'SRID=4326;LINESTRING(-22.21212 12.4447,-22.21212 12.4448)'::geography,
    'SRID=4326;POINT(-22.21212 12.444699999)'::geography );
 st_intersects
---------------
t

ST_Area

The ST_Area function returns the area of a polygonal object. For geometry types, a 2D Cartesian (planar) area is computed, with units specified by the SRID.

INSERT INTO geom_table1 (name, geometry_column)
  VALUES ('polygon1',ST_GeomFromEWKT('SRID=4326;POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1))'));

SELECT ST_Area(geometry_column) from geom_table1 WHERE name = 'polygon1';
 st_area
---------
      15

For geography types, the area is determined in square meters. The code below casts polygon1 to the geography type and calculates the polygon area with different values of the use_spheroid parameter: true — exact calculations on a spheroid; false — approximate calculations on a spherical model.

SELECT ST_Area(geometry_column::geography, true) from geom_table1 WHERE name = 'polygon1';
      st_area
--------------------
 184564470319.76245
SELECT ST_Area(geometry_column::geography, false) from geom_table1 WHERE name = 'polygon1';
      st_area
-------------------
 185388539247.5091

ST_Within

The ST_Within method returns true if all points of the first specified geometry object lie inside the second geometry object. Both geometry objects should have the same SRID. The code below determines whether the specified points (POINT(3 3), POINT(5 5)) belong to the polygon1 object from the geom_table1 table.

SELECT ST_Within('SRID=4326;POINT(3 3)'::geometry,geometry_column) FROM
(SELECT geometry_column from geom_table1 WHERE name = 'polygon1');
 st_within
-----------
 t
SELECT ST_Within('SRID=4326;POINT(5 5)'::geometry,geometry_column) FROM
(SELECT geometry_column from geom_table1 WHERE name = 'polygon1');
 st_within
-----------
 f

Raster type

PostGIS supports another kind of spatial data type called raster. Raster data is represented as an n-dimensional matrix consisting of pixels and bands. Each pixel stores a value corresponding to each band. For example, a 3-banded raster, such as an RGB image, has three values for each pixel corresponding to the red, green, blue bands. So, raster data is a matrix pinned on a coordinate system. Each pixel can store not only color, but any additional data: temperature, altitude, level of air pollution, etc.

If you need to use raster features, install the separate postgis_raster extension:

CREATE EXTENSION postgis_raster;

Consider an example of creating a table to store raster data.

Create a table with a column of the raster type:

CREATE TABLE raster_table(name varchar, raster_column raster);

Add data to the table. Use the ST_Letters function to transform text to a geometry object, and the ST_AsRaster function to convert a geometry type to raster:

INSERT INTO raster_table(name, raster_column)
    SELECT f.word, ST_AsRaster(geometry_text, width=>150, height=>150)
FROM (VALUES ('Test'), ('Raster') ) AS f(word), ST_Letters(word) AS geometry_text;

You can use the ST_MetaData function to see metadata of raster objects:

SELECT name, metadata.*
   FROM raster_table, ST_MetaData(raster_column) AS metadata;
  name  | upperleftx | upperlefty | width | height |       scalex       |       scaley        | skewx | skewy | srid | numbands
--------+------------+------------+-------+--------+--------------------+---------------------+-------+-------+------+----------
 Test   |          0 |       75.4 |   150 |    150 | 1.0778839468650032 | -0.5086666666666667 |     0 |     0 |    0 |        1
 Raster |          0 |       75.4 |   150 |    150 | 1.7226319023207244 | -0.5086666666666667 |     0 |     0 |    0 |        1

In the query output, you can see that the raster_table table contains two objects of the raster type.

It is also possible to load raster data via the raster2pgsql tool. See Using raster2pgsql to load rasters.

PostGIS raster functions that allow you to display raster objects are listed at Raster Outputs. Please note that by default the drivers for these functions are disabled. It is necessary to enable drivers to use output raster functions. See Enable GDAL Raster drivers.

The following code enables drivers for the current session and uses the ST_AsPNG function to output a raster image:

SET postgis.gdal_enabled_drivers = 'ENABLE_ALL';

SELECT 'data:image/png;base64,' ||
   encode(ST_AsPNG(raster_column),'base64')
   FROM raster_table
   WHERE name = 'Test';
                                              ?column?
----------------------------------------------------------------------------------------------------
 data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJYAAACWCAAAAAAZai4+AAAAAnRSTlMAAHaTzTgAAAMBSURBVHic+
 7ZzLdoMwDETtnvz/L6uLNm0By8zo4dCg2QRSQJex7BDZTW+vVN/ty3PjYzHIVqL94bVYqh57H1Wp                      +
 d5ahi7pVWIwKi1FhMSosRoXFqLAYFRajwmJUWIwKi1FhMSosRoXFqLAYXRSrT/aiakd9dK1pAUse                      +
 nlBtFE85sFN3acPqm+1JuL7dgcEsWHv/9XD4kTsZUn6QFUqi4EfuxWMNL+x88yAay20MxMViwYVp                      +
 n8KG0wOv6wZIrEmsUB85LCK0jzLwMzHSrqyPap0RGlAprEXdsMW6FUjNYC1L+Ms+Bt4Bqx82jsKe                      +
 bAisdf3wHo0YpzthuTOewEIyPqxX3KkR/Sqs1vDv+xd1y1oaaa01cfU8aU0rEXWHWyKuStPsVLG7                      +
 9XVZAfwa2XKmVbkl0hrhrhcLb0ZhWtzaiJasIs656ADhxspZMujGynmUftdGzNECLEsze7GgZ1Ye                      +
 zIkFBqS5fFhwOJbLhZVXwnFgcSnDcZmx6DymjrdijYPI5gU6ZayVwynBdd9R/le4XTlY6LSnqlCs                      +
 uGevxbmF2nWDlJfh5lZpc9VLFIgl6s5fJU2hr1Eclkx3Sa13K2XBgaqDOy67orAICMSuxEb02BWE                      +
 NURwcMVghddHQrA0KuV9ILkisHSvzC4GYM1rxjZ56vKuwHNll3SN1D4soHps43JhQSFNXI7cQuNZ                      +
 poYccz4ph37LikXNSfBc7gFi20JRw8X61W5nf2ytpWH16yxZ3GlSlztvanfZbbYS1e5Y7leMvntF                      +
 j3fUTqGArGHPq9oHiC4NscHG5SmA6zHds3me4TRxVd7b1yBC9e+x6ud1orF+VhQhR830Yrc0wls0                      +
 YpgIrJVdseZ8nkLq9wwWakbAR/j/T/lTYW5C0woU1rq++BaNOLXL1IbKaQluRTxLk1gTR4xmjU9M                      +
 cCuiY7BYakxkohqfhKHdUmJiM8LwNflGRG6YnG05vm/ILSRrx/HxWRhLyiMQo9oqMQsz/ZkKbNkA                      +
 urbgLOO6ss2pI8F+Lg/2gpX/bGjQJ2vGgjXGZUXpAAAAAElFTkSuQmCC

PostGIS topology

PostGIS topology is an advanced module of PostGIS that manages vector data based on spatial relationships. It is supports the "SQL/MM SQL-MM 3 Topo-Geo" and "Topo-Net 3" specifications and requires an advanced postgis_topology extension to be installed:

CREATE EXTENSION postgis_topology;

Topology allows you to store and process spatial objects not just as individual geometric shapes, but as an interconnected system where relations between objects are important. The main goal is to ensure topological integrity, that is, to check that objects have correct relations, for example, that polygon boundaries do not intersect where they should not. Topology allows you to perform more complex queries on spatial data, such as "find all roads leading to this intersection" or "find all buildings bordering this site."

You can learn about all the functions and types provided by this extension in the PostGIS Topology documentation.

postgis_sfcgal module

The postgis_sfcgal extension is a C++ wrapper around CGAL (Computational Geometry Algorithms Library) that provides advanced 2D and 3D spatial functions. For robustness, geometry coordinates have an exact rational number representation.

You can use the following command to install it:

CREATE EXTENSION postgis_sfcgal;

For information on SFCGAL functions, see SFCGAL Functions Reference.

postgis_tiger_geocoder module

The postgis_tiger_geocoder module is a PL/pgSQL-based geocoder written to work with the TIGER (Topologically Integrated Geographic Encoding and Referencing) system. There are four components of the geocoder: data loader functions, an address normalizer, an address geocoder, and a reverse geocoder. Although it is designed specifically for the US, a lot of the concepts and functions can be applied to work with other country address and road networks.

To install postgis_tiger_geocoder, use the CREATE EXTENSION command:

CREATE EXTENSION postgis_tiger_geocoder;

For additional information, see Tiger Geocoder.

Found a mistake? Seleсt text and press Ctrl+Enter to report it