Selecting Data with a SQL Query

Rows of the data table can be selected by entering a SQL expression, allowing more systematic and reproducible data selection than may be easily achieved with the mouse. The selected data will be highlighted in both the table and the map.

The Selections/Data query menu item displays the following dialog.

The prompt for a query expression to be used to highlight data on the map and table

The Alt-Q hotkey can also be used from the map interface to open this dialog. The dialog prompts for an expression that is used to select rows to be highlighted on the table and map. The expression should be in the form of a SQL ‘WHERE’ clause. The WHERE clause can use any operators and functions that are recognized by SQLite.

Column names used in the expression are not case-sensitive, but any column name that contains spaces must be entered with underscores in place of the spaces. Any column name that starts with a digit must be entered with a leading underscore.

Data values are case-sensitive. Text data values must be single-quoted. Date and date/time literals that are used in the query expression should be in ISO 8601 format, single-quoted.

The query expression should be entered in the text box in the center of the dialog. Above the text box are buttons that will insert individual SQL elements into the query expression. Clicking on any of these buttons will insert the corresponding element at the end of the SQL expression. To the left of the text box are buttons that affect the SQL entry as a whole.

On the right side of the dialog box is a drop-down list from which any of the table column names can be selected. Note that the column names in the drop-down list may differ slightly from those shown in the data table; for example, they will have spaces replaced with underscores. After a column is selected from the drop-down list, all unique values present in that column will be listed below the drop-down box. Double-clicking on any of these values will copy it to the end of the SQL expression. Numeric values will be unquoted, and text values will be quoted. The arrow button to the right of the drop-down box will copy the selected column name to the end of the query expression.

Below the text box for the query expression is a drop-down list that specifies the action to be taken with the results of the query expression. The available actions are:

  • Replace: All currently selected data are replaced with the results of the query.

  • Union: The results of the query will be added to any previously-selected set of values.

  • Intersection: Only the table rows that are in both the previously-selected set and the newly-selected set will be retained.

  • Difference O-N: The results of the query (New) will be subtracted from any previously-selected set of values (Old).

  • Difference N-O: The set of previously-selected values (Old) will be subtracted from the newly-selected set of values (Nnew).

The Replace action is the default.

When the ‘Apply’ or ‘OK’ buttons are selected, the query expression will be evaluated. If it is invalid, an error message will be displayed. If it is valid, the selected data values will be updated in the table and on the map. The ‘OK’ button closes the dialog, and the ‘Apply’ button leaves the dialog open so that the query expression can be immediately refined if necessary.