Query Filters

Query filters can be used to select or view a subset of rows in a table. When used on a collar table, this amounts to selecting collars. When used on an interval table, measurement intervals are selected. Query filters can be used to model using a subset of data and to filter information displayed in the scene window.

A query can be created for any type of table in a project, including GIS data, points data and structural data.

When a query filter has been defined for an object and the object is displayed in the scene, the query filter can be applied by clicking on the object in the shape list and selecting the query filter in the shape properties panel:

For a drillhole data table, this displays only the data selected by the filter. The trace lines remain displayed in the scene. To filter trace lines, tick the Filter trace lines box:

Query filters defined for collar tables are available to all other drillhole data tables.

The rest of this topic describes how to build queries. It is divided into:

Creating a New Query Filter

To create a query filter, right-click on the required table in the project tree and select New Query Filter. The Query Filter window will appear:

Type the required criteria into the Query box, using the query syntax described below. Press Ctrl-Enter for a new line in longer queries. Click the ... button for more advanced query building options, which are described in Building a Query and Advanced Queries below.

Assign the filter a name and click OK to save it. It will appear in the project tree under the table to which it applies. To apply a query filter to the scene, select the object in the shape list, then select the filter from the Query filter list in the shape properties panel:

In this case, because the query filter was created for the collar table, it is also available for the lithology table.

Leapfrog Geo Query Syntax

The Leapfrog Geo query syntax is based on the WHERE clause of the Structured Query Language (SQL) with some restrictions:

  • Unary operators are not allowed
  • SQL functions cannot be used
  • The SELECT statement should not be used (as in 'holeid in (SELECT holeid FROM ...)')
  • Following statements are also prohibited: CASE, WHEN or MATCH

There is also one main SQL extension:

  • IN and NOT IN will accept a partition group for the value list. E.g. 'zone IN layers.weathered' where "layers" is a partition of the "zone" column that has a group called 'weathered'.

These are some examples of valid Leapfrog Geo query statements:

  • holeid in ('m-001', 'm-002')
  • holeid not in ('m-001', 'm-002')
  • holeid not like 'MAR%'

To enter a query that contains an apostrophe, enter two apostrophes. For example, the following query will result in an error:

Adding the second apostrophe results in a valid query:

The Query box allows the following shortcut queries:

  • Typing a single word will match all hole-ids starting with that string. For example, typing "MAR" is a shortcut for "holeid like 'MAR%'"
  • Typing a comma-separated list of words will match all hole-ids that exactly match the given words. For example typing "M001, M002" is a shortcut for "holeid in ('M001', 'M002')".

Building a Query

Many common queries can be built using the Build Query window:

This window aims to be easy to use rather than comprehensive in its support for the full Leapfrog Geo query syntax. For more advanced queries, click on the Advanced button. See Advanced Queries for more information.

To add a criteria, click Column to view a list of columns available in the table. Select a Test and enter a Value. What can be entered for the Value depends on the type of column and test selected.

Quotes are not required around text values entered in the value column as they are in SQL. Leapfrog Geo will add quotes and brackets to the value "MX, PM" to make a valid SQL list of strings "('MX', 'PM')" when the query is saved.

Click on the Apply button to apply to query to the context in which the dialog was opened.

Use the Delete button to delete the criteria in the selected row and the Add button to add a blank row.

Advanced Queries

The Advanced Query window provides a powerful and flexible query building using the Leapfrog Geo query language.

Enter the query in the Criteria to Match area on the left. The query is displayed as a tree structure with AND and OR terms as the branch nodes and conditions as the leaves. Use the Delete button to delete a row from the query and the Add button to add a blank row. The Check button can be used at any time to check if the current query statement is valid. Below the buttons is a box showing the raw SQL form of the query.

Except for the arrow buttons, the buttons down the middle of the dialog are used for entering values into the query. The arrow buttons are used for moving the currently selected criteria to a different position in the query. The Date, List and Value buttons will open a builder dialog for the column selected in the current row. If there is no column found or the column is of the wrong type, an error message is displayed.

The tree on the right contains all the columns available to the query. Double-click on a column name to insert it into the query.