Database Queries InfoSewer
A query of a database allows the user to extract a record (or records) of a data using logical statements on a field (or fields) stored within a specific database. With InfoSewerH20Map Sewer, the user is able to select network components and related data by creating logical query statements through the user-friendly DB Query feature.
How Do I…
Using Queries in InfoSewerH20Map Sewer
DB Queries are a time-saving tool that can be used for:
- Activating a portion of the network for simulation, typically as a facility set associated with a custom scenario (see Facility Manager).
- Creating a domain to highlight a subset of network components (see Domain Manager.)
- Restricting the database records that are available for editing using the Database Editor.
- Customizing the map display by color-coding network components (see Query Sets).
- Creating Selection Sets (see Selection Sets)
- Output Relates (stored output result data) can also be associated with DB Queries which are important when creating Query Reports.
- Restricting model results to identify network components whose modeled performance (flows, velocities, etc.) meet or do not meet user specified criteria (e.g., pipes where the d/D ratio exceeds 0.90) using the Output Report Manager.
You may develop your own query statements or use one of the pre-defined H2OMAP Sewer special queries to select portions of your distribution system model.
To create a database query, from the Operation Data tab of the Control Center, select DB Query and click on the New icon at the top of the Control Center dialog box. This will initiate the DB Query dialog box as shown below.
Clicking on the New icon will create a new DB Query folder which will contain information from the following as well as the query statement.
- Element Type – Select the InfoSewerH20Map Sewer data element for which a query statement is to be made on the elements database.
- Disable Output Relate(s) – Check this box to prevent output relates from being associated with a database query.
- Query Statement – Shows the current query statement associated with the highlighted database query.
- Output Relate – From this tab the user is able to associate an output relate with a DB Query. Click here to learn how to create an output relate. Click here to learn how to associate an output relate with a DB Query.
To build a query statement, click on the query builder icon to see the following dialog box.
Differing database tabs will appear depending on the InfoSewerH20Map Sewer data element (pipe, manhole, etc.) selected.
To create a query statement, you must first decide which database field the statement will depend upon. This is done by clicking on the appropriate database tab at the top of the query builder dialog box and selecting the desired database field. Next, specify the desired function and operator to build the query statement. The last step is to type in a number or text value in the box just above the Add button.
Once the query statement is built, click on the Add button then click on the Validate button to ensure that you have created a valid query statement. InfoSewerH20Map Sewer will issue an error statement if the query structure is incorrect. Some examples of valid query statements are provided below:
- PIPE->ZONE = ‘1’
- PUMP->ZONE > ‘PROPOSED’
- PIPE->ZONE = ‘1’ .AND. PIPEHYD->DIAMETER >= 10
- (UPPER(PIPE->DESCRIPTION) = ‘MAIN STREET’ .OR. UPPER(PIPE->DESCRIPTION = ‘ELM STREET’)) .AND. YEAR(PIPE->YR_RETIRE) > 1998
(Note: The last query statement finds all pipes on either Main Street or Elm Street and, from just those pipes, finds only those pipes whose designated retirement date is after the year 1998. The UPPER function converts all descriptions – street names in this example – to upper case and the YEAR function extracts the year from a more detailed date such as 2/15/98.)
The DB Query function of InfoSewerH20Map Sewer allows the user a great degree of flexibility in managing the data contained within a project. Mastering query statements and how they control data ensures greater success when using InfoSewerH20Map Sewer.
An example of having output relates associated with a DB Query could answer a question like, “Where are the pipes in my system that have a 12″ diameter and a flow rate greater than 1.0 cfs at 2:00 in the afternoon?”
To associate an output relate with a database query, highlight the desired DB Query at the top of the dialog box and select the Output Relate tab from the DB Query dialog box to see the following:
The drop down box at the bottom of the dialog box contains a list of all of the currently created Output Relates.
Add Relate – This icon will associate the selected output relate to the highlighted DB Query.
Remove Relate – This icon will remove the associated output relate from the selected DB Query.
New Relate – Clicking this icon will allow the user to create a new output relate to be associated with the DB Query.
Edit Relate – Allows the user to edit the contents of the selected output relate. This feature is good in that it allows quick edits of output relates to be associated with other output data from other scenarios.
Tying output relates to DB Queries enable the user to see model results (graphs and reports) in a whole new light.