PostGIS Cookbook: Store, organize, manipulate, and analyze spatial data

576 Pages·2018·30.89 MB·English

by  Mayra Zurbaran et al.

PostGIS Cookbook
Second Edition
Store, organize, manipulate, and analyze spatial data
Mayra Zurbarán
Pedro M. Wightman
Paolo Corti
Stephen Vincent Mather
Thomas J Kraft
Bborie Park
BIRMINGHAM - MUMBAI
PostGIS Cookbook
Second Edition
Copyright © 2018 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 or its dealers and distributors, will be held liable for any damages caused or alleged to
have been 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.
Commissioning Editor: Merint Mathew
Acquisition Editors: Nitin Dasan, Shriram Shekhar
Content Development Editor: Nikhil Borkar
Technical Editor: Subhalaxmi Nadar
Copy Editor: Safis Editing
Project Coordinator: Ulhas Kambali
Proofreader: Safis Editing
Indexer: Mariammal Chettiyar
Graphics: Tania Dutta
Production Coordinator: Shantanu Zagade
First published: January 2014
Second edition: March 2018
Production reference: 1270318
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham
B3 2PB, UK.
ISBN 978-1-78829-932-9
www.packtpub.com
mapt.io
Mapt is an online digital library that gives you full access to over 5,000 books and videos, as
well as industry leading tools to help you plan your personal development and advance
your career. For more information, please visit our website.
Why subscribe?
Spend less time learning and more time coding with practical eBooks and Videos
from over 4,000 industry professionals
Improve your learning with Skill Plans built especially for you
Get a free eBook or video every month
Mapt is fully searchable
Copy and paste, print, and bookmark content
PacktPub.com
Did you know that Packt offers eBook versions of every book published, with PDF and
ePub files 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.
Contributors
About the authors
Mayra Zurbarán is a Colombian geogeek currently pursuing her PhD in geoprivacy. She
has a BS in computer science from Universidad del Norte and is interested in the
intersection of ethical location data management, free and open source software, and GIS.
She is a Pythonista with a marked preference for the PostgreSQL database. Mayra is a
member of the Geomatics and Earth Observation laboratory (GEOlab) at Politecnico di
Milano and is also a contributor to the FOSS community.
I would like to thank my mother for her patience and support, and my father and
grandmother for their love and for teaching me awesome life skills. To Jota, the kindest
person I have met and whose faith in me continues to push me forward. Thanks to Stephen
for passing on the task. To my adviser, coauthor, and friend Pedro, and to Jorge Martinez
for his help.
Pedro M. Wightman is an associate professor at the Systems Engineering Department of
Universidad del Norte, Barranquilla, Colombia. With a PhD in computer science from the
University of South Florida, he's a researcher in location-based information systems,
wireless sensor networks, and virtual and augmented reality, among other fields. Father of
two beautiful and smart girls, he's also a rookie writer of short stories, science fiction fan,
time travel enthusiast, and is worried about how to survive apocalyptic solar flares.
Thank you to my family and all the special people in my life for their love, support, and
sacrifice; without them, this wouldn't have been possible. Thanks to my doctoral student
for all effort and energy in her research and in life. Thanks to Universidad del Norte for
their support in this project. Finally, thanks to my cat for hours of fun (and scratches) that
make me forget bad times when things don't go as expected.
Paolo Corti is an environmental engineer with 20 years of experience in the GIS field,
currently working as a Geospatial Engineer Fellow at the Center for Geographic Analysis at
Harvard University. He is an advocate of open source geospatial technologies and Python,
an OSGeo Charter member, and a member of the pycsw and GeoNode Project Steering
Committees. He is a coauthor of the first edition of this book and the reviewer for the first
and second editions of the Mastering QGIS book by Packt.
Stephen Vincent Mather has worked in the geospatial industry for 15 years, having always
had a flair 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).
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 spends his weekends landscaping and in the outdoors in general.
Bborie Park has been breaking (and subsequently fixing) 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.
Packt is searching for authors like you
If you're interested in becoming an author for Packt, please visit authors.packtpub.com and
apply today. We have worked with thousands of developers and tech professionals, just
like you, to help them share their insight with the global tech community. You can make a
general application, apply for a specific hot topic that we are recruiting an author for, or
submit your own idea.
Table of Contents
Preface 1
Chapter 1: Moving Data In and Out of PostGIS 8
Introduction 8
Importing nonspatial tabular data (CSV) using PostGIS functions 9
Getting ready 9
How to do it... 10
How it works... 14
Importing nonspatial tabular data (CSV) using GDAL 15
Getting ready 15
How to do it... 16
How it works... 20
Importing shapefiles with shp2pgsql 22
How to do it... 22
How it works... 25
There's more... 26
Importing and exporting data with the ogr2ogr GDAL command 26
How to do it... 26
How it works... 30
See also 32
Handling batch importing and exporting of datasets 32
Getting ready 33
How to do it... 33
How it works... 40
Exporting data to a shapefile with the pgsql2shp PostGIS command 41
How to do it... 41
How it works... 43
Importing OpenStreetMap data with the osm2pgsql command 43
Getting ready 43
How to do it... 44
How it works... 47
Importing raster data with the raster2pgsql PostGIS command 47
Getting ready 48
How to do it... 49
How it works... 54
Importing multiple rasters at a time 55
Getting ready 56
How to do it... 56
How it works... 62
Table of Contents
Exporting rasters with the gdal_translate and gdalwarp GDAL
commands 63
Getting ready 63
How to do it... 64
How it works... 65
See also 65
Chapter 2: Structures That Work 66
Introduction 66
Using geospatial views 67
Getting ready 67
How to do it... 68
How it works... 69
There's more... 69
See also 70
Using triggers to populate the geometry column 70
Getting ready 70
How to do it... 71
There's more... 72
Extending further... 73
See also 74
Structuring spatial data with table inheritance 74
Getting ready 75
How to do it... 80
How it works... 82
See also 83
Extending inheritance – table partitioning 83
Getting ready 83
How to do it... 84
How it works... 87
See also 88
Normalizing imports 88
Getting ready 89
How to do it... 90
How it works... 93
There's more... 94
Normalizing internal overlays 95
Getting ready 95
How to do it... 95
How it works... 99
There's more... 99
Using polygon overlays for proportional census estimates 99
Getting ready 99
How to do it... 104
How it works... 105
[ ii ]
Table of Contents
Chapter 3: Working with Vector Data – The Basics 106
Introduction 106
Working with GPS data 107
Getting ready 108
How to do it... 108
How it works... 113
Fixing invalid geometries 115
Getting ready 115
How to do it... 115
How it works... 118
GIS analysis with spatial joins 119
Getting ready 119
How to do it... 122
How it works... 126
Simplifying geometries 127
How to do it... 127
How it works... 134
Measuring distances 135
Getting ready 135
How to do it... 135
How it works... 138
Merging polygons using a common attribute 139
Getting ready 139
How to do it... 140
How it works... 141
Computing intersections 141
Getting ready 142
How to do it... 142
How it works... 145
Clipping geometries to deploy data 146
Getting ready 146
How to do it... 147
How it works... 149
Simplifying geometries with PostGIS topology 150
Getting ready 150
How to do it... 151
How it works... 157
Chapter 4: Working with Vector Data – Advanced Recipes 158
Introduction 158
Improving proximity filtering with KNN 159
Getting ready 160
How to do it... 160
How it works... 163
[ iii ]
Table of Contents
See also 163
Improving proximity filtering with KNN – advanced 164
Getting ready 165
How to do it... 166
How it works... 167
See also 167
Rotating geometries 168
Getting ready 168
How to do it... 168
How it works... 169
See also 170
Improving ST_Polygonize 170
Getting ready 171
How to do it... 171
See also 172
Translating, scaling, and rotating geometries – advanced 172
Getting ready 173
How to do it... 174
How it works... 177
See also 178
Detailed building footprints from LiDAR 178
Getting ready 179
How to do it... 180
How it works... 182
Creating a fixed number of clusters from a set of points 183
Getting ready 183
How to do it... 184
Calculating Voronoi diagrams 185
Getting ready 186
How to do it... 187
Chapter 5: Working with Raster Data 190
Introduction 190
Getting and loading rasters 191
Getting ready 192
How to do it... 192
How it works... 196
Working with basic raster information and analysis 196
Getting ready 196
How to do it... 196
How it works... 201
Performing simple map-algebra operations 202
Getting ready 202
How to do it... 202
How it works... 207
[ iv ]