EIU QGIS Workshop

QGIS is built for relational databases. It handles SQL queries elegantly and can read many common database formats. The simplest format, great for getting started, is Spatialite. A lightweight database that resides in a single file, Spatialite is portable and hassle-free.

In this exercise, we’ll use Spatialite to examine perpetrators (“perps”) and where they operate. Let’s get started.

1. Load data into QGIS

• Load the crimes data into QGIS from before if it isn’t already loaded.
• Load perps.csv into QGS 

  This is a plain table with no xy coordinates, so select the option for No geometry.  

2. Create an empty Spatialite database

• Open the Browser panel by selecting View >> Panels >> Browser

• Right-click on Spatialite and select Create Database

• Save to your workshop folder as sqlite

3. Connect to your database in DB Manager

• Make sure the DBManager Plug-in is loaded (Plugins >> Manage and Install Plugins)
• Open DBManager by selecting Database >> DBManager >> DBManager
• Right-click on Spatialite/Geopackage and select New Connection
  • Locate and open the ChicagoCrime.sqlite file
• Expand the Spatialite/Geopackage entry in the tree. You should see the sqlite database:

• Make sure you are really connected. I find this usually does not go smoothly, as you can see here:

Honestly I’m not sure why this happens, but try the following (multiple times if necessary):
                1. Click the refresh button          
                2. Collapse and expand the tree                    
                3. Right-click on ChicagoCrime.sqlite  and click Re-connect

Be persistent! Eventually you should see confirmation that you’re connected:

4. Load crime and perps data into the database

• With the ChicagoCrime.sqlite  database selected, click the Import Layer/File button

•  Input the Perps table, click Update options. The output table should automatically be named Perps as well

• Leave the options alone and click
• Click the Refresh button to see the table in your database.
• Repeat to import the crimes table

5. Create a query to find out how many crimes were committed by each Perp:

• In DBManager, open the SQL window by clicking the SQL Window button or selecting Database >> SQL Window
• Enter the following query exactly:

• Click Execute to run the query. You should see a list of perpetrator IDs and a count of the number of crimes they committed:

• Click Create a View:

• Name the view PerpCounts:

6. Create a second query to find all Perp who committed at least 20 crimes:

• Click the SQL Window button again to start a new query
• Enter the following query:

• Execute the query:

• Create a view and call it RepeatOffenses.

7. Last, we’ll create a query to identify all repeat Narcotics offenses and then show the query on the map as a new layer:

• Start a new query
• The SQL is:

• Click Execute:

• Click Load in New Layer. You’ll see the following options:

• Set the ID column and geometry column and give the layer a name, then click Load now!
• Switch over to your map. You should see the locations of the repeat Narcotics offenses:

Note: The Perps data used in this workshop is fictional. The above map is not representative of actual repeat Narcotics offenses in Chicago.

For more information on using SQL in Spatialite, see the following website:

https://www.gaia-gis.it/gaia-sins/spatialite-cookbook/