Getting ready

1. First, import some data to be used as a test bed in PostGIS. Download the .kmz file containing information about 2012 global earthquakes from the USGS website at http://earthquake.usgs.gov/earthquakes/eqarchives/epic/kml/2012_Earthquakes_ALL.kmz. Save it in the working/chp03 directory (alternatively, you can use the copy of this file included in the code bundle provided with this book).
2. A .kmz file is a collection of .kml files packaged with the ZIP compressor. Therefore, after unzipping the file (you may need to change the .kmz file extension to .zip), you may notice that it is composed of just a single .kml file. This file, which is in the GDAL abstraction, constitutes an OGR KML data source composed of nine different layers and containing 3D point geometries. Each layer contains earthquake data for each distinct earthquake magnitude:

      $ ogrinfo 2012_Earthquakes_ALL.kml

The output for this is as follows:

3. Import all of those layers in a PostGIS table named chp03.earthquakes simultaneously by executing one of the following scripts using the ogr2ogr command.

The following is the Linux version (name it import_eq.sh):

        #!/bin/bash 
        for ((i = 1; i < 9 ; i++)) ; do 
          echo "Importing earthquakes with magnitude $i 
                to chp03.earthquakes PostGIS table..." 
          ogr2ogr -append -f PostgreSQL -nln chp03.earthquakes 
          PG:"dbname='postgis_cookbook' user='me' 
          password='mypassword'" 2012_Earthquakes_ALL.kml 
          -sql "SELECT name, description, CAST($i AS integer) 
          AS magnitude FROM \"Magnitude $i\"" 
        done 

The following is the Windows version (name it import_eq.bat):

        @echo off 
        for /l %%i in (1, 1, 9) do ( 
          echo "Importing earthquakes with magnitude %%i 
                to chp03.earthquakes PostGIS table..." 
          ogr2ogr -append -f PostgreSQL -nln chp03.earthquakes 
          PG:"dbname='postgis_cookbook' user='me' 
          password='mypassword'" 2012_Earthquakes_ALL.kml 
          -sql "SELECT name, description, CAST(%%i AS integer) 
          AS magnitude FROM \"Magnitude %%i\"" 
        )

4. Execute the following script (for Linux, you need to add execute permissions to it):

      $ chmod 775 import_eq.sh
      $ ./import_eq.sh
      Importing earthquakes with magnitude 1 to chp03.earthquakes 
        PostGIS table...
      Importing earthquakes with magnitude 2 to chp03.earthquakes 
        PostGIS table...
      ...

5. To maintain consistency with the book's conventions, rename the geometric column wkb_geometry (the default geometry output name in ogr2ogr) to the_geom, as illustrated in the following command:

      postgis_cookbook=# ALTER TABLE chp03.earthquakes 
      RENAME wkb_geometry  TO the_geom; 

6. Download the cities shapefile for the USA from the https://nationalmap.gov/ website at http://dds.cr.usgs.gov/pub/data/nationalatlas/citiesx020_nt00007.tar.gz (this archive is also included in the code bundle provided with this book) and import it in PostGIS by executing the following code:

      $ ogr2ogr -f PostgreSQL -s_srs EPSG:4269 -t_srs EPSG:4326 
      -lco GEOMETRY_NAME=the_geom -nln chp03.cities
      PG:"dbname='postgis_cookbook' user='me'
      password='mypassword'" citiesx020.shp

7. Download the states shapefile for the USA from the https://nationalmap.gov/ website at http://dds.cr.usgs.gov/pub/data/nationalatlas/statesp020_nt00032.tar.gz (this archive is also included in the code bundle provided with this book) and import it in PostGIS by executing the following code:

      $ ogr2ogr -f PostgreSQL -s_srs EPSG:4269 -t_srs EPSG:4326 
      -lco GEOMETRY_NAME=the_geom -nln chp03.states -nlt MULTIPOLYGON 
      PG:"dbname='postgis_cookbook' user='me'
      password='mypassword'" statesp020.shp