RockHound — Colorado Rockhounding Intelligence Platform

Eine verwaltete, räumlich orientierte Datenplattform, die eine reale Frage beantwortet: „Wo darf ich in Colorado legal auf Mineraliensuche gehen, und was kann ich dort wahrscheinlich finden?" Von Grund auf end-to-end aus rohen Bundes- und Landesregierungsdaten durch eine Medallion-Architektur (Bronze/Silver-Schichtung) in einen verwalteten MCP-Server (Model Context Protocol) aufgebaut – so kann ein KI-Agent Fragen zur Mineraliensuche beantworten, die auf echten, kuratierten, vertrauenswürdigen Geodaten basieren, statt auf rohen oder ungeprüften Quellen.

Repo-Struktur: SQL-Skripte in /sql, Python-Code in /python – weitere Details zur tatsächlichen Umsetzung finden Sie in diesen Ordnern.

Das Ziel

Mehrere unabhängige öffentliche Datensätze – Bergbau-Claim-Status, Landbesitz und historische Mineralvorkommen – in einer einzigen abfragbaren Plattform kombinieren und diese Daten dann über eine verwaltete Schnittstelle einem KI-System zugänglich machen, die nur bestimmte, sichere, vorab genehmigte Abfragen an die Oberfläche bringt, statt rohen Datenbankzugriff zu gewähren. Dies spiegelt dasselbe „KI-bereite, verwaltete Datenprodukt"-Muster wider, das in modernen Data-Engineering-Rollen zunehmend gefragt ist.

Konkrete Frage, die hier beantwortet wird: „Finde verfallene/erloschene Bergbau-Claims in der Nähe dokumentierter Vorkommen eines Minerals und sag mir, ob ich dort tatsächlich sein darf."

Related MCP server: arcgis-lacounty

Architektur

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

Echte Datenquellen (alle öffentlich, alle kostenlos)

Alle Quelldatensätze tragen source_url und source_type (z. B. „Government Agency") für vollständige Datenherkunft und Provenienz-Nachverfolgung – ein Governance-Muster, das bewusst eingebaut wurde und kein nachträglicher Einfall ist.

Tech-Stack

• SQL Server — nativer geography-Geodatentyp, räumliche Indizierung, STDistance/STIntersects/STContains, MakeValid()

• Python — geopandas, pandas, pyodbc, shapely

• MCP Python SDK (mcp.server) — Streamable-HTTP-Transport

• MCP Inspector — offizielles Tool zum Testen/Verifizieren von MCP-Servern

• Cloudflare Tunnel — lokale HTTPS-Exposition für Remote-MCP-Client-Tests

Verwendete Tools & Plattformen

Eine detaillierte Aufschlüsselung, wofür was verwendet wurde, da die tatsächliche Entwicklungsumgebung ein Teil der realen Geschichte hier ist.

Datenquellen (woher die Rohdaten stammen)

Datenbank- & Abfrageentwicklung

Python-Entwicklung

MCP-spezifisches Tooling

Versionskontrolle & Hosting

Zwei verwaltete Tools, bewusst eingegrenzt, statt einem KI-System rohen SQL-Zugriff zu gewähren:

find_vacant_claims_near_mineral(mineral_name, max_distance_miles) Findet verfallene/erloschene Claims in der Nähe dokumentierter historischer Vorkommen eines bestimmten Minerals, kennzeichnet, welche Claims am kürzlichsten geschlossen wurden (frischeste Gelegenheiten), in welchem County sich welche befindet, und sortiert nach Nähe.

check_land_access(latitude, longitude, mineral_search_radius_miles) Liefert zu einer Koordinate einen vollständigen Standortbericht: Art des Landbesitzes, ob ein Bergbau-Claim diesen Punkt abdeckt (und wenn ja, aktiv vs. verfallen), das County, die nächste Stadt und deren Entfernung sowie alle Mineralien, die innerhalb eines konfigurierbaren Suchradius dokumentiert sind.

Beide Tools fragen ausschließlich die kuratierte Silver-Schicht über feste, parametrisierte Abfragen ab – das KI-System erhält niemals beliebigen Datenbankzugriff, sondern nur diese spezifischen, sicheren, zweckgerichteten Antworten.

Reale technische Herausforderungen, die gelöst wurden

Dieser Abschnitt existiert, weil der Debugging-Prozess wohl der aussagekräftigste Teil des gesamten Projekts ist – echtes Data Engineering ist kein sauberer erster Entwurf. Siehe /sql/04_example_queries.sql für die tatsächlichen Diagnoseabfragen, die verwendet wurden, um diese Probleme zu finden und zu beheben.

1. Ungültige räumliche Geometrie. Reale GIS-Polygondaten von Regierungsbehörden enthielten selbstüberschneidende/ungültige Geometrien, die zur Laufzeit Fehler im strengen geography-Typ von SQL Server verursachten (24144: instance is not valid). Behoben mit .MakeValid(), das während der Bronze-to-Silver-Transformation angewendet wird — siehe /sql/02_silver_schema_and_transform.sql.

2. Ein stiller Datenzuordnungsfehler. Die Mineralsuche stimmte anfänglich gegen mineral_name (den Standortnamen einer Mine, z. B. „Silver King Mine") ab, statt gegen commodity_type (was dort tatsächlich als gefunden dokumentiert war) — ein Korrektheitsfehler, der durch den Vergleich von Zeilenzahlen aufgedeckt wurde: 11 Standortnamen-Treffer für „Quarz" gegenüber 82 echten Rohstoff-Treffern.

3. Ein echtes Leistungs-/Abfrageplan-Problem. Ein einfaches JOIN ... ON STDistance(...) < X-Muster führte dazu, dass Abfragen für häufige Mineralien stillschweigend 13+ Minuten dauerten, weil der Optimierer von SQL Server den räumlichen Index für diese Join-Form nicht nutzte — bestätigt durch eine Ausführungsplan-Analyse, die geschätzte 124M+ Zeilenoperationen bei einem Nested-Loop-Join zeigte. Behoben durch Umstrukturierung der Abfrage um CROSS APPLY (das dokumentierte Muster, um zuverlässig die Nutzung des räumlichen Index bei Nächste-Nachbarn-Suchen auszulösen), wodurch dieselbe Abfrage auf ~36 Sekunden reduziert wurde. Siehe /sql/04_example_queries.sql.

4. Datenfilterung auf nationaler Ebene. Mehrere „Colorado"-Datensätze aus Bundesquellen waren tatsächlich landesweit (eine Datei mit aktiven Claims hatte 579.730 Zeilen, bevor sie auf Colorados 14.699 gefiltert wurde). Gefiltert über Bounding-Box-Schnittmenge während der Erfassung, statt sie zu laden und später zu verwerfen — siehe COLORADO_BBOX_WKT in /python/load_bronze.py.

5. MCP-Client-Integration. Es stellte sich heraus, dass der Remote-Connector-Ablauf des Ziel-MCP-Clients eine OAuth-Client-Registrierung erwartete, selbst für nicht authentifizierte lokale Server. Umgangen, indem der Server über Streamable HTTP mit einem Cloudflare-Quick-Tunnel für HTTPS betrieben wurde, und die Funktionalität wurde über das offizielle MCP-Inspector-Tool validiert, statt über die spezifischen Authentifizierungsanforderungen einer einzelnen Consumer-App.

6. Invertierte Polygon-Ringorientierung, die drei separate Tabellen betrifft. Polygone aus Shapefiles und File-Geodatabases (Counties, Cities und der große historische Claims-Datensatz) wurden manchmal mit umgekehrter Ringwickelrichtung gespeichert — der geography-Typ von SQL Server interpretierte diese als „überall außer X" statt als „X", was .MakeValid() weder erkennt noch behebt (es repariert nur Selbstüberschneidungen, nicht die Orientierung). Diagnostiziert durch Prüfung von STArea() auf unplausibel große Werte (ein echtes, korrekt orientiertes County in Colorado sollte niemals ~510.000.000 km² erreichen — die gesamte Erdoberfläche). Behoben mit einem bedingten .ReorientObject() basierend auf einem Flächenschwellenwert. Ein erster Versuch dieser Korrektur verwendete die falsche Einheit (STArea() gibt Quadratmeter zurück, nicht Quadratkilometer), wodurch fälschlicherweise mehrere wirklich große, korrekt orientierte Counties umgedreht wurden — erkannt und korrigiert durch erneute Validierung gegen alle 64 echten Counties in Colorado.

7. Eine wiederkehrende Fehlerklasse bei der Parameteranzahl und eine strukturelle Lösung. Das wiederholte Inline-Verwenden von geography::Point(?, ?, 4326) innerhalb einer einzelnen Abfrage machte es leicht, die erforderliche Parameterliste falsch zu zählen, was zwei separate Laufzeitfehler „falsche Parameteranzahl" verursachte. Strukturell behoben, indem der Koordinatenpunkt einmal über eine SQL-Variable DECLARE @searchPoint GEOGRAPHY = ... berechnet und in der gesamten Abfrage referenziert wird, wodurch die meisten Abfragen auf nur 2 echte Parameter reduziert und die Fehlerklasse künftig eliminiert wurde, statt nur den unmittelbaren Fall zu beheben.

8. Eine Designlücke bei der Datenvollständigkeit, kein Fehler. check_land_access gab ursprünglich einen einzelnen beliebigen Claim über TOP 1 ohne explizite Sortierung zurück. Tests gegen einen echten, bekannten Claim („Rocket Six", verifiziert anhand der tatsächlichen Mining-Claim-Daten eines Freundes) zeigten, dass 14 separate Claims — 6 aktive, 8 verwaiste — diesen einen Koordinatenpunkt legitimerweise überlappen, was für ein dicht besiedeltes historisches Bergbaurevier in Colorado normal ist. Die Lösung war kein Fehler-Patch, sondern eine bewusste Designentscheidung: jeden aktiven Claim namentlich auflisten (da jeder einzelne davon „nicht graben" bedeutet) und verwaiste Claims als Anzahl zusammenfassen, statt stillschweigend einen auszuwählen und den Rest zu verbergen.

9. Eine mehrstufige Leistungsuntersuchung bei einer zeilenweisen Anreicherungssuche. Nach dem Hinzufügen einer County-Suche zur Anreicherung der Mineralsuchergebnisse begannen häufige Mineralien (Quarz: ~24.570 rohe Treffer) über den MCP-Toolaufruf mit Timeouts. Das Debugging schloss mehrere plausible Ursachen der Reihe nach aus: Das Begrenzen mit TOP (N) auf SQL-Ebene machte die Dinge tatsächlich dramatisch schlechter (4+ Minuten gegenüber ~6 Sekunden ohne Begrenzung) aufgrund einer Regression im Optimierer von SQL Server, wenn TOP mit ORDER BY auf einer teuren berechneten Spalte kombiniert wird; das Begrenzen in Python nach dem Abrufen half ebenfalls nicht, da die eigentlichen Kosten weiterhin in SQL Server anfielen, bevor die Ergebnisse zurückgegeben wurden; und das Umschreiben der County-Suche als korrelierte Unterabfrage, als JOIN und als OUTER APPLY war alles gleichermaßen langsam (~4 Minuten), was bewies, dass der Engpass die schiere Anzahl räumlicher Suchvorgänge war (einer pro rohem Treffer), nicht die Abfragesyntax. Die eigentliche Lösung: eine zweiphasige Abfrage — zuerst schnelles reines Distanz-Matching mit Begrenzung, dann eine räumliche County-Suche nur auf der kleinen endgültigen Ergebnismenge (<=50 Zeilen) statt auf jedem rohen Treffer. Dies ist ein gutes Beispiel dafür, dass die systematische Eliminierung plausibler, aber falscher Hypothesen die eigentliche Arbeit des Leistungs-Debuggings ist, nicht eine einzelne clevere Lösung, die sofort gefunden wird.

Beispielausgabe

> 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

Repository-Inhalt

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, abgegrenzt, aber noch nicht umgesetzt)

• Phase 2: Flüsse/Bäche (Potenzial für Seifenlagerstätten), heiße Quellen (mineralbildende Geologie), Grundgesteins-/geologische Formationsdaten (Macrostrat) — dasselbe Bronze-to-Silver-Raummuster, neue Quellen.

• Phase 3: Trailhead-/Parkplatzeinstiegspunkte, Höhendaten und fahrzeugspezifisches Abgleichen des Straßenzugangs (Bodenfreiheit / 4WD-Anforderungen gegenüber einem bestimmten Fahrzeugprofil).

Datenquellenangabe

Daten bereitgestellt vom Bureau of Land Management (BLM) und U.S. Geological Survey (USGS), verwendet gemäß ihren öffentlichen Datennutzungsbedingungen. Dies ist ein persönliches Projekt und steht in keiner Verbindung zu BLM oder USGS und wird von diesen nicht unterstützt. Die Daten werden „wie besehen" bereitgestellt und können Fehler oder Auslassungen enthalten — überprüfen Sie den Claim-Status und den Landzugang vor einem persönlichen Besuch immer unabhängig.

Weitere Projekte

• Data Engineering & Systems Architecture Portfolio — Eine produktionsreife Medallion-Architektur-Plattform, aufgebaut auf Microsoft Fabric, einschließlich PySpark/Delta-Lake-Pipelines, Copilot-Studio-KI-Agenten, KQL-Eventhouse-Analysen und vollständigem CI/CD über Azure DevOps.