Building database queries for model searches

Any referenced datasets can be downloaded from "Module downloads" in the module overview.

Step-by-step:

In InfoWater Pro, a database query is a time-saving tool that searches one or more fields within one or more database tables for specific information. The records that match the query are entered into a database table, domain, or facility.

Queries are built using Structured Query Language (SQL), but even if you do not know the language, you can use the Query Builder to help construct your searches in the proper SQL syntax. In this exercise, you set up a few DB queries to use with a modeling project.

  1. Open the appropriate .aprx file in ArcGIS Pro.
  2. From the ribbon, InfoWater Pro tab, Project panel, click Initialize.
  3. In the Model Explorer, Operation tab, right-click DB Query and select New.
    The Model Explorer, DB Query tool, with the New option called out in the submenu
  1. In the DB Identification popup, type “8inch, 8 inch pipes” as the DB Query ID.
  2. Click OK.
  3. In the DB Query dialog box, expand the Element Type drop-down and select Pipe.
  4. Click the Query Builder button (arrow icon) to open the Query Builder.
    The DB Query dialog box, with the Query Builder button highlighted for selection
  1. In the Query Builder, PIPEHYD (pipe hydraulics) tab:
    1. In the Data Field pane, select PIPEHYD->DIAMETER(in). Note the green check mark appears to indicate the selected field.
    2. Select the Operator = (equals).
    3. Enter 8 in the empty field.
    4. Click Add.

The Query Builder dialog box, with the PIPEHYD tab active and the query statement being built

The query statement to select 8-inch pipes is entered in the Query Statement box with the proper SQL format.

  1. Click Validate.

The query statement is validated. If the query statement is not a valid SQL statement, or if it does not select a single feature, an error is returned. In this case, your query should be valid.

  1. Click OK to close the Query Validation popup.
  2. Click OK to close the Query Builder.

Once you have an existing query, like the one you just created, you can use it as a starting point to create a more sophisticated query by cloning it.

  1. In the DB Query dialog box, select the 8INCH query.
  2. From the toolbar, click Clone.
    The DB Query dialog box, with the 8INCH query selected and the Clone tool highlighted for selection
  1. In the DB Identification popup, type “8IN_Z4, 8 inch pipes in Zone 4”as the DB Query ID.
  2. Click OK.
  3. In the DB Query dialog box, select the 8IN_Z4 query.
  4. Click the Query Builder button (arrow icon) to open the Query Builder.
  5. In the PIPE tab:
    1. Click in the Query Statement box so that your cursor is to the right of the existing statement (following all zeros in 8.000000).
    2. Double-click the .AND. operator. The .AND. function appears in the Query Statement box.
    3. In the Data Field pane, select PIPE->ZONE.
    4. Select the Operator = (equals).
    5. Enter 4 in the empty field.
    6. Click Add.
    7. Review your Query Statement.

The first query selected all 8-inch pipes, whereas this new query selects all 8-inch pipes in pressure zone 4.

The Query Builder, with the PIPE tab active and the query statement being built

  1. Click Validate.

The popup should indicate that the query is valid.

  1. Click OK to close the Query Validation popup.
  2. Click OK to close the Query Builder.
  3. Close the DB Query dialog box.

The queries are now available for use with databases, reports, and selections. They are listed in the Model Explorer, Operation tab, under DB Query:

The Model Explorer, Operation tab, with the DB Query node expanded and the two newly created queries highlighted

Any number of queries can be stored in this area of the Operation tab, and queries can be shared between projects.