Getting Regions and Rivers for USGS Stations

In order to get the rivers that correspond to each station, we had to download more files from USGS, and get them into a readable format. Stations have a river code assigned to them, but we needed a way to read the river codes. This required redoing the Station imports, as we previously didn’t include the RiverCodes for each station. The USGS Station Walkthrough has been updated to reflect this change. Following is the method we used.

1. A list of their codes was obtained from the following URLs:
   http://water.usgs.gov/GIS/regions.html
   http://water.usgs.gov/GIS/huc_name.html
   Where only a few of the regions pertain to our desired data .
2. A short Java program was written to read this data and parse it into a consistent format, specifically, two different text files: One for the Accounting Units (what we’re calling regions) and one for Cataloging Units (what we’re calling rivers).
3. These two txt files were then imported into excel and formatted in the following ways:
   1. The Regions csv gets two columns, the first is RegionCode and the second is a human-readable description.
   2. The Rivers csv gets 4 columns, the first being the RiverCode, the second its description, the third its related states, and fourth, the RegionCode to which it belongs (which is simply the first 5 digits of the RiverCode)
   3. All varchar fields had to be changed so they were encased in ‘ ‘ single quotes.
4. Then new tables were created and inserted into using BULK inserts from these two CSVs.
5. For more information on how the inserts work, refer to the document which was written for retrieving USGS stations (USGS Stations Walkthrough). They are the same process, but had slightly different formatting methods.
6. Finally, a delete query was run to remove stations from our database that didn’t correspond to any of the Rivers or regions.