[GIS] How to convert a complex project of shapefiles to a SpatiaLite database

databaseqgisshapefilespatial-databasespatialite

We have a QGIS project made of ~10 shapefile layers. The layers are saved on a shared server, and the project is used on different computers.

Recently polygons and polylines have been been disappearing when the layer is saved (still present in the attribute table but not in the mapping window). We have been advised that we would be better to move to a SpatiaLite database.

What is the best and quickest way to convert this number of shapefiles (with complex styling) to a SpatiaLite database?

Is it possible to put all of these shapefiles onto one SpatiaLite database, or will they have to be different databases?

Best Answer

It is definitely possible to pull all the layers into one database, although in a multiuser environment, this will be best if most users will only be reading (not editing) the data. The easiest way to quickly convert a large number of shapefiles to a single SpatiaLite database is to use ogr2ogr.

Make sure your shapefiles are in a single folder (e.g. gis_data). Ogr2ogr can load the folder as a data source, treating each shapefile as a layer. You can check this with ogrinfo:

ogrinfo -so /path/to/gis_data

The -so switch is "summary only".

If that works, use ogr2ogr to convert all shapefiles at once with:

ogr2ogr -f SQLite -dsco SPATIALITE=YES db_name.sqlite /path/to/gis_data -nlt PROMOTE_TO_MULTI

Some of the options are:

  • -dsco SPATIALITE=YES: SQLite can store geometry data in a variety of formats, so you have to use this switch to get a SpatiaLite database.
  • -lco LAUNDER=NO: By default, ogr2ogr will "launder" layer and field names, folding to lowercase and replacing spaces with underscores. This is usually a good choice, but if you don't want to do this, use this switch to override the default.
  • -nlt PROMOTE_TO_MULTI: The shapefile format is sloppy about distinguishing between simple polygons and multipolygons, but SpatiaLite is strict. This option is highly recommended, unless you absolutely know that your layer is and and will only ever contain simple polygons.
  • -lco SRID=<desired SRID>: Ogr2ogr will try to determine the matching SRID in the SpatiaLite database at load time, but shapefile PRJ files (esp. if they are ESRI) may not always match. You can force the desired SRID with this switch. (Note that if you are doing this as a batch, this will force the SRID on all imported layers. If this is not desired, and automated SRID matching doesn't work, you will either have clean up afterwards, or import each layer individually.

Other Options

You can also do this import/conversion in QGIS, although you will have to do it individually, rather than as a batch.

Save As

If you load your shapefiles in QGIS, you can export to SpatiaLite by right-clicking on the layer in the table of contents, choosing Save As, and setting the format to SpatiaLite. Note that this will create a new database for each layer (where the layer name will be inherited from the database), which is not your desired behavior.

DB Manager

In DB Manager, you can import a vector layer (any layer that QGIS can read) into a SpatiaLite database. The SpatiaLite database must already exist. So, one option is to create the SpatiaLite database by exporting one layer using Save As (previous option), then import each additional layer using DB Manager.

Styles

Styles can be saved in a SpatiaLite database. From the docs:

If the datasource of the layer is a database (PostGIS or Spatialite for example), you can save your layer style inside a table of the database. Just click on Save Style comboxbox and choose Save in database item then fill in the dialog to define a style name, add a description, an ui file and if the style is a default style. When loading a layer from the database, if a style already exists for this layer, QGIS will load the layer and its style. You can add several style in the database. Only one will be the default style anyway.

However, I don't know of a way to automatically export QGIS symbologies. The Save As method described above does give you the option to save "No symbology", "Feature symbology", or "Symbol layer symbology". However, I could detect no effect when I tested, and I haven't been able to find documentation on this option.

So I think what you have to do is the following:

  1. Load all shapefiles of interest in QGIS.
  2. Export symbol styles by going to the Style tab of the Layer Properties, and choosing Style→Save Style→QGIS Layer Style File.
  3. Convert your data to SpatiaLite using one of the methods above.
  4. Add the SpatiaLite layers to QGIS.
  5. Load the symbol styles by going to the Style tab of the Layer Properties, and choosing Style→Load Style→QGIS Layer Style File. Navigate to your saved QML file.
  6. If you want to store the style in the database (so that it loads automatically with the layer in the future), go to Style→Save Style→Save in database (spatialite).

This procedure is shown in this tutorial video for an older version of QGIS:

http://www.youtube.com/watch?v=SFNbO_p_Un4

Related Question