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 so as to maximize equipment utilization and minimize fuel consumption? Where should the next fire station should be built so as to minimize the response time?
People ask these questions and others like them every day all over this planet. Answering these questions require a mechanism capable of thinking in two or more dimensions. Historically, a Desktop GIS application was used to formulate an answer for each question. This method—though completely functional—is incapable of answering many questions at once. In addition, this method is typically unable to effectively manage and operate on massive spatial datasets, such as all of the roads of Europe for 2013 in one dataset, or allow tasks to be automated instead of significant pointing and clicking.
Once scalability, support for large datasets, and a direct input mechanism are required or desired, most users explore using a spatial database. There are several spatial database software available, some proprietary and others open source. PostGIS is an open source spatial database software and is 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 of the data together, questions such as "What is the rank of all of 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.
PostGIS Cookbook uses a problem-solving approach to help you acquire a solid understanding of PostGIS. Hopefully, 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.
Chapter 1, Moving Data In and Out of PostGIS, covers the processes available for importing and exporting spatial and nonspatial data to and from PostGIS. These processes include the use of utilities provided by PostGIS and 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.
Chapter 3, 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.
Chapter 4, 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.
Chapter 5, 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.
Chapter 6, Working with pgRouting, introduces the pgRouting extension that brings graph traversal and analysis capabilities to PostGIS. The recipes in this chapter answer the 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, covers tools and techniques used to process and analyze multi-dimensional spatial data in PostGIS, including LiDAR-sourced point cloud. 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.
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 nonspatial data and maximize the performance of various PostGIS operations.
Chapter 11, Using Desktop Clients, shows how spatial data in PostGIS can be consumed and manipulated by various open source desktop GIS applications. Several applications are discussed so as to highlight the different approaches to interact with spatial data and help you find the right tool for the task.
Before going further with this book, you will want to install the latest versions of PostgreSQL and PostGIS (9.3 and 2.1, respectively). You may also want to install pgAdmin (1.18), if you prefer a graphical SQL tool. For most computing environments (Windows, Linux, OSX), installers and packages include all of the 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.
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 those who are just starting out with PostGIS or even spatial datasets, this book is structured to help readers 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.
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, and user input are shown as follows: "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
New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "clicking the Next button moves you to the next screen".
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]> and mention the book title via the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors and our ability to bring you valuable content.
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.