Our previous RockWare Blog posting introduces how SQL statements can be embedded into the new Playlists tool in RockWorks 2020. We thought we would cover another useful SQL statement tool in this post.
The Filter and Select tools under the View menu in the Borehole Manager can be used to query all boreholes in the current project. We think of the Filter program as a “Simple” querying tool that is used in just a single step, while the Select program allows for more “Complex” queries where various filters can be applied one after another. Here is a description of each tool.
Use the Filter tool to query all boreholes in the current project using one or more filters – such as a rectangular map area, specific stratigraphic formations, or specific Location table fields – and disable those boreholes that don’t meet all of the criteria. This is a basic “AND” type of filter: each of the boreholes for which every criterion is true will be enabled and the remaining boreholes will be disabled.
As an example, let’s say that you want to determine which boreholes fall within an excavation polygon and also contain “Clay” in the lithology table. You can set up both of these queries at the same time using these settings.
Step 1: Polygon Filter query: In this case, we’re connecting to a Polygon Table in the database (Polygon_02).
Step 2: Lithology Type Query: I’ll check on the Lithology Type option and choose Clay from the pull- down menu.
When I click Apply, the program will show the Boreholes that meet the criteria. Boreholes that are disabled in the database are plotted as light gray. Boreholes that are enabled (which means they meet both criteria) are darker.
Note that when using the Filter program to query multiple types of data, the querying is done in a single step. So, you would set the Polygon Filter and the Lithology Filter, before clicking Apply.
Use the Select Boreholes tool to query a subset of boreholes in the current project using one or more filters, and either enable or disable those boreholes. This is similar to the Filter option, except that the Select Boreholes tool allows successive filters to be applied, and the enabling/disabling will apply only to the boreholes meeting the filter parameters, and no others. So, while the Filter tool works as an AND filter, the Select Boreholes tool can function as an AND or OR filter.
The Select Boreholes tool looks similar to the Filter tool, but offers more options. As I mentioned above, the program allows you to Enable or Disable boreholes based on the query criteria. The update to the Borehole Status can be applied to boreholes inside or outside a rectangular or circular region or a Polygon. Additionally, the program offers an option to Enable or Disable All of the boreholes within the project before applying the query.
As I mentioned, the Select tool allows you to apply one query after another. As an example, let’s say that you want to identify boreholes that contain either Clay or Silt, and fall outside the excavation polygon.
Step 1: Starting with all the boreholes disabled, enable boreholes that contain Clay: I’ll click on the Disable All button, and then set up the Lithology Type Query. Clicking the Apply button results in a group of boreholes enabled in the preview map on the right side of the window.
Step 2: Enable borehole containing Silt: I’ll go through the same process, but with the Silt Lithology Type selected. Notice that a couple more boreholes are now enabled. We now have a group of boreholes enabled that contain Clay OR Silt.
Step 3: Disable Boreholes that fall inside the Polygon: To do this, I’ll use the Polygon Filter, but choose to disable the boreholes that fall Inside the Polygon. After I click Apply, borehole inside the Polygon are disabled.
Note that when using the Select Boreholes program and stringing multiple queries together, you will want to click the Apply button to process each step. If you turn on more than one type of filter before clicking Apply, the query will be processed as an AND query.
Show Summary and SQL Statement buttons
The last thing we wanted to mention is that you can get a summary of the Query the program is using, or view the SQL Statement, using these two buttons. For users of RockWorks Advanced, you can even run your own SQL Statements by entering them into the SQL Statement window. As mentioned in our Help files, the principle of Caveat Emptor strongly applies to editing or creating your own SQL statements. In other words, back up your database BEFORE running your own SQL commands, unless you consider yourself to be an infallible SQL wizard.
And, for those of you interested in automating processes in RockWorks, don’t forget that SQL statements can be included in RockWorks 2020 Playlists, and well as RockWorks 2020 Command Scripts.