Regina O. Obe - PostGIS in Action, 2nd Edition

For online information and ordering of this and other Manning books, please visit www.manning.com. The publisher offers discounts on this book when ordered in quantity. For more information, please contact

Special Sales Department Manning Publications Co. 20 Baldwin Road PO Box 761 Shelter Island, NY 11964 Email: orders@manning.com

2015 by Manning Publications Co. All rights reserved.

No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by means electronic, mechanical, photocopying, or otherwise, without prior written permission of the publisher.

Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in the book, and Manning Publications was aware of a trademark claim, the designations have been printed in initial caps or all caps.

Recognizing the importance of preserving what has been written, it is Mannings policy to have the books we publish printed on acid-free paper, and we exert our best efforts to that end. Recognizing also our responsibility to conserve the resources of our planet, Manning books are printed on paper that is at least 15 percent recycled and processed without the use of elemental chlorine.

Manning Publications Co.20 Baldwin RoadPO Box 761Shelter Island, NY 11964

Development editor: Jeff BleielTechnical development editor: Deepak VohraCopyeditors: Benjamin Berg, Andy CarrollProofreader: Katie TennantTechnical proofreader: David PombalTypesetter: Dottie MarsicoCover designer: Marija Tudor

ISBN 9781617291395

Printed in the United States of America

1 2 3 4 5 6 7 8 9 10 EBM 20 19 18 17 16 15

To Dr. Ernest Olagbade Obe (19352012), professor, chief, daddy

All PostGIS users, from novices to experts, will benefit from this book. Dont use PostGIS without it!

Brent Wood, NIWA

An elegant introduction to a difficult domain.

Mark Leslie, LISAsoft Pty Ltd.

Its next to impossible to explain geospatial SQL succinctly, but this book pulls it off!

Andy Saurin, Saurin Solutions

A great guide for the beginner and a great reference for the advanced user.

Bruce Rindahl, Ventura County Watershed Protection District

Required reading for anyone wanting to get the most out of PostGIS.

James Fee, WeoGeo.com

The must-have guide if you aim to do any serious professional PostGIS development.

Paolo Corti, European Forest Fire Information System (EFFIS)

If you dont understand the benefits of PostGIS, this book will give you the Aha! moment you have been waiting for. It is the most comprehensive book of its kind.

Jeff Addison, Southgate Software Ltd.

This is the PostGIS reference Ive always wished for.

Bill Dollins, Zekiah Technologies, Inc.

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 areevery 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 taska 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 wordsnaturally, 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-GISwe dont 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 its 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.

P AUL R AMSEY

C HAIR , P OST GIS P ROJECT S TEERING C OMMITTEE

PostGIS (pronounced post-jis) is a spatial database extender for the PostgreSQL open source relational database management system. Its 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/MMcompliant spatial functions youll find in these other OGC-compliant databases, as well as numerous additional ones that are unique to PostGIS.

Readers coming from other ANSI/ISOcompliant spatial databases, or other relational databases such as those weve mentioned, will feel right at home with PostgreSQL and PostGIS. PostgreSQL is the most ANSI/ISO SQLcompliant 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.