Importing and exporting data with the ogr2ogr GDAL command

In this recipe, you will use the popular ogr2ogr GDAL command for importing and exporting vector data from PostGIS.

Firstly, you will import a shapefile in PostGIS using the most significant options of the ogr2ogr command. Then, still using ogr2ogr, you will export the results of a spatial query performed in PostGIS to a couple of GDAL-supported vector formats.

How to do it...

The steps you need to follow to complete this recipe are as follows:

1. Unzip the TM_WORLD_BORDERS-0.3.zip archive to your working directory. You can find this archive in the book's dataset.

2. Import the world countries shapefile (TM_WORLD_BORDERS-0.3.shp) in PostGIS using the ogr2ogr command. Using some of the ogr2ogr options, you will import only the features from SUBREGION=2 (Africa), and the ISO2 and NAME attributes, and rename the feature class to africa_countries:

   $ ogr2ogr -f PostgreSQL -sql "SELECT ISO2, NAME AS country_name FROM 'TM_WORLD_BORDERS-0.3' WHERE REGION=2" -nlt MULTIPOLYGON PG:"dbname='postgis_cookbook' user='me' password='mypassword'" -nln africa_countries -lco SCHEMA=chp01 -lco GEOMETRY_NAME=the_geom TM_WORLD_BORDERS-0.3.shp
   

3. Check if the shapefile was correctly imported in PostGIS, querying the spatial table in the database or displaying it in a Desktop GIS.

4. Query PostGIS to get a list of the 50 active hotspots with the highest brightness temperature (the bright_t31 field) from the global_24h table created in the previous recipe:

   postgis_cookbook=# SELECT
   ST_AsText(the_geom) AS the_geom, bright_t31
   FROM chp01.global_24h
   ORDER BY bright_t31 DESC LIMIT 100;
   

   The output of the preceding command is as follows:

                  the_geom                    | bright_t31
   --------------------------------------------------------------
    POINT(-13361233.2019535 4991419.20457202) | 360.6
    POINT(-13161080.7575072 8624445.64118912) | 359.6
    POINT(-13359897.3680639 4991124.84275376) | 357.4
   ...
   (100 rows)
   

5. You want to figure out in which African countries these hotspots are located. For this purpose, you can do a spatial join with the africa_countries table produced in the previous step:

   postgis_cookbook=# SELECT
   ST_AsText(f.the_geom) AS the_geom,   f.bright_t31, ac.iso2, ac.country_name
   FROM chp01.global_24h as f
   JOIN chp01.africa_countries as ac
   ON ST_Contains(ac.the_geom, ST_Transform(f.the_geom,     4326))
   ORDER BY f.bright_t31 DESC
   LIMIT 100;
   

   The output of the preceding command is as follows:

      the_geom   | bright_t31 | iso2 |  country_name
   -----------------------------------------------------------
    POINT(229...)| 316.1      | AO   | Angola
    POINT(363...)| 315.4      | TZ   | United Republic ofTanzaniaPOINT(229...)| 315        | AO   | Angola
   ...
   (100 rows)
   

6. You will now export the result of this query to a vector format supported by GDAL, such as GeoJSON, in the WGS 84 spatial reference using ogr2ogr:

   $ ogr2ogr -f GeoJSON -t_srs EPSG:4326 warmest_hs.geojson PG:"dbname='postgis_cookbook' user='me' password='mypassword'" -sql "SELECT f.the_geom as the_geom, f.bright_t31, ac.iso2, ac.country_name FROM chp01.global_24h as f JOIN chp01.africa_countries as ac ON ST_Contains(ac.the_geom, ST_Transform(f.the_geom, 4326)) ORDER BY f.bright_t31 DESC LIMIT 100"
   

7. Open the GeoJSON file and inspect it with your favorite Desktop GIS. The following screenshot shows you how it looks with QGIS:

   

8. Export the previous query to a CSV file. In this case, you have to indicate how the geometric information must be stored in the file; this is done using the -lco GEOMETRY option:

   $ ogr2ogr -t_srs EPSG:4326 -f CSV -lco GEOMETRY=AS_XY -lco SEPARATOR=TAB warmest_hs.csv PG:"dbname='postgis_cookbook' user='me' password='mypassword'" -sql "SELECT f.the_geom, f.bright_t31, ac.iso2, ac.country_name FROM chp01.global_24h as f JOIN chp01.africa_countries as ac ON ST_Contains(ac.the_geom, ST_Transform(f.the_geom, 4326)) ORDER BY f.bright_t31 DESC  LIMIT 100"
   

How it works...

GDAL is an open source library that comes together with several command-line utilities, which let the user translate and process raster and vector geo datasets in a plethora of formats. In the case of vector datasets, there is a GDAL sublibrary for managing vector datasets named OGR (therefore, when talking about vector datasets in the context of GDAL, we can also use the expression OGR dataset ).

When you are working with an OGR dataset, two of the most popular OGR commands are ogrinfo, which lists many kinds of information from an OGR dataset, and ogr2ogr, which converts the OGR dataset from one format to the other.

It is possible to retrieve a list of the supported OGR vector formats using the –formats option on any OGR commands, for example, with ogr2ogr:

$ ogr2ogr --formats

The output of the preceding command is as follows:

Supported Formats:
  -> "ESRI Shapefile" (read/write)
  -> "MapInfo File" (read/write)
  -> "UK .NTF" (readonly)
  -> "SDTS" (readonly)
  -> "TIGER" (read/write)
  ...

Note that some formats are read-only, while the others are read/write.

PostGIS is one of the supported read/write OGR formats, so it is possible to use the OGR API or any OGR commands (such as ogrinfo and ogr2ogr) to manipulate its datasets.

The ogr2ogr command has many options and parameters; in this recipe, you have seen some of the most notable ones such as -f—to define the output format, -t_srs—to reproject/transform the dataset, and -sql—to define an (eventually spatial) query in the input OGR dataset.

When using ogrinfo and ogr2ogr together with the desired option and parameters, you have to define the datasets. When specifying a PostGIS dataset, you need a connection string that is defined as follows:

PG:"dbname='postgis_cookbook' user='me' password='mypassword'"

See also

You can find more information about the ogrinfo and ogr2ogr commands on the GDAL website available at http://www.gdal.org.

If you need more information about the PostGIS driver, you should check its related documentation page available at http://www.gdal.org/ogr/drv_pg.html.