4 Load Data

Adding data to a SpatiaLite database in QGIS is admittedly a little clunky. The good news is that we only need to load each table in once and then it’s done.

The overview of the process is that we’ll add data to the QGIS interface, then we’ll be able to add the data we loaded to the database, then we’ll remove the data from QGIS, leaving the data in the databse. Yes, this is annoying. Now let’s try it.

4.1 Import Existing Data

Here’s how we add data to our database:

  1. Add the workshop data to your QGIS canvas. You can drag and drop the .shp files from your computer’s file explorer into the QGIS canvas, or you can use the Data Source Manager (from the tool bar or the Layer menu. All the data we are working with is vector data. With either option, the Select Transformation dialog is likely to pop up. Select the transformation that fits your data the best.
  2. In the Browser panel, right click on your sfbay.sqlilte database and select Import Vector Layer… The Import Vector Layers dialog will open.
  3. for the Source Layer, select the Flowlines data.
  4. For the Table name, call the table flowlines (all lowercase - nameing tables with all lowercase is convention for databases).
  5. Change the Primary key to id. This will make a unique identifier for each row.
  6. Geometry column is geom
  7. Output CRS is EPSG:3310 - NAD83 / California Albers - here we specify the project the data came with, not the projection you want it to have.
  8. Check over the table preview and confirm that the Type is correct for each of the attribute columns. (Be careful using your scroll wheel in this part of the interface because the dropdowns will scroll if you get close to them, changing the data type.)
  9. Click OK to add your table to the database.
  10. You should now see flowlines added to your Browser panel in your sfbay.sqlite database.

You can repeat this process to add the other two datasets. Call the WBDHU8_Points_SF table centroids and WBDHU8_SF table watersheds, but the rest of the parameters should be the same as those we used for the flowlines table.

Congratulations! You now have a database with files related to the San Francisco Bay’s watersheds!

4.2 Remove Data From the Canvas

Now that our data is in the database, we can remove the files from the map canvas. In the Layers panel, select all of the layers by either clicking and draging your mouse across all the files, or selecting the top one, holding down shift, and then clicking the bottom one. Right click on the selected layers. Select Remove layer. Confirm that you want to remove these layers by clicking OK. This does not delete your data from your computer. It only removes it from the project.