Short Note: export layers to PostGIS with Processing

When using QGIS along with PostGIS you might want to publish data directly from inside QGIS into your PostGIS database. This is not only convenient as you don’t need to change the software of use but also easier as it only takes a drag and drop in QGIS instead of any commandline/fancytool. Yet it comes with a disadvantage: the number of inserts from QGIS into the db is limited to 200 per transaction. So it will take some time to insert a bigger dataset with 150.000 points or so. So how to overcome this?

The main idea

The source code of QGIS reveals, that the limitation is set inside the C++ file qgsvectorlayerexporter.cpp:

Now, there are two ways to take a better and faster way: write your own processing or use a default one. The latter will be part of this blog post: the GDAL processing tools.

ogr2ogr FTW

The GDAL processing toolbox provides a set of tools for both vector and raster processing. Victor Olaya already served us with a dedicated import tool for bigger datasets:

This uses the ogr2ogr command from the QGIS installation and pushes the data into your defined PostGIS db.

Comparison

I’ve checked the differences with a dump from OSM which has 23.000 points. The results are stunning but of course somehow predictable as we compare 200 with 20.000 features per transaction:

The import with the prcoessing tool took about 18seconds. This is great, compared to the 20mins using the normal drag and drop within QGIS: