Crosswalk EPA watershed numbers (old) with USGS WBD

[ericnost]

An example of the problem:

The 5 digit Zip code for Madison Wisconsin is 53703.

That intersects with the following 10 digit HUC 0709000206 from the following data source (copied to the Stony Brook University server): https://prd-tnm.s3.amazonaws.com/StagedProducts/Hydrography/WBD/National/GDB/WBD_National_GDB.xml

We do not store ECHO facility information in a spatialized database (one indexed on FAC_LAT and FAC_LONG fields) meaning that we have to do an attribute query for facilities rather than a spatial one.

EPA's ECHO_EXPORTER file provides two relevant fields for this attribute query:

FAC_DERIVED_HUC - The 8 digit Watershed Hydrologic Unit Code (HUC) derived from the facility coordinate.
FAC_DERIVED_WBD - The 12 digit Watershed Boundary Dataset code derived from the facility coordinate.

Unfortunately, these are not always in sync with USGS's current WBD model.

For example, to find the facilities in the 0709000206 watershed, we would need to trim the HUC to 07090002 and then match it with the FAC_DERIVED_HUC field in ECHO_EXPORTER.

However, while for USGS 07090002 is indeed the 8 digit HUC covering Madison, WI, for EPA 07090002 refers to some other nearby 8 digit watershed, returning facilities elsewhere in Wisconsin.

This problem also occurs in New York State, and we deal with that on an ad hoc basis in the watershed notebook.

Do we know of any good crosswalks between old HUC definitions, which the EPA's seem to be, and new ones? We want to be able to take any 8, 10, or 12 digit HUC from the USGS database and match it with the 8 digit version EPA is thinking of.

Perhaps the simple solution is just to turn the ECHO_EXPORTER table into a spatially-indexed one.

[ericnost]

Concretely, compare: https://www.sciencebase.gov/catalog/item/5a58a506e4b00b291cd68bfb USGS National Hydrography Dataset Best Resolution (NHD) for Hydrologic Unit (HU) 8 - 07090002 (published 20210905) (what we pull from USGS)

with

https://water.usgs.gov/wsc/cat/07090002.html (what EPA uses)