Loading vector layers into SpatiaLite

SpatiaLite is a single file relational database that is built on top of the well-known SQLite database. It can store many layers of various types, including nonspatial tables. Interfaces to the format also allow the ability to run spatial queries of various kinds. It's a highly-flexible and portable format that is great for everyday use, especially when working on standalone projects or with only one user at a time. SpatiaLite works in a similar manner to PostGIS without the need to configure or run a database server.

Getting ready

Pick a vector layer and load it up in QGIS. This step is optional, as you can pick the source layer from the filesystem in a later dialog.

How to do it…

1. Create a SpatiaLite database if you don't already have one and name it cookbook.db. The easiest way to do this is with the Browser tab, as shown in the following screenshot:
   
2. Then, pick one of the following methods to importpick one of the following methods to import your data. The first option is faster, but the second option gives you more control over the import settings:
   • Import method 1—the fast method
     1. In the QGIS Browser tab, find the layer that you want to copy to the database.
     2. Drag and drop this layer on the Spatialite DB entry.

        Tip

        If you have a lot of files listed, this will be quite difficult as the browser doesn't scroll during the drag operation. You can optionally open a second browser window and drag the layer across. Also, note that this defaults to multi-type geometry. If you need to control the options, use the next method.

   • Import method 2—the standard method
     1. Open DB Manager from the Database menu.
     2. Expand the Spatialite item to list your databases. Expand the database that you want to connect to.
     3. Click on the following import layer icon:
        
     4. A dialog will pop up, providing you with import options.

        Tip

        SQL databases are usually case insensitive, so you can use all lower case characters. Also, never use spaces or special characters in table names; this can just lead to headaches later. An occasional underscore is okay.

     5. Select the layer to import from the drop-down list.
     6. Fill in a name for the new table.
     7. In most cases, the only thing left to do is check the Create spatial index checkbox.
     8. If this works, great. Now, you can load the layer to the map and verify that it's identical to the input.

        Tip

        This method is more similar to traditional database import and very similar to the PostGIS recipe next in this chapter.

How it works…

QGIS converts your geometry to a format that is compatible with SpatiaLite and inserts it, along with the attribute table. Afterwards, it updates the metadata tables in SpatiaLite to register the geometry column and build the spatial index on it. These two postprocesses make the database table appear as a spatial layer to QGIS and speed up the loading of data from the table when panning and zooming.

There's more…

The import dialog contained a few other features that are often useful. You can reproject data as part of the import process if you want, or you can specify the projection if QGIS didn't detect it properly. You can also name the geometry column something different than the default, geom; for example, utmz10n83 (this is normally not recommended). You can specify the character encoding of the text in the event that it's not handled correctly.

You can even use the dialog to append data to an existing table; for example, you have multiple counties with the same data structure that come as two separate files, but you want them all in one layer.

If, for some reason, the layer didn't import the way that you want, delete it and redo the import. If you delete layers, make sure to learn how to vacuum the database to recover the now empty space in the file and shrink its total size (this is not automatic).

Tip

Look for the Vacuum option as a button in many graphical tools. If you don't see it, no worries, just run the SQL, VACUUM;.

What happens if this fails? Databases can be really picky sometimes. Here are some common issues and solutions:

• It could be character encoding (accents, non-Latin languages), which requires that you specify the encoding.
• It could be picky about mixing multilayers with regular layers. Multilayers is when you have several separate geometries that are part of one record. For example, Hawaii is actually many islands. So, if you only have one row representing Hawaii, you need to cram all the island polygons into one geometry field. However, if you mix this with North Dakota, which is just a polygon, the import will fail. If you have this problem, you'll need to perform the import on the command-line using ogr2ogr and its newish feature, -nlt PROMOTE_TO_MULTI, which converts all single items to multi-items to fix this.
• Depending on your original source, you may have a mix of points, lines, and polygons. You'll either need to convert this to a Geometry Collection, or you need to split each type of geometry into a separate layer. Geometry Collections are currently poorly-supported in many GIS viewers, so this is only recommended for advanced users.

See also

If you need more advanced settings or can't get the QGIS tool to work, you may need to use the QspatiaLite Plugin (install this with Manage Python Plugins under the Plugins menu), the spatialite-gui (download this from https://www.gaia-gis.it/fossil/spatialite_gui/index) application, or the ogr2ogr command line (this comes with QGIS, which is part of OSGeo4w shell on Windows, or the terminal on Mac or Linux).