Importing shapefiles with shp2pgsql

If you need to import a shapefile in PostGIS, you have at least a couple of options such as the ogr2ogr GDAL command, as you have seen previously, or the shp2pgsql PostGIS command.

In this recipe, you will load a shapefile in the database using the shp2pgsql command, analyze it with the ogrinfo command, and display it in QGIS desktop software.

How to do it...

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

1. Create a shapefile from the virtual driver created in the previous recipe using the ogr2ogr command (note that in this case, you do not need to specify the -f option as the shapefile is the default output format for the ogr2ogr command):

      $ ogr2ogr global_24h.shp global_24h.vrt

2. Generate the SQL dump file for the shapefile using the shp2pgsql command. You are going to use the -G option to generate a PostGIS spatial table using the geography type, and the -I option to generate the spatial index on the geometric column:

      $ shp2pgsql -G -I global_24h.shp
        chp01.global_24h_geographic > global_24h.sql

3. Analyze the global_24h.sql file (in Windows, use a text editor such as Notepad):

      $ head -n 20 global_24h.sql

The output of the preceding command is as follows:

4. Run the global_24h.sql file in PostgreSQL:

      $ psql -U me -d postgis_cookbook -f global_24h.sql

Note

If you are on Linux, you may concatenate the commands from the last two steps in a single line in the following manner:$ shp2pgsql -G -I global_24h.shp chp01.global_24h_geographic | psql -U me -d postgis_cookbook

5. Check if the metadata record is visible in the geography_columns view (and not in the geometry_columns view, as with the -G option of the shp2pgsql command, we have opted for a geography type):

      postgis_cookbook=# SELECT f_geography_column,   coord_dimension,
      srid, type FROM geography_columns   
      WHERE f_table_name = 'global_24h_geographic';

The output of the preceding command is as follows:

6. Analyze the new PostGIS table with ogrinfo (use the -fid option just to display one record from the table):

      $ ogrinfo PG:"dbname='postgis_cookbook' user='me'
        password='mypassword'" chp01.global_24h_geographic -fid 1

The output of the preceding command is as follows:

Now, open QGIS and try to add the new layer to the map. Navigate to Layer | Add Layer | Add PostGIS layers and provide the connection information, and then add the layer to the map as shown in the following screenshot:

How it works...

The PostGIS command, shp2pgsql, allows the user to import a shapefile in the PostGIS database. Basically, it generates a PostgreSQL dump file that can be used to load data by running it from within PostgreSQL.

The SQL file will be generally composed of the following sections:

• The CREATE TABLE section (if the -a option is not selected, in which case, the table should already exist in the database)
• The INSERT INTO section (one INSERT statement for each feature to be imported from the shapefile)
• The CREATE INDEX section (if the -I option is selected)

Note

Unlike ogr2ogr, there is no way to make spatial or attribute selections (-spat, -where ogr2ogr options) for features in the shapefile to import. On the other hand, with the shp2pgsql command, it is possible to import the m coordinate of the features too (ogr2ogr only supports x, y, and z at the time of writing).

To get a complete list of the shp2pgsql command options and their meanings, just type the command name in the shell (or in the command prompt, if you are on Windows) and check the output.

There's more...

There are GUI tools to manage data in and out of PostGIS, generally integrated into GIS desktop software such as QGIS. In the last chapter of this book, we will take a look at the most popular one.