Book Image

PostGIS Cookbook

Book Image

PostGIS Cookbook

Overview of this book

Related Content you might be interested in

Current Title:

Small book image
PostGIS Cookbook
Jan, 2014 | 484 pages
No titles found
Table of Contents (18 chapters)
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
1
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
2
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
3
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
4
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
5
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
6
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
7
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
8
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
9
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
10
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
11
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

Exporting data to the 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:

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

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

  3. 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 MIN(subregion) AS subregion,ST_Union(the_geom) AS the_geom, SUM(pop2005) AS pop2005FROM chp01.countries GROUP BY subregion;

  4. Export the results of this query by using the pgsql2shp PostGIS command:

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

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

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 have a full list of these options, just type pgsql2shp in your command prompt and read the output.

Previous Section
End of Section 8
Next Section