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.3.7dev

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 http://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.3.1. Installing Regex::Assemble

2.4. Installing, Upgrading Tiger Geocoder and loading data

2.4.1. Tiger Geocoder Enabling your PostGIS database: Using Extension

2.4.2. Tiger Geocoder Enabling your PostGIS database: Not Using Extensions

2.4.3. Using Address Standardizer Extension with Tiger geocoder

2.4.4. Loading Tiger Data

2.4.5. Upgrading your Tiger Geocoder Install

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.3.3. Create a spatially-enabled database from a template

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. Building Applications

7.1. Using MapServer

7.1.1. Basic Usage

7.1.2. Frequently Asked Questions

7.1.3. Advanced Usage

7.1.4. Examples

7.2. Java Clients (JDBC)

7.3. C Clients (libpq)

7.3.1. Text Cursors

7.3.2. Binary Cursors

8. PostGIS Reference

8.1. PostGIS Geometry/Geography/Box Data Types

8.2. Table Management Functions

8.3. Geometry Constructors

8.4. Geometry Accessors

8.5. Geometry Editors

8.6. Geometry Validation

8.7. Spatial Reference System Functions

8.8. Geometry Input

8.8.1. Well-Known Text (WKT)

8.8.2. Well-Known Binary (WKB)

8.8.3. Other Formats

8.9. Geometry Output

8.9.1. Well-Known Text (WKT)

8.9.2. Well-Known Binary (WKB)

8.9.3. Other Formats

8.10. Operators

8.10.1. Bounding Box Operators

8.10.2. Distance Operators

8.11. Spatial Relationships

8.11.1. Topological Relationships

8.11.2. Distance Relationships

8.12. Measurement Functions

8.13. Overlay Functions

8.14. Geometry Processing

8.15. Affine Transformations

8.16. Clustering Functions

8.17. Bounding Box Functions

8.18. Linear Referencing

8.19. Trajectory Functions

8.20. SFCGAL Functions

8.21. Long Transaction Support

8.22. Version Functions

8.23. Grand Unified Custom Variables (GUCs)

8.24. Troubleshooting Functions

9. PostGIS Frequently Asked Questions

10. Topology

10.1. Topology Types

10.2. Topology Domains

10.3. Topology and TopoGeometry Management

10.4. Topology Statistics Management

10.5. Topology Constructors

10.6. Topology Editors

10.7. Topology Accessors

10.8. Topology Processing

10.9. TopoGeometry Constructors

10.10. TopoGeometry Editors

10.11. TopoGeometry Accessors

10.12. TopoGeometry Outputs

10.13. Topology Spatial Relationships

10.14. Importing and exporting Topologies

10.14.1. Using the Topology exporter

10.14.2. Using the Topology importer

11. Raster Data Management, Queries, and Applications

11.1. Loading and Creating Rasters

11.1.1. Using raster2pgsql to load rasters

11.1.2. Creating rasters using PostGIS raster functions

11.1.3. Using "out db" cloud rasters

11.2. Raster Catalogs

11.2.1. Raster Columns Catalog

11.2.2. Raster Overviews

11.3. Building Custom Applications with PostGIS Raster

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

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

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

11.3.4. Use PLPython to dump out images via SQL

11.3.5. Outputting Rasters with PSQL

12. Raster Reference

12.1. Raster Support Data types

12.2. Raster Management

12.3. Raster Constructors

12.4. Raster Accessors

12.5. Raster Band Accessors

12.6. Raster Pixel Accessors and Setters

12.7. Raster Editors

12.8. Raster Band Editors

12.9. Raster Band Statistics and Analytics

12.10. Raster Inputs

12.11. Raster Outputs

12.12. Raster Processing: Map Algebra

12.13. Built-in Map Algebra Callback Functions

12.14. Raster Processing: DEM (Elevation)

12.15. Raster Processing: Raster to Geometry

12.16. Raster Operators

12.17. Raster and Raster Band Spatial Relationships

12.18. Raster Tips

12.18.1. Out-DB Rasters

13. PostGIS Raster Frequently Asked Questions

14. PostGIS Extras

14.1. Address Standardizer

14.1.1. How the Parser Works

14.1.2. Address Standardizer Types

14.1.3. Address Standardizer Tables

14.1.4. Address Standardizer Functions

14.2. Tiger Geocoder

15. PostGIS Special Functions Index

15.1. PostGIS Aggregate Functions

15.2. PostGIS Window Functions

15.3. PostGIS SQL-MM Compliant Functions

15.4. PostGIS Geography Support Functions

15.5. PostGIS Raster Support Functions

15.6. PostGIS Geometry / Geography / Raster Dump Functions

15.7. PostGIS Box Functions

15.8. PostGIS Functions that support 3D

15.9. PostGIS Curved Geometry Support Functions

15.10. PostGIS Polyhedral Surface Support Functions

15.11. PostGIS Function Support Matrix

15.12. New, Enhanced or changed PostGIS Functions

15.12.1. PostGIS Functions new or enhanced in 3.3

15.12.2. PostGIS Functions new or enhanced in 3.2

15.12.3. PostGIS Functions new or enhanced in 3.1

15.12.4. PostGIS Functions new or enhanced in 3.0

15.12.5. PostGIS Functions new or enhanced in 2.5

15.12.6. PostGIS Functions new or enhanced in 2.4

15.12.7. PostGIS Functions new or enhanced in 2.3

15.12.8. PostGIS Functions new or enhanced in 2.2

15.12.9. PostGIS functions breaking changes in 2.2

15.12.10. PostGIS Functions new or enhanced in 2.1

15.12.11. PostGIS functions breaking changes in 2.1

15.12.12. PostGIS Functions new, behavior changed, or enhanced in 2.0

15.12.13. PostGIS Functions changed behavior in 2.0

15.12.14. PostGIS Functions new, behavior changed, or enhanced in 1.5

15.12.15. PostGIS Functions new, behavior changed, or enhanced in 1.4

15.12.16. PostGIS Functions new in 1.3

16. Reporting Problems

16.1. Reporting Software Bugs

16.2. Reporting Documentation Issues

A. Appendix

A.1. PostGIS 3.3.6

A.2. PostGIS 3.3.5

A.3. PostGIS 3.3.4

A.4. PostGIS 3.3.3

A.5. PostGIS 3.3.2

A.6. PostGIS 3.3.1

A.7. PostGIS 3.3.0

A.8. PostGIS 3.3.0rc2

A.9. PostGIS 3.3.0rc1

A.10. PostGIS 3.3.0beta2

A.11. PostGIS 3.3.0beta1

A.12. PostGIS 3.3.0alpha1

A.13. PostGIS 3.2.0 (Olivier Courtin Edition)

A.14. PostGIS 3.2.0beta3

A.15. Release 3.2.0beta2

A.16. Release 3.2.0beta1

A.17. Release 3.2.0alpha1

A.18. Release 3.1.0beta1

A.19. Release 3.1.0alpha3

A.20. Release 3.1.0alpha2

A.21. Release 3.1.0alpha1

A.22. Release 3.0.0

A.23. Release 3.0.0rc2

A.24. Release 3.0.0rc1

A.25. Release 3.0.0beta1

A.26. Release 3.0.0alpha4

A.27. Release 3.0.0alpha3

A.28. Release 3.0.0alpha2

A.29. Release 3.0.0alpha1

A.30. Release 2.5.0

A.31. Release 2.4.5

A.32. Release 2.4.4

A.33. Release 2.4.3

A.34. Release 2.4.2

A.35. Release 2.4.1

A.36. Release 2.4.0

A.37. Release 2.3.3

A.38. Release 2.3.2

A.39. Release 2.3.1

A.40. Release 2.3.0

A.41. Release 2.2.2

A.42. Release 2.2.1

A.43. Release 2.2.0

A.44. Release 2.1.8

A.45. Release 2.1.7

A.46. Release 2.1.6

A.47. Release 2.1.5

A.48. Release 2.1.4

A.49. Release 2.1.3

A.50. Release 2.1.2

A.51. Release 2.1.1

A.52. Release 2.1.0

A.53. Release 2.0.5

A.54. Release 2.0.4

A.55. Release 2.0.3

A.56. Release 2.0.2

A.57. Release 2.0.1

A.58. Release 2.0.0

A.59. Release 1.5.4

A.60. Release 1.5.3

A.61. Release 1.5.2

A.62. Release 1.5.1

A.63. Release 1.5.0

A.64. Release 1.4.0

A.65. Release 1.3.6

A.66. Release 1.3.5

A.67. Release 1.3.4

A.68. Release 1.3.3

A.69. Release 1.3.2

A.70. Release 1.3.1

A.71. Release 1.3.0

A.72. Release 1.2.1

A.73. Release 1.2.0

A.74. Release 1.1.6

A.75. Release 1.1.5

A.76. Release 1.1.4

A.77. Release 1.1.3

A.78. Release 1.1.2

A.79. Release 1.1.1

A.80. Release 1.1.0

A.81. Release 1.0.6

A.82. Release 1.0.5

A.83. Release 1.0.4

A.84. Release 1.0.3

A.85. Release 1.0.2

A.86. Release 1.0.1

A.87. Release 1.0.0

A.88. Release 1.0.0RC6

A.89. Release 1.0.0RC5

A.90. Release 1.0.0RC4

A.91. Release 1.0.0RC3

A.92. Release 1.0.0RC2

A.93. Release 1.0.0RC1