Table Of ContentPostGIS Cookbook
Over 80 task-based recipes to store, organize, manipulate,
and analyze spatial data in a PostGIS database
Paolo Corti
Thomas J Kraft
Stephen Vincent Mather
Bborie Park
BIRMINGHAM - MUMBAI
PostGIS Cookbook
Copyright © 2014 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or
transmitted in any form or by any means, without the prior written permission of the publisher,
except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the
information presented. However, the information contained in this book is sold without
warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers
and distributors will be held liable for any damages caused or alleged to be caused directly or
indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies
and products mentioned in this book by the appropriate use of capitals. However, Packt
Publishing cannot guarantee the accuracy of this information.
First published: January 2014
Production Reference: 1200114
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-84951-866-6
www.packtpub.com
Cover Image by Charles E. Mather ([email protected])
Credits
Authors Copy Editors
Paolo Corti Shambhavi Pai
Thomas J Kraft Kirti Pai
Stephen Vincent Mather Tanvi Gaitonde
Bborie Park Dipti Kapadia
Reviewers Project Coordinator
Jorge Arévalo Mary Alex
Andrea Flesca
Proofreaders
Lauren Harkins
Acquisition Editor
Mary Jasmine Nadar Stephen Copestake
Lead Technical Editor Indexers
Azharuddin Shaikh Rekha Nair
Tejal Soni
Technical Editors
Vrinda Nitesh Bhosale
Production Coordinator
Rahul Nair Shantanu Zagade
Anita Nayak
Cover Work
Humera Shaikh
Shantanu Zagade
About the Authors
Paolo Corti is based in Rome, Italy. He is an environmental engineer with more than
15 years of experience in the GIS sector. After working with proprietary solutions for some
years, he has proudly switched to open-source technologies and Python for almost a decade.
He has been working as a developer and analyst for organizations such as the EU Joint
Research Center, UN World Food Program, and the Italian Government.
Currently, he is working within the GeoNode project, for which he is a core developer, in the
context of emergency preparedness and response.
He is an OSGeo Charter member and writes a blog on open-source GIS at
http://www.paolocorti.net/.
He is the author of the book's chapters 1, 3, 8, and 9.
I would like to thank the PostGIS Steering Committee and everyone else who
makes PostGIS such a beautiful project.
A special thanks must go to the co-authors of this book: they have been
brilliant mates always ready to give me suggestions and help.
A mention is needed here to some geospatial minds that are great source
of inspiration for me: Paul Ramsey, Sandro Santilli, Frank Warmerdam,and
Even Rouault.
Last, but not least, I would like to thank my wife Renata and my family for
their support and patience.
Thomas J Kraft is currently a Planning Technician at Cleveland Metroparks after beginning
as a GIS intern in 2011.
He graduated with Honors from Cleveland State University in 2012, majoring in Environmental
Science with an emphasis on GIS.
When not in front of a computer, he enjoys his weekends landscaping and the outdoors
in general.
I'd like to thank the co-authors of this book, who are some of the most
knowledgeable and motivated professionals in the ield. It's truly an honor
to have been involved in this process.
I'd like to give special acknowledgements to Stephen Mather (also a
co-author) for introducing me to the world of open-source GIS and my
girlfriend, Sandy, for keeping me on the straight and narrow.
Stephen Vincent Mather has worked in the geospatial industry for 15 years, having always
had a lair for geospatial analyses in general, especially those at the intersection of Geography
and Ecology. His work in open-source geospatial databases started 5 years ago with PostGIS
and he immediately began using PostGIS as an analytic tool, attempting a range of innovative
and sometimes bleeding-edge techniques (although he admittedly prefers the cutting edge).
His geospatial career has spanned a variety of interesting and novel natural-resource projects,
everything from the movement of ice sheets in Antarctica to hiking viewsheds and mobile trail
applications to help park users ind trails, picnic areas, and restrooms.
Stephen is currently the GIS manager for Cleveland Metroparks in Cleveland, Ohio.
He manages a small geospatial shop that specializes in high-end cartography, crating
and generating data, geospatial web development, and analyses for natural-resource
management, largely with open-source software.
Stephen is also a Mennonite technologist, aka a straw-hat hacker, interested in creating fair and
open data and infrastructure for better governance and humanitarian purposes. He is heavily
involved in the Cleveland Civic Hacking movement as he works with the public to help them get
engaged with geospatial data. In his spare time, he builds guitars really, really slowly.
Thanks go out to those who form my geospatial pedigree: Gordon
Longsworth (and his advisor Ian Mcharg), Kevin Czajkowski, Karl Schneider,
and Ken Jezek, as well as to the geospatial minds who inspire me, including
Martin Davis.
A special thanks goes to the blessings that are my two beautiful and
bright children and my wife (who is equally so), all of whom exhibit endless
patience and love. They are three people who both structure my life and ill
its interstitial spaces with the glow of their love.
Bborie Park
has been breaking (and subsequently ixing) computers for most of his life. His
primary interests involve developing end-to-end pipelines for spatial datasets. He is an active
contributor to the PostGIS project and is a member of the PostGIS Steering Committee. He
happily resides with his wife Nicole in the San Francisco Bay Area.
I would like to thank my wife Nicole, who patiently tolerated many hours,
days, and weeks of my working when I should have been relaxing. I would
also like to thank the PostGIS community and Steering Committee for
accepting and providing feedback for my contributions to the project.
About the Reviewers
Jorge Arévalo is a computer engineer from Universidad Autónoma de Madrid, UAM.
He started developing web applications with JS, PHP, and Python. In 2010, he began
collaborating with PostGIS and GDAL projects after participating in GSoC 2009, creating
the PostGIS Raster GDAL driver. He currently works as a freelance Web/GIS developer and
collaborates with the geomati.co group in projects, such as gvSIG CE or QGIS. He also
writes a blog about GIS at http://www.libregis.org.
Andrea Flesca is an Italian electronic engineer working in the software world for Selex ES,
a primary Italian electronic systems and software provider. After extensive experience with
software development and over the past several years, he has dealt with GIS systems and
Enterprise Architectures. He's Technical Head for systems integration.
Andrea loves rock music and knows how to prepare a great tiramisu.
www.PacktPub.com
Support iles, eBooks, discount offers and more
You might want to visit www.PacktPub.com for support iles and downloads related to
your book.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub
iles available? You can upgrade to the eBook version at www.PacktPub.com and as a print
book customer, you are entitled to a discount on the eBook copy. Get in touch with us at
[email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up
for a range of free newsletters and receive exclusive discounts and offers on Packt books
and eBooks.
TM
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book
library. Here, you can access, read and search across Packt's entire library of books.
Why Subscribe?
f Fully searchable across every book published by Packt
f Copy and paste, print and bookmark content
f On demand and accessible via web browser
Free Access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access
PacktLib today and view nine entirely free books. Simply use your login credentials for
immediate access.
Table of Contents
Preface 1
Chapter 1: Moving Data In and Out of PostGIS 7
Introduction 7
Importing nonspatial tabular data (CSV) using PostGIS functions 7
Importing nonspatial tabular data (CSV) using GDAL 12
Importing shapeiles with shp2pgsql 17
Importing and exporting data with the ogr2ogr GDAL command 21
Handling batch importing and exporting of datasets 25
Exporting data to the shapeile with the pgsql2shp PostGIS command 33
Importing OpenStreetMap data with the osm2pgsql command 34
Importing raster data with the raster2pgsql PostGIS command 39
Importing multiple rasters at a time 45
Exporting rasters with the gdal_translate and gdalwarp GDAL commands 51
Chapter 2: Structures that Work 55
Introduction 55
Using geospatial views 56
Using triggers to populate a geometry column 58
Structuring spatial data with table inheritance 62
Extending inheritance – table partitioning 67
Normalizing imports 71
Normalizing internal overlays 76
Using polygon overlays for proportional census estimates 80
