Connecting to the Data
- Open ArcView and create a new project. In the project window, choose ‘SQL connect’ from the ‘Project’ menu. If the menu item is not available (greyed out), then ArcView was installed without the SQL connectivity feature and you need to reinstall it.
- You may be prompted for a username and password. This is the same username and password that you would use to access your database.
- In the dialog box, choose the desired database (the name you choose during the ODBC setup) under ‘Connection’ and click on ‘Connect’.
- Once a connection is established, you will see the tables in the database under ‘Tables’. The tables listed in Figure 1 are the tables available in EPA’s 305(b) Assessment Database version 2 (ADB v.2). Once you have selected a table, a list of the fields in the table will appear under ‘Columns’. If you double click on ‘<All Columns>’, an "*" will appear in the ‘Select’ box indicating that you would like ArcView to import all of the fields (Figure 1).
- The 'Where' box allows you to enter an SQL query so that you can select a subset of the data in the database for mapping. You are going to want to join the data you import from the database to your event table for display. An event table will have many records for each surface water entity (because something like an Assessment Unit can be made up of several reaches), so you will need to perform an SQL query that outputs a table where the ID's are unique. This will allow you to perform a one to many join with the event table (if you are confused about the implications of this, click here to read more about table relationships).
In the example in Figure 1, entering the query "Use_ID = 206", selects all of the records in the Category 5 Impairments table with the Recreation Use (from the TN sample data that comes with ADB v.2). If the entire table had been imported, it would have records for all of the uses in the database. We could not join this to the event table because one Assessment Unit can have many uses.
- The ‘Output Table’ box allows you to name the table that will be created in ArcView. Enter a name and click 'Query'.
- ArcView creates a table in your project containing all of the records that meet the query requirements from the selected table in the database.
Figure 1.Suggested input for SQL connect to the ADB v.2 from ArcView.
Back to text.