WaterLevels.org

One of my more favorite projects, I wanted an easier way to find out what the current water level was for a handful of bodies of water near me. I could bookmark the page(s) hosted by the USGS, but wasn’t ever really a fan of how they looked on mobile.
Data Aggregation
WaterLevels.org aggregates data from U.S. Geological Survey, U.S. Bureau of Reclamation , U.S. Army Corps of Engineers , The National Weather Service, and the California Data Exchange Center to produce a unified interface that looks the same regardless of the location and its source type.
Everything someone would want to know in the moment is readily accessible on a site’s page including flood information, the location, and the measurements available. For most locations the measurements on offer are the water height (in whatever relative format provided) and flow rate.
Aside: some locations will share their gauge height relative to the bottom of the body, ex a river, some will be absolute against sea level. For measurements against sea level, some provide a modern measurement baseline (also known as NAVD 1988 ), but some will only provide based on older NGVD 1929 -adjusted measurements which can be up to a foot off.
While I didn’t want the site to be an authoritative source on life-altering events like floods, I did want to capture that data and display it relative to the measurements I was recording and archiving, so bodies with flood status have representation in their graphs.
This came about because of a flooding event near me a couple of years ago that I wanted to track, but found myself having to do so on a different site than where I’d go for water level data. By putting the two sources in one spot, I believe it’s nominally more useful.
Computing Challenges
A downside to all this data processing is computational time and effort. I’ve found myself having to parallelize a lot of this work. Each data-based exercise does not run along side any other, generally speaking. For example, when I retrieve the latest measurements from the USGS, that worker and job queue is entirely separate from the one handling retrieving the latest flood stage information. Because the latter is slow, I’d eventually end up with a plugged queue. Monday through Saturday I am triggering 15,000 to 25,000 jobs per day. Data collection is a small percentage of the bunch. The biggest effort is backfilling historical data and filling in data gaps. Sunday is the exception wherein I only trigger data collection.
Aside: some stations lag in reporting their most recent metrics, so I need to go back at a future date and ensure those holes are plugged. 95/100 times those data points are eventually made available and I can fill them in.
The USGS has a relatively strict API rate limiting system in place, wherein an API key can only request a hard limit of resources per hour. They don’t specifically prohibit only having one key, so I have a handful that do specific things (ex: one for hourly syncs, one for filling holes), but that still means I’m limited on how often I can do any of those things. This guarantees there’ll always be a lot of work to do.
Eventually I’d like to get decades of historical data in place to do long-term trends and patterns, but for now I’m running at about 3-5 years back depending on the location.
Storage
With all this data comes the question of where to put it? Short-term data (35 days) is readily available in PostgreSQL. Anything older than that is shuttled off into Cloudflare R2 and sharded by year then by station, then by measurement. This makes it cheap to store these small data points, and quick to retrieve and process. As I type this the site holds about 150 million data points at present. A station has 1-3 measurements with a rare handful of cases having 4. That creates about 500,000 data points per day for a single station.
I found keeping it all in PostgreSQL was more expensive than it was worth and retrival and writing of older data wasn’t any slower in R2. With 150 million measurements I find the R2 bucket holding the archive data to be about 100 MB in size and cost less than a dollar per month (purely because of operation counts at this point in time.)
I imagine I could have re-worked how I was storing in PostgreSQL but it felt complex for no gain. I’ve considered ditching PostgreSQL altogether and storing everything in R2 because that would save me $20 a month.
Connected Stations
Another favorite feature of mine is showing other stations nearby and showing, when relevant, stations upstream and downstream from the one you’re looking at. That information comes from the USGS Network Linked Data Index (NLDI) and is a neat trick where USGS will describe all the “connected” stations along that river or stream. Any station within 80 kilometers that’s “connected” to the current station in either direction is displayed as an upstream or downstream station and also linked on the Related Stations map.
More detail on how data is used and managed is found on the Data Disclosures page .