On PostGIS: the inserts from a drap and drop operation in QGIS
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:
The cpp file with the crucial number 200
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: Export to PostgreSQL processing
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 GDAL/ogr2ogr import dialog
The import with the prcoessing tool took about 18seconds. This is great, compared to the 20mins using the normal drag and drop within QGIS:
On PostGIS: the inserts from a drap and drop operation in QGIS
On PostGIS: the inserts from a GDAL operation in QGIS
One of my favourite horror exercises for students was the digitization of geological maps. Everyone hated it as you need to take of snapping options, correct attributes, and…
A short time ago I’ve posted an article on the installation of QGIS in combination with a PostGIS database in the background. As we all know, Windows is…