PostGIS Cookbook - Second Edition

PostGIS Cookbook - Second Edition

By : Pedro Wightman, Bborie Park, Stephen Vincent Mather, Thomas Kraft, Mayra Zurbarán

Buy this Book

PostGIS Cookbook - Second Edition

By: Pedro Wightman, Bborie Park, Stephen Vincent Mather, Thomas Kraft, Mayra Zurbarán

Buy this Book

Overview of this book

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.

Title Page

Packt Upsell

Contributors

Preface

Free Chapter

Moving Data In and Out of PostGIS

Introduction

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

Structures That Work

Introduction

Using geospatial views

Using triggers to populate the geometry column

Structuring spatial data with table inheritance

Extending inheritance – table partitioning

Normalizing imports

Normalizing internal overlays

Using polygon overlays for proportional census estimates

Working with Vector Data – The Basics

Introduction

Working with GPS data

Fixing invalid geometries

GIS analysis with spatial joins

Simplifying geometries

Measuring distances

Merging polygons using a common attribute

Computing intersections

Clipping geometries to deploy data

Simplifying geometries with PostGIS topology

Working with Vector Data – Advanced Recipes

Introduction

Improving proximity filtering with KNN

Improving proximity filtering with KNN – advanced

Rotating geometries

Improving ST_Polygonize

Translating, scaling, and rotating geometries – advanced

Detailed building footprints from LiDAR

Creating a fixed number of clusters from a set of points

Calculating Voronoi diagrams

Working with Raster Data

Introduction

Getting and loading rasters

Working with basic raster information and analysis

Performing simple map-algebra operations

Combining geometries with rasters for analysis

Converting between rasters and geometries

Processing and loading rasters with GDAL VRT

Warping and resampling rasters

Performing advanced map-algebra operations

Executing DEM operations

Sharing and visualizing rasters through SQL

Working with pgRouting

Introduction

Startup – Dijkstra routing

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

Calculating the driving distance/service area

Calculating the driving distance with demographics

Extracting the centerlines of polygons

Into the Nth Dimension

Introduction

Importing LiDAR data

Performing 3D queries on a LiDAR point cloud

Constructing and serving buildings 2.5D

Using ST_Extrude to extrude building footprints

Creating arbitrary 3D objects for PostGIS

Exporting models as X3D for the web

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

UAV photogrammetry in PostGIS – point cloud

UAV photogrammetry in PostGIS – DSM creation

PostGIS Programming

Introduction

Writing PostGIS vector data with Psycopg

Writing PostGIS vector data with OGR Python bindings

Writing PostGIS functions with PL/Python

Geocoding and reverse geocoding using the GeoNames datasets

Geocoding using the OSM datasets with trigrams

Geocoding with geopy and PL/Python

Importing NetCDF datasets with Python and GDAL

PostGIS and the Web

Introduction

Creating WMS and WFS services with MapServer

Creating WMS and WFS services with GeoServer

Creating a WMS Time service with MapServer

Consuming WMS services with OpenLayers

Consuming WMS services with Leaflet

Consuming WFS-T services with OpenLayers

Developing web applications with GeoDjango – part 1

Developing web applications with GeoDjango – part 2

Developing a web GPX viewer with Mapbox

Maintenance, Optimization, and Performance Tuning

Introduction

Organizing the database

Setting up the correct data privilege mechanism

Backing up the database

Using indexes

Clustering for efficiency

Optimizing SQL queries

Migrating a PostGIS database to a different server

Replicating a PostGIS database with streaming replication

Geospatial sharding

Paralellizing in PosgtreSQL

Using Desktop Clients

Introduction

Adding PostGIS layers – QGIS

Using the Database Manager plugin – QGIS

Adding PostGIS layers – OpenJUMP GIS

Running database queries – OpenJUMP GIS

Adding PostGIS layers – gvSIG

Adding PostGIS layers – uDig

Introduction to Location Privacy Protection Mechanisms

Introduction

Adding noise to protect location data

Creating redundancy in geographical query results

References

Other Books You May Enjoy

Leave a review - let other readers know what you think

Index

Customer Reviews

5 star

4 star

3 star

2 star

1 star

Exporting data to a shapefile with the pgsql2shp PostGIS command

In this recipe, you will export a PostGIS table to a shapefile using the pgsql2shp command that is shipped with any PostGIS distribution.

How to do it...

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

In case you still haven't done it, export the countries shapefile to PostGIS using the ogr2ogr or the shp2pgsql commands. The shp2pgsql approach is as shown:

      $ shp2pgsql -I -d -s 4326 -W LATIN1 -g the_geom countries.shp
      chp01.countries > countries.sql
      $ psql -U me -d postgis_cookbook -f countries.sql

The ogr2ogr approach is as follows:

      $ ogr2ogr -f PostgreSQL PG:"dbname='postgis_cookbook' user='me'
      password='mypassword'"
      -lco SCHEMA=chp01 countries.shp -nlt MULTIPOLYGON -lco OVERWRITE=YES
      -lco GEOMETRY_NAME=the_geom

Now, query PostGIS in order to get a list of countries grouped by the subregion field. For this purpose, you will merge the geometries for features having the same subregion code, using the ST_Union PostGIS geometric processing function:

      postgis_cookbook=> SELECT subregion,
        ST_Union(the_geom) AS the_geom, SUM(pop2005) AS pop2005
        FROM chp01.countries GROUP BY subregion;

Execute the pgsql2shp PostGIS command to export into a shapefile the result of the given query:

      $ pgsql2shp -f subregions.shp -h localhost -u me -P mypassword
      postgis_cookbook "SELECT MIN(subregion) AS subregion, 
      ST_Union(the_geom) AS the_geom, SUM(pop2005) AS pop2005 
      FROM chp01.countries GROUP BY subregion;" 
 
      Initializing... 
      Done (postgis major version: 2). 
      Output shape: Polygon 
      Dumping: X [23 rows].

Open the shapefile and inspect it with your favorite desktop GIS. This is how it looks in QGIS after applying a graduated classification symbology style based on the aggregated population for each subregion:

Visualization in QGIS of the classification of subregions based on population and information of the selected feature

How it works...

You have exported the results of a spatial query to a shapefile using the pgsql2shp PostGIS command. The spatial query you have used aggregates fields using the SUM PostgreSQL function for summing country populations in the same subregion, and the ST_Union PostGIS function to aggregate the corresponding geometries as a geometric union.

The pgsql2shp command allows you to export PostGIS tables and queries to shapefiles. The options you need to specify are quite similar to the ones you use to connect to PostgreSQL with psql. To get a full list of these options, just type pgsql2shp in your command prompt and read the output.

PostGIS Cookbook - Second Edition

By : Pedro Wightman, Bborie Park, Stephen Vincent Mather, Thomas Kraft, Mayra Zurbarán

PostGIS Cookbook - Second Edition

By: Pedro Wightman, Bborie Park, Stephen Vincent Mather, Thomas Kraft, Mayra Zurbarán

Overview of this book

Related Content you might be interested in

Current Title:

PostGIS Cookbook - Second Edition

Mastering PostGIS

GeoServer Beginner's Guide

Mastering Geospatial Analysis with Python

Exporting data to a shapefile with the pgsql2shp PostGIS command

How to do it...

How it works...