PostGIS Cookbook: Over 80 Task-Based Recipes to Store, Organize, Manipulate and Analyze Spatial Data in a PostGIS Database PDF
Preview PostGIS Cookbook: Over 80 Task-Based Recipes to Store, Organize, Manipulate and Analyze Spatial Data in a PostGIS Database
PostGIS 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
The list of books you might like
