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.
The steps you need to follow to complete this recipe are as follows:
- Create a shapefile from the virtual driver created in the previous recipe using the
ogr2ogrcommand (note that in this case, you do not need to specify the-foption as the shapefile is the default output format for theogr2ogrcommand):
$ ogr2ogr global_24h.shp global_24h.vrt- Generate the SQL dump file for the shapefile using the
shp2pgsqlcommand. You are going to use the-Goption to generate a PostGIS spatial table using the geography type, and the-Ioption to generate the spatial index on the geometric column:
$ shp2pgsql -G -I global_24h.shp
chp01.global_24h_geographic > global_24h.sql- Analyze the
global_24h.sqlfile (in Windows, use a text editor such as Notepad):
$ head -n 20 global_24h.sqlThe output of the preceding command is as follows:
- Run the
global_24h.sqlfile in PostgreSQL:
$ psql -U me -d postgis_cookbook -f global_24h.sqlNote
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
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:
- Analyze the new PostGIS table with
ogrinfo(use the-fidoption just to display one record from the table):
$ ogrinfo PG:"dbname='postgis_cookbook' user='me'
password='mypassword'" chp01.global_24h_geographic -fid 1The 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:
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 TABLEsection (if the-aoption is not selected, in which case, the table should already exist in the database) - The
INSERT INTOsection (oneINSERTstatement for each feature to be imported from the shapefile) - The
CREATE INDEXsection (if the-Ioption 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.