Maintenance, Optimization, and Performance Tuning | PostGIS Cookbook

Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

PostGIS Cookbook

4.6 (12)

PostGIS Cookbook

4.6 (12)

Overview of this book

PostGIS Cookbook

PostGIS Cookbook

Credits

Credits

About the Authors

About the Authors

About the Reviewers

About the Reviewers

www.PacktPub.com

www.PacktPub.com

Preface

Preface

Free Chapter

Moving Data In and Out of PostGIS

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

Structures that Work

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

Working with Vector Data – The Basics

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

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

Working with Raster Data

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

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

Into the Nth Dimension

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

PostGIS Programming

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

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

Maintenance, Optimization, and Performance Tuning

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

Using Desktop Clients

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

Index

Index

Using indexes

A database index is very much like the index of a book (such as this one). While a book's index indicates the pages on which a word is present, a database column index indicates the rows in a table containing a searched value. Just as a book's index does not indicate exactly where on the page a word is located, the database index may not be able to denote the exact location of the searched value in a row's column.

PostgreSQL has several types of indexes, such as B-Tree, Hash, GIST, SP-GIST, and GIN. All these index types are designed to help queries find matching rows faster. What makes the indexes different is the underlying algorithms. Generally, to keep things simple, almost all PostgreSQL indexes are of the B-Tree type. PostGIS (spatial) indexes are of the GIST type.

Geometries, geographies, and rasters are all large, complex objects, and relating to or among these objects takes time. Spatial indexes are added to the PostGIS data types to improve search performance. The performance...

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

PostGIS Cookbook

Search

Your notes and bookmarks