Navigation:  Query Builder > Query Types >

Union Query - Example

 

A Union Query is a data query that combines similar types of information from two or more tables and is used to find, return, and append data from information from two or more tables based on a set of conditions and common fields.  You can view the tabulated results of the query in the Query Results viewer, and also use union queries queries as sources of data in many modules in HGA, such as plot data in a Well Profile, thematic data in a Map, source data for a contour map or plume in a 3D Scene, or source data for one or more plots in a Plot Collection, provided the necessary fields are present (e.g. maps and scenes require coordinate fields to be present in the query).

A common use case for a union query is to collate time series data from two or more different tables in the database such as precipitation and water levels. The following provides an example of creating a Union Query with guided steps for combining precipitation and water level data from separate tables using the Demo Project that can be used in a multi-axis time series plot.

Creating a Union Query (Example)

In this example, you will build a union query that combines time series data for water level elevations (based on the measured water levels in the "Water Level" table) and precipitation (from the Meteorology table). The information in this query will be structured so that it can be used to plot a multi-series plot in the plot collection.

If the Query Builder tab is not yet opened, select the Query Builder icon from the main toolbar.

In the Query Builder tab toolbar, select the New button , and the following dialog will appear:

 

oSelect a Query Type: Note the default Query Type is the General - we need to change this by selecting the Union Query option.

oProvide a Query Name: WaterLevels and Precip (for this example)

 

 

 

We also have the option to specify the location of the Query. By default, new queries will be stored in the Queries branch of the Project tree - however, you can create folders to organize your queries. You can select the folder where you wish to save your query.  For this example, we we will leave the default which will save the query to the main Queries branch (i.e. not in a folder).

Select OK to create the query.

 

By default the only field selected to be displayed in the query is the Source Name, this is a placeholder field for contain a label for the source of each table. The easiest way to build out the union query is to create the sub-queries which make up the union query:

 

Adding the First Sub-Query: Water Level Time Series Data

In this section of the example, you will construct a sub-query that will return the water level time series data for the W-15 station:

Click the add column [] button at the bottom of the display fields section to add the first sub-query

 

Type the name "WaterLevels" and Click OK to continue

 

This will open a new sub-query to the "Root" query called "WaterLevels". This new sub-query is a general query that will return values to the Root query. Now you can begin adding the desired fields to the "WaterLevels" query:

First, select the "available to map, and plume modules" option

 

This will automatically add several fields to the Display Fields section and will help make the query available in other modules within HGA:

 

Click the expand [] button next to the Monitoring Event data category in the tree of Available Fields section to find the "Water Level" table

 

Click the expand [] button next to the "Water Level" table in the tree of Available Fields section to find the date field

 

Select and (double-click to) add the date field to the Display Fields section of the Query Builder.

 

Select and (double-click to) add the depth_to_water_level field to the Display Fields section of the Query Builder

 

As indicated by its name, the depth_to_water_level field contains water level depths as measured from the top of casing (TOC).  While this information does indicate how water levels change over time, it is less useful than the elevation of the water levels.  In order to convert these values to an elevation, we can use the following relationship: elevation = TOC-depth_to_water_level.  Fortunately, the QueryBuilder and SQL, in general, support simple mathematical statements.

Click in the Expression column in the last row and edit the expression to: [station].[TOC]-[gw_level].[depth_]

 

Click in the Alias column in the last row and change the alias to: WL

 

Click the add [] button at the bottom of the display fields section to add a final field.

 

This will act as a placeholder for the precipitation data - even though the current sub-query will not return any precipitation data, we will need a field with this data so that all sub-queries have the same number and type of fields.

 

Rename the Alias of the new field to Precip and the Expression of the new field to null:

 

 

We now have completed defining the structure of the first sub-query. However; as currently written, the sub-query would return blank information from all stations. In this case, we only want information for one station: W-15. Therefore, we need to add a condition:

Click the expand [] button next to the Description data category in the tree of Available Fields section to find the "Location" table

 

Click the expand [] button next to the "Location" table in the tree of Available Fields section to find the "Station Name" field

 

Select, drag and drop the Station Name field onto the Conditions section of the Query Builder

 

Set the Operator to =

 

Set the Value to W-15

 

We now have completed WaterLevels sub-query, which should appear similar to the following:

 

Return to the parent Root query, by clicking on it in the Hierarchy section of the query builder (shown above)

 

 

Adding the Second Sub-Query: Precipitation Time Series Data

In this section of the example, you will construct a sub-query that will return the precipitation time series data for the WS-1 weather station:

Add the second sub-query by clicking the add column [] button at the bottom of the display fields section

 

Type the name "Precip" and Click OK to continue

 

Select the "available to map, and plume modules" option to add the ID, Name, X, and Y fields to the subquery

 

Click the expand [] button next to the Monitoring Event data category in the tree of Available Fields section to find the "Meteorology" table

 

Click the expand [] button next to the "Meteorology" table (under the Monitoring Event category) in the tree of Available Fields section to find the Date field

 

Select and (double-click to) add the Date field to the Display Fields section of the Query Builder.

 

Select and (double-click to) add the Total Precipitation field to the Display Fields section of the Query Builder

 

Rename the Alias of the new field to Precip

 

Click the add [] button at the bottom of the display fields section to add the placeholder field for the null water level data.

 

Rename the Alias of the new field to WL and the Expression of the new field to null

 

Select, drag and drop the Station Name field from the Location table (in the tree of available fields) onto the Conditions section of the Query Builder

 

Set the Operator to =

 

Set the Value to WS-1

 

The Precip sub-query is complete and should look similar to the following:

 

Return to the parent Root query, by clicking on it in the Hierarchy section of the query builder (shown above)

 

Mapping Fields in the Root Union Query

In this section, you will combine the sub-queries by adding all of the fields from the sub-queries and mapping the sub-query fields to in the Root union query.

Select the "available to map, and plume modules" option to add the ID, Name, X, and Y fields to the Root query

 

Click the add [] button at the bottom of the display fields section three times to add three fields

 

Rename the newly added fields to "Date", "WL", and "Precip"

 

The list of fields in the Display Fields now contains all of the fields that were created in each of the subqueries.  The Display Fields section of the Root query also contains one column for each of the sub-queries: "WaterLevels" and "Precip". These columns are used to map/assign fields from each sub-query to the fields in the Root query:

Click the cell in the ID row and the WaterlLevels column of the Display Fields table and select ID from the list of available fields (from the WaterLevels sub-query)

 

Continue to map all fields for both the WaterLevels and Precip columns until your mappings appear as follows:

 

 

The full union query is complete and ready to executed and/or used in other modules.

First save the query so that it can be reused, by clicking the save [] button

 

Run the Union Query to review the results by clicking the execute [] button

 

The Query Results view will appear with values similar to the following:

 

For further information on what can be done with the query once it has been executed and the results are being displayed - please see the section on the Query Results section.

The results include a column for the RowID and for all of the fields that were added in the Root union query. Note that in rows where the Source Name column has a value of WaterLevels, there is a value in the WL column and blank (null) value in the Precip column; similarly, in the rows where the Source Name column has a value of Precip, there is a value in the Precip column and a blank (null) value in the WL column.  The Source name column represents which subquery returned the values for each row. Given this field, a more streamlined way of generating the table might have been to add both the WL and Precip values to a single "Value" column; however, in order to plot these data separately, they must be in a distinct field.

 

Generating a Multi-Series plot using a Union Query

In this section, you will create a time series plot with the water level and precipitation data plotted on different Y-value axes:

Open a new plot collection by selecting the Plot Collection icon from the main toolbar.

 

Click the add plot [] button from the Plot Collection toolbar

 

Select Time Series

 

The Chart Settings window will appear

 

Click on the dropdown menu at the top of the window (under the Source label) and select Query as the data source type

 

Click the first Select... Button to select the Query to be used in the plot

 

Select the WaterLevels and Precip union query you made above:

 

 

Click OK

 

Click the second Select... Button to select the Data Source to be used for the series names:

 

Select the Source Name field:

 

 

Select Triangles as the Symbol type

 

Click the add parameter [] button in the Parameters toolbar

 

In the Parameter column of the Parameters table, select "Date", "WL", and "Precip" as the parameters for the axes by double-clicking in the appropriate cell and selecting the appropriate values when prompted

 

Set Axis of the last row (with the Precip parameter) as a Y2-axis

 

Click Apply

 

The chart settings window should appear similar to the following:

 

The plot will appear similar to the following:

 

 

The range of the precipitation data spans a longer time period than that of the water level data.  You can adjust the range of the Time (x) axis:

Click on the Axes tab in the Chart settings window

 

In the scale section of the axis settings, deselect the checkbox next to the "Automatically generate rate to fit data"

 

Set the Min value to Jan 1, 1996 and the Max value to Jan 1, 1997

 

Set the Major tick interval to 3 months and the minor tick interval to 1 month

 

Click OK

 

 

Your plot should appear similar to the image above.  Feel free to make additional formatting changes. Note that you could have also added additional constraints to the data in either of the subqueries.  As an exercise, try to constrain the data using the query rather than the plot settings.

 

 


Page url:https://www.waterloohydrogeologic.com/help/hga/index.html?queries_union_query.htm