Querying Data

Overview

QGIS allows us to apply filters and run queries on the data, so that we can view - and if necessary save - a subset of the data we have loaded. We will use the DB Manager and Query Builder to run queries on the data.

Run SQL Queries on Data Source

Now that we have the data loaded into QGIS, let's get familiar with the data we are working with by running a few simple SQL queries.

1. Navigate to Database > DB manager

   The DB Manager is the main tool to integrate and manage spatial database formats supported by QGIS (PostGIS, SpatiaLite, Geopackage, Oracle Spatial, Virtual layers) in one user interface. It allows you to connect to an existing database, run SWL queries on it, preview the data in a map, view the schema and attributes.

2. Select Virtual Layers > points

   From here you can select info to see it's metadata and fields. The table tab will show you the layer's attribute table. Preview will show you the data on a map extent.

1. Select SQL Window. Now we're reading to run some SQL queries!

• Query for all data.

SELECT * FROM points_projected ORDER BY timestamp

• Query for number of positions

SELECT count(*) from points_projected

• Query for a specific animal.

SELECT * FROM points_projected WHERE tag_ident = '17373'

Query for how many unique animals we have in our dataset

SELECT DISTINCT (tag_ident) FROM points_projected 

• Query for time span of data.

SELECT  MIN(timestamp), MAX(timestamp) FROM points_projected 

• Query for time span and number of points for each individual.

SELECT  tag_ident, MIN(timestamp), MAX(timestamp), COUNT(*) as num_points FROM points_projected GROUP BY tag_ident

• Query for distance between points for an individaul animal. The lag function allows us to access the previous rows data.

SELECT tag_ident, timestamp, ST_Distance(geometry, lag(geometry, 1) OVER (ORDER by timestamp ASC)) FROM points_projected WHERE tag_ident = '17650';

• Query for total distance grouped by each animal id.

SELECT tag_ident, timestamp, SUM(distance) FROM (SELECT *, ST_Distance(geometry, lag(geometry, 1) OVER (ORDER by timestamp ASC) ) as distance FROM points_projected) a GROUP BY tag_ident;

Query Builder

We may also want to query the geometries on the map canvas. For example, only viewing one collared animal.

QGIS allows us to apply filters and run queries on the data, so that we can view - and if necessary save - a subset of the data we have loaded.

Create a query which returns a single collared animal:

1. Right click on the points_projected layer > Properties > Source

1. Select Query Builder

2. In the query dialogue box, enter the query "tag_ident" = '15827'. The Fields, Values and Operators sections also help you to construct the SQL-like query.

The Fields list contains all attribute columns of the attribute table to be searched. To add an attribute column to the SQL WHERE clause field, double click its name in the Fields list. Generally, you can use the various fields, values and operators to construct the query, or you can just type it into the SQL box.

The Values list lists the values of an attribute table. To list all possible values of an attribute, select the attribute in the Fields list and click the [all] button. To list the first 25 unique values of an attribute column, select the attribute column in the Fields list and click the [Sample] button. To add a value to the SQL WHERE clause field, double click its name in the Values list.

The Operators section contains all usable operators. To add an operator to the SQL WHERE clause field, click the appropriate button. Relational operators ( = , > , ...), string comparison operator (LIKE), and logical operators (AND, OR, ...) are available.

1. Click Test to make sure the query will return some rows, then click OK to run the query against the data

2. Click OK > OK. Your map should now only display data for animal 15827.