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, Python code in /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."

Related MCP server: arcgis-lacounty

Architecture

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)

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)

Database & Query Development

Python Development

MCP-Specific Tooling

Version Control & Hosting

Two governed tools, deliberately scoped rather than exposing raw SQL access to an AI system:

find_vacant_claims_near_mineral(mineral_name, max_distance_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.

check_land_access(latitude, longitude, mineral_search_radius_miles) Given a coordinate, returns a full site report: land ownership type, whether any mining claim covers that point (and if so, active vs. vacant), the county, the nearest city and its distance, and any minerals documented within a configurable search radius.

Both tools query only the curated Silver layer through fixed, parameterized queries — the AI never gets arbitrary database access, only these specific, safe, purpose-built answers.

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 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.

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.

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.

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.

Example Output

> find_vacant_claims_near_mineral(mineral_name="Quartz", max_distance_miles=20)

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=39.5, longitude=-105.7)

Land type: USFS, covered by claim '#1' (VACANT)
County: Park County
Nearest city: Fairplay (3.2 mi away)
Documented minerals within 2.0 mi: Gold, Quartz, Silver

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
└── python/
    ├── load_bronze.py                -- Bronze ingestion (Colorado-filtered, fast bulk insert)
    └── rockhound_server.py           -- MCP server with governed tools

Roadmap (Phase 2 / Phase 3, scoped but not yet built)

• Phase 2: Rivers/streams (placer deposit potential), hot springs (mineral-forming geology), bedrock/geologic formation data (Macrostrat) — same Bronze-to-Silver spatial pattern, new sources.

• Phase 3: Trailhead/parking entry points, elevation data, and vehicle-specific road access matching (ground clearance / 4WD requirements vs. a specific vehicle profile).

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 — 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.