The last time I wrote about Let’s Fish, I described a simple fishing question that had turned into a nationwide data and decision-support project. At the time, the database was chewing through rivers and streams, the checkpoints were moving, and I was feeling pretty good about the plumbing I built.
That was true at that moment in time, but things went a bit sideways.
Since then, the project has done what ambitious side projects tend to do. It’s grown, wandered off into the woods, found a few problems nobody invited, and come back carrying several more requirements to stock the fire with. Some of that was unnecessary scope creep. Some of it was absolutely necessary. The trick has been figuring out which was which before I spent a month building a mansion where a tent would’ve worked.
Was that really a great idea?
One of my early assumptions was that the United States could be treated as the practical center of the data problem. It wasn’t completely unreasonable. The first major hydrology source was national U.S. data, most of my initial use cases were domestic, and the first geographic boundary work was built around states and counties.
Water doesn’t give a frack about my assumptions in my database schema.
Rivers cross borders. Lakes straddle jurisdictions. Flowlines near Maine, the Great Lakes, the Pacific Northwest, the Gulf, and the southwest don’t stop being real because the first version of the code expected a U.S. state. A record that failed a state match wasn’t automatically bad data. Sometimes it was Canadian. Sometimes it was Mexican. Sometimes it was coastal, offshore, marshy, or sitting close enough to a boundary that two respectable datasets disagreed about where the line ought to be.
That exposed a deeper problem I needed to rabbit hole into. I was asking a U.S. specific question before I’d answered the more basic geographic question. Before assigning a state, the system needed to know the country. Before treating a missing state as a failure, it needed to know whether a state was even the right kind of answer.
The scope creep that wasn’t
Adding Canada and Mexico initially looked like classic scope creep. Why would I build in something to the initial functionality which could reasonably considered future expansion? The project is called Let’s Fish, not Let’s Rebuild the Political Geography of North America. It would’ve been easy to call cross border records edge cases, push them into an exceptions table, and promise to clean them up someday.
That would’ve been the wrong move and let’s be clear that the promise to address them someday might have become never.
Adding country level classification, Canadian provinces and territories, and Mexican states solved a whole category of false failures. More importantly, it forced the platform to use a real geographic hierarchy instead of treating every place like a U.S. state with different cultural norms.
The hierarchy now works in stages. A river or stream is classified by country first. Then it’s assigned to the correct first-level division for that country. That could be a state in the United States or Mexico, or a province or territory in Canada. The stages run together inside one controlled transaction, and the system won’t advance the batch unless the quality checks pass.
That sounds like extra machinery because IT IS extra machinery. It’s also the kind that prevents a quiet logical error from spreading across tens of millions of records. Necessary scope creep is still scope creep. It just earns its keep.
Stranger Waterways
The other major correction has been admitting that a waterbody isn’t as obvious as it sounds.
A lake polygon looks like a waterbody. Fine. I really thought I knew what a lake looked like. A named reservoir usually behaves well enough. Then the data reaches the coast and starts acting like it was raised by wild sea wolves.
Coastal areas, bays, tidal creeks, marshes, wetlands, canals, sloughs, estuaries, and connected shoreline features don’t fit neatly into a lake-versus-river box. Marshlands may contain fishable channels, flooded vegetation, open-water pockets, and polygons that represent habitats rather than a single destination. Coastal polygons can overlap or connect in ways that are geographically valid but useless if the product shows each raw feature as a separate place to fish.
Spending a portion of my childhood on the coast and you would think I knew better.
The reconciliation work also exposed duplicate names, unnamed features, fragmented river segments, neighboring polygons that are probably one practical destination, and giant named systems that are far too large to treat as one result. A river may need to be divided into reaches. A marsh may need to be represented as a complex with access points. A coastal feature may need a different identity rule than an inland lake.
Don’t even get me started on offshore destinations. I’m still spicy about that specific headache.
This was one of the several failures in the original roadmap. I had “identify waterbodies” written down like it was a clean transformation step. It isn’t. It’s a modeling problem involving geography, naming, hydrologic connectivity, public use, and human meaning. A rule that splits too aggressively creates thousands of pretend destinations. A rule that merges too aggressively swallows real channels and access areas into one giant blob. That dog won’t hunt.
When the data says don’t trust the math
The flowline work has moved into production-scale batches. Each batch processes a fixed number of actual rows, assigns the geographic hierarchy, records an audit, advances a cursor, and commits atomically. If something goes wrong, the active batch rolls back while earlier completed batches stay safe.
During the first large runs, another assumption got caught. I expected a cursor to advance by the number of rows processed. Process five million rows and the ending ID should be the starting ID plus five million, right? Nope. The primary-key sequence has legitimate gaps.
The processing was working correctly. The verification script was wrong.
That correction changed how progress and reconciliation work. Percentage complete is based on actual rows examined, not the numerical distance between IDs. The persisted cursor is compared to the final audit record, not to a guess made with addition. It’s a small correction on paper, but it’s exactly the sort of thing that can turn a valid run into a false alarm or, worse, let an invalid run look good.
The scripts need to be treated as a system
The early versions could import data and update records, which felt like progress because it was progress. It just wasn’t enough. Once the flowline table grew to nearly 25 million records, a script that works once isn’t a production process. It needs to stop safely, resume safely, prove what it changed, and not leave half a geographic hierarchy committed when the last step fails.
That led to another chunk of necessary scope of persistent runs, batch audits, checkpoints, transaction boundaries, quality gates, and progress reporting. Every batch run now assigns country, applies the right first-level administrative logic for the U.S., Canada, and Mexico, records the audit, advances the cursor, and commits as one unit. If a fatal quality issue shows up, that active batch rolls back while the previously completed batches stay put.
This wasn’t glamorous. Nobody starts a project because they’re fired up about resumable cursors. But after watching a long-running process chew through millions of records without losing its place, I’m sold. The boring parts are what leads to what’s interesting.
The scope creep hasn’t at all noble
I’d love to pretend every detour was required by the data. It wasn’t. Some scope creep came from seeing what the project might eventually become and trying to design for all of it too early. Species models, stocking feeds, weather, stream gauges, regulations, access points, lunar calculations, scoring, user preferences, and automated dataset maintenance all belong in the long-term system. They don’t all need to be solved while I’m still proving the geographic foundation.
There’s a difference between leaving room for the future and building the future before the present works. I’ve crossed that line more than a few times. The roadmap has been corrected so the work now moves in layers: finish the geographic hierarchy, reconcile canonical waterbodies, build connectivity, then add biological and environmental data on top of something stable.
Lunar data is a good example of scope that belongs, but doesn’t need a recurring external feed. Moon phase, illumination, rise and set times, and related astronomical values can be calculated locally for a date and location. That’s a future feature, not a reason to interrupt a 25-million-row hierarchy run today.
Automated dataset maintenance is different. That’s necessary platform scope. NHDPlus, Census boundaries, Canadian and Mexican administrative boundaries, stocking reports, regulations, weather observations, gauge data, access data, and the rest will all change on different schedules. A mature platform can’t depend on me remembering to download a ZIP file once a year and hoping its schema didn’t change.
The roadmap now includes a dataset registry, automated downloads, checksums, version tracking, delta comparisons, integrity checks, staged updates, rollback, and reporting. Every source will eventually have a known review cadence, whether that’s yearly, quarterly, monthly, weekly, daily, or several times a day. The current hierarchy audit code is the prototype for that broader system. It’s still evolving because the live run is teaching me what the final checks actually need to be.
What’s working
The biggest success is that the architecture is finally behaving like a platform instead of a pile of scripts.
The database currently holds roughly 25 million flowlines. The hierarchy runner has already processed and verified more than 16 million of them in restartable, audited batches, and a much larger run is underway as I write this. The system has resumed the same run across one-batch, five-batch, 25-batch, 100-batch, and 500-batch executions without losing its place.
Country matching, U.S. state matching, Canadian province and territory matching, and Mexican state matching are working together in one transaction. Unknown codes and concurrent-update conflicts are treated as quality failures. Persistent geographic misses are recorded instead of covered up. Progress is based on real row counts. The cursor comes from the actual data. Completed batches remain safe even if a later one fails.
That’s a long way from “I imported a file and the row count looked about right. Isn’t that f-ing great?!”
What’s broken or not finished
The project still doesn’t deliver the final thing an angler would care about. I can’t yet ask it where I should fish Saturday morning and expect a complete answer. The foundation is much stronger, but the product layer is still ahead of me unfortunately.
Waterbody reconciliation isn’t finished. Coastal and marsh features still need careful canonicalization. Unmatched country and administrative records need clustering and explanation. The full hierarchy run has to finish, including the final partial batch. Then the entire run needs a complete reconciliation against the production tables.
After that comes the hard work of connecting flowlines to useful fishing destinations, building upstream and downstream relationships, adding public access, normalizing species, importing stocking records, layering in weather and water conditions, and dealing with regulations that can change faster than a static database would like.
There have also been the plain ol’ failures. migrations that didn’t match MySQL spatial behavior, verification scripts with bad assumptions, boundary datasets that didn’t line up perfectly, and early schemas that treated U.S. terminology as universal. None of those were fun, but each one forced the project to become more honest about it’s data handling.
The road keeps getting longer
The original roadmap was shorter because I didn’t know enough yet. It went something like: import water, add fish, add weather, make predictions. That version fit nicely on a page and left out most of the work.
The current roadmap starts with source management and auditing. It separates raw hydrography from canonical waterbodies. It treats geography and administrative boundaries as versioned datasets. It gives Canada and Mexico first-class accountability instead of calling them exceptions. It includes recurring downloads and delta comparisons so a future data releases can be reviewed and updated without rebuilding the database from scratch.
It also accepts that some data changes daily while some barely changes at all. Weather and gauge readings may update hourly or daily. Stocking and regulations may need weekly or monthly checks. Administrative boundaries and hydrography may be reviewed annually or whenever a new official release appears. Lunar information can be calculated and doesn’t need a permanent external update feed. The maintenance plan has become part of the product, not an afterthought.
That’s more scope, no question. But it’s the kind that keeps the project alive after the first fishing trip.
Where we’re going we don’t need roads
The immediate job is simple to describe; finish the hierarchy run, verify it, and explain the misses. Then I can close this phase without pretending unresolved coastal, border, or marsh records don’t exist.
Next comes canonical waterbody construction and hydrologic connectivity. That’s where the raw map begins turning into recognizable fishing places. In parallel, the temporary audit lessons need to become permanent platform capabilities. Every dataset should eventually have a registered source, expected cadence, checksum, schema validation, before-and-after counts, delta report, controlled update path, rollback strategy, and retained release history.
Only then does it make sense to accelerate into species, stocking, access, regulations, weather, gauges, and scoring. That may sound slower than the original roadmap. In one sense, it is. In another, it’s the difference between building a project that works once and building a platform that can stay useful after the source data changes.
The project has taken longer and gotten so much bigger than I expected. Some of that was self-inflicted. I am a gluten for punishment. Some of it was the data informing me, rather firmly, that my first plan was cute but incomplete.
Still, it’s working. Millions of records are being classified, audited, committed, and resumed without the system having to cross its fingers. The failures are becoming test cases. The edge cases are becoming design rules. The roadmap is less tidy than it used to be, but I trust it a whole lot more.
And eventually it ought to tell me where the fish are.
<End Trans>
Leave a Reply