A new QGIS tool (based on ogr2ogr) to import vectors in PostGIS, the fast way

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 long ago but now seems to be a unmaintained tool and therefore will be probably removed in a future QGIS release. It  has the advantage to allow import several vectors in one run, but on the other hand has cons like not laundering table and columns names and is overall quite slow especially for fair large vectors.
  • DB Manager: it has several pros like supporting drag & drop import, it has a few important import options, but misses to allow import several vectors and is overall slow especially for fair large vectors.
  • QGIS browser: it allows importing vectors using drag & drop, but no multiple vector import and overall slow especially for fair large vectors.
  • Processing toolbox “Import into PostGIS“: it can import several vectors at once, because as any tool in the Processing toolbox can run in batch mode, but again 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 it can be scripted to import several vectors at once in a PostGIS database. While shp2pgsql is usually installed only were PostGIS is installed, it is not on desktop machines were only desktop GIS like QGIS is. On the other hand ogr2ogr is installed and available on any machine where QGIS is installed because GDAL/OGR is a dependency for QGIS.

So the idea was to test how importing vectors in PostGIS using ogr2ogr compared to the tools available in QGIS and compared to shp2pgsql:

  • 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 give  similar results

To compare ogr2ogr and shp2pgsql we used as input dataset a 4 million features (polygons) shapefile (1.3GB of space occupied) and 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 (“-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 “-D” for shp2pgsql or “–config PG_USE_COPY YES” for ogr2ogr means a huge improvement is speed: ogr2ogr takes 0.8 seconds to process the small dataset and 2.21 minutes the process the big dataset. Shp2pgsql/psql take respectively 24 seconds and 1.56 minutes.

So ogr2ogr seemed really a good choice to add a new tool in QGIS to allow import in a fast way even really big vectors. We implemented such tool as part of the QGIS Processing toolbox and therefore is available for tests in QGIS master (the development version of QGIS) and will be available for everyone in the next stable release of QGIS (2.8).

ogr2ogr

The tool makes exposes also options that are not usually available in any other tool of QGIS like vector dimension, append, append and add new fields, skip failures, simplification, densification, import selected features by extent, import clipped features by extent and a few others.

ogr2ogr and QGIS

 

 

QGIS training (3 days) and Birdwatching/Field data collection (2 days) in South Portugal, January 2015

Faunalia and Imagine-Science are pleased to announce a QGIS training (3 days) course during a week that will include 2 days of field activities related to birdwatching and field data collection.

Where:

Portugal/Algarve, specifically “Ria de Alvor -Mexilhoeira Grande” near Portimão.

When:

26 to 30 January 2015.

 

For more informations about QGIS training program, field activities, terms and conditions:

http://www.imagine-science.org/en/geographic-information-systems-quantum-gis-initiation-training/

hello@imagine-science.org

 

QGIS Training and Birdwatching