PostGIS in Action

Regina Obe and Leo Hsu are database consultants. They are co-authors of the O'Reilly book, PostgreSQL: Up and Running. Regina is a member of the PostGIS core development team and the Project Steering Committee. They are hosts of BostonGIS.com and PostgresOnLine.com.

foreword
preface
acknowledgments
about this book
Part 1 Introduction to PostGIS

Chapter 1 What is a spatial database?
Thinking spatially
Introducing PostGIS
Spatial data types
Hello real world
Summary
Chapter 2 Spatial data types
Type modifiers
Geometry
Geography
Raster
Summary
Chapter 3 Spatial reference system considerations
Spatial reference systems: what are they?
Selecting a spatial reference system for storing data
Determining the spatial reference system of source data
Summary
Chapter 4 Working with real data
General utilities
Importing and exporting shapefiles
Importing and exporting vector data with ogr2ogr
Importing OpenStreetMap data with osm2pgsql
Importing and exporting raster data
Summary
Chapter 5 Using PostGIS on the desktop
Desktop viewing tools at a glance
OpenJUMP workbench
QGIS
uDig
gvSIG
Summary
Chapter 6 Geometry and geography functions
Output functions
Constructor functions
Accessor and setter functions
Measurement functions
Decomposition functions
Composition functions
Simplification functions
Summary
Chapter 7 Raster functions
Raster terminology
Raster constructors
Raster output functions
Raster accessors and setters
Georeferencing functions
Reclassing functions
Polygonizing functions
Summary
Chapter 8 PostGIS TIGER geocoder
Installing the PostGIS TIGER geocoder
Loading TIGER data
Normalizing addresses
Geocoding
Reverse geocoding
Summary
Chapter 9 Geometry relationships
Bounding box and geometry comparators
Relating two geometries
The faces of equality: geometry
Underpinnings of relationship functions
Summary

Part 2 Putting PostGIS to work

Chapter 10 Proximity analysis
Nearest neighbor searches
Using KNN with geography types
Geotagging
Summary
Chapter 11 Geometry and geography processing
Using spatial aggregate functions
Clipping, splitting, tessellating
Breaking linestrings into smaller segments
Translating, scaling, and rotating geometries
Using geometry functions to manipulate and create geographies
Summary
Chapter 12 Raster processing
Loading and preparing data
Forming larger rasters using spatial aggregate functions
Working with bands
Tiling rasters
Raster and geometry intersections
Raster statistics
Map algebra
Summary
Chapter 13 Building and using topologies
What topology is
Using topologies
Topology of Victoria, BC
Fixing topogeometry issues by editing topology primitives
Inserting and editing large data sets
Simplifying with topology in mind
Topology validation and summary functions
Summary
Chapter 14 Organizing spatial data
Spatial storage approaches
Modeling a real city
Making auto-updateable views
Using rules and triggers
Summary
Chapter 15 Query performance tuning
The query planner
Planner statistics
Using explain to diagnose problems
Planner and indexes
Common SQL patterns and how they affect plans
System and function settings
Optimizing spatial data
Summary

Part 3 Using PostGIS with other tools

Chapter 16 Extending PostGIS with pgRouting and procedural languages
Solving network routing problems with pgRouting
Extending PostgreSQL with PLs
PL/R
PL/Python
PL/V8, CoffeeScript, and LiveScript
Summary
Chapter 17 Using PostGIS in web applications
Limitations of conventional web technologies
Mapping servers
Mapping clients
Using MapServer
Using GeoServer
Basics of OpenLayers and Leaflet
Displaying data with PostGIS queries and web scripting
Summary

appendix A Additional resources
appendix B Installing, compiling, and upgrading
appendix C SQL primer
appendix D PostgreSQL features
index

PostGIS (pronounced post-jis) is a spatial database extender for the PostgreSQL open source relational database management system. It’s the most powerful open source spatial database engine. It adds to PostgreSQL several spatial data types and over 300 functions for working with these spatial types. It does for PostgreSQL what Oracle Spatial and Oracle Locator do for Oracle, what IBM DB2 Spatial Extender and IBM Informix Spatial DataBlade do for DB2 and Informix, and what the geometry and geography types packaged in Microsoft SQL Server 2008+ do for SQL Server. PostGIS supports many of the OGC/ISO SQL/MM–compliant spatial functions you’ll find in these other OGC-compliant databases, as well as numerous additional ones that are unique to PostGIS. Readers coming from other ANSI/ISO–compliant spatial databases, or other relational databases such as those we’ve mentioned, will feel right at home with PostgreSQL and PostGIS. PostgreSQL is the most ANSI/ISO SQL–compliant database management system around. In a similar vein, PostGIS supports many of the industry-standard OGC/ISO SQL/MM spatial database functions, types, and operations. The main raison d’être of this book is to provide a companion volume to the official PostGIS documentation—to serve as a guidebook for navigating through the hundreds of functions offered by PostGIS. We wanted to create a book that would catalog many of the common spatial problems we’ve come across and various strategies for solving them with PostGIS. Above and beyond our primary mission, we hope to lay the foundation for thinking spatially. We hope that you’ll be able to adapt our numerous examples and recipes to your own field of endeavor, and perhaps even spawn creative scions of your own.

As children, we were probably all told at one time or another that “we are what we eat,” as a reminder that our diet is integral to our health and quality of life. In the modern world, with location-aware smartphones in our pockets, GPS units in our vehicles, and the internet addresses of our computers geocoded, it has also become true that “who we are is where we are”—every individual is now a mobile sensor, generating a ceaseless flow of location-encoded data as they move about the planet. To manage and tame that flow of data, and the parallel flow of data opened up by economical satellite imaging and crowdsourced mapping, we need a tool equal to the task—a tool that can persistently store the data, efficiently access it, and powerfully analyze it. We need a spatial database, like PostGIS. Prior to the advent of spatial databases, computer analysis of location and mapping data was done with geographic information systems (GIS) running on desktop workstations. When it was first released in 2001, the project name was just a simple play on words—naturally, a spatial extension of the PostgreSQL database would be named PostGIS. But the name has come to have further significance as the project has matured. Each year, new functions have been added for data analysis, and each year users have pressed those functions further and further, doing the kinds of work that in earlier years would have required a specialized GIS workstation. PostGIS is actually creating a world that is post-GIS—we don’t need GIS software to do GIS work anymore. A spatial database suffices. In March of 2002, not even one year after the first release of PostGIS, I asked on the user mailing list for examples of how people were using PostGIS. In her first post to the list, Regina Obe answered this way: We use it here [city of Boston] for proximity analysis. Part of our department is in charge of distributing foreclosed property to developers, etc., to build houses, businesses, etc. We use PostGIS to list properties by proximity ... so that if a developer wants to develop on a piece of land that is, say, X in size, they will be able to get a better sense of whether it can be done. Even at that early date in the project, Regina Obe was already testing the capabilities of PostGIS and creating clever analyses. Since the first edition of PostGIS in Action in 2011, PostGIS has itself remained very much in action, adding new features for raster analysis, 3D, and more. And the world has kept on moving, too. Only a decade ago, when PostGIS was brand new, the idea that almost every person would have a GPS unit (a phone) in their pocket was pretty crazy, and now it’s commonplace. The features of PostGIS for managing location are now being used widely by developers who only a few years ago had never heard of spatial data. Within the next few years, satellite and aerial imagery will move into the mass market, as drone systems and new low-cost satellite systems become affordable. The raster-management capabilities of PostGIS are now there for the next generation of developers to work with. Enjoy this book and enjoy the insights it provides in putting location data to work. Regina and Leo have distilled a huge body of information into a concise guide that is truly one of a kind. Paul Ramsey Chair, PostGIS Project Steering Committee