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.