Building database queries for model searches

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.
   

4. In the DB Identification popup, type “8inch, 8 inch pipes” as the DB Query ID.
5. Click OK.
6. In the DB Query dialog box, expand the Element Type drop-down and select Pipe.
7. Click the Query Builder button (arrow icon) to open the Query Builder.
   

8. 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 statement to select 8-inch pipes is entered in the Query Statement box with the proper SQL format.

9. 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.

10. Click OK to close the Query Validation popup.
11. 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.

12. In the DB Query dialog box, select the 8INCH query.
13. From the toolbar, click Clone.
    

14. In the DB Identification popup, type “8IN_Z4, 8 inch pipes in Zone 4”as the DB Query ID.
15. Click OK.
16. In the DB Query dialog box, select the 8IN_Z4 query.
17. Click the Query Builder button (arrow icon) to open the Query Builder.
18. 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.

19. Click Validate.

The popup should indicate that the query is valid.

20. Click OK to close the Query Validation popup.
21. Click OK to close the Query Builder.
22. 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:

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