PostGIS Cookbook

For web developers and software architects this book will provide a vital guide to the tools and capabilities available to PostGIS spatial databases. Packed with hands-on recipes and powerful concepts

PostGIS Cookbook

Cookbook
Paolo Corti, Stephen Vincent Mather, Thomas J Kraft, Bborie Park

For web developers and software architects this book will provide a vital guide to the tools and capabilities available to PostGIS spatial databases. Packed with hands-on recipes and powerful concepts
$29.99
$49.99
RRP $29.99
RRP $49.99
eBook
Print + eBook
$12.99 p/month

Want this title & more? Subscribe to PacktLib

Enjoy full and instant access to over 2000 books and videos – you’ll find everything you need to stay ahead of the curve and make sure you can always get the job done.
+ Collection
Free sample

Book Details

ISBN 139781849518666
Paperback484 pages

About This Book

  • Integrate PostGIS with web frameworks and implement OGC standards such as WMS and WFS using MapServer and GeoServer
  • Convert 2D and 3D vector data, raster data, and routing data into usable forms
  • Visualize data from the PostGIS database using a desktop GIS program such as QGIS and OpenJUMP
  • Easy-to-use recipes with advanced analyses of spatial data and practical applications

Who This Book Is For

If you are a web developer or a software architect, especially in location-based companies, and want to expand the range of techniques you are using with PostGIS, then this book is for you. You should have some prior experience with PostgreSQL database and spatial concepts.

Table of Contents

Chapter 1: 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 the 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
Chapter 2: Structures that Work
Introduction
Using geospatial views
Using triggers to populate a geometry column
Structuring spatial data with table inheritance
Extending inheritance – table partitioning
Normalizing imports
Normalizing internal overlays
Using polygon overlays for proportional census estimates
Chapter 3: 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
Chapter 4: 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
Generating detailed building footprints from LiDAR
Using external scripts to embed new functionality in order to calculate a Voronoi diagram
Using external scripts to embed other libraries in order to calculate a Voronoi diagram – advanced
Chapter 5: 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
Chapter 6: Working with pgRouting
Introduction
Startup – Dijkstra routing
Loading data from OpenStreetMap and finding the shortest path using A*
Driving distance/service area calculation
Calculating demographics using driving distance
Extracting the centerlines of polygons
Chapter 7: Into the Nth Dimension
Introduction
Importing LiDAR data
Performing 3D queries on a LiDAR point cloud
Constructing and serving buildings 2.5 D
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 – orthorectification
UAV photogrammetry in PostGIS – DSM creation
Chapter 8: 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
Chapter 9: PostGIS and the Web
Introduction
Creating WMS and WFS services with MapServer
Creating WMS and WFS services with GeoServer
Creating a WMS Time 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
Chapter 10: 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
Chapter 11: 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

What You Will Learn

  • Import and export geographic data from the PostGIS database using the available tools
  • Structure spatial data using the functionality provided by the combination of PostgreSQL and PostGIS
  • Work with a set of PostGIS functions to perform basic and advanced vector analyses
  • Connect PostGIS with Python
  • Learn to use programming frameworks around PostGIS
  • Maintain, optimize, and fine-tune spatial data for long-term viability
  • Explore the 3D capabilities of PostGIS, including LiDAR point clouds and point clouds derived from Structure from Motion (SfM) techniques
  • Distribute 3D models through the Web using the X3D standard
  • Use PostGIS to develop powerful GIS web applications using Open Geospatial Consortium web standards
  • Master PostGIS Raster

In Detail

PostGIS is a spatial database that integrates 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 the related extensions, this book is a must-have.

This book is a deep-dive into the full range of PostGIS topics, with practical applications of the concepts and code. It is a comprehensive guide on PostGIS tools and concepts which are required to manage, manipulate, and analyse spatial data in PostGIS. This book is packed with systematic instructions of hands-on examples and in-depth explanations. Even for experienced users, this book will serve as a great source of reference by providing new ways of working with PostGIS through the book's easy-to-follow approach.

This hands-on guide looks at 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.

This practical book will help you take advantage of basic and advanced vector, raster, and routing approaches. You will learn to use the concepts of data maintenance, optimization, and performance, which will help you to integrate these into a large ecosystem of desktop and web tools.

With this comprehensive guide, 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.

Authors

Table of Contents

Chapter 1: 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 the 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
Chapter 2: Structures that Work
Introduction
Using geospatial views
Using triggers to populate a geometry column
Structuring spatial data with table inheritance
Extending inheritance – table partitioning
Normalizing imports
Normalizing internal overlays
Using polygon overlays for proportional census estimates
Chapter 3: 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
Chapter 4: 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
Generating detailed building footprints from LiDAR
Using external scripts to embed new functionality in order to calculate a Voronoi diagram
Using external scripts to embed other libraries in order to calculate a Voronoi diagram – advanced
Chapter 5: 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
Chapter 6: Working with pgRouting
Introduction
Startup – Dijkstra routing
Loading data from OpenStreetMap and finding the shortest path using A*
Driving distance/service area calculation
Calculating demographics using driving distance
Extracting the centerlines of polygons
Chapter 7: Into the Nth Dimension
Introduction
Importing LiDAR data
Performing 3D queries on a LiDAR point cloud
Constructing and serving buildings 2.5 D
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 – orthorectification
UAV photogrammetry in PostGIS – DSM creation
Chapter 8: 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
Chapter 9: PostGIS and the Web
Introduction
Creating WMS and WFS services with MapServer
Creating WMS and WFS services with GeoServer
Creating a WMS Time 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
Chapter 10: 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
Chapter 11: 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

Book Details

ISBN 139781849518666
Paperback484 pages
Read More

Recommended for You