In QGIS there are many tools that can be used to import vectors inside a PostGIS database, each one has pros and cons:

  • SPIT core plugin: available since early QGIS releases but now unmaintained tool and therefore candidate to be removed from future QGIS releases. It has the advantage to allow import several vectors in one run, but on the other hand it does not have an option to launder table/columns names and is overall quite slow especially for fair large vectors.
  • DB Manager: it has several pros, like supporting drag & drop import and a few important other options, but misses to allow import several vectors and is overall slow especially for fair large vectors.
  • QGIS browser: it allows import vectors using drag & drop but as the above tools missed to allow multiple vectors import. Overall slow especially for fair large vectors.
  • Processing toolbox ”Import into PostGIS”: it can import several vectors at once because, as any other tool in the QGIS Processing toolbox, it can run in batch mode. Overall slow especially for fair large vectors.

There are of course also command line alternatives, in particular shp2pgsql (together with psql) and ogr2ogr. Each one is rich of options/switches and they can be scripted to import several vectors in one go. While shp2pgsql is installed were PostGIS is installed, usually it is not on common users desktop machines. On the other hand ogr2ogr is installed and available on any machine where QGIS is installed because is part of the GDAL/OGR libary, that is basic dependency of any QGIS installation.

We compared how importing vectors in PostGIS performed using ogr2ogr compared to the tools available in QGIS, and then also compared to shp2pgsql. In short, the results are the following:

  • even without recurring to any particular switch/trick, ogr2ogr is on average much more faster than any available tools available in QGIS.
  • ogr2ogr and shp2pgsql performed in a similar way.

To compare ogr2ogr and shp2pgsql we used as input dataset a 4 million features (polygons) shapefile (1.3GB of space occupied) and also a small subset of it (4000 features, 10MB) using PostGIS installed on the local machine (Ubuntu GNU/Linux 14.04).

Without using any particular switch to make imports faster (like “-D” for shp2pgsql or “-config PG_USE_COPY YES” for ogr2ogr) ogr2ogr is much faster than shp2pgsql/psql with the small dataset (2.5 seconds against 35 seconds).

With the large dataset things gets the other way, with shp2pgsql/psql ending the task in 17 minutes against 19.5 minutes with ogr2ogr.

Adding the options “-D” to shp2pgsql and “-config PG_USE_COPY YES” to ogr2ogr is possible to get a dramatic improvement of the performace of both tools: ogr2ogr takes 0.8 seconds to process the small dataset and 2.21 minutes the process the big dataset, while shp2pgsql/psql takes respectively 24 seconds and 1.56 minutes.

ogr2ogr seemed a good choice to create a new tool for QGIS to allow import vectors in a fast way. We implemented such tool as part of the QGIS Processing toolbox and therefore is available the brand new QGIS 2.8 release.

QGIS Processing tools to import vector layers in PostGIS
The new tool(s) in the QGIS Processing toolbox

The tool also exposes options that are not usually available in any other QGIS tool. Aming the others: Vector dimensions, Append, Append and add new fields, Skip failures, Simplification, Densification, Import selected features by extent, Import clipped features by extent and a few others.

Tool GUI
Tool GUI