The last time I wrote about Lets Fish, the map was giving me hell. Rivers ignored borders, coastal polygons behaved like they were raised by feral sea wolves, and my tidy little fishing project had become a North American geography lesson with a database attached.
I thought I was deep in the plumbing then. That was adorable.
Since then, the project has moved past importing data and into proving that the data can survive being transformed, interrupted, restarted, audited, replaced, and eventually updated again without the whole thing turning into a digital fish kill. The work is slower now because I stopped accepting “the row count looks about right” as evidence. Every major step has to show its work, retain its lineage, and leave production alone until the complete result passes inspection.
That’s why my computer is currently spending days creating thousands of files that no human being will ever open for fun. The checkpoints have awakened, and apparently they demand tribute in disk space.
To catch you up if you’re new here
The short version is that Lets Fish is supposed to help answer a practical question: where should I fish, for what, and why does that answer make sense?
The longer version includes waterbodies, rivers, streams, species, stocking records, public access, boat ramps, regulations, weather, stream gauges, reservoir levels, tides, lunar conditions, seasonal behavior, fishing methods, lures, bait, and enough source documentation to prove the recommendation didn’t come from an algorithm licking it’s finger and holding it in the wind.
The platform isn’t meant to be a single frozen database. Source data changes. Agencies correct records. Regulations expire. Ramps close. Stocking events arrive on different schedules. Rivers do whatever the hell rivers want. It’s becoming a maintained fishing intelligence system that can detect new source releases, compare them, prepare replacements, explain the differences, and activate a new generation only after the evidence passes.
The eventual experience should feel simple. Pick a species, a place, a date, and/or the way you want to fish. Get useful destinations with current conditions, legal constraints, access information, and an explanation. All the machinery I’m building now exists so that simple answer isn’t confidently wrong.
The boring shit became the product
The biggest change came because I wanted a clean checkpoint before the waterbody construction. Instead, it found a gap large enough to park the Millennium Falcon in.
The system built so far already knew how to register immutable source releases. It could verify files, checksums, schemas, and active release pointers. What it didn’t yet guarantee was that every production record could be traced through a declared transformation into a specific prepared generation. Activating a source file didn’t magically prepare its contents. Some update paths could preserve stale derived fields after geometry changed. Existing records from corrected publisher feeds could be skipped instead of reconciled. The Python models and the SQL spatial schema were not in complete agreement.
None of those problems make for a sexy demo. They do make for a future disaster if I stack species, stocking, access, regulations, and weather on top of data whose history can’t be reconstructed.
So, the project grew teeth. Every dataset family now needs a versioned preparation profile, immutable inputs, staged output, record lineage, reconciliation counts, dependency handling, validation evidence, idempotent reruns, controlled activation, and rollback. That applies to polygons and flowlines, but also to stocking events, forecasts, regulations, taxonomy, surveys, API feeds, and whatever future me decides would be cool at two in the morning.
I wanted a fishing recommendation engine. I accidentally started building mission control. At least nobody has asked me to calculate a lunar launch window yet. Lunar fishing conditions are still on the roadmap, though, so give it time.
The waterbody boss fight
The first production scale proof used the US Geological Survey waterbody data initially (there will be some lessons learned from that choice which you’ll see soon). The source contained 7,036,369 publisher occurrences. The preparation system read every one of them in restartable batches, retained the original publisher feature IDs, and committed 704 checkpoints without changing the active production tables.
Then came the part that earlier versions tried to do too soon: global duplicate resolution. A duplicate may appear millions of records away from its partner, so resolving each batch independently can quietly create two canonical objects for the same permanent feature. The new process keeps every occurrence until the complete scan is available, sorts the whole set, groups matching permanent identities, and chooses the highest publisher feature ID as the representative. It doesn’t union geometry and hope for the best.
Those 7,036,369 occurrences became 7,031,662 canonical waterbodies. Every contributing occurrence stayed attached as lineage. The result was published into 256 deterministic partitions with separate prepared data and audit artifacts. The final retained waterbody output is about 20.9 billion bytes.
The temporary assembly workspace was much larger. Once publication was independently verified, I removed exactly that workspace and recovered about 56.9 billion bytes. Nothing else moved. Nothing else disappeared. I’ve learned enough painful lessons about backups to treat deletion like defusing a bomb in a movie: identify the wire, verify the wire, ask whether I really mean that wire, and only then cut the damn thing. I don’t want a recreation of Lethal Weapon 3 and “Rodge grab the cat” on my hands.
Twenty-five million flowlines enter
Waterbodies were the proof. Flowlines are the industrial scale version.
The source contains 24,943,827 flowline occurrences. The complete source scan is finished. It produced 2,495 contiguous checkpoints, 638,720 compressed fragment files, and about 16.3 billion bytes of durable occurrence data. Every occurrence identity and publisher feature ID was verified as globally unique.
After all that, the grand duplicate count was 25. Only 25 extra fucking occurrences among nearly 25 million records. It would have been easy to call that statistically irrelevant and move on. It would also mean the platform has two answers for twenty five permanent features and no honest rule for choosing between them. Small bad data is still bad data.
The known duplicate proof did exactly what it was supposed to do. Two occurrences of the same permanent flowline appeared far apart in the publisher range. The resolver kept both feature IDs, selected the higher one as the representative, retained the conflict evidence, and produced one canonical entity. No geometry union. No early guess. No Jedi mind trick where I wave at the audit and say these are not the duplicates you are looking for.
The machine is now assembling the full flowline result. It’s reading the durable fragments, building 2,495 sorted staging chunks, combining them through 82 bounded merge groups across three passes, and then writing as many as 2,495 restart-able SQL spool transactions. When that finishes, 24,943,827 publisher occurrences should become exactly 24,943,802 canonical flowlines distributed across 256 final buckets. I say should here yet it’s still running and I won’t know until it’s done. Are your eyes glazed over yet?
Why this takes so damn long
One does not simply import twenty five million flowlines.
The current job isn’t running one clever query and waiting for the SQL to finish. I’m not that clever. Most of the work is deliberately outside the production tables. For each checkpoint, the runner opens and verifies compressed fragments, reconstructs records, validates hashes and counts, sorts them into deterministic order, writes a durable chunk, records the checkpoint in a local SQL index, and checks the storage reserve before continuing. Disk space has become an ongoing issue with this project.
The merge phase can open only 32 input files at once. That limit keeps the process inside normal operating boundaries, but it means the 2,495 staging files must collapse through three hierarchical passes. Once there’s one globally ordered stream, the canonical spool groups matching identities, selects representatives, preserves occurrence lineage, assigns records to the 256 buckets, and commits every entity as a restart point.
This is mostly a disk, compression, hashing, sorting, and file system workload. Throwing a GPU at it wouldn’t automatically make it fast. Some future spatial or raster calculations may benefit from GPU acceleration, but the first optimization targets here are better I/O patterns, fewer redundant integrity scans, safe CPU parallelism, and profiling the actual bottleneck instead of stapling CUDA to the side and hoping it becomes the Batmobile.
The assembly was conservatively estimated to need about 176 gigs of temporary working data, plus 10 gigs of operational headroom. I also required 100 gigs to remain free. The initial run passed with only about 4.4 gigs of projected margin above the reserve. That margin has all the emotional comfort of the Death Star exhaust port.
The runner checks free space before every durable transaction. It renews its preparation claim hourly so the scheduled backup knows to stay out of the way. If it didn’t then I would get a lovely error. If the computer reboots, SQL restarts, I press Control C, or the free space reaches the guardrail, the completed work remains reusable. The next run verifies the boundary and continues instead of starting over.
That safety costs A LOT of time. I’m okay with that. I would rather spend days proving the result than spend days discovering that a fast run produced an unrepeatable pile of shit.
What’s working now
The platform can now scan national geospatial layers in bounded batches, retain every publisher occurrence, commit restartable checkpoints, reconcile duplicates globally, assemble canonical partitions, and prove that failed or interrupted work didn’t change production.
The waterbody output is complete and retained. The flowline occurrence scan is complete and verified. The full flowline assembly is running as I write this. The current reviewed code passed 2,424 automated tests before the large workspace was authorized. Production still contains 7,031,662 waterbodies and 24,943,802 flowlines, unchanged by the preparation work.
That last point matters. Prepared data is not active data. I haven’t pushed a ceremonial red button and yelled, “Let’s GOOO.” The new multi-layer generation can’t be registered and activated until its required boundaries all exist and pass together.
The project is also much less manual than it was during the first stages. Flowline checkpointing continued automatically under explicit total, runtime, lease, storage, and operator stop limits. I no longer have to authorize every million rows like a bureaucrat stamping passports at the border.
What’s not done
This thing still can’t tell me where to go fishing yet. The foundation has become serious, but the part an angler sees is still ahead.
The current flowline assembly must finish, publish, verify, and surrender its temporary workspace. I never thought a terabyte would fill up so fast.
Catchments come next as their own drainage area dataset, linked to flowlines and waterbodies without pretending every catchment is a fishing destination. What’s a catchment you ask? Oh, you didn’t, but I’m going to answer it anyway because I had to ask the same question. When it rains it’s the area where the water flows into lakes and streams. Why is that important? Because fish like water.
Next the current stage needs to close, the current branch can be merged, and the real progress can restart from a clean branch instead of carrying years of archaeological sediment in its pockets.
The water construction still has human meaning to solve. Neighboring polygons may represent a lake, multiple legitimate basins, a marsh complex, a reservoir arm, or something that should never appear as an independent result. Rivers need authoritative upstream and downstream relationships. Artificial paths, divergences, convergences, barriers, gauges, and cumulative upstream drainage all have to remain honest.
There is a lot of work between a technically valid hydrography feature and a place where a person can park, launch a kayak, legally fish a species, and avoid being introduced to a locked gate by an irritated landowner.
Where I want this to go
The destination hasn’t changed. The path has become less naive.
The next milestone turns prepared hydrography into canonical fishing waterbodies with stable identities, names, policies, etc. The one after that builds the hydrologic network. That should mostly solve the base 3 body water problem.
Then comes the fun part adding species, taxonomy, stocking history, fishing methods, lures, flies, and bait with evidence instead of folklore disguised as certainty. I still want to add regulations, public access, gauges, reservoirs, tides, advisories, weather, rolling upstream precipitation, and lunar conditions.
Then’s the part where I turn that into searchable and explainable recommendations. The whole platform becomes maintainable without me standing over every dataset with a clipboard and a twitchy eye.
The final product should be able to say more than “there is water here.” It should say which fish are likely present, what was stocked, what conditions are doing, what’s legal, how I can access the place, what techniques fit, how fresh the evidence is, and why this destination ranks above the one down the road.
That’s the useful version of machine learning for this project. Not a confident oracle. A system that can assemble the evidence, expose uncertainty, and help me make a better call before I burn gas and daylight.
The next cast
The project has taken longer because I finally understand more of what “done” has to mean. An import isn’t done when the rows exist. It is done when the sources are known, the transformations are repeatable, the duplicates have rules, the failures are recoverable, the result can be replaced later, and the person using it doesn’t need to know that 638,720 compressed fragments once stood between them and a fishing recommendation.
Some of this complexity was self inflicted. I remain a glutton for punishment with a roadmap drawn in crayon. Some of it came from the data pointing at my first design and laughing. Most of it came from deciding that if I am going to build the damn thing, I want to trust it.
Right now the checkpoints are moving, the merge passes are waiting, the SQL is hungry, and the storage sounds like it deserves its own opening crawl.
Today, it can at least prove where every byte came from. That IS progress, even if it takes a while to finish loading the next scene.
<END TRANSMISSON>
Leave a Reply