PostGIS

Abstract

PostGIS is an extension to the PostgreSQL object-relational database system which allows GIS (Geographic Information Systems) objects to be stored in the database. PostGIS includes support for GiST-based R-Tree spatial indexes, and functions for analysis and processing of GIS objects.

This is the manual for version 3.4.3dev

This work is licensed under a Creative Commons Attribution-Share Alike 3.0 License. Feel free to use this material any way you like, but we ask that you attribute credit to the PostGIS Project and wherever possible, a link back to https://postgis.net.

Table of Contents

1. Introduction

1.1. Project Steering Committee

1.2. Core Contributors Present

1.3. Core Contributors Past

1.4. Other Contributors

2. PostGIS Installation

2.1. Short Version

2.2. Compiling and Install from Source

2.2.1. Getting the Source

2.2.2. Install Requirements

2.2.3. Build configuration

2.2.4. Building

2.2.5. Building PostGIS Extensions and Deploying them

2.2.6. Testing

2.2.7. Installation

2.3. Installing and Using the address standardizer

2.4. Installing, Upgrading Tiger Geocoder, and loading data

2.4.1. Tiger Geocoder Enabling your PostGIS database

2.4.2. Using Address Standardizer Extension with Tiger geocoder

2.4.3. Required tools for tiger data loading

2.4.4. Upgrading your Tiger Geocoder Install and Data

2.5. Common Problems during installation

3. PostGIS Administration

3.1. Performance Tuning

3.1.1. Startup

3.1.2. Runtime

3.2. Configuring raster support

3.3. Creating spatial databases

3.3.1. Spatially enable database using EXTENSION

3.3.2. Spatially enable database without using EXTENSION (discouraged)

3.4. Upgrading spatial databases

3.4.1. Soft upgrade

3.4.2. Hard upgrade

4. Data Management

4.1. Spatial Data Model

4.1.1. OGC Geometry

4.1.2. SQL/MM Part 3 - Curves

4.1.3. WKT and WKB

4.2. Geometry Data Type

4.2.1. PostGIS EWKB and EWKT

4.3. Geography Data Type

4.3.1. Creating Geography Tables

4.3.2. Using Geography Tables

4.3.3. When to use the Geography data type

4.3.4. Geography Advanced FAQ

4.4. Geometry Validation

4.4.1. Simple Geometry

4.4.2. Valid Geometry

4.4.3. Managing Validity

4.5. Spatial Reference Systems

4.5.1. SPATIAL_REF_SYS Table

4.5.2. User-Defined Spatial Reference Systems

4.6. Spatial Tables

4.6.1. Creating a Spatial Table

4.6.2. GEOMETRY_COLUMNS View

4.6.3. Manually Registering Geometry Columns

4.7. Loading Spatial Data

4.7.1. Using SQL to Load Data

4.7.2. Using the Shapefile Loader

4.8. Extracting Spatial Data

4.8.1. Using SQL to Extract Data

4.8.2. Using the Shapefile Dumper

4.9. Spatial Indexes

4.9.1. GiST Indexes

4.9.2. BRIN Indexes

4.9.3. SP-GiST Indexes

4.9.4. Tuning Index Usage

5. Spatial Queries

5.1. Determining Spatial Relationships

5.1.1. Dimensionally Extended 9-Intersection Model

5.1.2. Named Spatial Relationships

5.1.3. General Spatial Relationships

5.2. Using Spatial Indexes

5.3. Examples of Spatial SQL

6. Performance Tips

6.1. Small tables of large geometries

6.1.1. Problem description

6.1.2. Workarounds

6.2. CLUSTERing on geometry indices

6.3. Avoiding dimension conversion

7. PostGIS Reference

7.1. PostGIS Geometry/Geography/Box Data Types

7.2. Table Management Functions

7.3. Geometry Constructors

7.4. Geometry Accessors

7.5. Geometry Editors

7.6. Geometry Validation

7.7. Spatial Reference System Functions

7.8. Geometry Input

7.8.1. Well-Known Text (WKT)

7.8.2. Well-Known Binary (WKB)

7.8.3. Other Formats

7.9. Geometry Output

7.9.1. Well-Known Text (WKT)

7.9.2. Well-Known Binary (WKB)

7.9.3. Other Formats

7.10. Operators

7.10.1. Bounding Box Operators

7.10.2. Distance Operators

7.11. Spatial Relationships

7.11.1. Topological Relationships

7.11.2. Distance Relationships

7.12. Measurement Functions

7.13. Overlay Functions

7.14. Geometry Processing

7.15. Coverages

7.16. Affine Transformations

7.17. Clustering Functions

7.18. Bounding Box Functions

7.19. Linear Referencing

7.20. Trajectory Functions

7.21. SFCGAL Functions

7.22. Long Transaction Support

7.23. Version Functions

7.24. Grand Unified Custom Variables (GUCs)

7.25. Troubleshooting Functions

8. Topology

8.1. Topology Types

8.2. Topology Domains

8.3. Topology and TopoGeometry Management

8.4. Topology Statistics Management

8.5. Topology Constructors

8.6. Topology Editors

8.7. Topology Accessors

8.8. Topology Processing

8.9. TopoGeometry Constructors

8.10. TopoGeometry Editors

8.11. TopoGeometry Accessors

8.12. TopoGeometry Outputs

8.13. Topology Spatial Relationships

8.14. Importing and exporting Topologies

8.14.1. Using the Topology exporter

8.14.2. Using the Topology importer

9. Raster Data Management, Queries, and Applications

9.1. Loading and Creating Rasters

9.1.1. Using raster2pgsql to load rasters

9.1.2. Creating rasters using PostGIS raster functions

9.1.3. Using "out db" cloud rasters

9.2. Raster Catalogs

9.2.1. Raster Columns Catalog

9.2.2. Raster Overviews

9.3. Building Custom Applications with PostGIS Raster

9.3.1. PHP Example Outputting using ST_AsPNG in concert with other raster functions

9.3.2. ASP.NET C Example Outputting using ST_AsPNG in concert with other raster functions#

9.3.3. Java console app that outputs raster query as Image file

9.3.4. Use PLPython to dump out images via SQL

9.3.5. Outputting Rasters with PSQL

10. Raster Reference

10.1. Raster Support Data types

10.2. Raster Management

10.3. Raster Constructors

10.4. Raster Accessors

10.5. Raster Band Accessors

10.6. Raster Pixel Accessors and Setters

10.7. Raster Editors

10.8. Raster Band Editors

10.9. Raster Band Statistics and Analytics

10.10. Raster Inputs

10.11. Raster Outputs

10.12. Raster Processing: Map Algebra

10.13. Built-in Map Algebra Callback Functions

10.14. Raster Processing: DEM (Elevation)

10.15. Raster Processing: Raster to Geometry

10.16. Raster Operators

10.17. Raster and Raster Band Spatial Relationships

10.18. Raster Tips

10.18.1. Out-DB Rasters

11. PostGIS Extras

11.1. Address Standardizer

11.1.1. How the Parser Works

11.1.2. Address Standardizer Types

11.1.3. Address Standardizer Tables

11.1.4. Address Standardizer Functions

11.2. Tiger Geocoder

12. PostGIS Special Functions Index

12.1. PostGIS Aggregate Functions

12.2. PostGIS Window Functions

12.3. PostGIS SQL-MM Compliant Functions

12.4. PostGIS Geography Support Functions

12.5. PostGIS Raster Support Functions

12.6. PostGIS Geometry / Geography / Raster Dump Functions

12.7. PostGIS Box Functions

12.8. PostGIS Functions that support 3D

12.9. PostGIS Curved Geometry Support Functions

12.10. PostGIS Polyhedral Surface Support Functions

12.11. PostGIS Function Support Matrix

12.12. New, Enhanced or changed PostGIS Functions

12.12.1. PostGIS Functions new or enhanced in 3.4

12.12.2. PostGIS Functions new or enhanced in 3.3

12.12.3. PostGIS Functions new or enhanced in 3.2

12.12.4. PostGIS Functions new or enhanced in 3.1

12.12.5. PostGIS Functions new or enhanced in 3.0

12.12.6. PostGIS Functions new or enhanced in 2.5

12.12.7. PostGIS Functions new or enhanced in 2.4

12.12.8. PostGIS Functions new or enhanced in 2.3

12.12.9. PostGIS Functions new or enhanced in 2.2

12.12.10. PostGIS Functions new or enhanced in 2.1

12.12.11. PostGIS Functions new or enhanced in 2.0

12.12.12. PostGIS Functions new or enhanced in 1.5

12.12.13. PostGIS Functions new or enhanced in 1.4

12.12.14. PostGIS Functions new or enhanced in 1.3

13. Reporting Problems

13.1. Reporting Software Bugs

13.2. Reporting Documentation Issues

A. Appendix

A.1. PostGIS 3.4.2

A.2. PostGIS 3.4.1

A.3. PostGIS 3.4.0