PostGIS Cookbook E-Book

Paolo Corti

• Herausgeber: Packt Publishing
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 516

Veröffentlichungsjahr: 2018

Beschreibung

PostGIS is a spatial database that integrates the advanced storage and analysis of vector and raster data, and is remarkably flexible and powerful. PostGIS provides support for geographic objects to the PostgreSQL object-relational database and is currently the most popular open source spatial databases.

If you want to explore the complete range of PostGIS techniques and expose related extensions, then this book is for you.

This book is a comprehensive guide to PostGIS tools and concepts which are required to manage, manipulate, and analyze spatial data in PostGIS. It covers key spatial data manipulation tasks, explaining not only how each task is performed, but also why. It provides practical guidance allowing you to safely take advantage of the advanced technology in PostGIS in order to simplify your spatial database administration tasks. Furthermore, you will learn to take advantage of basic and advanced vector, raster, and routing approaches along with the concepts of data maintenance, optimization, and performance, and will help you to integrate these into a large ecosystem of desktop and web tools.

By the end, you will be armed with all the tools and instructions you need to both manage the spatial database system and make better decisions as your project's requirements evolve.

Leseprobe

PostGIS CookbookSecond 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 MathewAcquisition Editors: Nitin Dasan, Shriram ShekharContent Development Editor: Nikhil BorkarTechnical Editor: Subhalaxmi NadarCopy Editor: Safis EditingProject Coordinator: Ulhas KambaliProofreader: Safis EditingIndexer: Mariammal ChettiyarGraphics: Tania DuttaProduction 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

Title Page

Copyright and Credits

PostGIS Cookbook Second Edition

Packt Upsell

Why subscribe?

PacktPub.com

Contributors

About the authors

Packt is searching for authors like you

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the example code files

Download the color images

Conventions used

Sections

Getting ready

How to do it…

How it works…

There's more…

See also

Get in touch

Reviews

Moving Data In and Out of PostGIS

Introduction

Importing nonspatial tabular data (CSV) using PostGIS functions

Getting ready

How to do it...

How it works...

Importing nonspatial tabular data (CSV) using GDAL

Getting ready

How to do it...

How it works...

Importing shapefiles with shp2pgsql

How to do it...

How it works...

There's more...

Importing and exporting data with the ogr2ogr GDAL command

How to do it...

How it works...

See also

Handling batch importing and exporting of datasets

Getting ready

How to do it...

How it works...

Exporting data to a shapefile with the pgsql2shp PostGIS command

How to do it...

How it works...

Importing OpenStreetMap data with the osm2pgsql command

Getting ready

How to do it...

How it works...

Importing raster data with the raster2pgsql PostGIS command

Getting ready

How to do it...

How it works...

Importing multiple rasters at a time

Getting ready

How to do it...

How it works...

Exporting rasters with the gdal_translate and gdalwarp GDAL commands

Getting ready

How to do it...

How it works...

See also

Structures That Work

Introduction

Using geospatial views

Getting ready

How to do it...

How it works...

There's more...

See also

Using triggers to populate the geometry column

Getting ready

How to do it...

There's more...

Extending further...

See also

Structuring spatial data with table inheritance

Getting ready

How to do it...

How it works...

See also

Extending inheritance – table partitioning

Getting ready

How to do it...

How it works...

See also

Normalizing imports

Getting ready

How to do it...

How it works...

There's more...

Normalizing internal overlays

Getting ready

How to do it...

How it works...

There's more...

Using polygon overlays for proportional census estimates

Getting ready

How to do it...

How it works...

Working with Vector Data – The Basics

Introduction

Working with GPS data

Getting ready

How to do it...

How it works...

Fixing invalid geometries

Getting ready

How to do it...

How it works...

GIS analysis with spatial joins

Getting ready

How to do it...

How it works...

Simplifying geometries

How to do it...

How it works...

Measuring distances

Getting ready

How to do it...

How it works...

Merging polygons using a common attribute

Getting ready

How to do it...

How it works...

Computing intersections

Getting ready

How to do it...

How it works...

Clipping geometries to deploy data

Getting ready

How to do it...

How it works...

Simplifying geometries with PostGIS topology

Getting ready

How to do it...

How it works...

Working with Vector Data – Advanced Recipes

Introduction

Improving proximity filtering with KNN

Getting ready

How to do it...

How it works...

See also

Improving proximity filtering with KNN – advanced

Getting ready

How to do it...

How it works...

See also

Rotating geometries

Getting ready

How to do it...

How it works...

See also

Improving ST_Polygonize

Getting ready

How to do it...

See also

Translating, scaling, and rotating geometries – advanced

Getting ready

How to do it...

How it works...

See also

Detailed building footprints from LiDAR

Getting ready

How to do it...

How it works...

Creating a fixed number of clusters from a set of points

Getting ready

How to do it...

Calculating Voronoi diagrams

Getting ready

How to do it...

Working with Raster Data

Introduction

Getting and loading rasters

Getting ready

How to do it...

How it works...

Working with basic raster information and analysis

Getting ready

How to do it...

How it works...

Performing simple map-algebra operations

Getting ready

How to do it...

How it works...

Combining geometries with rasters for analysis

Getting ready

How to do it...

How it works...

Converting between rasters and geometries

Getting ready

How to do it...

How it works...

Processing and loading rasters with GDAL VRT

Getting ready

How to do it...

How it works...

Warping and resampling rasters

Getting ready

How to do it...

How it works...

Performing advanced map-algebra operations

Getting ready

How to do it...

How it works...

Executing DEM operations

Getting ready

How to do it...

How it works...

Sharing and visualizing rasters through SQL

Getting ready

How to do it...

How it works...

Working with pgRouting

Introduction

Startup – Dijkstra routing

Getting ready

How to do it...

Loading data from OpenStreetMap and finding the shortest path using A*

Getting ready

How to do it...

How it works...

Calculating the driving distance/service area

Getting ready

How to do it...

See also

Calculating the driving distance with demographics

Getting ready

How to do it...

Extracting the centerlines of polygons

Getting ready

How to do it...

There's more...

Into the Nth Dimension

Introduction

Importing LiDAR data

Getting ready

How to do it...

See also

Performing 3D queries on a LiDAR point cloud

How to do it...

Constructing and serving buildings 2.5D

Getting ready

How to do it...

Using ST_Extrude to extrude building footprints

How to do it...

Creating arbitrary 3D objects for PostGIS

Getting ready

How to do it...

Exporting models as X3D for the web

Getting ready

How to do it...

There's more...

Reconstructing Unmanned Aerial Vehicle (UAV) image footprints with PostGIS 3D

Getting started

How to do it...

UAV photogrammetry in PostGIS – point cloud

Getting ready

How to do it...

UAV photogrammetry in PostGIS – DSM creation

Getting ready

How to do it...

PostGIS Programming

Introduction

Writing PostGIS vector data with Psycopg

Getting ready

How to do it...

How it works...

Writing PostGIS vector data with OGR Python bindings

Getting ready

How to do it...

How it works...

Writing PostGIS functions with PL/Python

Getting ready

How to do it...

How it works...

Geocoding and reverse geocoding using the GeoNames datasets

Getting ready

How to do it...

How it works...

Geocoding using the OSM datasets with trigrams

Getting ready

How to do it...

How it works...

Geocoding with geopy and PL/Python

Getting ready

How to do it...

How it works...

Importing NetCDF datasets with Python and GDAL

Getting ready

How to do it...

How it works...

PostGIS and the Web

Introduction

Creating WMS and WFS services with MapServer

Getting ready

How to do it...

How it works...

See also

Creating WMS and WFS services with GeoServer

Getting ready

How to do it...

How it works...

See also

Creating a WMS Time service with MapServer

Getting ready

How to do it...

How it works...

Consuming WMS services with OpenLayers

Getting ready

How to do it...

How it works..

Consuming WMS services with Leaflet

How to do it...

How it works...

Consuming WFS-T services with OpenLayers

Getting ready

How to do it...

How it works...

Developing web applications with GeoDjango – part 1

Getting ready

How to do it...

How it works...

Developing web applications with GeoDjango – part 2

Getting ready

How to do it...

How it works...

Developing a web GPX viewer with Mapbox

How to do it...

How it works...

Maintenance, Optimization, and Performance Tuning

Introduction

Organizing the database

Getting ready

How to do it...

How it works...

Setting up the correct data privilege mechanism

Getting ready

How to do it...

How it works...

Backing up the database

Getting ready

How to do it...

How it works...

Using indexes

Getting ready

How to do it...

How it works...

Clustering for efficiency

Getting ready

How to do it...

How it works...

Optimizing SQL queries

Getting ready

How to do it...

How it works...

Migrating a PostGIS database to a different server

Getting ready

How to do it...

How it works...

Replicating a PostGIS database with streaming replication

Getting ready

How to do it...

How it works...

Geospatial sharding

Getting ready

How to do it...

How it works...

Paralellizing in PosgtreSQL

Getting ready

How to do it...

How it works...

Using Desktop Clients

Introduction

Adding PostGIS layers – QGIS

Getting ready

How to do it...

How it works...

Using the Database Manager plugin – QGIS

Getting ready

How to do it...

How it works...

Adding PostGIS layers – OpenJUMP GIS

Getting ready

How to do it...

How it works...

Running database queries – OpenJUMP GIS

Getting ready

How to do it...

How it works...

Adding PostGIS layers – gvSIG

Getting ready

How to do it...

How it works...

Adding PostGIS layers – uDig

How to do it...

How it works...

Introduction to Location Privacy Protection Mechanisms

Introduction

Definition of Location Privacy Protection Mechanisms – LPPMs

Classifying LPPMs

Adding noise to protect location data

Getting ready

How to do it...

How it works...

Creating redundancy in geographical query results

Getting ready

How to do it...

How it works...

References

Other Books You May Enjoy

Leave a review - let other readers know what you think

Preface

How close is the nearest hospital from my children's school? Where were the property crimes in my city for the last three months? What is the shortest route from my home to my office? What route should I prescribe for my company's delivery truck to maximize equipment utilization and minimize fuel consumption? Where should the next fire station be built to minimize response times?

People ask these questions, and others like them, every day all over this planet. Answering these questions requires a mechanism capable of thinking in two or more dimensions. Historically, desktop GIS applications were the only ones capable of answering these questions. This method—though completely functional—is not viable for the average person; most people do not need all the functionalities that these applications can offer, or they do not know how to use them. In addition, more and more location-based services offer the specific features that people use and are accessible even from their smartphones. Clearly, the massification of these services requires the support of a robust backend platform to process a large number of geographical operations.

Since scalability, support for large datasets, and a direct input mechanism are required or desired, most developers have opted to adopt spatial databases as their support platform. There are several spatial database software available, some proprietary and others open source. PostGIS is an open source spatial database software available, and probably the most accessible of all spatial database software.

PostGIS runs as an extension to provide spatial capabilities to PostgreSQL databases. In this capacity, PostGIS permits the inclusion of spatial data alongside data typically found in a database. By having all the data together, questions such as "What is the rank of all the police stations, after taking into account the distance for each response time?" are possible. New or enhanced capabilities are possible by building upon the core functions provided by PostGIS and the inherent extensibility of PostgreSQL. Furthermore, this book also includes an invitation to include location privacy protection mechanisms in new GIS applications and in location-based services so that users feel respected and not necessarily at risk for sharing their information, especially information as sensitive as their whereabouts.

PostGIS Cookbook, Second Edition uses a problem-solving approach to help you acquire a solid understanding of PostGIS. It is hoped that this book provides answers to some common spatial questions and gives you the inspiration and confidence to use and enhance PostGIS in finding solutions to challenging spatial problems.

Who this book is for

This book is written for those who are looking for the best method to solve their spatial problems using PostGIS. These problems can be as simple as finding the nearest restaurant to a specific location, or as complex as finding the shortest and/or most efficient route from point A to point B.

For readers who are just starting out with PostGIS, or even with spatial datasets, this book is structured to help them become comfortable and proficient at running spatial operations in the database. For experienced users, the book provides opportunities to dive into advanced topics such as point clouds, raster map-algebra, and PostGIS programming.

What this book covers

Chapter 1, Moving Data In and Out of PostGIS, covers the processes available for importing and exporting spatial and non-spatial data to and from PostGIS. These processes include the use of utilities provided by PostGIS and by third parties, such as GDAL/OGR.

Chapter 2, Structures That Work, discusses how to organize PostGIS data using mechanisms available through PostgreSQL. These mechanisms are used to normalize potentially unclean and unstructured import data.

Chapter3, Working with Vector Data – The Basics, introduces PostGIS operations commonly done on vectors, known as geometries and geographies in PostGIS. Operations covered include the processing of invalid geometries, determining relationships between geometries, and simplifying complex geometries.

Chapter4, Working with Vector Data – Advanced Recipes, dives into advanced topics for analyzing geometries. You will learn how to make use of KNN filters to increase the performance of proximity queries, create polygons from LiDAR data, and compute Voronoi cells usable in neighborhood analyses.

Chapter5, Working with Raster Data, presents a realistic workflow for operating on rasters in PostGIS. You will learn how to import a raster, modify the raster, conduct analysis on the raster, and export the raster in standard raster formats.

Chapter6, Working with pgRouting, introduces the pgRouting extension, which brings graph traversal and analysis capabilities to PostGIS. The recipes in this chapter answer real-world questions of conditionally navigating from point A to point B and accurately modeling complex routes, such as waterways.

Chapter 7, Into the Nth Dimension, focuses on the tools and techniques used to process and analyze multidimensional spatial data in PostGIS, including LiDAR-sourced point clouds. Topics covered include the loading of point clouds into PostGIS, creating 2.5D and 3D geometries from point clouds, and the application of several photogrammetry principles.

Chapter 8, PostGIS Programming, shows how to use the Python language to write applications that operate on and interact with PostGIS. The applications written include methods to read and write external datasets to and from PostGIS, as well as a basic geocoding engine using OpenStreetMap datasets.

Chapter 9, PostGIS and the Web, presents the use of OGC and REST web services to deliver PostGIS data and services to the web. This chapter discusses providing OGC, WFS, and WMS services with MapServer and GeoServer, and consuming them from clients such as OpenLayers and Leaflet. It then shows how to build a web application with GeoDjango and how to include your PostGIS data in a Mapbox application.

Chapter 10, Maintenance, Optimization, and Performance Tuning, takes a step back from PostGIS and focuses on the capabilities of the PostgreSQL database server. By leveraging the tools provided by PostgreSQL, you can ensure the long-term viability of your spatial and non-spatial data, and maximize the performance of various PostGIS operations. In addition, it explores new features such as geospatial sharding and parallelism in PostgreSQL.

Chapter11, Using Desktop Clients, tells you about how spatial data in PostGIS can be consumed and manipulated using various open source desktop GIS applications. Several applications are discussed so as to highlight the different approaches to interacting with spatial data and help you find the right tool for the task.

Chapter12, Introduction to Location Privacy Protection Mechanisms, provides an introductory approximation to the concept of location privacy and presents the implementation of two different location privacy protection mechanisms that can be included in commercial applications to give a basic level of protection to the user's location data.

To get the most out of this book

Before going further into this book, you will want to install latest versions of PostgreSQL and PostGIS (9.6 or 103 and 2.3 or 2.41, respectively). You may also want to install pgAdmin (1.18) if you prefer a graphical SQL tool. For most computing environments (Windows, Linux, macOS X), installers and packages include all required dependencies of PostGIS. The minimum required dependencies for PostGIS are PROJ.4, GEOS, libjson and GDAL. A basic understanding of the SQL language is required to understand and adapt the code found in this book's recipes.

Download the example code files

You can download the example code files for this book from your account at www.packtpub.com. If you purchased this book elsewhere, you can visit www.packtpub.com/support and register to have the files emailed directly to you.

You can download the code files by following these steps:

Log in or register at

www.packtpub.com

.

Select the

SUPPORT

tab.

Click on

Code Downloads & Errata

.

Enter the name of the book in the

Search

box and follow the onscreen instructions.

Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:

WinRAR/7-Zip for Windows

Zipeg/iZip/UnRarX for Mac

7-Zip/PeaZip for Linux

The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/PostGIS-Cookbook-Second-Edition. In case there's an update to the code, it will be updated on the existing GitHub repository.

We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!

Download the color images

We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: https://www.packtpub.com/sites/default/files/downloads/PostGISCookbookSecondEdition_ColorImages.pdf.

Conventions used

There are a number of text conventions used throughout this book.

CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: "We will import the firenews.csv file that stores a series of web news collected from various RSS feeds."

A block of code is set as follows:

SELECT ROUND(SUM(chp02.proportional_sum(ST_Transform(a.geom,3734), b.geom, b.pop))) AS population FROM nc_walkzone AS a, census_viewpolygon as b WHERE ST_Intersects(ST_Transform(a.geom, 3734), b.geom) GROUP BY a.id;

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

SELECT ROUND(SUM(chp02.proportional_sum(ST_Transform(a.geom,3734), b.geom, b.pop))) AS population FROM nc_walkzone AS a, census_viewpolygon as b WHERE

ST_Intersects(ST_Transform(a.geom, 3734), b.geom)

GROUP BY a.id;

Any command-line input or output is written as follows:

> raster2pgsql -s 4322 -t 100x100 -F -I -C -Y C:\postgis_cookbook\data\chap5\PRISM\us_tmin_2012.*.asc chap5.prism | psql -d postgis_cookbook

Bold: Indicates a new term, an important word, or words that you see onscreen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: "Clicking the Next button moves you to the next screen."

Warnings or important notes appear like this.

Tips and tricks appear like this.

Sections

In this book, you will find several headings that appear frequently (Getting ready, How to do it..., How it works..., There's more..., and See also).

To give clear instructions on how to complete a recipe, use these sections as follows:

Getting ready

This section tells you what to expect in the recipe and describes how to set up any software or any preliminary settings required for the recipe.

How to do it…

This section contains the steps required to follow the recipe.

How it works…

This section usually consists of a detailed explanation of what happened in the previous section.

There's more…

This section consists of additional information about the recipe in order to make you more knowledgeable about the recipe.

See also

This section provides helpful links to other useful information for the recipe.

Get in touch

Feedback from our readers is always welcome.

General feedback: Email [email protected] and mention the book title in the subject of your message. If you have questions about any aspect of this book, please email us at [email protected].

Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.

Piracy: If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.

If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.

Reviews

Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!

For more information about Packt, please visit packtpub.com.

Moving Data In and Out of PostGIS

In this chapter, we will cover:

Importing nonspatial tabular data (CSV) using PostGIS functions

Importing nonspatial tabular data (CSV) using GDAL

Importing shapefiles with shp2pgsql

Importing and exporting data with the ogr2ogr GDAL command

Handling batch importing and exporting of datasets

Exporting data to a shapefile with the pgsql2shp PostGIS command

Importing OpenStreetMap data with the osm2pgsql command

Importing raster data with the raster2pgsql PostGIS command

Importing multiple rasters at a time

Exporting rasters with the gdal_translate and gdalwarp GDAL commands

Introduction

PostGIS is an open source extension for the PostgreSQL database that allows support for geographic objects; throughout this book you will find recipes that will guide you step by step to explore the different functionalities it offers.

The purpose of the book is to become a useful tool for understanding the capabilities of PostGIS and how to apply them in no time. Each recipe presents a preparation stage, in order to organize your workspace with everything you may need, then the set of steps that you need to perform in order to achieve the main goal of the task, that includes all the external commands and SQL sentences you will need (which have been tested in Linux, Mac and Windows environments), and finally a small summary of the recipe. This book will go over a large set of common tasks in geographical information systems and location-based services, which makes it a must-have book in your technical library.

In this first chapter, we will show you a set of recipes covering different tools and methodologies to import and export geographic data from the PostGIS spatial database, given that pretty much every common action to perform in a GIS starts with inserting or exporting geospatial data.

Importing nonspatial tabular data (CSV) using PostGIS functions

There are a couple of alternative approaches to importing a Comma Separated Values (CSV) file, which stores attributes and geometries in PostGIS. In this recipe, we will use the approach of importing such a file using the PostgreSQL COPY command and a couple of PostGIS functions.

How it works...

This recipe showed you how to load nonspatial tabular data (in CSV format) in PostGIS using the COPY PostgreSQL command.

After creating the table and copying the CSV file rows to the PostgreSQL table, you updated the geometric column using one of the geometry constructor functions that PostGIS provides (ST_MakePoint and ST_PointFromText for bi-dimensional points).

These geometry constructors (in this case, ST_MakePoint and ST_PointFromText) must always provide the spatial reference system identifier (SRID) together with the point coordinates to define the point geometry.

Each geometric field added in any table in the database is tracked with a record in the geometry_columns PostGIS metadata view. In the previous PostGIS version (< 2.0), the geometry_fields view was a table and needed to be manually updated, possibly with the convenient AddGeometryColumn function.

For the same reason, to maintain the updated geometry_columns view when dropping a geometry column or removing a spatial table in the previous PostGIS versions, there were the DropGeometryColumn and DropGeometryTable functions. With PostGIS 2.0 and newer, you don't need to use these functions any more, but you can safely remove the column or the table with the standard ALTER TABLE, DROP COLUMN, and DROP TABLE SQL commands.

In the last step of the recipe, you have created a spatial index on the table to improve performance. Please be aware that as in the case of alphanumerical database fields, indexes improve performances only when reading data using the SELECT command. In this case, you are making a number of updates on the table (INSERT, UPDATE, and DELETE); depending on the scenario, it could be less time consuming to drop and recreate the index after the updates.

Importing nonspatial tabular data (CSV) using GDAL

As an alternative approach to the previous recipe, you will import a CSV file to PostGIS using the ogr2ogr GDAL command and the GDAL OGR virtual format. The Geospatial Data Abstraction Library (GDAL) is a translator library for raster geospatial data formats. OGR is the related library that provides similar capabilities for vector data formats.

This time, as an extra step, you will import only a part of the features in the file and you will reproject them to a different spatial reference system.

How it works...

As mentioned in the GDAL documentation:

"OGR Virtual Format is a driver that transforms features read from other drivers based on criteria specified in an XML control file."

GDAL supports the reading and writing of nonspatial tabular data stored as a CSV file, but we need to use a virtual format to derive the geometry of the layers from attribute columns in the CSV file (the longitude and latitude coordinates for each point). For this purpose, you need to at least specify in the driver the path to the CSV file (the SrcDataSource element), the geometry type (the GeometryType element), the spatial reference definition for the layer (the LayerSRS element), and the way the driver can derive the geometric information (the GeometryField element).

There are many other options and reasons for using OGR virtual formats; if you are interested in developing a better understanding, please refer to the GDAL documentation available at http://www.gdal.org/drv_vrt.html.

After a virtual format is correctly created, the original flat nonspatial dataset is spatially supported by GDAL and software-based on GDAL. This is the reason why we can manipulate these files with GDAL commands such as ogrinfo and ogr2ogr, and with desktop GIS software such as QGIS.

Once we have verified that GDAL can correctly read the features from the virtual driver, we can easily import them in PostGIS using the popular ogr2ogr command-line utility. The ogr2ogr command has a plethora of options, so refer to its documentation at http://www.gdal.org/ogr2ogr.html for a more in-depth discussion.

In this recipe, you have just seen some of these options, such as:

-where

: It is used to export just a selection of the original feature class

-t_srs

: It is used to reproject the data to a different spatial reference system

-lco layer creation

: It is used to provide the schema where we would want to store the table (without it, the new spatial table would be created in the

public

schema) and the name of the geometry field in the output layer

Importing shapefiles with shp2pgsql

If you need to import a shapefile in PostGIS, you have at least a couple of options such as the ogr2ogr GDAL command, as you have seen previously, or the shp2pgsql PostGIS command.

In this recipe, you will load a shapefile in the database using the shp2pgsql command, analyze it with the ogrinfo command, and display it in QGIS desktop software.

How it works...

The PostGIS command, shp2pgsql, allows the user to import a shapefile in the PostGIS database. Basically, it generates a PostgreSQL dump file that can be used to load data by running it from within PostgreSQL.

The SQL file will be generally composed of the following sections:

The

CREATE TABLE

section (if the

-a

option is not selected, in which case, the table should already exist in the database)

The

INSERT INTO

section (one

INSERT

statement for each feature to be imported from the shapefile)

The

CREATE INDEX

section (if the

-I

option is selected)

Unlike ogr2ogr, there is no way to make spatial or attribute selections (-spat, -where ogr2ogr options) for features in the shapefile to import. On the other hand, with the shp2pgsql command, it is possible to import the m coordinate of the features too (ogr2ogr only supports x, y, and z at the time of writing).

To get a complete list of the shp2pgsql command options and their meanings, just type the command name in the shell (or in the command prompt, if you are on Windows) and check the output.

There's more...

There are GUI tools to manage data in and out of PostGIS, generally integrated into GIS desktop software such as QGIS. In the last chapter of this book, we will take a look at the most popular one.

Importing and exporting data with the ogr2ogr GDAL command

In this recipe, you will use the popular ogr2ogr GDAL command for importing and exporting vector data from PostGIS.

Firstly, you will import a shapefile in PostGIS using the most significant options of the ogr2ogr command. Then, still using ogr2ogr, you will export the results of a spatial query performed in PostGIS to a couple of GDAL-supported vector formats.

How to do it...

The steps you need to follow to complete this recipe are as follows:

Unzip the

wborders.zip

archive to your working directory. You can find this archive in the book's dataset.

Import the world countries shapefile (

wborders.shp

) in PostGIS using the

ogr2ogr

command. Using some of the options from 

ogr2ogr

, you will import only the features from

SUBREGION=2

(Africa), and the

ISO2

and

NAME

attributes, and rename the feature class to

africa_countries

:

$ ogr2ogr -f PostgreSQL -sql "SELECT ISO2, NAME AS country_name FROM wborders WHERE REGION=2" -nlt MULTIPOLYGON PG:"dbname='postgis_cookbook' user='me' password='mypassword'" -nln africa_countries -lco SCHEMA=chp01 -lco GEOMETRY_NAME=the_geom wborders.shp

Check if the shapefile was correctly imported in PostGIS, querying the spatial table in the database or displaying it in a desktop GIS.

Query PostGIS to get a list of the 100 active hotspots with the highest brightness temperature (the

bright_t31

field) from the

global_24h

table created in the previous recipe:

postgis_cookbook=# SELECT

ST_AsText(the_geom) AS the_geom, bright_t31

FROM chp01.global_24h

ORDER BY bright_t31 DESC LIMIT 100;

The output of the preceding command is as follows:

You want to figure out in which African countries these hotspots are located. For this purpose, you can do a spatial join with the

africa_countries

table produced in the previous step:

postgis_cookbook=# SELECT

ST_AsText(f.the_geom) AS the_geom, f.bright_t31, ac.iso2, ac.country_name

FROM chp01.global_24h as f

JOIN chp01.africa_countries as ac

ON ST_Contains(ac.the_geom, ST_Transform(f.the_geom, 4326))

ORDER BY f.bright_t31 DESC

LIMIT 100;

The output of the preceding command is as follows:

You will now export the result of this query to a vector format supported by GDAL, such as GeoJSON, in the WGS 84 spatial reference using ogr2ogr:

$ ogr2ogr -f GeoJSON -t_srs EPSG:4326 warmest_hs.geojson PG:"dbname='postgis_cookbook' user='me' password='mypassword'" -sql " SELECT f.the_geom as the_geom, f.bright_t31, ac.iso2, ac.country_name FROM chp01.global_24h as f JOIN chp01.africa_countries as ac ON ST_Contains(ac.the_geom, ST_Transform(f.the_geom, 4326)) ORDER BY f.bright_t31 DESC LIMIT 100"

Open the GeoJSON file and inspect it with your favorite desktop GIS. The following screenshot shows you how it looks with QGIS:

Export the previous query to a CSV file. In this case, you have to indicate how the geometric information must be stored in the file; this is done using the

-lco GEOMETRY

option:

$ ogr2ogr -t_srs EPSG:4326 -f CSV -lco GEOMETRY=AS_XY -lco SEPARATOR=TAB warmest_hs.csv PG:"dbname='postgis_cookbook' user='me' password='mypassword'" -sql " SELECT f.the_geom, f.bright_t31, ac.iso2, ac.country_name FROM chp01.global_24h as f JOIN chp01.africa_countries as ac ON ST_Contains(ac.the_geom, ST_Transform(f.the_geom, 4326)) ORDER BY f.bright_t31 DESC LIMIT 100"

How it works...

GDAL is an open source library that comes together with several command-line utilities, which let the user translate and process raster and vector geodatasets into a plethora of formats. In the case of vector datasets, there is a GDAL sublibrary for managing vector datasets named OGR (therefore, when talking about vector datasets in the context of GDAL, we can also use the expression OGR dataset).

When you are working with an OGR dataset, two of the most popular OGR commands are ogrinfo, which lists many kinds of information from an OGR dataset, and ogr2ogr, which converts the OGR dataset from one format to another.

It is possible to retrieve a list of the supported OGR vector formats using the -formats option on any OGR commands, for example, with ogr2ogr:

$ ogr2ogr --formats

The output of the preceding command is as follows:

Note that some formats are read-only, while others are read/write.

PostGIS is one of the supported read/write OGR formats, so it is possible to use the OGR API or any OGR commands (such as ogrinfo and ogr2ogr) to manipulate its datasets.

The ogr2ogr command has many options and parameters; in this recipe, you have seen some of the most notable ones such as -f to define the output format, -t_srs to reproject/transform the dataset, and -sql to define an (eventually spatial) query in the input OGR dataset.

When using ogrinfo and ogr2ogr together with the desired option and parameters, you have to define the datasets. When specifying a PostGIS dataset, you need a connection string that is defined as follows:

PG:"dbname='postgis_cookbook' user='me' password='mypassword'"

See also

You can find more information about the ogrinfo and ogr2ogr commands on the GDAL website available at http://www.gdal.org.

If you need more information about the PostGIS driver, you should check its related documentation page available at http://www.gdal.org/drv_pg.html.

Handling batch importing and exporting of datasets

In many GIS workflows, there is a typical scenario where subsets of a PostGIS table must be deployed to external users in a filesystem format (most typically, shapefiles or a spatialite database). Often, there is also the reverse process, where datasets received from different users have to be uploaded to the PostGIS database.

In this recipe, we will simulate both of these data flows. You will first create the data flow for processing the shapefiles out of PostGIS, and then the reverse data flow for uploading the shapefiles.

You will do it using the power of bash scripting and the ogr2ogr command.

Getting ready

If you didn't follow all the other recipes, be sure to import the hotspots (Global_24h.csv) and the countries dataset (countries.shp) in PostGIS. The following is how to do it with ogr2ogr (you should import both the datasets in their original SRID, 4326, to make spatial operations faster):

Import in PostGIS the

Global_24h.csv

file, using the

global_24.vrt

virtual driver you created in a previous recipe:

$ ogr2ogr -f PostgreSQL PG:"dbname='postgis_cookbook' user='me' password='mypassword'" -lco SCHEMA=chp01 global_24h.vrt -lco OVERWRITE=YES -lco GEOMETRY_NAME=the_geom -nln hotspots

Import the countries shapefile using

ogr2ogr

:

$ ogr2ogr -f PostgreSQL -sql "SELECT ISO2, NAME AS country_name FROM wborders" -nlt MULTIPOLYGON PG:"dbname='postgis_cookbook' user='me' password='mypassword'" -nln countries -lco SCHEMA=chp01 -lco OVERWRITE=YES -lco GEOMETRY_NAME=the_geom wborders.shp

If you already imported the hotspots dataset using the 3857 SRID, you can use the PostGIS 2.0 method that allows the user to modify the geometry type column of an existing spatial table. You can update the SRID definition for the hotspots table in this way thanks to the support of typmod on geometry objects:postgis_cookbook=# ALTER TABLE chp01.hotspotsALTER COLUMN the_geomSET DATA TYPE geometry(Point, 4326)USING ST_Transform(the_geom, 4326);