Load data into PostgreSQL from QGIS

Owned by Andrew Bailey
Last updated: 21 May, 2020 by Kim Stimpson

Say you have some spatial data in QGIS and you want to load it into PostgreSQL. You can make use of the QGIS DB Manager tool to transfer data. The tool will create the necessary constraints and spatial indexes for further consumption in iShare applications.

Step-by-step guide

Using the DB Manager

  1. In QGIS make sure that you have a PostGIS connection to your PostgreSQL/PostGIS database. Click the blue elephant icon  (Add PostGIS Layers) and explore the available connections. 
  2. If QGIS doesn't have your database listed click 'New Connection...' and enter the details making sure to test the connection before continuing.
  3. Select from the Database menu select DB Manager > DB Manager
  4. Highlight the destination Postgresql database and click the Import Layer File button.
  5. Fill in the necessary details.
    Select the Input file, destination Schema and Table name.
    Set primary key as 'ogc_fid', geometry column as 'wkb_geometry', and the correct Source SRID and Target SRID projection and choose Create spatial index.
    You can also chose to Replace destination table (if exists) to overwrite an existing table and choose the correct Encoding. Shapefile and Mapinfo Tab files can sometimes be stored in Latin-1 but often the default UTF-8 character set will be fine.
    Press OK to import.
  6. Import has been successful. If not the problem might be character set encoding, invalid geometries or a precision difference. PostGIS supports geometry nodes with 11 decimal place precision. Source data may be more precise. Please see alternative way of Importing Using the Processing Toolbox.
  7. We can verify the data has been loaded correctly by running PGAdmin3. Here we can see that an entry is present in the geometry_columns table and the table definition has the spatial index and the geometric constraints.
  8. Make a cup of tea.

Using the Processing Toolbox

If you could not import successfully using the DB Manager then you could try using the Processing Toolbox.

  1. Select Toolbox from the Processing menu. The Processing Toolbox panel opens on the right hand side of QGIS e.g. 
     
  2. Expand the GDAL/OGR entry and then the OGR Miscellaneous entry.
  3. Double click on the Import Vector into PostGIS database (new connection) entry.
  4. Now you have a plethora of Parameters you can specify.
  5. Select your Input Layer and Output geometry type. Enter your database details, Primary key and Geometry column name as before.
  6. Now scroll further down the list and you can specify Additional creation options. Enter PRECISION=NO e.g.
  7. Click Run to Import your Layer. 
  8. Make another cup of tea.