PostGIS Cookbook电子书

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

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...

11. 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...

Index