PostGIS in Action, Third Edition
Leo S. Hsu and Regina O. Obe
  • MEAP began August 2019
  • Publication in January 2021 (estimated)
  • ISBN 9781617296697
  • 600 pages (estimated)
  • printed in black & white
free previous edition eBook included
An eBook copy of the previous edition of this book is included at no additional cost. It will be automatically added to your Manning Bookshelf within 24 hours of purchase.

A must-read book for novice geographers.

Daniele Andreis
PostGIS in Action, Third Edition teaches readers of all levels to write spatial queries for PostgreSQL. It first gives you a background in vector-, raster-, and topology-based GIS and then quickly moves into analyzing, viewing, and mapping data. This fully updated third edition covers key changes in PostGIS 3.0 and PostgreSQL 12, including parallelization support, partitioned tables, and new JSON functions that help in creating web mapping applications.

About the Technology

Processing location and topology data requires specialized know-how. PostGIS is a free spatial database extender for PostgreSQL that delivers the features and firepower you need to take on nearly any geodata task. With it, you can easily create location-aware queries in just a few lines of SQL code and build the back end for a mapping, raster analysis, or routing application with minimal effort.

About the book

PostGIS in Action, Third Edition teaches you to solve real-world geodata problems. It first gives you a background in vector-, raster-, and topology-based GIS and then quickly moves into analyzing, viewing, and mapping data. You'll learn how to optimize queries for maximum speed, simplify geometries for greater efficiency, and create custom functions for your own applications. You'll also learn how to apply your existing GIS knowledge to PostGIS and integrate with other GIS tools. Fully updated to the latest versions of PostGIS and PostgreSQL, this Third Edition covers new PostGIS features including Foreign Data Wrappers, Database as a Service, parallelization of queries, and new JSON and Vector Tiles functions that help in creating web mapping applications.
Table of Contents detailed table of contents

Part 1: Introduction to PostGIS

1 What is a spatial database?

1.1 Thinking spatially

1.2 Introducing PostGIS

1.3 Why PostGIS

1.3.1 Standards conformance

1.3.2 Built on top of PostgreSQL

1.3.3 Free—​as in money

1.3.4 Alternatives to PostGIS

1.3.5 Oracle Spatial

1.3.6 Microsoft SQL Server

1.4 Installing PostGIS

1.4.1 Verifying versions of PostGIS and PostgreSQL

1.5 Spatial data types

1.5.1 Geometry type

1.5.2 Geography type

1.5.3 Raster type

1.5.4 Topology type

1.6 Hello real world

1.6.1 Digesting the problem

1.6.2 Modeling

1.6.3 Loading data

1.6.4 Writing the query

1.6.5 Viewing spatial data with OpenJump

1.7 Summary

2 Spatial data types

2.1 Type modifiers

2.1.1 Subtype type modifiers

2.1.2 Spatial reference identifier

2.2 Geometry

2.2.1 Points

2.3 Geography

2.3.1 Differences between geography and geometry

2.3.2 Spatial catalogs for geography

2.4 Raster

2.4.1 Properties of rasters

2.4.2 Creating rasters

2.4.3 Spatial catalog for rasters

2.5 Summary

3 Spatial reference systems

3.1 Spatial reference systems: What are they?

3.1.1 Geoids

3.1.2 Ellipsoids

3.1.3 Datum

3.1.4 Selecting a spatial reference system for storing data

3.1.5 Determining the spatial reference system of source data

3.2 Summary

4 Working with real data

4.1 General utilities

4.2 PostgreSQL built-in tools

4.2.1 psql

4.2.2 pgAdmin4

4.2.3 pg_dump and pg_restore

4.3 Downloading files

4.3.1 Extracting files

4.3.2 Importing and exporting shapefiles

4.3.3 Importing and exporting vector data with ogr2ogr

4.4 Querying external data using PostgreSQL foreign data wrappers

4.4.1 file_fdw Foreign Data wrapper

4.4.2 ogr_fdw Foreign Data wrapper

4.4.3 Converting hstore tags to jsonb

4.4.4 Importing raster data with raster2pgsql

4.4.5 Exporting raster data with GDAL

4.5 Summary

5 Using PostGIS on the desktop

5.1 Desktop viewing tools at a glance

5.2 Capsule reviews

5.2.1 OpenJUMP

5.2.2 QGIS

5.2.3 gvSIG

5.2.4 Jupyter notebook and lab

5.2.5 Spatial database support

5.2.6 Format support

5.2.7 Web services supported

5.3 OpenJUMP workbench

5.3.1 OpenJUMP feature summary

5.3.2 Installing OpenJUMP

5.3.3 PostGIS support

5.3.4 Register data source

5.3.5 Rendering PostGIS geometries

5.3.6 Exporting data

5.4 QGIS

5.4.1 Installing QGIS

5.4.2 Using QGIS with PostGIS

5.4.3 gvSIG

5.4.4 Jupyter lab and notebook

5.4.5 Installing Jupyter

5.4.6 Launching Jupyter Notebook App

5.4.7 Launching Jupyter Lab App

5.4.8 Creating a Python notebook

5.4.9 Magic Commands

5.4.10 Doing raw queries with Jupyter Notebook

5.5 Using GeoPandas, shapely, and matplotlib to work with spatial data

5.5.1 Loading data with GeoPandas

5.6 Viewing data on a map with folium

5.7 Summary

Part 2: PostGIS basic operations

6 Geometry and geography functions

6.1 Output functions

6.1.1 Well-known binary (WKB) and well-known text (WKT)

6.1.2 Keyhole Markup Language (KML)

6.1.3 Geography Markup Language (GML)

6.1.4 Geometry JavaScript Object Notation (GeoJSON)

6.1.5 Scalable Vector Graphics (SVG)

6.1.6 Mapbox Vector Tile (MVT) and Protocol Buffers

6.1.7 Tiny WKB (TWKB)

6.1.8 Extensible 3D Graphics (X3D)

6.1.9 Examples of output functions

6.1.10 Geohash

6.2 Constructor functions

6.2.1 Creating geometries from text and binary formats

6.2.2 ST_GeomFromText

6.2.3 ST_GeomFromWKB and ST_GeomFromEWKB

6.2.4 ST_GeomFromGML, ST_GeomFromGeoJSON, ST_GeomFromKML, ST_GeomFromTWKB, ST_GeomFromGeoHash

6.2.5 Creating geographies from text and binary formats

6.2.6 Using text or binary representations as function arguments

6.3 Accessor and setter functions

6.3.1 Spatial reference identifiers

6.3.2 Transforming geometry to different spatial references

6.3.3 Using transformation with the geography type

6.3.4 Geometry type functions

6.3.5 Geometry and coordinate dimensions

6.3.6 Retrieving coordinates

6.3.7 Checking geometry validity

6.3.8 Number of points that defines a geometry

6.4 Measurement functions

6.4.1 Geometry planar measurements

6.4.2 Geodetic measurements

6.5 Decomposition functions

6.5.1 Bounding box of geometries

6.5.2 Boundaries and converting polygons to linestrings

6.5.3 Centroid, Median, and Point on surface

6.5.4 Returning points defining a geometry

6.5.5 Decomposing multi-geometries and geometry collections

6.6 Composition functions

6.6.1 Making points

6.6.2 Making polygons

6.6.3 ST_MakeEnvelope

6.6.4 ST_MakePolygon

6.6.5 ST_BuildArea

6.6.6 ST_Polygonize

6.6.7 Promoting single geometries to multi-geometries

6.7 Simplification functions

6.7.1 Grid snapping and coordinate rounding

6.7.2 Simplification

6.8 Summary

7 Raster functions

7.1 Raster terminology

7.2 Raster constructors

7.2.1 Converting geometries to rasters with ST_AsRaster

7.2.2 Loading rasters with raster2pgsql

7.2.3 Constructing rasters from scratch: ST_MakeEmptyRaster and ST_AddBand

7.2.4 Setting pixels: ST_SetValue and ST_SetValues

7.2.5 Creating rasters from other rasters

7.2.6 Converting other raster formats with ST_FromGDALRaster

7.3 Raster output functions

7.3.1 ST_AsPNG, ST_AsJPEG, and ST_AsTiff

7.3.2 Output using ST_AsGDALRaster

7.3.3 Using psql to export rasters

7.4 Raster accessors and setters

7.4.1 Basic raster metadata properties

7.4.2 Pixel statistics

7.4.3 Pixel value accessors

7.4.4 Band metadata setters

7.5 Georeferencing functions

7.5.1 Metadata setters

7.5.2 Processing functions

7.6 Reclassing functions

7.7 Polygonizing functions

7.7.1 ST_ConvexHull

7.7.2 ST_Envelope

7.7.3 ST_Polygon

7.7.4 ST_MinConvexHull

7.8 Summary

8 Spatial relationships

8.1 Bounding box and geometry comparators

8.1.1 The bounding box

8.1.2 Bounding box comparators

8.2 Relating two geometries

8.2.1 Interior, exterior, and boundary of a geometry

8.2.2 Intersections

8.2.3 A house plan model

8.2.4 Contains and within

8.2.5 Covers and covered by

8.2.6 Contains properly

8.2.7 Overlapping geometries

8.2.8 Touching geometries

8.2.9 The faces of equality: geometry

8.2.10 Underpinnings of relationship functions

8.3 Summary

Part 3: Putting PostGIS to work

9 Proximity analysis

9.1 Nearest neighbor searches

9.1.1 Which places are within X distance?

9.1.2 Using ST_DWithin and ST_Distance for N closest results

9.1.3 Using ST_DWithin and DISTINCT ON to find closest locations

9.1.4 Intersects with tolerance

9.1.5 Finding N closest places using KNN distance operators

9.2 Using KNN with geography types

9.2.1 Using window functions to number closest N places

9.3 Geotagging

9.3.1 Tagging data to a specific region

9.3.2 Linear referencing: snapping points to the closest linestring

9.4 Summary

10 PostGIS TIGER geocoder

10.1 Installing the PostGIS TIGER geocoder

10.2 Loading TIGER data

10.2.1 Configuration tables

10.2.2 Loading nation and state data

10.3 Normalizing addresses

10.3.1 Using normalize_address

10.3.2 Using the PAGC address normalizer

10.4 Geocoding

10.4.1 Geocoding using address text

10.4.2 Geocoding using normalized addresses

10.4.3 Geocoding intersections

10.4.4 Batch geocoding

10.5 Reverse geocoding

10.6 Summary

11 Geometry and geography processing

11.1 Using spatial aggregate functions

11.1.1 Creating a multipolygon from many multipolygon records

11.1.2 Creating linestrings from points

11.2 Clipping, splitting, tessellating

11.2.1 Clipping

11.2.2 Splitting

11.2.3 Tessellating

11.3 Breaking linestrings into smaller segments

11.3.1 Segmentizing linestrings

11.3.2 Creating two-point linestrings from many-point linestrings

11.3.3 Breaking linestrings at point junctions

11.4 Translating, scaling, and rotating geometries

11.4.1 Translating

11.4.2 Scaling

11.4.3 Rotating

11.5 Using geometry functions to manipulate and create geographies

11.5.1 Cast-safe functions

11.6 Summary

12 Raster processing

12.1 Loading and preparing data

12.1.1 Forming larger rasters using spatial aggregate functions

12.1.2 Working with bands

12.1.3 Tiling rasters

12.1.4 Raster and geometry intersections

12.1.5 Raster statistics

12.1.6 Map algebra

12.2 Summary

13 Building and using topologies

13.1 What topology is

13.2 Using topologies

13.2.1 Installing the topology extension

13.2.2 Creating a topology

13.2.3 The topogeometry type

13.2.4 Recap of using topologies

13.3 Topology of Victoria, BC

13.3.1 Creating the Victoria topology

13.3.2 Adding primitives to a topology

13.3.3 Creating topogeometries

13.4 Fixing topogeometry issues by editing topology primitives

13.4.1 Removing faces by removing edges

13.4.2 Checking for shared faces

13.4.3 Editing topogeometries

13.5 Inserting and editing large data sets

13.6 Simplifying with topology in mind

13.7 Topology validation and summary functions

13.8 Summary

14 Organizing spatial data

14.1 Spatial storage approaches

14.2 Heterogeneous columns

14.2.1 Pros of heterogeneous columns

14.2.2 Homogeneous columns

14.2.3 Typmod vs. constraints

14.2.4 Table inheritance

14.2.5 Table partitioning

14.3 Modeling a real city

14.3.1 Modeling using heterogeneous geometry columns

14.3.2 Modeling using homogeneous geometry columns

14.3.3 Modeling using partitioning

14.4 Making auto-updatable views

14.5 Using Triggers and rules

14.5.1 Triggers

14.5.2 Using INSTEAD OF triggers

14.5.3 Using other triggers

14.6 Summary

15 Query performance tuning

15.1 The query planner

15.1.1 Different kinds of spatial queries

15.1.2 Common table expressions and how they affect plans

15.2 Planner statistics

15.3 Using explain to diagnose problems

15.3.1 Text explain vs. pgAdmin graphical explain

15.3.2 The plan with no index

15.4 Planner and indexes

15.4.1 The plan with a spatial index

15.4.2 Indexes

15.5 Common SQL patterns and how they affect plans

15.5.1 Subqueries in SELECT

15.5.2 FROM subqueries and basic CTEs

15.5.3 Window functions and self joins

15.5.4 Lateral joins

15.6 System and function settings

15.6.1 Key system variables that affect plan strategy

15.6.2 Function-specific settings

15.6.3 Encouraging Parallel Plans

15.7 Optimizing spatial data

15.7.1 Fixing invalid geometries

15.7.2 Reducing the number of vertices by simplification

15.7.3 Reducing the number of vertices by breaking geometries apart

15.7.4 Clustering

15.8 Summary

Part 4: Using PostGIS with other tools

16 Extending PostGIS with pgRouting and procedural languages

16.1 Solving network routing problems with pgRouting

16.1.1 Installing pgRouting

16.1.2 Extending PostgreSQL with PLs

16.1.3 PL/R

16.1.4 PL/Python

16.1.5 PL/V8 - JavaScript in the Database

16.1.6 Summary

17 Using PostGIS in web applications


Appendix A: Additional resources

A.1 Planet sites

A.2 Open source tools and offerings

A.2.1 Self-contained GIS suites

A.2.2 Open source desktop tools

A.3 Open source extract-transform-load (ETL)

A.4 Places to get free data

Appendix B: Installing, compiling, and upgrading

B.1 Installing PostgreSQL and PostGIS

B.1.1 Using PostgreSQL and PostGIS in Docker

B.1.2 EnterpriseDB one-click installers

B.1.3 Creating a PostGIS database

B.1.4 Upgrading PostGIS

Appendix C: SQL primer

C.1 information_schema

C.2 Querying data with SQL

C.2.1 SELECT, FROM, WHERE, and ORDER BY clauses

C.2.2 Indexes

C.2.3 Aliasing

C.2.4 Why use AS when you don’t need to

C.2.5 Using subselects

C.2.6 JOINs


Appendix D: PostgreSQL features

D.1 What makes PostgreSQL special?

D.1.1 PostgreSQL’s unique features

D.1.2 Basic enterprise features

D.1.3 Advanced enterprise features

D.1.4 More features in PostgreSQL 12, and coming in 13

D.1.5 Useful PostgreSQL resources

D.1.6 Connecting to a PostgreSQL server

D.1.7 Controlling access to data

D.1.8 Backup and restore

D.1.9 Data structures and objects

D.1.10 Writing functions in SQL

D.1.11 Writing functions in PL/pgSQL

D.1.12 Index performance

D.1.13 Computed Columns

What's inside

  • An introduction to spatial databases
  • Geometry, geography, raster, and topology spatial types, functions, and queries
  • Applying PostGIS to real-world problems
  • Extending PostGIS to web and desktop applications
  • Updated for PostGIS 3 and PostgreSQL 12

About the reader

For readers familiar with relational databases and basic SQL. No prior geodata or GIS experience required.

About the authors

Regina Obe and Leo Hsu are database consultants and authors. Regina is a member of the PostGIS core development team and the Project Steering Committee.

placing your order...

Don't refresh or navigate away from the page.
Manning Early Access Program (MEAP) Read chapters as they are written, get the finished eBook as soon as it’s ready, and receive the pBook long before it's in bookstores.
print book $35.99 $59.99 pBook + eBook + liveBook
includes previous edition eBook
Additional shipping charges may apply
PostGIS in Action, Third Edition (print book) added to cart
continue shopping
go to cart

eBook $38.39 $47.99 3 formats + liveBook
includes previous edition eBook
PostGIS in Action, Third Edition (eBook) added to cart
continue shopping
go to cart

Prices displayed in rupees will be charged in USD when you check out.
customers also reading

This book 1-hop 2-hops 3-hops

FREE domestic shipping on three or more pBooks