PostGIS in Action, Second Edition

PostGIS in Action, Second Edition

by Regina O. Obe, Leo S. Hsu
Released April 2015
Publisher(s): Manning Publications
ISBN: 9781617291395

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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.

Table of contents

  1. PostGIS in Action, Second Edition
    1. Regina O. Obe and Leo S. Hsu
  2. Copyright
    1. Dedication
  3. Brief Table of Contents
  4. Table of Contents
  5. Praise for the First Edition of PostGIS in Action
  6. Foreword
  7. Preface
  8. Acknowledgments
  9. About this Book
    1. Who should read this book?
      1. GIS practitioners and programmers
      2. DB practitioners
      3. Scientists, researchers, educators, and engineers
    2. Roadmap
      1. Part 1: Learning PostGIS
      2. Part 2: Putting PostGIS to work
      3. Part 3: Using PostGIS with other tools
      4. Appendixes
    3. Code and other conventions
    4. Code downloads
    5. Author Online
    6. About the title
    7. About the cover illustration
  10. Part 1. Introduction to PostGIS
  11. Chapter 1. What is a spatial database?
    1. 1.1. Thinking spatially
    2. 1.2. Introducing PostGIS
      1. 1.2.1. Why PostGIS
      2. 1.2.2. Alternatives to PostGIS
      3. 1.2.3. Installing PostGIS
    3. 1.3. Spatial data types
      1. 1.3.1. Geometry type
      2. 1.3.2. Geography type
      3. 1.3.3. Raster type
      4. 1.3.4. Topology type
    4. 1.4. Hello real world
      1. 1.4.1. Digesting the problem
      2. 1.4.2. Modeling
      3. 1.4.3. Loading data
      4. 1.4.4. Writing the query
      5. 1.4.5. Viewing spatial data with OpenJump
    5. 1.5. Summary
  12. Chapter 2. Spatial data types
    1. 2.1. Type modifiers
      1. 2.1.1. Subtype type modifiers
      2. 2.1.2. Spatial reference identifier
    2. 2.2. Geometry
      1. 2.2.1. Points
      2. 2.2.2. Linestrings
      3. 2.2.3. Polygons
      4. 2.2.4. Collection geometries
      5. 2.2.5. The M coordinate
      6. 2.2.6. The Z coordinate
      7. 2.2.7. Polyhedral surfaces and TINs
      8. 2.2.8. Curved geometries
      9. 2.2.9. Spatial catalog for geometry
      10. 2.2.10. Managing geometry columns
    3. 2.3. Geography
      1. 2.3.1. Differences between geography and geometry
      2. 2.3.2. Spatial catalogs for geography
    4. 2.4. Raster
      1. 2.4.1. Properties of rasters
      2. 2.4.2. Creating rasters
      3. 2.4.3. Spatial catalog for rasters
    5. 2.5. Summary
  13. Chapter 3. Spatial reference system considerations
    1. 3.1. Spatial reference systems: what are they?
      1. 3.1.1. Geoids
      2. 3.1.2. Ellipsoids
      3. 3.1.3. Datum
      4. 3.1.4. Coordinate reference system
      5. 3.1.5. Spatial reference system essentials
      6. 3.1.6. Projections
    2. 3.2. Selecting a spatial reference system for storing data
      1. 3.2.1. Pros and cons of using EPSG:4326
      2. 3.2.2. Geography data type for EPSG:4326
      3. 3.2.3. Mapping just for presentation
      4. 3.2.4. Covering the globe when distance is a concern
    3. 3.3. Determining the spatial reference system of source data
      1. 3.3.1. Guessing at a spatial reference system
      2. 3.3.2. When the spatial reference system is missing from spatial_ref_sys table
    4. 3.4. Summary
  14. Chapter 4. Working with real data
    1. 4.1. General utilities
      1. 4.1.1. PostgreSQL built-in tools
      2. 4.1.2. Downloading files
      3. 4.1.3. Extracting files
    2. 4.2. Importing and exporting shapefiles
      1. 4.2.1. Importing with shp2pgsql
      2. 4.2.2. Importing and exporting with shp2pgsql-gui
      3. 4.2.3. Exporting with pgsql2shp
    3. 4.3. Importing and exporting vector data with ogr2ogr
      1. 4.3.1. Environment variables
      2. 4.3.2. Ogrinfo
      3. 4.3.3. Importing with ogr2ogr
      4. 4.3.4. Exporting with ogr2ogr
    4. 4.4. Importing OpenStreetMap data with osm2pgsql
      1. 4.4.1. Getting OSM data
      2. 4.4.2. Loading OSM-formatted data with osm2pgsql
    5. 4.5. Importing and exporting raster data
      1. 4.5.1. Using gdalinfo to inspect rasters
      2. 4.5.2. Importing raster data with raster2pgsql
      3. 4.5.3. Gdal_translate and gdalwarp
      4. 4.5.4. Using PostgreSQL functions to output raster data
    6. 4.6. Summary
  15. Chapter 5. Using PostGIS on the desktop
    1. 5.1. Desktop viewing tools at a glance
      1. 5.1.1. Capsule reviews
      2. 5.1.2. Spatial database support
      3. 5.1.3. Format support
      4. 5.1.4. Web services supported
    2. 5.2. OpenJUMP workbench
      1. 5.2.1. OpenJUMP feature summary
      2. 5.2.2. PostGIS support
      3. 5.2.3. Register data source
      4. 5.2.4. Rendering PostGIS geometries
      5. 5.2.5. Exporting data
    3. 5.3. QGIS
      1. 5.3.1. Installing QGIS
      2. 5.3.2. Using QGIS with PostGIS
      3. 5.3.3. Importing and exporting layers
    4. 5.4. uDig
      1. 5.4.1. Using uDig with PostGIS
      2. 5.4.2. Connecting to PostGIS
      3. 5.4.3. Viewing and filtering PostGIS data
    5. 5.5. gvSIG
      1. 5.5.1. Using gvSIG with PostGIS
      2. 5.5.2. Exporting data
    6. 5.6. Summary
  16. Chapter 6. Geometry and geography functions
    1. 6.1. Output functions
      1. 6.1.1. Well-known binary (WKB) and well-known text (WKT)
      2. 6.1.2. Keyhole Markup Language (KML)
      3. 6.1.3. Geography Markup Language (GML)
      4. 6.1.4. Geometry JavaScript Object Notation (GeoJSON)
      5. 6.1.5. Scalable Vector Graphics (SVG)
      6. 6.1.6. Extensible 3D Graphics (X3D)
      7. 6.1.7. Examples of output functions
      8. 6.1.8. Geohash
    2. 6.2. Constructor functions
      1. 6.2.1. Creating geometries from text and binary formats
      2. 6.2.2. Creating geographies from text and binary formats
      3. 6.2.3. Using text or binary representations as function arguments
    3. 6.3. Accessor and setter functions
      1. 6.3.1. Spatial reference identifiers
      2. 6.3.2. Transforming geometry to different spatial references
      3. 6.3.3. Using transformation with the geography type
      4. 6.3.4. Geometry type functions
      5. 6.3.5. Geometry and coordinate dimensions
      6. 6.3.6. Retrieving coordinates
      7. 6.3.7. Checking geometry validity
      8. 6.3.8. Number of points that defines a geometry
    4. 6.4. Measurement functions
      1. 6.4.1. Geometry planar measurements
      2. 6.4.2. Geodetic measurements
    5. 6.5. Decomposition functions
      1. 6.5.1. Bounding box of geometries
      2. 6.5.2. Boundaries and converting polygons to linestrings
      3. 6.5.3. Centroid and point on surface
      4. 6.5.4. Returning points defining a geometry
      5. 6.5.5. Decomposing multi-geometries and geometry collections
    6. 6.6. Composition functions
      1. 6.6.1. Making points
      2. 6.6.2. Making polygons
      3. 6.6.3. Promoting single geometries to multi-geometries
    7. 6.7. Simplification functions
      1. 6.7.1. Grid snapping and coordinate rounding
      2. 6.7.2. Simplification
    8. 6.8. Summary
  17. Chapter 7. Raster functions
    1. 7.1. Raster terminology
    2. 7.2. Raster constructors
      1. 7.2.1. Converting geometries to rasters with ST_AsRaster
      2. 7.2.2. Loading rasters with raster2pgsql
      3. 7.2.3. Constructing rasters from scratch: ST_MakeEmptyRaster and ST_AddBand
      4. 7.2.4. Setting pixels: ST_SetValue and ST_SetValues
      5. 7.2.5. Creating rasters from other rasters
      6. 7.2.6. Converting other raster formats with ST_FromGDALRaster
    3. 7.3. Raster output functions
      1. 7.3.1. ST_AsPNG, ST_AsJPEG, and ST_AsTiff
      2. 7.3.2. Output using ST_AsGDALRaster
      3. 7.3.3. Using psql to export rasters
    4. 7.4. Raster accessors and setters
      1. 7.4.1. Basic raster metadata properties
      2. 7.4.2. Pixel statistics
      3. 7.4.3. Pixel value accessors
      4. 7.4.4. Band metadata setters
    5. 7.5. Georeferencing functions
      1. 7.5.1. Metadata setters
      2. 7.5.2. Processing functions
    6. 7.6. Reclassing functions
    7. 7.7. Polygonizing functions
      1. 7.7.1. ST_ConvexHull
      2. 7.7.2. ST_Envelope
      3. 7.7.3. ST_Polygon
      4. 7.7.4. ST_MinConvexHull
    8. 7.8. Summary
  18. Chapter 8. PostGIS TIGER geocoder
    1. 8.1. Installing the PostGIS TIGER geocoder
    2. 8.2. Loading TIGER data
      1. 8.2.1. Configuration tables
      2. 8.2.2. Loading nation and state data
    3. 8.3. Normalizing addresses
      1. 8.3.1. Using normalize_address
      2. 8.3.2. Using the PAGC address normalizer
    4. 8.4. Geocoding
      1. 8.4.1. Geocoding using address text
      2. 8.4.2. Geocoding using normalized addresses
      3. 8.4.3. Batch geocoding
    5. 8.5. Reverse geocoding
    6. 8.6. Summary
  19. Chapter 9. Geometry relationships
    1. 9.1. Bounding box and geometry comparators
      1. 9.1.1. The bounding box
      2. 9.1.2. Bounding box comparators
    2. 9.2. Relating two geometries
      1. 9.2.1. Interior, exterior, and boundary of a geometry
      2. 9.2.2. Intersections
      3. 9.2.3. A house plan model
      4. 9.2.4. Contains and within
      5. 9.2.5. Covers and covered by
      6. 9.2.6. Contains properly
      7. 9.2.7. Overlapping geometries
      8. 9.2.8. Touching geometries
      9. 9.2.9. Crossing geometries
      10. 9.2.10. Disjoint geometries
    3. 9.3. The faces of equality: geometry
      1. 9.3.1. Spatial equality versus geometric equality
      2. 9.3.2. Bounding-box equality
    4. 9.4. Underpinnings of relationship functions
      1. 9.4.1. The intersection matrix
      2. 9.4.2. Using ST_Relate
    5. 9.5. Summary
  20. Part 2. Putting PostGIS to work
  21. Chapter 10. Proximity analysis
    1. 10.1. Nearest neighbor searches
      1. 10.1.1. Which places are within X distance?
      2. 10.1.2. Using ST_DWithin and ST_Distance for N closest results
      3. 10.1.3. Using ST_DWithin and DISTINCT ON to find closest locations
      4. 10.1.4. Intersects with tolerance
      5. 10.1.5. Finding N closest places using KNN distance bounding-box operators
      6. 10.1.6. Combining KNN distance-box operators with ST_Distance
      7. 10.1.7. Using window functions to find closest N places
    2. 10.2. Using KNN with geography types
    3. 10.3. Geotagging
      1. 10.3.1. Tagging data to a specific region
      2. 10.3.2. Linear referencing: snapping points to the closest linestring
    4. 10.4. Summary
  22. Chapter 11. Geometry and geography processing
    1. 11.1. Using spatial aggregate functions
      1. 11.1.1. Creating a multipolygon from many multipolygon records
      2. 11.1.2. Creating linestrings from points
    2. 11.2. Clipping, splitting, tessellating
      1. 11.2.1. Clipping
      2. 11.2.2. Splitting
      3. 11.2.3. Tessellating
    3. 11.3. Breaking linestrings into smaller segments
      1. 11.3.1. Segmentizing linestrings
      2. 11.3.2. Creating two-point linestrings from many-point linestrings
      3. 11.3.3. Breaking linestrings at point junctions
    4. 11.4. Translating, scaling, and rotating geometries
      1. 11.4.1. Translating
      2. 11.4.2. Scaling
      3. 11.4.3. Rotating
    5. 11.5. Using geometry functions to manipulate and create geographies
      1. 11.5.1. Cast-safe functions
      2. 11.5.2. Transformation-recommended functions
    6. 11.6. Summary
  23. Chapter 12. Raster processing
    1. 12.1. Loading and preparing data
    2. 12.2. Forming larger rasters using spatial aggregate functions
      1. 12.2.1. Reconstituting tiled files
      2. 12.2.2. Carving out areas of interest using clipping and unioning
      3. 12.2.3. Using specific expression types with ST_Union
    3. 12.3. Working with bands
      1. 12.3.1. Using ST_AddBand to form multiband rasters from single-band rasters
      2. 12.3.2. Using ST_Band to process a subset of bands
    4. 12.4. Tiling rasters
    5. 12.5. Raster and geometry intersections
      1. 12.5.1. Pixel stats
      2. 12.5.2. Adding a Z coordinate to a 2D linestring using ST_Value
      3. 12.5.3. Converting 2D polygon to 3D polygon
    6. 12.6. Raster statistics
      1. 12.6.1. Extruding pixel values
      2. 12.6.2. Raster statistics functions
    7. 12.7. Map algebra
      1. 12.7.1. Choosing between expression or callback function
      2. 12.7.2. Using a single-band map algebra expression
      3. 12.7.3. Using a single-band map algebra function
      4. 12.7.4. Map algebra with neighborhoods
    8. 12.8. Summary
  24. Chapter 13. Building and using topologies
    1. 13.1. What topology is
    2. 13.2. Using topologies
      1. 13.2.1. Installing the topology extension
      2. 13.2.2. Creating a topology
      3. 13.2.3. The topogeometry type
      4. 13.2.4. Recap of using topologies
    3. 13.3. Topology of Victoria, BC
      1. 13.3.1. Creating the Victoria topology
      2. 13.3.2. Adding primitives to a topology
      3. 13.3.3. Creating topogeometries
    4. 13.4. Fixing topogeometry issues by editing topology primitives
      1. 13.4.1. Removing faces by removing edges
      2. 13.4.2. Checking for shared faces
      3. 13.4.3. Editing topogeometries
    5. 13.5. Inserting and editing large data sets
    6. 13.6. Simplifying with topology in mind
    7. 13.7. Topology validation and summary functions
    8. 13.8. Summary
  25. Chapter 14. Organizing spatial data
    1. 14.1. Spatial storage approaches
      1. 14.1.1. Heterogeneous columns
      2. 14.1.2. Homogeneous columns
      3. 14.1.3. Typmod versus constraints
      4. 14.1.4. Table inheritance
    2. 14.2. Modeling a real city
      1. 14.2.1. Modeling using heterogeneous geometry columns
      2. 14.2.2. Modeling using homogeneous geometry columns
      3. 14.2.3. Modeling using inheritance
    3. 14.3. Making auto-updateable views
    4. 14.4. Using rules and triggers
      1. 14.4.1. Rules versus triggers
      2. 14.4.2. Using rules
      3. 14.4.3. Using triggers
    5. 14.5. Summary
  26. Chapter 15. Query performance tuning
    1. 15.1. The query planner
      1. 15.1.1. Different kinds of spatial queries
      2. 15.1.2. Common table expressions and how they affect plans
    2. 15.2. Planner statistics
    3. 15.3. Using explain to diagnose problems
      1. 15.3.1. Text explain versus pgAdmin graphical explain
      2. 15.3.2. The plan with no index
    4. 15.4. Planner and indexes
      1. 15.4.1. The plan with a spatial index
      2. 15.4.2. Options for defining indexes
    5. 15.5. Common SQL patterns and how they affect plans
      1. 15.5.1. SELECT subqueries
      2. 15.5.2. FROM subqueries and basic CTEs
      3. 15.5.3. Window functions and self-joins
      4. 15.5.4. Laterals
    6. 15.6. System and function settings
      1. 15.6.1. Key system variables that affect plan strategy
      2. 15.6.2. Function-specific settings
    7. 15.7. Optimizing spatial data
      1. 15.7.1. Fixing invalid geometries
      2. 15.7.2. Reducing the number of vertices by simplification
      3. 15.7.3. Clustering
    8. 15.8. Summary
  27. Part 3. Using PostGIS with other tools
  28. Chapter 16. Extending PostGIS with pgRouting and procedural languages
    1. 16.1. Solving network routing problems with pgRouting
      1. 16.1.1. Installing pgRouting
      2. 16.1.2. Basic navigation
      3. 16.1.3. Traveling salesman
    2. 16.2. Extending PostgreSQL with PLs
      1. 16.2.1. Basic installation of PLs
      2. 16.2.2. What you can do with PLs
    3. 16.3. PL/R
      1. 16.3.1. Getting started with PL/R
      2. 16.3.2. Saving data sets and plotting
      3. 16.3.3. Using R packages in PL/R
      4. 16.3.4. Converting geometries into R spatial objects and plotting spatial objects
      5. 16.3.5. Outputting plots as binaries
    4. 16.4. PL/Python
      1. 16.4.1. Installing PL/Python
      2. 16.4.2. Writing a PL/Python function
      3. 16.4.3. Using Python packages
      4. 16.4.4. Geocoding example
    5. 16.5. PL/V8, CoffeeScript, and LiveScript
      1. 16.5.1. Installing PL/V8
      2. 16.5.2. Using other JavaScript libraries and functions in PL/V8
      3. 16.5.3. Using PL/V8 to write map algebra functions
    6. 16.6. Summary
  29. Chapter 17. Using PostGIS in web applications
    1. 17.1. Limitations of conventional web technologies
    2. 17.2. Mapping servers
      1. 17.2.1. Platform considerations
      2. 17.2.2. OGC web service support
      3. 17.2.3. Supported data sources
    3. 17.3. Mapping clients
      1. 17.3.1. Proprietary services
    4. 17.4. Using MapServer
      1. 17.4.1. Installing MapServer
      2. 17.4.2. Creating WMS and WFS services
      3. 17.4.3. Calling a mapping service using a reverse proxy
    5. 17.5. Using GeoServer
      1. 17.5.1. Installing GeoServer
      2. 17.5.2. Setting up PostGIS workspaces
      3. 17.5.3. Accessing PostGIS layers via GeoServer WMS/WFS
    6. 17.6. Basics of OpenLayers and Leaflet
      1. 17.6.1. OpenLayers primer
      2. 17.6.2. Leaflet primer
      3. 17.6.3. Synopsis of the three different APIs
    7. 17.7. Displaying data with PostGIS queries and web scripting
      1. 17.7.1. Displaying PostGIS rasters using raster queries
      2. 17.7.2. Using PostGIS and PostgreSQL geometry output functions
    8. 17.8. Summary
  30. Appendix A. Additional resources
    1. A.1. Planet sites
    2. A.2. Open source tools and offerings
      1. A.2.1. Self-contained GIS suites that include PostGIS
      2. A.2.2. Open source desktop tools
      3. A.2.3. Open source extract-transform-load (ETL)
    3. A.3. Proprietary vendors that support PostGIS
    4. A.4. Places to get free data
  31. Appendix B. Installing, compiling, and upgrading
    1. B.1. Installing PostgreSQL and PostGIS
      1. B.1.1. Desktop Linux, Windows, Mac OS X using one-click installers
      2. B.1.2. Installing on a Linux server (Red Hat EL, CentOS) using YUM
      3. B.1.3. Mac OS X–specific installers
      4. B.1.4. PostgreSQL APT repository
      5. B.1.5. Other available binaries and distros
      6. B.1.6. Compiling and installing from PostGIS source
    2. B.2. Creating a PostGIS database
      1. B.2.1. Spatializing a PostgreSQL 9.0 or lower database or PostGIS without raster
    3. B.3. Upgrading PostGIS
      1. B.3.1. PostGIS soft upgrade using extensions
      2. B.3.2. Upgrading PostGIS from 1.X to 2.X
  32. Appendix C. SQL primer
    1. C.1. information_schema
    2. C.2. Querying data with SQL
      1. C.2.1. SELECT, FROM, WHERE, and ORDER BY clauses
      2. C.2.2. Using subselects
      3. C.2.3. JOINs
      4. C.2.4. Sets
      5. C.2.5. Using SQL aggregates
      6. C.2.6. Window functions and window aggregates
      7. C.2.7. LATERALs
    3. C.3. UPDATE, INSERT, and DELETE
      1. C.3.1. UPDATE
      2. C.3.2. INSERT
      3. C.3.3. DELETE
  33. Appendix D. PostgreSQL features
    1. D.1. What makes PostgreSQL special?
      1. D.1.1. PostgreSQL’s unique features
      2. D.1.2. Basic enterprise features
      3. D.1.3. Advanced enterprise features
      4. D.1.4. More features in PostgreSQL 9.3, 9.4, and coming in 9.5
    2. D.2. Useful PostgreSQL resources
      1. D.2.1. General resources
      2. D.2.2. PostgreSQL-specific tools
    3. D.3. Connecting to a PostgreSQL server
      1. D.3.1. Core configuration files
      2. D.3.2. Launching psql
      3. D.3.3. Launching pgAdmin III
      4. D.3.4. Connection difficulties
      5. D.3.5. Enabling advanced administration for pgAdmin III
    4. D.4. Controlling access to data
      1. D.4.1. Connection rules
      2. D.4.2. Users and groups (roles)
      3. D.4.3. Rights management
    5. D.5. Backup and restore
      1. D.5.1. Backup
      2. D.5.2. Restore
      3. D.5.3. Setting up automated jobs for backup
    6. D.6. Data structures and objects
      1. D.6.1. PostgreSQL objects
      2. D.6.2. Built-in data types
      3. D.6.3. Anatomy of a database function
      4. D.6.4. Defining custom data types
      5. D.6.5. Creating tables and views
    7. D.7. Writing functions in SQL
      1. D.7.1. When to use SQL functions
      2. D.7.2. Creating an SQL function
      3. D.7.3. Rules
      4. D.7.4. Creating aggregate functions
    8. D.8. Writing functions in PL/pgSQL
      1. D.8.1. When to use PL/pgSQL functions
      2. D.8.2. Creating a PL/pgSQL function
      3. D.8.3. Creating triggers
    9. D.9. Index performance
      1. D.9.1. B-tree index gotchas
      2. D.9.2. Functional index gotchas
  34. Index
    1. SYMBOL
    2. A
    3. B
    4. C
    5. D
    6. E
    7. F
    8. G
    9. H
    10. I
    11. J
    12. K
    13. L
    14. M
    15. N
    16. O
    17. P
    18. Q
    19. R
    20. S
    21. T
    22. U
    23. V
    24. W
    25. X
    26. Y
  35. List of Figures
  36. List of Tables
  37. List of Listings

Product information

  • Title: PostGIS in Action, Second Edition
  • Author(s): Regina O. Obe, Leo S. Hsu
  • Release date: April 2015
  • Publisher(s): Manning Publications
  • ISBN: 9781617291395