Monthly Archives: September 2020

More SQL Tools in RockWorks 2020, the Filter and Select Borehole Tools

Posted on by

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.

View|Filter Boreholes

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.

View|Select Boreholes

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.

Using Optional Fields and Playlists to Enabled Borehole Groups in RockWorks 2020

Posted on by

Did you know that you can use optional fields in the RockWorks Borehole Manager Database, along with SQL statements, to easily enable groups of boreholes within a project?  We’ll describe how to do this in the post below.

RockWorks Optional Fields

The Location table in your RockWorks borehole database is really flexible – you can add any number of customized data fields to this table. You can even use a set of industry templates that are built into the program. These optional fields can be displayed in the Optional tab or in user-created tabs.

We’ll add a new “Groups” field to the database by clicking on the Optional Fields button, and then clicking New Field.  We’ll then populate the field with 1s and 2s to represent two groups within the project.

The RockWorks Playlist

The Playlist tab in the main RockWorks program window is used to automate tasks that you do frequently in the program. Let’s say that you need to quickly select different groups of boreholes as you are working in the program.  This can be done by creating and using a Playlist that includes SQL statements.

To add a SQL item to a Playlist, go the Add menu in the Playlist tab and choose SQL Statements.  Here are some examples of SQL Statements that can be used in this project:

  • Item Title:  Disable All – This will disable all boreholes in the database.  If you are trying to enable just a single group of boreholes, you would want to run this before running any other SQL Statements.

UPDATE [Location]
SET Enabled = 0

  • Item Title:  Enable Group 1 – This will enable all of the boreholes with a 1 in the “Groups” field in the Location Table.

UPDATE [Location]
SET Enabled = 1
WHERE (“Location”.”Groups” = ‘1’)

  • Item Title:  Enable Group 2 – This will enable all of the boreholes with a 2 in the “Groups” field in the Location Table.

UPDATE [Location]
SET Enabled = 1
WHERE (“Location”.”Groups” = ‘2’)

This is what the Playlist looks like.  You can see that the different “items” can be turned on and off, and the Playlist can then be processed through the yellow button at the bottom of the window.

To enable only Group 1 in the database, uncheck Enable Group 2, and hit the Process Playlist button.  Group 1 will be the only boreholes enabled in the Borehole Manager database.

To enable the boreholes in Group 2, Disable All and Enable Group 2 should be checked on, and Enable Group 1 should be checked off.

Keep in mind that these SQL statements do NOT have to be limited to Optional Fields.  SQL statements can be created to query other Location fields (Easting, Northing, Elevation, Total Depth, Etc), Polygon tables, downhole data such as Lithology, Stratigraphy, I/P/T Data, or really any type of data that is stored in the Borehole Manager Database. 

Also, this is just one very limited example of how the RockWorks Playlists can make your life easier.  Playlists can be used to automate the creation of models, cross-sections, maps, or anything else that you would normally do through the RockWorks interface.  Playlists are available in the Basic, Standard and Advanced versions of RockWorks 2020.  If you haven’t taken a look at them yet, you should definitely check them out! Click here for more information about Playlists in RockWorks 2020!