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

 

 

Advertisements

Transfer 3D shapefiles “z” values to the table of attributes

3D shapefiles are not that common, but sometimes they appear. The 3rd dimension value is stored inside the geometries and usually does not show in the table of attributes. As most of the GIS software does not handle the 3rd dimension it is then necessary to transfer this value from the geometries to the table of attributes. Databases like PostGIS and Spatialite together with Quantum GIS come to help.

The Spatialte way:

  • Open QGIS, add your shapefile and save it as Spatialite (or alternatively use the QGIS DB Manager to drag and drop the shapefile to an already existing Spatialite Database)
  • Add the newly created Spatialite vector and through the QGIS vector properties (“fields” tab) add a new column, that will store the “z” values
  • In the QGIS DB Manager SQL Window use this command:

update tablename set columnname = st_z(st_pointn(geom,1)

The PostGIS way:

  • Import the shapefile to your PostGIS database

ogr2ogr -f “PostgreSQL” PG:”host=yourhost user=user dbname=dbname password=*****” shapename.shp

  • Connect to you database

psql -h yourhost -U user dbname

  • Add a column

ALTER TABLE tablename ADD COLUMN columnname numeric(19,11);

  • Fill the column with the “z” values

update  tablename  set columnname = st_z(ST_PointN(wkb_geometry,1));