PGIS-03 13: Loading Data to Postgres

Owned by Antony Scott (Deactivated)
Last updated: 11 Mar, 2024 by David Renn
4 min read

3. Loading Data to Postgres

Data can be loaded into Postgres in a number of ways - this module covers:

  • Tools in QGIS

  • The COPY command in SQL

  • The GDAL ogr2ogr tool from the command line

3.1. QGIS

If you have QGIS installed, it provides a GUI-based means of loading data to a Postgres database. There are a few ways of getting data into Postgres using QGIS.

3.1.1. Load data with DB Manager

This approach gives the the greatest degree of control over the conversion from your source format to Postgres.

  1. In QGIS, go to the Browser panel, and navigate to one of the layers in the database - for example general > neighbourhoods

  2. Right-click on the layer in the Browser, and select Export Layer > To File…, then save the layer as a GeoPackage in your Home folder - this creates a sample file-based layer for you to load back into the database

  3. Remove all layers from the Layers panel (right-click > Remove Layer)

  4. Go to Database > DB Manager, and navigate to your database

  5. Click the Import Layer/File button to load the Import vector layer dialog

  6. Using the … button to the right of the Input field, navigate to the file you just created

  7. Under Output table, select the training schema, and give the table an appropriate name

  8. In the Options section, you can leave most of the defaults in place, but note:

    1. Use Primary key to set a primary key for the table - the default is id

    2. The Geometry column holds details of the geometry for each feature - the default name for the column is geom

    3. You can use Target SRID to change the SRID of the data - i.e. to reproject it

    4. Replace destination table (if exists): will overwrite a table with the same name

    5. Convert field names to lower case: is good practice, as otherwise field names need double quotes in SQL statements

    6. Create spatial index: will make your spatial queries run faster

  9. When the table has been loaded successfully, refresh the connection in the Browser and check the table is there - double-click to load it

  10. Navigate the file-based version of the data you created earlier in the Browser, and double-click it to load it into the Layers panel - it should be identical to the database version

3.1.2. Load data using the Browser

The QGIS Browser panel also contains functionality to load and manage data in the database.

  1. In the Layers panel, click on the GeoPackage version of the data you have been working with, and drag it into the Browser panel, dropping it over one of the schemas

  2. The layer will be loaded into the database with the default settings from the DB Manager dialog

  3. Right-click on the new layer to see the options available for layer management in the Browser

3.2. Import CSV to Postgres

The Copy command can be used to import Comma Separated Value files (CSV) / text files to and from PostgreSQL. 

  1. In QGIS, create a CSV file we can use to import

    1. In the Browser, navigate to general > llsoa_2011_ew and double-click to load the data into QGIS

    2. Zoom in to a small area so that only about 50 features are visible (this will save time for the export)

    3. Select the layer in the Layers panel and right-click > Export Layer > To File…, set the Format to Comma Separated Value [CSV], under Extent click Map Canvas Extent, and save the layer to your Home folder with a suitable name

  2. In Windows Explorer, navigate to the layer and double-click on it to open it as a spreadsheet - note that there is no geometry column

  3. In pgAdmin, look at the source table and make a note of the datatype of each field (omitting wkb_geometry)

  4. Create a table in the general schema called llsoa_2011_clip, and use the Columns tab of the Create - Table dialog to create columns (apart from wkb_geometry) using the field names in the spreadsheet (in lower case, with no spaces), in the order they appear, and with the same datatype as in the source table

  5. Got to Tools > PSQL Tool in the pgAdmin menu to open a PSQL window

  6. Use the \COPY command to copy the data from the CSV file into the blank table

    1. Paste or type the following code in the PSQL prompt, editor, adjusting the file path/name if necessary, then press Enter
      \COPY general.llsoa_clip from 'D:\\PhotonUser\\My Files\\Home Folder\\llsoa.csv' CSV HEADER

    2. The double backslashes in the file path ensure the file path is recognised, CSV sets the mode, and HEADER ensures the first line is not included as this contains header information

    3. You will get a confirmation, for example COPY 220, indicating that 220 rows were loaded into the table

  7. Look at the table in pgAdmin to see that it was successfully populated

3.3. ogr2ogr

The GDAL library contains an ogr2ogr executable which will convert between most spatial formats, including PosgresQL/PostGIS, Shapefile, MapInfo and GeoPackage. It can be run from the command line with appropriate parameters - see the GDAL site for full details.

It’s also possible to use ogr2ogr from QGIS, so in this exercise we will use QGIS to create a command line, then run it in the command prompt.

3.3.1. Load data with ogr2ogr

  1. In QGIS, find the Export to PostgreSQL (available connection) tool in the Processing Toolbox - make sure you are using the one in the GDAL folder

  2. In the dialog:

    1. Set the Database to your database connection

    2. Set Input Layer as the GeoPackage layer you created earlier

    3. Set the Schema and Table to a location in your database

    4. Leave other options as defaults

    5. Scroll to the bottom of the dialog to see the command line that QGIS has created, and copy it

  3. Click Run to load the data

  4. Check in the QGIS Browser that the data has loaded correctly

  5. In AppStream, open the OSGeo4W Shell application - this will give you a command prompt

  6. Copy the command line from the QGIS dialog at the prompt, and press Enter to run it - this will give you the same result as running from QGIS