PostGIS Cookbook -- Table of Contents -- PostGIS Cookbook -- Credits -- About the Authors -- About the Reviewers -- www.PacktPub.com -- Support files, eBooks, discount offers and more -- Why Subscribe? -- Free Access for Packt account holders -- Preface -- What this book covers -- What you need for this book -- Who this book is for -- Conventions -- Reader feedback -- Customer support -- Downloading the example code -- Errata -- Piracy -- Questions -- 1. 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 the 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 -- 2. 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 a 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... -- Converting polygons to linestrings -- Converting linestrings back to polygons -- Finding center points of resultant polygons -- Using resultant points to query tabular relationships -- How it works... -- There's more... -- Using polygon overlays for proportional census estimates -- Getting ready -- How to do it... -- How it works... -- 3. 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... -- 4. 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 -- Generating detailed building footprints from LiDAR -- Getting ready -- How to do it... -- How it works... -- Using external scripts to embed new functionality in order to calculate a Voronoi diagram -- Getting ready -- How to do it... -- See also -- Using external scripts to embed other libraries in order to calculate a Voronoi diagram - advanced -- Getting ready -- How to do it... -- There's more... -- Step zero - preparing the test table -- Step one - preparing the input text -- Step two - returning results -- Step three - bundling as a function -- Step four - translating into geometry -- 5. 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... -- 6. 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... -- Driving distance/service area calculation -- Getting ready -- How to do it... -- See also -- Calculating demographics using driving distance -- Getting ready -- How to do it... -- Extracting the centerlines of polygons -- Getting ready -- How to do it... -- There's more... -- 7. 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.5 D -- Getting ready -- How to do it... -- Using ST_Extrude to extrude building footprints -- 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 ready -- How to do it... -- UAV photogrammetry in PostGIS - point cloud -- Getting ready -- How to do it... -- UAV photogrammetry in PostGIS - orthorectification -- Getting ready -- How to do it... -- Converting the point cloud to Voronoi polygons -- Attributing polygons with the color -- Rendering polygons to raster -- UAV photogrammetry in PostGIS - DSM creation -- Getting ready -- How it works... -- 8. 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... -- 9. 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 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... -- 10. 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.