PostGIS in Action, Second Edition
Regina O. Obe and Leo S. Hsu
Foreword by Paul Ramsey
  • April 2015
  • ISBN 9781617291395
  • 600 pages
  • printed in black & white

A huge body of information distilled into a concise guide.

From the Foreword by Paul Ramsey, Chair PostGIS Steering Committee

PostGIS in Action, Second Edition teaches readers of all levels to write spatial queries that solve real-world problems. It first gives you a background in vector-, raster-, and topology-based GIS and then quickly moves into analyzing, viewing, and mapping data. This second edition covers PostGIS 2.0 and 2.1 series, PostgreSQL 9.1, 9.2, and 9.3 features, and shows you how to integrate with other GIS tools.

Table of Contents show full

praise for the first edition of PostGIS in Action




about this book

Part 1 Introduction to PostGIS

1. What is a spatial database?

1.1. Thinking spatially

1.2. Introducing PostGIS

1.2.1. Why PostGIS

1.2.2. Alternatives to PostGIS

1.2.3. Installing PostGIS

1.3. Spatial data types

1.3.1. Geometry type

1.3.2. Geography type

1.3.3. Raster type

1.3.4. Topology type

1.4. Hello real world

1.4.1. Digesting the problem

1.4.2. Modeling

1.4.3. Loading data

1.4.4. Writing the query

1.4.5. Viewing spatial data with OpenJump

1.5. 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.2.2. Linestrings

2.2.3. Polygons

2.2.4. Collection geometries

2.2.5. The M coordinate

2.2.6. The Z coordinate

2.2.7. Polyhedral surfaces and TINs

2.2.8. Curved geometries

2.2.9. Spatial catalog for geometry

2.2.10. Managing geometry columns

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 system considerations

3.1. Spatial reference systems: what are they?

3.1.1. Geoids

3.1.2. Ellipsoids

3.1.3. Datum

3.1.4. Coordinate reference system

3.1.5. Spatial reference system essentials

3.1.6. Projections

3.2. Selecting a spatial reference system for storing data

3.2.1. Pros and cons of using EPSG:4326

3.2.2. Geography data type for EPSG:4326

3.2.3. Mapping just for presentation

3.2.4. Covering the globe when distance is a concern

3.3. Determining the spatial reference system of source data

3.3.1. Guessing at a spatial reference system

3.3.2. When the spatial reference system is missing from spatial_ref_sys table

3.4. Summary

4. Working with real data

4.1. General utilities

4.1.1. PostgreSQL built-in tools

4.1.2. Downloading files

4.1.3. Extracting files

4.2. Importing and exporting shapefiles

4.2.1. Importing with shp2pgsql

4.2.2. Importing and exporting with shp2pgsql-gui

4.2.3. Exporting with pgsql2shp

4.3. Importing and exporting vector data with ogr2ogr

4.3.1. Environment variables

4.3.2. Ogrinfo

4.3.3. Importing with ogr2ogr

4.3.4. Exporting with ogr2ogr

4.4. Importing OpenStreetMap data with osm2pgsql

4.4.1. Getting OSM data

4.4.2. Loading OSM-formatted data with osm2pgsql

4.5. Importing and exporting raster data

4.5.1. Using gdalinfo to inspect rasters

4.5.2. Importing raster data with raster2pgsql

4.5.3. Gdal_translate and gdalwarp

4.5.4. Using PostgreSQL functions to output raster data

4.6. Summary

5. Using PostGIS on the desktop

5.1. Desktop viewing tools at a glance

5.1.1. Capsule reviews

5.1.2. Spatial database support

5.1.3. Format support

5.1.4. Web services supported

5.2. OpenJUMP workbench

5.2.1. OpenJUMP feature summary

5.2.2. PostGIS support

5.2.3. Register data source

5.2.4. Rendering PostGIS geometries

5.2.5. Exporting data

5.3. QGIS

5.3.1. Installing QGIS

5.3.2. Using QGIS with PostGIS

5.3.3. Importing and exporting layers

5.4. uDig

5.4.1. Using uDig with PostGIS

5.4.2. Connecting to PostGIS

5.4.3. Viewing and filtering PostGIS data

5.5. gvSIG

5.5.1. Using gvSIG with PostGIS

5.5.2. Exporting data

5.6. Summary

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. Extensible 3D Graphics (X3D)

6.1.7. Examples of output functions

6.1.8. Geohash

6.2. Constructor functions

6.2.1. Creating geometries from text and binary formats

6.2.2. Creating geographies from text and binary formats

6.2.3. 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 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. 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. PostGIS TIGER geocoder

8.1. Installing the PostGIS TIGER geocoder

8.2. Loading TIGER data

8.2.1. Configuration tables

8.2.2. Loading nation and state data

8.3. Normalizing addresses

8.3.1. Using normalize_address

8.3.2. Using the PAGC address normalizer

8.4. Geocoding

8.4.1. Geocoding using address text

8.4.2. Geocoding using normalized addresses

8.4.3. Batch geocoding

8.5. Reverse geocoding

8.6. Summary

9. Geometry relationships

9.1. Bounding box and geometry comparators

9.1.1. The bounding box

9.1.2. Bounding box comparators

9.2. Relating two geometries

9.2.1. Interior, exterior, and boundary of a geometry

9.2.2. Intersections

9.2.3. A house plan model

9.2.4. Contains and within

9.2.5. Covers and covered by

9.2.6. Contains properly

9.2.7. Overlapping geometries

9.2.8. Touching geometries

9.2.9. Crossing geometries

9.2.10. Disjoint geometries

9.3. The faces of equality: geometry

9.3.1. Spatial equality versus geometric equality

9.3.2. Bounding-box equality

9.4. Underpinnings of relationship functions

9.4.1. The intersection matrix

9.4.2. Using ST_Relate

9.5. Summary

Part 2 Putting PostGIS to work

10. Proximity analysis

10.1. Nearest neighbor searches

10.1.1. Which places are within X distance?

10.1.2. Using ST_DWithin and ST_Distance for N closest results

10.1.3. Using ST_DWithin and DISTINCT ON to find closest locations

10.1.4. Intersects with tolerance

10.1.5. Finding N closest places using KNN distance bounding-box operators

10.1.6. Combining KNN distance-box operators with ST_Distance

10.1.7. Using window functions to find closest N places

10.2. Using KNN with geography types

10.3. Geotagging

10.3.1. Tagging data to a specific region

10.3.2. Linear referencing: snapping points to the closest linestring

10.4. 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.2. Forming larger rasters using spatial aggregate functions

12.2.1. Reconstituting tiled files

12.2.2. Carving out areas of interest using clipping and unioning

12.2.3. Using specific expression types with ST_Union

12.3. Working with bands

12.3.1. Using ST_AddBand to form multiband rasters from single-band rasters

12.3.2. Using ST_Band to process a subset of bands

12.4. Tiling rasters

12.5. Raster and geometry intersections

12.5.1. Pixel stats

12.5.2. Adding a Z coordinate to a 2D linestring using ST_Value

12.5.3. Converting 2D polygon to 3D polygon

12.6. Raster statistics

12.6.1. Extruding pixel values

12.6.2. Raster statistics functions

12.7. Map algebra

12.7.1. Choosing between expression or callback function

12.7.2. Using a single-band map algebra expression

12.7.3. Using a single-band map algebra function

12.7.4. Map algebra with neighborhoods

12.8. 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.1.1. Heterogeneous columns

14.1.2. Homogeneous columns

14.1.3. Typmod versus constraints

14.1.4. Table inheritance

14.2. Modeling a real city

14.2.1. Modeling using heterogeneous geometry columns

14.2.2. Modeling using homogeneous geometry columns

14.2.3. Modeling using inheritance

14.3. Making auto-updateable views

14.4. Using rules and triggers

14.4.1. Rules versus triggers

14.4.2. Using rules

14.4.3. Using triggers

14.5. 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 versus 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. Options for defining indexes

15.5. Common SQL patterns and how they affect plans

15.5.1. SELECT subqueries

15.5.2. FROM subqueries and basic CTEs

15.5.3. Window functions and self-joins

15.5.4. Laterals

15.6. System and function settings

15.6.1. Key system variables that affect plan strategy

15.6.2. Function-specific settings

15.7. Optimizing spatial data

15.7.1. Fixing invalid geometries

15.7.2. Reducing the number of vertices by simplification

15.7.3. Clustering

15.8. Summary

Part 3 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. Basic navigation

16.1.3. Traveling salesman

16.2. Extending PostgreSQL with PLs

16.2.1. Basic installation of PLs

16.2.2. What you can do with PLs

16.3. PL/R

16.3.1. Getting started with PL/R

16.3.2. Saving data sets and plotting

16.3.3. Using R packages in PL/R

16.3.4. Converting geometries into R spatial objects and plotting spatial objects

16.3.5. Outputting plots as binaries

16.4. PL/Python

16.4.1. Installing PL/Python

16.4.2. Writing a PL/Python function

16.4.3. Using Python packages

16.4.4. Geocoding example

16.5. PL/V8, CoffeeScript, and LiveScript

16.5.1. Installing PL/V8

16.5.2. Using other JavaScript libraries and functions in PL/V8

16.5.3. Using PL/V8 to write map algebra functions

16.6. Summary

17. Using PostGIS in web applications

17.1. Limitations of conventional web technologies

17.2. Mapping servers

17.2.1. Platform considerations

17.2.2. OGC web service support

17.2.3. Supported data sources

17.3. Mapping clients

17.3.1. Proprietary services

17.4. Using MapServer

17.4.1. Installing MapServer

17.4.2. Creating WMS and WFS services

17.4.3. Calling a mapping service using a reverse proxy

17.5. Using GeoServer

17.5.1. Installing GeoServer

17.5.2. Setting up PostGIS workspaces

17.5.3. Accessing PostGIS layers via GeoServer WMS/WFS

17.6. Basics of OpenLayers and Leaflet

17.6.1. OpenLayers primer

17.6.2. Leaflet primer

17.6.3. Synopsis of the three different APIs

17.7. Displaying data with PostGIS queries and web scripting

17.7.1. Displaying PostGIS rasters using raster queries

17.7.2. Using PostGIS and PostgreSQL geometry output functions

17.8. Summary

Appendix A: Additional resources

A.1. Planet sites

A.2. Open source tools and offerings

A.2.1. Self-contained GIS suites that include PostGIS

A.2.2. Open source desktop tools

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

A.3. Proprietary vendors that support PostGIS

A.4. Places to get free data

Appendix B: Installing, compiling, and upgrading

B.1. Installing PostgreSQL and PostGIS

B.1.1. Desktop Linux, Windows, Mac OS X using one-click installers

B.1.2. Installing on a Linux server (Red Hat EL, CentOS) using YUM

B.1.3. Mac OS X-specific installers

B.1.4. PostgreSQL APT repository

B.1.5. Other available binaries and distros

B.1.6. Compiling and installing from PostGIS source

B.2. Creating a PostGIS database

B.2.1. Spatializing a PostgreSQL 9.0 or lower database or PostGIS without raster

B.3. Upgrading PostGIS

B.3.1. PostGIS soft upgrade using extensions

B.3.2. Upgrading PostGIS from 1.X to 2.X

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. Using subselects

C.2.3. JOINs

C.2.4. Sets

C.2.5. Using SQL aggregates

C.2.6. Window functions and window aggregates






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 9.3, 9.4, and coming in 9.5

D.2. Useful PostgreSQL resources

D.2.1. General resources

D.2.2. PostgreSQL-specific tools

D.3. Connecting to a PostgreSQL server

D.3.1. Core configuration files

D.3.2. Launching psql

D.3.3. Launching pgAdmin III

D.3.4. Connection difficulties

D.3.5. Enabling advanced administration for pgAdmin III

D.4. Controlling access to data

D.4.1. Connection rules

D.4.2. Users and groups (roles)

D.4.3. Rights management

D.5. Backup and restore

D.5.1. Backup

D.5.2. Restore

D.5.3. Setting up automated jobs for backup

D.6. Data structures and objects

D.6.1. PostgreSQL objects

D.6.2. Built-in data types

D.6.3. Anatomy of a database function

D.6.4. Defining custom data types

D.6.5. Creating tables and views

D.7. Writing functions in SQL

D.7.1. When to use SQL functions

D.7.2. Creating an SQL function

D.7.3. Rules

D.7.4. Creating aggregate functions

D.8. Writing functions in PL/pgSQL

D.8.1. When to use PL/pgSQL functions

D.8.2. Creating a PL/pgSQL function

D.8.3. Creating triggers

D.9. Index performance

D.9.1. B-tree index gotchas

D.9.2. Functional index gotchas


About the book

Processing data tied to location and topology requires specialized know-how. PostGIS is a free spatial database extender for PostgreSQL, every bit as good as proprietary software. 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.

PostGIS in Action, Second 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.

Familiarity with relational database and GIS concepts is helpful but not required.

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 2.x and PostgreSQL 9.x

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.

combo $49.99 pBook + eBook
eBook $39.99 pdf + ePub + kindle

FREE domestic shipping on three or more pBooks

A more-than-worthy update to "the" definitive book on PostGIS.

Jonathan DeCarlo, Bentley Systems, Inc.

The most comprehensive guide to spatial data on PostgreSQL.

Sergio Arbeo,

Provides the science and the tools needed to create innovative applications for the new digital age.

Guy Ndjeng, NTSP