Table of Contents

Copyright

Brief Table of Contents

Table of Contents

Foreword

Preface

Acknowledgments

About this Book

About the Cover Illustration

1. Learning PostGIS

Chapter 1. What is a spatial database?

1.1. Thinking spatially

1.1.1. Introducing the geometry data type

1.2. Modeling

1.2.1. Imagine the possibilities

1.3. Introducing PostgreSQL and PostGIS

1.3.1. PostgreSQL strengths

1.3.2. PostGIS, adding GIS to PostgreSQL

1.3.3. Alternatives to PostgreSQL and PostGIS

1.3.4. What works with PostGIS

1.4. Getting started with PostGIS

1.4.1. Verifying version of PostGIS and PostgreSQL

1.4.2. Creating geometries with PostGIS

1.5. Working with real data

1.5.1. Loading comma-separated data

1.5.2. Spatializing flat file data

1.5.3. Loading data from spatial data sources

1.6. Using spatial queries to analyze data

1.6.1. Proximity queries

1.6.2. Viewing spatial data with OpenJUMP

1.7. Summary

Chapter 2. Geometry types

2.1. Geometry columns in PostGIS

2.1.1. The geometry_columns table

2.1.2. Interacting with the geometry_columns table

2.2. A panoply of geometries

2.2.1. What’s a geometry?

2.2.2. Points

2.2.3. Linestrings

2.2.4. Polygons

2.2.5. Collection geometries

2.2.6. Curved geometries

2.2.7. 3D geometries

2.3. Summary

Chapter 3. Organizing spatial data

3.1. Spatial storage approaches

3.1.1. Heterogeneous geometry columns

3.1.2. Homogeneous geometry columns

3.1.3. Table inheritance

3.2. Modeling a real city

3.2.1. Modeling using a heterogeneous geometry column

3.2.2. Modeling using homogeneous geometry columns

3.2.3. Modeling using inheritance

3.3. Using rules and triggers

3.3.1. Rules versus triggers

3.3.2. Using rules

3.3.3. Using triggers

Redirecting Inserts with Before Triggers

3.4. Summary

Chapter 4. Geometry functions

4.1. Constructors

4.1.1. Creating geometries from well-known text and well-known binary representations

4.1.2. Autocasting in PostgreSQL/PostGIS

4.2. Outputs

4.2.1. Well-known text and well-known binary

4.2.2. Keyhole Markup Language

4.2.3. Geography Markup Language

4.2.4. Geometry JavaScript Object Notation

4.2.5. Scalable Vector Graphics

4.2.6. Geohash

4.2.7. Examples of output functions

4.3. Accessor functions: getters and setters

4.3.1. Getting and setting spatial reference system

4.3.2. Transform to a different spatial reference

4.3.3. Geometry type

4.3.4. Coordinate and geometry dimensions

4.3.5. Geometry validity

4.3.6. Number of points that define a geometry

4.4. Measurement functions

4.4.1. Planar measures for geometry types

4.4.2. Geodetic measurement for geometry types

4.4.3. Measurement with geography type

4.5. Decomposition

4.5.1. Boxes and envelopes

4.5.2. Coordinates

4.5.3. Boundaries

4.5.4. Point marker for a geometry: centroid, point on surface, and nth point

4.5.5. Breaking down multi and collection geometries

4.6. Composition

4.6.1. Making points

4.6.2. Making polygons

4.6.3. Promoting single to multi geometries

4.7. Simplification

4.7.1. Coordinate rounding using ST_SnapToGrid

4.7.2. Simplifying geometries

4.8. Summary

Chapter 5. Relationships between geometries

5.1. Introducing spatial relationship functions

5.2. Intersections

5.2.1. Segmenting linestrings with polygons

5.2.2. Clipping polygons with polygons

5.3. Specific intersection relationships

5.3.1. Interior, exterior, and boundary of a geometry

5.3.2. Contains and Within

5.3.3. Covers and CoveredBy

5.3.4. ContainsProperly

5.3.5. Overlapping geometries

5.3.6. Touching geometries

5.3.7. Crossing geometries

5.3.8. Disjoint geometries

5.4. The remainder: ST_Difference and ST_SymDifference

5.5. Nearest neighbor

5.5.1. Intersects with tolerance

5.5.2. Finding N closest objects

5.5.3. Using SQL Window functions to number results

5.6. Bounding box and geometry comparators

5.6.1. The bounding box

5.6.2. Bounding box and geometry operators

5.7. The many faces of equality

5.7.1. Spatial equality

5.7.2. Geometric equality

5.7.3. Bounding box equality

5.8. Underpinnings of relationship functions

5.8.1. The intersection matrix

5.8.2. Equality and the intersection matrix

5.8.3. Using the intersection matrix with ST_Relate

5.9. Summary

Chapter 6. Spatial reference system considerations

6.1. Spatial reference system: What is it?

6.1.1. The geoid

6.1.2. Ellipsoids

6.1.3. Datum

6.1.4. Coordinate reference system

6.1.5. Projection

6.1.6. Different kinds of projections

6.2. Selecting a spatial reference system to store data

6.2.1. Pros and cons of using EPSG:4326

6.2.2. Geography data type for EPSG:4326

6.2.3. Mapping just for presentation

6.2.4. Covering the globe when distance is a concern

6.3. Determining the spatial reference system of source data

6.3.1. Guessing at a spatial reference system

6.3.2. When the spatial reference system is missing

6.4. Summary

Chapter 7. Working with real data

7.1. Tools for importing/exporting data

7.1.1. PostgreSQL built-in tools

7.1.2. PostGIS packaged tools

7.1.3. OGR2OGR: all-purpose vector data loader

7.1.4. Quantum GIS Shapefile to PostGIS Import Tool

7.1.5. osm2pgsql: OpenStreetMap to PostGIS loader

7.2. Loading data

7.2.1. Getting and extracting compressed files

7.2.2. Using PostGIS and PostgreSQL tools to load data

7.2.3. Loading data with OGR2OGR

7.2.4. Importing OpenStreetMap data with osm2pgsql

7.3. Exporting data from PostGIS

7.3.1. Using pgsql2shp to dispense PostGIS data

7.3.2. Using OGR2OGR to dispense PostGIS data

7.4. Summary

2. Putting PostGIS to Work

Chapter 8. Techniques to solve spatial problems

8.1. Proximity analysis

8.1.1. Check for intersections and measuring distances

8.1.2. Convert to different units of measurement

8.1.3. Measure large distances

8.1.4. Choose spatial reference systems when measuring area

8.2. Data tagging

8.2.1. Techniques for generating dummy data

8.2.2. Tag data to a specific region

8.2.3. Snapping points to closest linestring

8.2.4. Geocoding an address to a point on a street

8.3. Slicing and splicing linestrings

8.3.1. Create linestrings from points

8.3.2. Break linestrings into smaller segments

8.4. Slicing and splicing polygons

8.4.1. Create a single multipolygon from many multipolygon records

8.4.2. Tessellate areas

8.4.3. Create equal-area slices

8.5. Translating, scaling, and rotating geometries

8.5.1. Move a geometry along X, Y, Z

8.5.2. Increase and decrease size of geometry

8.5.3. Rotate a geometry

8.6. Summary

Chapter 9. Performance tuning

9.1. The query planner

9.1.1. Planner statistics

9.2. Using explain to diagnose problems

9.2.1. Text explain versus pgAdmin III graphical explain

9.2.2. The plan without an index

9.3. Indexes and keys

9.3.1. The plan with a spatial index scan

9.3.2. Options for defining indexes

9.4. Common SQL patterns and how they affect performance

9.4.1. SELECT subselects

9.4.2. FROM subselects and basic common table expressions

9.4.3. Window functions and self-joins

9.5. System and function settings

9.5.1. Key system variables that affect plan strategy

9.5.2. Function-specific settings

9.6. Optimizing geometries

9.6.1. Fixing invalid geometries

9.6.2. Reducing number of vertices with simplification

9.6.3. Removing holes

9.6.4. Clustering

9.7. Summary

3. Using PostGIS with other tools

Chapter 10. Enhancing SQL with add-ons

10.1. Georeferencing with the TIGER geocoder

10.1.1. Installing the TIGER geocoder

10.1.2. Loading TIGER data

10.1.3. Geocoding and address normalization

10.1.4. Summary

10.2. Solving network routing problems with pgRouting

10.2.1. Installation

10.2.2. Shortest route

10.2.3. Traveling salesperson problem

10.2.4. Summary

10.3. Extending PostgreSQL power with PLs

10.3.1. Basic installation of PLs

10.3.2. What can you do with a non-native PL

10.4. Graphing and accessing spatial analysis libraries with PL/R

10.4.1. Getting started with PL/R

10.4.2. Saving datasets and plotting

10.4.3. Using R packages in PL/R

10.4.4. Quick primer on rgdal

10.4.5. Getting PostGIS geometries into R spatial objects

10.4.6. Outputting plots as binaries

10.5. PL/Python

10.5.1. Installing PL/Python

10.5.2. Our first PL/Python function

10.5.3. Using Python packages

10.5.4. Geocoding with PL/Python

10.6. Summary

Chapter 11. Using PostGIS in web applications

11.1. GIS and the web

11.1.1. Limitations of conventional web technologies

11.1.2. Mapping servers

11.1.3. Mapping clients

11.1.4. Proprietary services

11.2. Using MapServer

11.2.1. Installing MapServer

11.2.2. Creating WMS and WFS services

11.2.3. Calling a mapping service using a reverse proxy

11.3. Using GeoServer

11.3.1. Installing GeoServer

11.3.2. Setting up PostGIS workspaces

11.3.3. Accessing PostGIS Layers via GeoServer WMS/WFS

11.4. Basics of OpenLayers and GeoExt

11.4.1. Using OpenLayers

11.4.2. Enhancing OpenLayers with GeoExt

11.5. Displaying data with server-side web scripting

11.5.1. Using PostGIS output functions with PHP

11.5.2. Displaying data in Google Earth

11.5.3. Loading custom layers with GeoExt

11.5.4. Proximity queries with PostGIS geography

11.6. Summary

Chapter 12. Using PostGIS in a desktop environment

12.1. At a glance

12.1.1. Capsule review

12.1.2. Spatial database support

12.1.3. Format support

12.1.4. Web services supported

12.2. OpenJUMP Workbench

12.2.1. Feature summary

12.2.2. Register data source

12.2.3. Rendering PostGIS geometry data

12.2.4. Exporting data

12.2.5. Summary

12.3. Quantum GIS

12.3.1. Feature summary

12.3.2. Adding a PostGIS connection

12.3.3. Viewing and filtering PostGIS data

12.3.4. Connecting with other spatial databases

12.3.5. Loading other vector and raster layers

12.3.6. Exporting data

12.3.7. Summary

12.4. uDig

12.4.1. Feature summary

12.4.2. Connecting to PostGIS and other spatial databases

12.4.3. Viewing and filtering PostGIS data

12.4.4. Exporting data

12.4.5. Summary

12.5. gvSIG

12.5.1. Feature summary

12.5.2. Adding a PostGIS layer to a view

12.5.3. Exporting data

12.5.4. Connecting to other spatial databases

12.6. Summary

Chapter 13. PostGIS raster

13.1. What is PostGIS raster?

13.1.1. What is raster data and how is it different from vector data?

13.1.2. Why analyze raster data?

13.1.3. Getting started with raster support in PostGIS

13.2. Storing and loading raster data

13.2.1. Options for storage

13.2.2. Using a loader to load data

13.3. Raster maintenance tables and functions

13.3.1. raster_columns metadata table

13.3.2. AddRasterColumn function

13.3.3. Other management functions

13.4. Commonly used functions

13.4.1. Common accessors

13.4.2. Georeferencing functions

13.5. Combining raster processing with vector processing

13.5.1. Pixel value getters and setters

13.5.2. Intersects and Intersections

13.5.3. Adding bands

13.5.4. Adding additional attributes to raster records

13.6. Exporting raster data into other raster formats

13.6.1. Gdal_translate basics to convert to other formats

13.6.2. Using gdalwarp to transform from one spatial ref to another

13.7. Viewing raster data with MapServer

13.8. The future of PostGIS raster support

13.8.1. Input/output functionality

13.8.2. Open source viewing tools

13.8.3. Database raster functions

13.9. Summary

Appendix A. Additional resources

PostGIS-focused tutorials and sites

Getting-started tutorials

Important GIS sites

Noteworthy PostGIS blogs and sites

Noteworthy R, PL/R sites, and newsgroups

pgRouting installation and examples

PL/Python installation and examples

Raster-related information

Open source tools and offerings

Installers and self-contained suites that include/work with PostGIS

Free open source desktop GIS

Extract Transform Load (ETL)

Proprietary tools that support PostGIS

Places to get free vector data

All geographic regions

North America

Other countries and continents

Regional

Sample data for training

Spatial reference systems resources

Appendix B. Installing, compiling, and upgrading

Installing PostgreSQL and PostGIS

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

Installing on Linux server (Red Hat EL, CentOS) using YUM

Mac OS X–specific installers

Other available binaries and distros

Compiling and installing from PostGIS source

Creating a PostGIS database

Creating template_postgis under PostGIS 1.3.x

Creating template_postgis under PostGIS 1.4,1.5+

Creating a new spatially enabled database

Spatially enabling an existing PostgreSQL database

Upgrading an existing install

Upgrading database from 1.3.x to 1.3.x+

Upgrading database from 1.3.x to 1.4.x or 1.3.x to 1.5.x

Hard upgrades

Appendix C. SQL primer

Information_schema

Querying data with Structured Query Language

SELECT, FROM, WHERE, and ORDER BY clauses

JOINs

Sets

Using SQL aggregates

Window functions and window aggregates

UPDATE, INSERT, and DELETE

Updates

INSERTs

DELETEs

Appendix D. PostgreSQL features

Useful PostgreSQL resources

General

Performance

PostgreSQL-specific tools

Connecting to a PostgreSQL server

Core configuration files

Launching psql

Launching pgAdmin III

Connection difficulties

Enabling advanced administration for pgAdmin III

Controlling access to data

Connection rules

Users and groups (roles)

Rights management

Backup and restore

Backup

Restore

Setting up automated jobs for backup

Data structures and objects

PostgreSQL objects

Built-in data types

Anatomy of a database function

Defining custom data types

Creating tables and views

Writing functions in SQL

When to use SQL functions

Creating an SQL function

Creating rules

Creating aggregate functions

Writing functions in PL/PgSQL

When to use PL/PgSQL functions

Creating a PL/PgSQL function

Creating triggers

Performance

Index

Summary

Index

List of Figures

List of Tables

List of Listings