RockHound
by crjiminez03
README.md
# RockHound — Colorado Rockhounding Intelligence Platform
A governed, spatially-aware data platform that answers a real question: "Where can I legally go rockhounding in Colorado, and what am I likely to find?" Built end-to-end from raw federal and state government data through a Medallion Architecture (Bronze/Silver-style layering) into a governed **MCP (Model Context Protocol) server** — allowing an AI agent to answer rockhounding questions grounded in real, curated, trustworthy spatial data rather than raw or unverified sources.
**Repo structure:** SQL scripts in [`/sql`](./sql), Python code in [`/python`](./python) — see those folders for the actual implementation.
---
## The Goal
Combine multiple independent public datasets — mining claim status, land ownership, and historical mineral occurrence records — into a single queryable platform, then expose that data to an AI system through a governed interface that only surfaces specific, safe, pre-approved queries rather than raw database access. This mirrors the same "AI-ready, governed data product" pattern increasingly asked for in modern data engineering roles.
**Specific question this answers:** "Find vacant/lapsed mining claims near documented occurrences of a mineral, and tell me whether I'm actually allowed to be there."
---
## Architecture
```mermaid
flowchart TD
A["BLM Mining Claims<br/>(Active + Closed + Closed-Recent)"] --> D
B["BLM Surface Management Agency<br/>(Land Ownership)"] --> D
C["USGS MRDS<br/>(Mineral Occurrences)"] --> D
D["BRONZE LAYER<br/>Raw ingestion, full provenance<br/>(source_url + source_type)"] --> E
E["SILVER LAYER<br/>Cleansed, deduplicated<br/>Native geography types, MakeValid()<br/>Colorado-filtered"] --> F
F["Spatial Indexes +<br/>CROSS APPLY Query Layer"] --> G
G["MCP SERVER<br/>Streamable HTTP"] --> H["find_vacant_claims_near_mineral()"]
G --> I["check_land_access()"]
H --> J["MCP Inspector / AI Client"]
I --> J
```
---
## Real Data Sources (all public, all free)
| Source | What it provides | Records (Colorado, filtered) |
|---|---|---|
| [BLM National GIS Hub](https://gbp-blm-egis.hub.arcgis.com/) — MLRS Mining Claims, Not Closed | Active mining claims | 14,699 |
| [BLM National GIS Hub](https://gbp-blm-egis.hub.arcgis.com/) — MLRS Mining Claims, Closed (full history) | Historical/vacant claims | 288,158 |
| [BLM National GIS Hub](https://gbp-blm-egis.hub.arcgis.com/) — MLRS Mining Claims, Closed (last year) | Recency flag source | 1,165 |
| [BLM Colorado GIS Data Portal](https://www.blm.gov/site-page/services-geospatial-gis-data-colorado) — Surface Management Agency | Land ownership (BLM, USFS, private, tribal, etc.) | 21,175 |
| [USGS Mineral Resources Data System (MRDS)](https://mrdata.usgs.gov/mrds/) | Historical documented mineral occurrences | 17,669 |
| [US Census TIGER/Line — Counties](https://catalog.data.gov/dataset/tiger-line-shapefile-current-nation-u-s-county-and-equivalent-entities) | County boundaries (national file, Colorado-filtered) | 64 |
| [US Census TIGER/Line — Places](https://catalog.data.gov/dataset/tiger-line-shapefile-current-state-colorado-place) | City/town/CDP boundaries, Colorado-specific | varies |
| [Colorado Geological Survey — Geothermal Map v3](https://coloradogeologicalsurvey.org/geology/gis-data-map-portal/) (Hot Springs) | Hot spring locations, temperature, use type, geothermometer estimates | 93 (matches official published count) |
| [USGS National Hydrography Dataset (NHD)](https://apps.nationalmap.gov/downloader/) — NHDFlowline | Named rivers/streams, filtered to StreamRiver + ArtificialPath feature types | 125,495 segments (aggregates to real river-scale totals — see challenges below) |
| [Macrostrat](https://macrostrat.org) — Geologic Units API | Bedrock/geologic formation data, lithology, geologic age | Queried live per-coordinate, not bulk-loaded (see "Tech Stack" note) |
| [OpenStreetMap](https://www.openstreetmap.org) (via the [Overpass API](https://overpass-api.de/), tag [highway=trailhead](https://wiki.openstreetmap.org/wiki/Tag:highway%3Dtrailhead)) | Trailhead locations, operator, fee info | 552 (name populated on 85%, operator on 16%, fee on 8%) |
| [Open-Elevation](https://open-elevation.com) (API) | Ground elevation at a coordinate, in meters | Queried live per-coordinate, not bulk-loaded |
| [OpenStreetMap](https://www.openstreetmap.org) (via the [Overpass API](https://overpass-api.de/), tag [highway=track](https://wiki.openstreetmap.org/wiki/Tag:highway%3Dtrack)) | Track/dirt road locations, surface, tracktype, 4wd_only, smoothness — for vehicle-access matching | 73,703 (surface populated on 29%, tracktype on 16%, 4wd_only on 5%, smoothness on 10%) |
| [BLM PFYC (Potential Fossil Yield Classification)](https://www.blm.gov/blog/2021-07-08/pfyc-rapid-assessment-tool-paleontology) — Geologic Formation Polygons | Fossil-yield potential ranking (1-5, or "U" for unstudied) by geologic formation, with formation name and age | Queried live per-coordinate, not bulk-loaded (BLM-administered land only — see "Known Limitations") |
All source records carry `source_url` and `source_type` (e.g., "Government Agency") for full data lineage and provenance tracking — a governance pattern built in intentionally, not an afterthought.
---
## Tech Stack
- **SQL Server** — native `geography` spatial data type, spatial indexing, `STDistance`/`STIntersects`/`STContains`, `MakeValid()`
- **Python** — `geopandas`, `pandas`, `pyodbc`, `shapely`
- **MCP Python SDK** (`mcp.server`) — Streamable HTTP transport
- **MCP Inspector** — official tooling for testing/verifying MCP servers
- **Cloudflare Tunnel** — local HTTPS exposure for remote MCP client testing
---
## Tools & Platforms Used
A detailed breakdown of what was used for what, since the actual development environment is part of the real story here.
### Data Sources (where the raw data came from)
| Source | Access Method | Use Case |
|---|---|---|
| [BLM Colorado GIS Data Portal](https://www.blm.gov/site-page/services-geospatial-gis-data-colorado) | Direct download (Shapefile/GeoJSON) | Colorado-specific Surface Management Agency (land ownership) data |
| [BLM National GIS Hub](https://gbp-blm-egis.hub.arcgis.com/) (ArcGIS Hub) | Direct download (GeoJSON / File Geodatabase) | Mining claims (Active, Closed, Closed-Last-Year) — note: these particular downloads turned out to be *national* scope despite being found via a Colorado-focused search, which is why the Colorado bounding-box filter exists in `load_bronze.py` |
| [USGS MRDS](https://mrdata.usgs.gov/mrds/) | Direct download (CSV, "Flattened" format) | Historical mineral occurrence records — also nationwide by default, filtered to Colorado via the `state` column |
| [US Census TIGER/Line Shapefiles](https://catalog.data.gov/dataset/tiger-line-shapefile-current-nation-u-s-county-and-equivalent-entities) | Direct download (Shapefile) | County boundaries (national file, filtered to Colorado via `STATEFP`) and Colorado-specific Places (cities/towns/CDPs) |
| [Colorado Geological Survey GIS Data Portal](https://coloradogeologicalsurvey.org/geology/gis-data-map-portal/) | Live ArcGIS REST Feature Service query (found by browsing the agency's REST services directory, not exposed in the public web map's UI) | Hot spring locations, temperature, use type, geothermometer estimates |
| [USGS National Map Downloader](https://apps.nationalmap.gov/downloader/) | Direct download (Shapefile, NHDFlowline feature class) | Named rivers/streams for placer-deposit proximity search |
| [Macrostrat](https://macrostrat.org) | Live REST API query per-coordinate | Bedrock/geologic formation data, lithology, geologic age |
| [Overpass Turbo](https://overpass-turbo.eu/) (OpenStreetMap query tool) | One-time bulk GeoJSON export via Overpass QL query, tag `highway=trailhead` | Trailhead locations across Colorado |
| [Open-Elevation](https://open-elevation.com) | Live REST API query per-coordinate | Ground elevation lookup |
| [Overpass Turbo](https://overpass-turbo.eu/) (OpenStreetMap query tool) | One-time bulk GeoJSON export via Overpass QL query, tag `highway=track` | Track/dirt road segments for vehicle-access matching (73,703 records — see "Real Engineering Challenges Solved" for the scale/coverage checks done before committing to this bulk-load approach) |
### Database & Query Development
| Tool | Use Case |
|---|---|
| **SQL Server Express** (local instance, named `SQLEXPRESS`) | The actual database engine — chosen because it's free and already commonly available for a personal project |
| **SQL Server Management Studio (SSMS)** | Schema creation, data verification, query development and testing, and — critically — **execution plan analysis** (Ctrl+M) used to diagnose the spatial index performance issue |
### Python Development
| Tool | Use Case |
|---|---|
| **Python 3.14** | Data ingestion scripting (`load_bronze.py`) and the MCP server itself (`rockhound_server.py`) |
| **pip** | Package management — `geopandas`, `pandas`, `pyodbc`, `shapely`, `mcp` |
| **PowerShell** | Running all Python scripts, file/folder management, and — notably — used directly to *write* source files via here-strings (`@'...'@ \| Set-Content`) when a text-editor save issue caused repeated stale-file problems mid-build |
| **winget** (Windows Package Manager) | Installing Python, the ODBC Driver 18 for SQL Server, and `cloudflared` |
### MCP-Specific Tooling
| Tool | Use Case |
|---|---|
| **MCP Python SDK** (`mcp` package, `mcp.server`) | Building the actual governed MCP server and its two tools |
| **MCP Inspector** (`npx @modelcontextprotocol/inspector`) | The official tool used to test and verify the server's tools work correctly — this became the primary demo/verification method after a specific consumer AI client's remote-connector flow turned out to require OAuth client registration that was out of scope for this project |
| **Cloudflare Tunnel** (`cloudflared`) | Exposed the local Streamable HTTP server over a temporary public HTTPS URL, since some MCP client integrations require HTTPS even for local development/testing |
### Version Control & Hosting
| Tool | Use Case |
|---|---|
| **GitHub** | Hosting this repository as part of a broader data engineering portfolio |
Eight governed tools, deliberately scoped rather than exposing raw SQL access to an AI system:
**`find_vacant_claims_near_mineral(mineral_name, max_distance_miles, max_results, latitude, longitude, search_radius_miles)`**
Finds vacant/lapsed claims near documented historical occurrences of a given mineral, flagging which claims closed most recently (freshest opportunities), which county each falls in, and sorting by proximity. Results are capped (default 50) with a note when more exist, for both usability and performance reasons -- see the performance investigation in "Real Engineering Challenges Solved." Optional latitude/longitude narrow the search to occurrences within search_radius_miles (default 2.0) of a specific point, rather than searching the entire state -- useful for "what can I find near my claim" rather than "where can I find this anywhere in Colorado."
**`find_vacant_claims_near_location(latitude, longitude, radius_miles, max_results)`**
A location-based counterpart to the tool above: "what's open near this specific spot," with no mineral name required. Also automatically reports documented minerals within the same radius, reusing the same individual-mineral deduplication logic already proven in `check_land_access`. Every returned claim includes its approximate center coordinate (via `boundary.EnvelopeCenter()`) directly in the result, so results can be plotted on a map for real trip planning without a separate manual query. Built directly from two real, sequential user requests: first wanting to see open ground and mineral context around an existing claim generally, then wanting the coordinates themselves to actually plan a prospecting trip with a real claim holder.
**`find_mineral_locations(mineral_name, latitude, longitude, radius_miles, max_results)`**
A trip-planning tool: "where should I even start looking for X," grouped by nearest town and county, not a flat list of raw coordinates. Deliberately does not check claim availability -- pairs with `find_vacant_claims_near_mineral` or `check_land_access` as a natural next step once a promising area is identified. Works statewide by default, or narrows to a radius around a point if coordinates are given.
**`check_land_access(latitude, longitude, mineral_search_radius_miles)`**
Given a coordinate, returns a complete site report: land ownership type, every active mining claim covering that point (since any one active claim means "do not dig," and multiple claims commonly overlap in dense historic districts), a summarized vacant-claim count, the county, the nearest city and its distance, the nearest named river and its distance (useful for placer-deposit potential), the nearest trailhead and its distance, documented minerals within a configurable radius (deduplicated at the individual-mineral level, not the raw-record level), and any hot springs within that same radius -- since hot springs and mineral-rich water are geologically related.
**`check_vehicle_access(latitude, longitude, vehicle_name)`**
Given a coordinate and a vehicle profile (from a small `Dim_Vehicle` reference table, currently populated with a real 2020 Ford Escape: 7.9" clearance, AWD, no low-range), finds the nearest mapped track/road and evaluates its OpenStreetMap surface/difficulty tags against that vehicle's real capabilities. Honestly reports "no difficulty data available" when a segment isn't tagged (the majority case), rather than assuming a road is safe by default -- verified against both a real untagged segment and a real known-hazardous one (`4wd_only=yes`, `smoothness=very_bad`) to confirm both branches of the logic actually work, not just the fallback.
**`get_bedrock_geology(latitude, longitude)`**
Given a coordinate, queries the live Macrostrat API for bedrock/geologic formation data at that point -- rock unit name, lithology, and geologic age. Deduplicates by (unit name, lithology) since multiple overlapping source maps at different scales commonly cover the same coordinate.
**`get_elevation(latitude, longitude)`**
Given a coordinate, queries the live Open-Elevation API for ground elevation, returned in both meters and feet.
**`check_fossil_potential(latitude, longitude)`**
Given a coordinate, queries BLM's live PFYC (Potential Fossil Yield Classification) API -- a real BLM system ranking geologic formations by their likelihood of containing scientifically significant fossils, on a 1 (very low) to 5 (very high) scale, plus "U" for unknown/understudied. Mirrors `get_bedrock_geology`'s architecture (live external API, kept separate from local governed data) for the same reason -- PFYC classifies geologic formation polygons, naturally point-queried rather than bulk-loadable. Validated across every distinct real outcome the tool can produce (see "Real Engineering Challenges Solved"), each independently checked against real, known geology -- including a genuine jurisdictional coverage gap discovered at a National Park Service unit, since PFYC is fundamentally a BLM system.
**Gem-variety name translation, shared by both mineral-search tools:** MRDS is an economic-minerals database, not a gem-collector's database -- it records "Feldspar," not "Amazonite," and "Manganese," not "Rhodochrosite." A curated `GEM_VARIETY_TO_COMMODITY` mapping (confirmed against the real loaded data for entries like Amazonite/Feldspar and Rhodochrosite/Manganese) translates common collector names automatically, always telling the user when a translation happened. A second, separate `GEM_VARIETY_TO_COMMODITY_LOWER_CONFIDENCE` mapping covers additional Colorado classics (Rhodonite, Turquoise, Wulfenite, Chrysocolla, Apatite, Zircon) as educated geologic guesses, not individually confirmed against the data, and is worded differently in the output specifically to signal that lower certainty rather than implying the same confidence as a verified entry. Deliberately not exhaustive -- e.g. Topaz was left unmapped after genuinely conflicting reasoning about its likely parent commodity, rather than force a guess.
The first five tools (`find_vacant_claims_near_mineral`, `find_vacant_claims_near_location`, `find_mineral_locations`, `check_land_access`, `check_vehicle_access`) query only the curated Silver layer through fixed, parameterized queries -- the AI never gets arbitrary database access, only these specific, safe, purpose-built answers. The last three (`get_bedrock_geology`, `get_elevation`, `check_fossil_potential`) are the deliberate exception: all three query live external APIs rather than local data, and are kept as separate, clearly-labeled tools so an external API's latency or availability can never affect the core governed local-data tools.
---
## Real Engineering Challenges Solved
This section exists because the debugging process is arguably the most representative part of the whole project — real data engineering isn't a clean first pass. See [`/sql/04_example_queries.sql`](./sql/04_example_queries.sql) for the actual diagnostic queries used to find and fix these.
1. **Invalid spatial geometry.** Real-world government GIS polygon data included self-intersecting/invalid geometries that caused runtime failures in SQL Server's strict `geography` type (`24144: instance is not valid`). Fixed with `.MakeValid()` applied during the Bronze-to-Silver transformation — see [`/sql/02_silver_schema_and_transform.sql`](./sql/02_silver_schema_and_transform.sql).
2. **A silent data-mapping bug.** The mineral search was initially matching against `mineral_name` (a mine's site name, e.g. "Silver King Mine") rather than `commodity_type` (what was actually documented as found there) — a correctness bug caught by comparing row counts: 11 site-name matches for "Quartz" vs. 82 real commodity matches.
3. **A real performance/query-plan problem.** A straightforward `JOIN ... ON STDistance(...) < X` pattern caused queries to silently take 13+ minutes for common minerals, because SQL Server's optimizer wasn't using the spatial index for that join shape — confirmed via execution plan analysis showing 124M+ estimated row operations on a nested loop join. Fixed by restructuring the query around `CROSS APPLY` (the documented pattern for reliably triggering spatial index usage in nearest-neighbor searches), bringing the same query down to ~36 seconds. See [`/sql/04_example_queries.sql`](./sql/04_example_queries.sql).
4. **National-scope data filtering.** Several "Colorado" datasets from federal sources were actually nationwide (one active-claims file was 579,730 rows before filtering to Colorado's 14,699). Filtered via bounding-box intersection during ingestion rather than loading and discarding downstream — see `COLORADO_BBOX_WKT` in [`/python/load_bronze.py`](./python/load_bronze.py).
5. **MCP client integration.** Discovered that the target MCP client's remote-connector flow expected OAuth client registration even for unauthenticated local servers. Worked around by running the server over Streamable HTTP with a Cloudflare quick tunnel for HTTPS, and validated functionality through the official MCP Inspector tool rather than a single consumer app's specific auth requirements.
6. **Inverted polygon ring orientation, affecting three separate tables.** Shapefile- and File-Geodatabase-sourced polygons (Counties, Cities, and the large historical Claims dataset) were sometimes stored with reversed ring winding order -- SQL Server's `geography` type interpreted these as "everywhere except X" rather than "X," which `.MakeValid()` does not detect or fix (it only repairs self-intersections, not orientation). Diagnosed by checking `STArea()` for implausibly large values (a genuine, correctly-oriented Colorado county should never approach ~510,000,000 sq km -- Earth's total surface area). Fixed with a conditional `.ReorientObject()` based on an area threshold. A first attempt at this fix used the wrong unit (`STArea()` returns square *meters*, not square kilometers), which incorrectly flipped several genuinely large, correctly-oriented counties -- caught and corrected by re-validating against all 64 real Colorado counties.
7. **A recurring parameter-count bug class, and a structural fix.** Repeating `geography::Point(?, ?, 4326)` inline multiple times within a single query made it easy to miscount the required parameter list, causing two separate "wrong parameter count" runtime errors. Fixed structurally by computing the coordinate point once via a SQL `DECLARE @searchPoint GEOGRAPHY = ...` variable and referencing it throughout the query, reducing most queries to just 2 real parameters and eliminating the bug class going forward rather than just fixing the immediate instance.
8. **A data-completeness design gap, not a bug.** `check_land_access` originally returned a single arbitrary claim via `TOP 1` with no explicit ordering. Testing against a real, known claim ("Rocket Six," verified against a friend's actual mining claim data) revealed that 14 separate claims -- 6 active, 8 vacant -- legitimately overlap that one coordinate, which is normal for a dense historic Colorado mining district. The fix wasn't a bug patch but a deliberate design decision: list every active claim by name (since any one of them means "do not dig"), and summarize vacant claims as a count rather than silently picking one and hiding the rest.
9. **A multi-stage performance investigation on a per-row enrichment lookup.** After adding a county lookup to enrich mineral-search results, common minerals (Quartz: ~24,570 raw matches) began timing out via the MCP tool call. Debugging ruled out several plausible causes in turn: capping with `TOP (N)` at the SQL level actually made things *dramatically worse* (4+ minutes vs. ~6 seconds uncapped) due to a SQL Server optimizer regression when `TOP` is combined with `ORDER BY` on an expensive computed column; capping in Python after fetching didn't help either, since the real cost was still being paid inside SQL Server before results were returned; and rewriting the county lookup as a correlated subquery, a `JOIN`, and an `OUTER APPLY` were all equally slow (~4 minutes), proving the bottleneck was the sheer number of spatial lookups (one per raw match), not query syntax. The actual fix: a two-phase query -- fast distance-only matching and capping first, then a spatial county lookup only on the small final result set (<=50 rows) instead of on every raw match. This is a good example of systematic elimination of plausible-but-wrong hypotheses being the real work of performance debugging, not a single clever fix found immediately.
10. **Finding an undocumented data source (Phase 2).** A public-facing Colorado Geological Survey hot springs web map (an Esri Web AppBuilder app) didn't expose its underlying data source anywhere in its UI. Rather than falling back to manually transcribing a narrative PDF report, the real REST Feature Service was tracked down by browsing the agency's public ArcGIS REST services directory (`cgsarcimage.mines.edu/arcgis/rest/services`) folder by folder, then confirming the correct layer by inspecting its field list before writing any load code. This turned a planned manual-entry data source into a fully automated one, and surfaced richer data (real geothermometer chemistry estimates, flow rate, use type) than the PDF alone would have.
11. **A field-level deduplication bug.** A "documented minerals nearby" feature deduplicated on entire comma-separated commodity strings (e.g. `"Beryllium, Tantalum"` vs. `"Tantalum, Beryllium, REE"`), so the same individual mineral could still appear multiple times in the output if it showed up across different multi-mineral site records -- correct SQL-level `DISTINCT`, wrong level of granularity. Fixed by splitting each record's commodity list into individual mineral names and deduplicating at that level instead, verified by confirming a real test location's mineral list dropped from 28 entries with visible repeats to 15 genuinely distinct minerals.
12. **A silent WKT format incompatibility.** Line geometries exported by geopandas from this source (which includes an elevation/M-value dimension) were written as `LINESTRING Z (...)`, a WKT tag format SQL Server's `geography` parser does not recognize (`24142: Expected "(" at position 11. The input has "Z"`). Fixed by stripping the `Z` tag from the WKT string before parsing -- the underlying coordinate data is unaffected, only the malformed tag needed removal.
13. **A wrong data-completeness assumption caught by aggregation, not a single test case.** Filtering NHDFlowline to `FType 460` (StreamRiver) seemed like the obvious way to isolate real rivers/streams from canals, ditches, and pipelines. Loading succeeded without error and returned a plausible-looking row count -- but grouping the loaded data by river name and summing length revealed every major Colorado river (Colorado River, South Platte, Arkansas River) had implausibly short total lengths (e.g. Colorado River: ~51 km, when its real length through the state is roughly 450-500 km), while minor named creeks correctly showed hundreds of kilometers. Root cause: NHD represents the wider stretches of major rivers (anywhere they're mapped as a polygon area rather than a simple line) using a separate `FType 558` (ArtificialPath) code for network connectivity, which the original filter excluded entirely. Including both FTypes brought every major river to a realistic total length. This is a good example of why a single successful test case (a small creek, correctly represented as FType 460 for its entire length) doesn't validate a filter for the whole dataset -- aggregate validation caught what a spot-check would have missed.
14. **A silently wrong test result from a caching/URL-matching quirk.** Before writing any bedrock-geology code, an initial test fetch of Macrostrat's lat/lng query endpoint appeared to succeed, but actually returned content from a different, earlier-cached query (a `strat_name_id`-based request from unrelated documentation) rather than genuinely querying the intended coordinate -- the response looked plausible (real GeoJSON, real geologic unit names) but was quietly wrong. Caught by noticing the returned units were scattered across Texas, Wyoming, and Alabama rather than clustered at the one Colorado coordinate requested. Re-tested by having the actual query URL fetched directly and independently, which returned correct, tightly-clustered results. A good reminder that a response "looking successful" (valid JSON, real-looking data) is not the same as confirming it actually answers the specific question asked.
15. **A deliberate architectural boundary between governed local data and live external data.** Bedrock geology data doesn't fit the Bronze/Silver bulk-load pattern used everywhere else in this project -- Macrostrat is naturally point-queried rather than bulk-downloadable in a useful way. Rather than forcing it into the existing pattern or, alternatively, folding it into `check_land_access` for convenience, it was built as a separate, isolated tool (`get_bedrock_geology`) that queries the live API directly. This keeps the core governed tools (backed entirely by curated local data) free of external-API latency and availability risk, while still surfacing genuinely useful bedrock context through a clearly separate, clearly-labeled tool.
16. **Realistic field-coverage assessment before overselling a data source (Phase 3).** OpenStreetMap's `highway=trailhead` tag set includes fields (`ele`, `4wd_only`, `access`, `motor_vehicle`) that looked like they might partially satisfy two other planned Phase 3 goals (elevation and vehicle-access matching) for free. Checking actual coverage across all 552 real Colorado trailheads showed these fields populated on fewer than 5% of records -- not a real dataset, just scattered examples. Only `name` (85%), `operator` (16%), and `fee` (8%) had meaningful, usable coverage. Caught before building anything on the assumption that "the field exists" meant "the field is usable" -- Phase 3's elevation and vehicle-access work still needed their own dedicated sources.
17. **A silent Phase 1 data-quality bug surfaced two phases later.** A `check_land_access` result unexpectedly included `"nan"` in its documented-minerals list, alongside real mineral names like Gold and Silver -- plausible enough at a glance to almost pass as legitimate. Root cause: the original Phase 1 loader (`load_bronze.py`) used pandas, which represents missing numeric-like values as `NaN`; when cast to a Python string during insertion, this became the literal text `"nan"` rather than a true NULL, and was stored as if it were a real commodity value. Affected 1,564 rows, undetected through all of Phase 1 and most of Phase 2. Confirmed via `COUNT(*)` (1,564 exact matches) and ruled out messier mixed-string cases (a `LIKE '%nan%'` check after the fix returned zero rows) before concluding a single `UPDATE` had resolved it completely. Fixed at the Silver data layer (converted to true `NULL`) rather than patched at the application/tool level, so every future query against the table is covered automatically.
18. **The same caching/URL-matching quirk from challenge #14, recurring with a different API.** Before writing the elevation tool, an initial test fetch of the Open-Elevation API appeared to succeed -- valid JSON, a real-looking elevation value -- but the returned coordinates (41.16, -8.58, in Portugal) were the literal example coordinate from that API's own documentation page, not the Colorado coordinate actually requested. Caught immediately this time, faster than the first occurrence, specifically *because* it had already been documented as a known failure mode earlier in the project. Re-verified by having the real URL tested directly and independently, which returned a correct, plausible Colorado elevation (2,704 m at a known test coordinate). A good demonstration of why documenting a lesson (not just fixing the immediate instance) pays off the next time the same failure mode shows up somewhere new.
19. **Two rounds of the same column-width bug, one layer apart.** Loading real OpenStreetMap track/road tag data (`highway=track`, 73,703 segments) hit a `String data, right truncation` error on `tracktype` at the Bronze layer -- an initial `VARCHAR(20)` was too narrow for real crowdsourced tag values (which aren't always the clean `grade1`-`grade5` format assumed). Widened the affected Bronze columns and reloaded successfully -- but the identical error then recurred one layer downstream, in Silver, which still had the original narrower column definitions inherited from the initial schema design. A reminder that a fix applied to one layer of a Bronze/Silver pipeline doesn't automatically propagate to the next; both layers needed the same correction applied separately.
20. **A deliberate "honest uncertainty" design principle, verified against both of its own branches.** OpenStreetMap's difficulty-related tags (`surface`, `tracktype`, `4wd_only`, `smoothness`) are populated on only 5-29% of the 73,703 loaded track segments -- meaning most coordinate lookups will find a segment with no explicit difficulty rating at all. Rather than defaulting to "likely passable" for untagged segments (which would be a false, unearned assurance) or "unknown, proceed with extreme caution" for everything (which would make the tool useless whenever real hazard data *does* exist), `check_vehicle_access` explicitly distinguishes and reports three different states: a real, specific hazard flag; an honest "no data available"; and a genuine "no red flags in available tags." Verified all the way through by testing against two real coordinates that happened to hit untagged segments (confirming the honest fallback), then deliberately pulling a real coordinate directly from a known-tagged road (`Forrester Road`: `surface=unpaved`, `4wd_only=yes`, `smoothness=very_bad`) to confirm the actual hazard-flagging branch fires correctly too -- not just the easier-to-hit fallback case.
21. **A silent wrong-column bug from a copy-pasted pattern.** A new trip-planning tool (`find_mineral_locations`) needed a nearest-city lookup and copied the pattern used elsewhere in the project, referencing `Silver.Cities`' spatial column as `location`. That table's actual spatial column is named `boundary`. Because SQL Server resolves unqualified column references by scope rather than raising an error for a genuinely missing column name, the reference silently fell back to a column named `location` that did exist one scope up (the query's own CTE) -- meaning every city was scored at distance zero from itself, and `TOP 1` returned an arbitrary row with no real geographic relationship to the actual search point. The query ran without error and returned syntactically valid, plausible-looking output (a real town, a real county) -- caught only by noticing the returned town (Cheraw, Otero County) was hundreds of miles from the Chaffee County coordinate actually searched. A good example of why a successful-looking result still needs a plausibility check, not just an error check.
22. **A per-row correlated-subquery pattern recreating an already-solved performance problem.** `find_mineral_locations` enriched each of up to 500 capped raw occurrences with a nearest-city lookup and a county lookup via correlated scalar subqueries -- structurally the same anti-pattern already identified and fixed once before in `find_vacant_claims_near_mineral`'s county enrichment (challenge #10), just recreated in a new tool built independently later. Confirmed via real timeouts on statewide searches for common minerals (Moonstone, Galena, Amazonite/Feldspar). Fixed the same way as before: switching to `CROSS APPLY`/`OUTER APPLY` (the pattern already proven to reliably trigger spatial index usage) and further reducing the raw sample cap from 500 to 200. Worth noting as a real limitation of institutional knowledge within a single project -- a lesson learned and documented in one tool didn't automatically prevent the same mistake in a different tool built later, until it was hit again directly.
23. **A Python typing subtlety that passed local syntax checks but failed at runtime.** Adding optional coordinate parameters to two tools used the pattern `latitude: float = None`, which is syntactically valid Python and looks correct at a glance. The MCP framework's argument validation, however, checks the type hint itself (`float`, meaning "must be a real number") rather than inferring nullability from the default value -- so any call omitting coordinates failed with a Pydantic validation error, even though `None` was the documented, intended default. Fixed by using `Optional[float]` instead of bare `float` for every parameter meant to be genuinely optional. A good reminder that a default value and a type hint are two separate contracts, and a strict validation layer will enforce the type hint literally.
24. **A curated knowledge layer, deliberately split by confidence level rather than presented as uniformly authoritative.** MRDS records minerals by economic commodity name, not by the collector/gem-variety name a rockhound is likely to actually search for (confirmed directly: "Amazonite" appears nowhere in the dataset, only "Feldspar"; same pattern for Rhodochrosite/Manganese and Halite/Salt). Rather than leaving this as a silent gap or guessing uniformly across the board, a `GEM_VARIETY_TO_COMMODITY` mapping was built and split into two explicitly separate dictionaries: entries individually confirmed against the real data (or so mineralogically universal that no reasonable database would differ, e.g. Ruby/Sapphire as Corundum varieties), and a second `GEM_VARIETY_TO_COMMODITY_LOWER_CONFIDENCE` set of educated guesses based on standard economic geology reasoning but not individually verified (e.g. Wulfenite -> Molybdenum, reasoned from its chemical formula). Each dictionary produces different, honestly worded output, so a lower-confidence guess is never presented with the same certainty as a confirmed mapping. At least one genuinely plausible Colorado name (Topaz) was deliberately left unmapped after real, conflicting reasoning about its likely parent commodity, rather than forcing a guess purely for the sake of completeness.
25. **A tool gap identified directly from real end-user feedback, not a hypothetical.** After sharing early results with a friend and real claim holder, a genuine, specific question came up: what's generally open and documented near a given claim, without having to search by one specific mineral name first. The existing `find_vacant_claims_near_mineral` genuinely couldn't answer this cleanly -- it requires a mineral name as its primary search key, only optionally narrowed by location. Rather than overloading that tool's parameters further, a new, purpose-built tool (`find_vacant_claims_near_location`) was added as its natural counterpart: location as the primary key, mineral name not required at all, and automatically enriched with the same documented-minerals summary already proven in `check_land_access`. Verified against a real, known claim by confirming the closest results (0.0 mi) matched claims already independently confirmed to overlap that exact point in earlier testing, before trusting the wider-radius results around it.
26. **A second iteration of the same tool, driven by a genuine follow-on real-world need.** After using `find_vacant_claims_near_location` to identify open ground near a real claim, the next real request was concrete: plan an actual prospecting trip to several of those claims, which meant needing real coordinates to plot on a map -- not just names and distances. Rather than requiring a manual one-off SQL query (`boundary.EnvelopeCenter().Lat/.Long`) every time this came up, the tool itself was updated to return each claim's approximate center coordinate automatically. This is a good example of a feature evolving directly from real usage rather than being fully speculated upfront -- the first version solved the immediately-stated need, and the coordinate requirement only became clear once someone tried to actually act on the results. The resulting coordinates were verified by plotting them on a real map and cross-checking that claims already known to overlap the search point (from earlier `check_land_access` testing) showed 0.0 mi distances, matching expectations.
27. **A sentinel placeholder value hiding in plain sight, caught by asking a genuine follow-up question rather than a code review.** A user question -- "when did these specific claims actually go vacant?" -- led to checking `date_closed` on 24 real claims near a known point, and every single one showed the exact same date: `1900-01-01`. A single coincidental match might be plausible; 24 claims in one small area all closing on the literal same calendar day over a century ago is not. Confirmed at scale with `GROUP BY date_closed`: 244,515 rows -- the overwhelming majority of `Silver.Claims` -- share this one value, versus a normal, varied spread of real dates you'd expect across genuinely different historical closures. This is a classic sentinel/default value baked into the original BLM source data itself (likely from an older bulk-administrative closure process that didn't track individual dates), not a bug introduced anywhere in this pipeline -- but it went completely undetected through every previous phase of this project because no prior tool or test actually surfaced or scrutinized the `date_closed` field's real distribution until a genuine user question prompted it.
28. **A real, working cross-service join technique discovered, honestly scoped, and not over-applied.** Investigating whether real BLM serial numbers (`CSE_NR`) or closure dates could be added to `Silver.Claims` led through several dead ends -- an internal GIS object ID mistaken at first glance for a real serial number (ruled out: 19-digit format, and the same claim name appeared under multiple different ID values, which a real unique identifier would never do), a live "Closed" REST service that turned out to only contain ~1,171 recently-closed Colorado records rather than the full ~288,000-claim historical archive, and an unrelated action-history layer (`NLSDB_LND_HIST`) with no obvious shared key to the claims data. A working join was eventually found via a shared `SF_ID` field present on both the Closed-claims service and the action-history layer, confirmed live by pulling one real claim's `SF_ID` and finding matching action records for it, including a real closure reason (`"Claim Abandoned/Forfeited"`) alongside a real date. Rather than assuming this solved the problem generally, it was explicitly tested against all 24 real claims from the earlier prospecting-trip example: only 2 of 24 existed in the smaller recently-closed service at all, confirming the technique is real and useful, but genuinely limited to recent closures -- not a fix for the sentinel-date problem across the bulk of historical claims. Concluded as a documented, honest limitation rather than forcing a partial technique to look like a complete solution.
29. **A wrong layer selected on the first attempt, caught before any code was written around it.** Adding fossil-yield potential meant finding BLM's live PFYC REST service, initially queried at `MapServer/0` -- which returned real, well-formed JSON with real-looking field names, but was actually the wrong layer entirely: a map-index/citation layer describing which source USGS maps were used to build PFYC, not the fossil classification data itself. Caught by checking the real layer list first (`MapServer/0: PFYC Geologic Map Index Polygons`, `MapServer/1: PFYC Geologic Formation Polygons`) rather than assuming layer 0 was correct by default, and confirmed by the real fields returned (map titles and authors, not a PFYC rank) not matching what the tool needed. The same lesson as challenge #14/#18 in a new form -- a well-formed, error-free response is not the same as confirming it's the *right* response.
30. **A five-value validation matrix, deliberately pursued to completion rather than stopping at the first successful test.** `check_fossil_potential`'s live API could theoretically return five meaningfully different outcomes (ranks 1 through 5, "U" for unstudied, or no data at all), and confirming just one or two of these would have left real, untested code paths in production. Each was deliberately sought out and confirmed against independently known real geology: rank 1 at a known intrusive-rock coordinate (correct -- igneous rock cannot preserve fossils), rank 3 at the Fountain Formation (correct -- real sedimentary rock), rank 5 at Douglas Pass's Wasatch Formation (correct -- a well-documented fossil-bearing formation directly underlying the famous Green River Formation in that region), rank U at a coordinate that returned recent surface colluvium, and a genuine "no data" result at Florissant Fossil Beds -- which, on inspection, revealed a real, previously undiscussed limitation: PFYC is fundamentally a BLM system, and its coverage may not extend into non-BLM land (Florissant is a National Park Service unit) regardless of how well-studied the underlying geology is. Two coordinate guesses aimed at specific named formations (Dinosaur Ridge's Morrison Formation, and a trail near Florissant's exposed shale) missed their target formations entirely and landed on adjacent, differently-aged rock instead -- a concrete reminder that these geologic polygon boundaries are frequently narrow, and "close" in ordinary terms is not the same as "within the same mapped unit" for this kind of data.
---
## Example Output
```
> find_vacant_claims_near_mineral(mineral_name="Quartz", max_distance_miles=20)
Showing closest 50 of 24570 total matches:
AVENGER #15, Park County - 0.7 mi from documented Quartz
GAMBLE NO 1, Park County - 2.9 mi from documented Quartz
SARAH K #45, Chaffee County - 4.6 mi from documented Quartz
...
> check_land_access(latitude=38.7431, longitude=-106.1742, mineral_search_radius_miles=5.0)
Land type: PRI
County: Chaffee
Nearest city: Buena Vista (4.7 mi away)
Nearest named river: Merriam Creek (0.4 mi away)
Nearest trailhead: Wagon Loop Trail (5.1 mi away)
Documented minerals within 5.0 mi: Construction, Copper, Geothermal, Gold, Granite, Sand and Gravel, Silver
Hot springs within 5.0 mi: Mt. Princeton Hot Springs (84.00°C, 0.7 mi)
> get_bedrock_geology(latitude=39.5, longitude=-105.7)
Bedrock geology at this location (3 mapped unit(s), from overlapping source maps at different scales):
- Paleoproterozoic metamorphic and undivided crystalline: sedimentary and volcanic gneiss (Paleoproterozoic) -- lithology: metamorphic and undivided crystalline: sedimentary and volcanic gneiss
- Biotitic gneiss, schist, and migmatite (Paleoproterozoic) -- lithology: Major:{biotite gneiss,schist,migmatite}, Minor:{gneiss,calc silicate schist,marble}
- Paleoproterozoic crystalline metamorphic rocks (Paleoproterozoic) -- lithology: orthogneiss/paragneiss
> get_elevation(latitude=38.7431, longitude=-106.1742)
Elevation at this location: 2704 m (8871 ft)
> find_mineral_locations(mineral_name="Rhodochrosite")
Note: 'Rhodochrosite' is a variety/species of Manganese, which is the name MRDS actually
records -- searched as 'Manganese' instead.
Areas with documented Manganese occurrences (statewide (based on a sample of up to 200
documented occurrences)), by nearest town:
- Leadville, Lake -- 64 occurrence(s) in this sample
- Leadville North, Lake -- 25 occurrence(s) in this sample
- Smeltertown, Chaffee -- 8 occurrence(s) in this sample
- Alma, Park -- 2 occurrence(s) in this sample
...
This shows WHERE Manganese has been documented, not claim availability -- use
find_vacant_claims_near_mineral or check_land_access next to see the real claim situation
in a promising area before visiting.
> check_vehicle_access(latitude=40.942218, longitude=-106.001034)
Nearest mapped track: Forrester Road (0.0 mi away)
Vehicle: 2020 Ford Escape (7.9" clearance, AWD, no low-range)
LIKELY NOT SUITABLE for this vehicle: tagged 4wd_only='yes'; smoothness='very_bad'. This vehicle has no low-range transfer case.
> find_vacant_claims_near_location(latitude=39.5214528, longitude=-105.4841972, radius_miles=1.0)
Showing all 24 vacant claims within 1.0 mi (coordinates are each claim's approximate center, for trip planning):
J-PEG, Park County - 0.0 mi away (39.523677, -105.486443)
LONE WALKER, Park County - 0.0 mi away (39.519991, -105.488820)
DOUBLE RAINBOW, Park County - 0.0 mi away (39.523585, -105.488859)
PEGMATITE #2, Park County - 0.0 mi away (39.523858, -105.481722)
PEGMATITE #1, Park County - 0.2 mi away (39.527187, -105.488891)
...
ELK CREEK #3, Park County - 1.0 mi away (39.519959, -105.507266)
ELK CREEK #4, Park County - 1.0 mi away (39.519959, -105.507266)
No documented mineral occurrences within 1.0 mi
> check_fossil_potential(latitude=39.5975, longitude=-108.80306)
Fossil yield potential (PFYC): 5 - Very High
Geologic unit: Wasatch Formation (Paleocene to Eocene)
Note: PFYC ranks the underlying geologic formation, not confirmed fossil finds -- a low rank
often reflects rock type unsuitable for fossil preservation (e.g. igneous/metamorphic rock),
not an absence of study.
```
Note the cross-source validation: "Geothermal" appears independently in the USGS mineral database at the same location where the Colorado Geological Survey's hot springs data shows Mt. Princeton Hot Springs (84°C), and the nearest named river (Merriam Creek, a real tributary in that same drainage) confirms the tool correctly favors precise nearby features over the much larger but farther-away Arkansas River -- three independently sourced datasets all coherently describing the same real place. The vehicle-access example above was deliberately tested against a coordinate pulled directly from a known-tagged road (rather than an arbitrary point) specifically to confirm the hazard-flagging logic works, not just its "no data available" fallback. The Rhodochrosite example is a real-world check too: Alma, Park County correctly appears in the results, and Alma is genuinely the nearest town to the Sweet Home Mine, one of the most famous rhodochrosite localities in the world -- the gem-variety translation and the underlying spatial data agree with real, independently-known geology. The `find_vacant_claims_near_location` example above shows real coordinates that were plotted on an actual map for real-world prospecting trip planning -- 24 distinct vacant claims within a mile of one point, several sharing identical coordinates, consistent with the same historic-district overlap pattern already confirmed elsewhere in this project. The `check_fossil_potential` example shows the strongest possible real-world validation of the whole tool: Douglas Pass's Wasatch Formation is independently documented as directly underlying the famous Green River Formation in this exact region, and the tool's live-queried "5 - Very High" ranking agrees precisely with that established, independently-known geology.
---
## Repo Contents
```
RockHound/
├── README.md
├── sql/
│ ├── 01_bronze_schema.sql -- Bronze table DDL
│ ├── 02_silver_schema_and_transform.sql -- Silver DDL + MakeValid() + dedup logic
│ ├── 03_spatial_indexes.sql -- Spatial index creation
│ ├── 04_example_queries.sql -- Diagnostic + optimized query patterns
│ ├── 05_cities_counties_schema_and_load.sql -- County/city boundary layer
│ ├── 06_hot_springs_schema_and_load.sql -- Hot springs layer (Phase 2)
│ ├── 07_rivers_schema_and_load.sql -- Rivers layer (Phase 2)
│ ├── 08_trailheads_schema_and_load.sql -- Trailheads layer (Phase 3)
│ ├── 09_tracks_vehicle_access_schema_and_load.sql -- Tracks + Dim_Vehicle (Phase 3)
│ └── 10_mineral_name_verification_queries.sql -- Diagnostic queries for the gem-variety
│ name mapping and a real column-name bug
│ (reference only -- no new schema)
└── python/
├── load_bronze.py -- Bronze ingestion (Colorado-filtered, fast bulk insert)
├── load_hot_springs.py -- Hot springs loader (live REST API query, Phase 2)
├── load_rivers.py -- Rivers loader (NHD, Phase 2)
├── load_trailheads.py -- Trailheads loader (OpenStreetMap/Overpass, Phase 3)
├── load_tracks.py -- Tracks loader (OpenStreetMap/Overpass, Phase 3)
└── rockhound_server.py -- MCP server with all 8 governed tools, including
get_bedrock_geology, get_elevation, and
check_fossil_potential (no separate load script or
SQL file for any of the three -- all are queried
live from their respective APIs on each tool call,
not bulk-loaded, so there's no Bronze/Silver step
for any of these three sources)
```
---
## How to Run This
**Prerequisites:** SQL Server Express, Python 3.10+, ODBC Driver 18 for SQL Server, and the packages in `requirements.txt` (`pip install -r requirements.txt`).
**Note on ordering:** most sources here interleave a Python loader (which populates Bronze from a downloaded file or a live API) and a SQL file (which creates the schema and transforms Bronze into Silver) -- run them as pairs, in this order, not all the SQL files first or all the Python files first:
1. **`sql/01_bronze_schema.sql`** -- creates the Bronze schema and tables (structure only, no data yet).
2. **Download the Phase 1 source files** using the links in "Real Data Sources" above (BLM claims, BLM land ownership, USGS MRDS, Census Counties/Places) into a local `data/` folder.
3. **`python/load_bronze.py`** -- populates Bronze with claims, land ownership, mineral occurrences, counties, and cities from those downloaded files.
4. **`sql/02_silver_schema_and_transform.sql`**, then **`sql/03_spatial_indexes.sql`**, then **`sql/05_cities_counties_schema_and_load.sql`** -- creates Silver and transforms the Phase 1 data (counties/cities Silver logic lives in `05`, not `02`).
5. For each Phase 2/3 source, run its Python loader, then its matching numbered SQL file, in this order: **hot springs** (`load_hot_springs.py` -> `06_...sql`, no download needed, queries a live REST API), **rivers** (download NHDFlowline first -> `load_rivers.py` -> `07_...sql`), **trailheads** (Overpass Turbo export first -> `load_trailheads.py` -> `08_...sql`), **tracks** (Overpass Turbo export first -> `load_tracks.py` -> `09_...sql`, which also creates and populates `Dim_Vehicle`).
6. **`sql/04_example_queries.sql`** is diagnostic/reference only -- not required to run the platform, but useful for seeing the real before/after of the CROSS APPLY performance fix.
7. **`python/rockhound_server.py`** -- starts the MCP server (`python rockhound_server.py`), listening on `http://127.0.0.1:8000/mcp`.
8. **Verify with the official MCP Inspector** (`npx @modelcontextprotocol/inspector`), pointed at the running server -- this is the same tool used to test and verify every result documented in this README.
---
## Roadmap
- **✅ Phase 1 (complete):** Core platform — mining claims, land ownership, mineral occurrences, counties, and cities. Full Bronze/Silver Medallion Architecture, spatial indexing, and the two foundational governed MCP tools (`find_vacant_claims_near_mineral`, `check_land_access`). See "Real Engineering Challenges Solved" #1-9 for the real bugs found and fixed here.
- **✅ Phase 2 (complete):**
- ✅ Hot springs (Colorado Geological Survey, live REST API) — done, integrated into `check_land_access`
- ✅ Rivers/streams (USGS NHD, placer deposit potential) — done, integrated into `check_land_access`
- ✅ Bedrock/geologic formation data (Macrostrat live API) — done, as a separate `get_bedrock_geology` tool
- **✅ Phase 3 (complete):**
- ✅ Trailheads (OpenStreetMap via Overpass API) — done, integrated into `check_land_access`
- ✅ Elevation data (Open-Elevation API) — done, as a separate `get_elevation` tool
- ✅ Vehicle-specific road access matching (OpenStreetMap track/road tags vs. a real vehicle profile in `Dim_Vehicle`) — done, as a separate `check_vehicle_access` tool
- **✅ Phase 4 (complete):**
- ✅ Fossil-yield potential (BLM PFYC live API) — done, as a separate `check_fossil_potential` tool, added directly from real personal interest (fossil collecting) rather than being part of the original three-phase plan
All three original planning phases, plus a fourth added directly from real personal interest, are now complete. Eight governed MCP tools, real data end to end, verified against real ground-truth coordinates throughout.
---
## Known Limitations
Several honest caveats are already noted individually throughout this README (in tool docstrings and specific challenge write-ups) -- collected here in one place:
- **This is a snapshot, not a live sync.** Every dataset was downloaded or queried on a specific date and loaded once. Claim status, land ownership, and similar facts can change in the real world after that point. `get_bedrock_geology` and `get_elevation` are the only two tools that query genuinely live data on every call; everything else reflects the data as of when it was loaded.
- **OpenStreetMap-sourced layers (trailheads, tracks) have partial, crowdsourced tag coverage.** Most track segments (71-95%, depending on the specific tag) have no explicit surface/difficulty rating at all, and some entries are auto-imported from Census TIGER data and were never manually verified by a human OSM mapper. `check_vehicle_access` is explicitly designed to report this honestly rather than guess.
- **`Dim_Vehicle` currently has one real vehicle profile** (a 2020 Ford Escape), not a general vehicle database -- the schema supports adding more profiles, but only one is populated.
- **This platform is scoped to Colorado only**, by design (every bounding-box filter, every data source, every sanity check assumes Colorado). Extending to another state would mean re-validating each source's actual coverage and quirks from scratch, not just changing a bounding box.
- **No automated test suite.** Every tool and data-loading step in this project was verified through manual, interactive testing against known real-world ground truth (documented throughout "Real Engineering Challenges Solved" and "Example Output") rather than a formal unit/integration test framework.
- **Advisory only, not a legal or safety authority.** `check_land_access` and `check_vehicle_access` in particular are meant to inform a decision, not replace verifying claim status, land access, and road conditions independently and in person before relying on them.
- **`date_closed` on `Silver.Claims` is unreliable for the vast majority of records.** 244,515 of the ~288,000 historical closed claims share the exact same date (`1900-01-01`) -- a sentinel/placeholder value baked into the original BLM source data, not a real closure date. A working technique exists to get real closure dates and reasons for a small subset of *recently* closed claims (via a shared `SF_ID` join to a separate BLM action-history service -- confirmed live, but only covering roughly 1,171 of Colorado's closed claims), but no reliable date exists for the bulk of older historical closures through any source found so far.
- **`check_fossil_potential`'s coverage is limited to BLM-administered land.** Confirmed directly: querying a real, well-known fossil locality on National Park Service land (Florissant Fossil Beds) returns no data at all, even though the underlying geology is extremely well studied -- PFYC is fundamentally a BLM system and its polygon coverage does not appear to extend into other agencies' land.
---
## Data Attribution
Data provided by the Bureau of Land Management (BLM) and U.S. Geological Survey (USGS), used in accordance with their public data terms. This is a personal project and is not affiliated with or endorsed by BLM or USGS. Data is provided "as is" and may contain errors or omissions — always verify claim status and land access independently before visiting any site in person.
---
## Other Projects
- **[Data Engineering & Systems Architecture Portfolio](https://github.com/crjiminez03/Data-Engineering-Systems-Architecture-Portfolio)** — A production-grade Medallion Architecture platform built on Microsoft Fabric, including PySpark/Delta Lake pipelines, Copilot Studio AI agents, KQL Eventhouse analytics, and full CI/CD via Azure DevOps.
This server cannot be deployed
Maintenance
ActivityMaintained
ResponsivenessNo issues