PostGIS in Action, Second Edition
A huge body of information distilled into a concise guide.
GET MORE WITH MANNING
An eBook copy of the previous edition, PostGIS in Action (First Edition), is included at no additional cost. It will be automatically added to your Manning Bookshelf within 24 hours of purchase.
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 detailed table of contents
praise for the first edition of PostGIS in Action
foreword
preface
acknowledgments
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.5.2. Transformation-recommended 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
C.2.7. LATERALs
C.3. UPDATE, INSERT, and DELETE
C.3.1. UPDATE
C.3.2. INSERT
C.3.3. DELETE
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
index
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.