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.
The steps you need to follow to complete this recipe are as follows:
Unzip the
TM_WORLD_BORDERS-0.3.ziparchive to your working directory. You can find this archive in the book's dataset.Import the world countries shapefile (
TM_WORLD_BORDERS-0.3.shp) in PostGIS using theogr2ogrcommand. Using some of theogr2ogroptions, you will import only the features fromSUBREGION=2(Africa), and theISO2andNAMEattributes, and rename the feature class toafrica_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.shpCheck if the shapefile was correctly imported in PostGIS, querying the spatial table in the database or displaying it in a Desktop GIS.
Query PostGIS to get a list of the 50 active hotspots with the highest brightness temperature (the
bright_t31field) from theglobal_24htable 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)
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_countriestable 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)
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"Open the GeoJSON file and inspect it with your favorite Desktop GIS. The following screenshot shows you how it looks with QGIS:
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 GEOMETRYoption:$ 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"
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'"
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.