RockHound — コロラド州ロックハウンディング情報プラットフォーム

統治された空間認識型データプラットフォームであり、「コロラド州で合法的にロックハウンディングができる場所はどこか、そして何が見つかる可能性が高いか」という現実の問いに答えます。連邦政府および州政府の生データから、Medallion Architecture（Bronze/Silver 層）を経由して、統治された MCP（Model Context Protocol）サーバー までエンドツーエンドで構築されています。これにより、AI エージェントは、生の情報や未検証の情報源ではなく、実際の厳選された信頼できる空間データに基づいてロックハウンディングの質問に答えることができます。

リポジトリ構成: SQL スクリプトは /sql、Python コードは /python — 実際の実装はこれらのフォルダを参照してください。

目標

鉱区のステータス、土地所有権、歴史的な鉱物産出記録という複数の独立した公開データセットを、単一のクエリ可能なプラットフォームに統合し、そのデータを、生のデータベースアクセスではなく、特定の安全で事前承認されたクエリのみを公開する統治されたインターフェースを通じて AI システムに公開します。これは、現代のデータエンジニアリングの役割でますます求められている「AI 対応・統治済みデータプロダクト」と同じパターンを反映しています。

このプラットフォームが答える具体的な質問: 「ある鉱物の記録された産出地点の近くにある、放棄済み/失効済みの鉱区を見つけ、そこに実際に立ち入ることが許可されているかどうかを教えてください。」

Related MCP server: arcgis-lacounty

アーキテクチャ

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

実際のデータソース（すべて公開・無料）

すべてのソースレコードには、完全なデータ系統と来歴の追跡のために source_url と source_type（例：「政府機関」）が含まれています — これは意図的に組み込まれたガバナンスパターンであり、後付けではありません。

技術スタック

• SQL Server — ネイティブの geography 空間データ型、空間インデックス、STDistance/STIntersects/STContains、MakeValid()

• Python — geopandas、pandas、pyodbc、shapely

• MCP Python SDK（mcp.server）— Streamable HTTP トランスポート

• MCP Inspector — MCP サーバーのテスト・検証用の公式ツール

• Cloudflare Tunnel — リモート MCP クライアントテスト用のローカル HTTPS 公開

使用したツールとプラットフォーム

実際の開発環境がこのプロジェクトの重要な一部であるため、何に何を使ったかの詳細な内訳を示します。

データソース（生データの入手元）

データベースとクエリ開発

Python 開発

MCP 固有のツール

バージョン管理とホスティング

AI システムに生の SQL アクセスを公開するのではなく、意図的に範囲を限定した 2 つの統治されたツール:

find_vacant_claims_near_mineral(mineral_name, max_distance_miles) 指定された鉱物の記録された歴史的産出地点の近くにある放棄済み/失効済み鉱区を検索し、最も最近閉鎖された鉱区（最も新しい機会）にフラグを立て、各鉱区がどの郡に該当するかを示し、近接度で並べ替えます。

check_land_access(latitude, longitude, mineral_search_radius_miles) 座標を指定すると、完全なサイトレポートを返します: 土地所有権の種類、その地点をカバーする鉱区があるかどうか（ある場合は有効か放棄済みか）、郡、最寄りの都市とその距離、および設定可能な検索半径内で記録された鉱物。

両方のツールは、固定されたパラメータ化クエリを通じて厳選された Silver 層のみをクエリします — AI は任意のデータベースアクセスを決して取得できず、これらの特定の安全で目的に特化した回答のみを取得できます。

解決した実際のエンジニアリング課題

このセクションが存在する理由は、デバッグプロセスがプロジェクト全体の中で最も代表的な部分であるためです — 実際のデータエンジニアリングは、きれいな最初のパスではありません。これらの問題の発見と修正に使用した実際の診断クエリについては、/sql/04_example_queries.sql を参照してください。

1. 無効な空間ジオメトリ。 実際の政府GISポリゴンデータには自己交差・無効なジオメトリが含まれており、SQL Serverの厳格なgeography型（24144: instance is not valid）で実行時エラーが発生しました。BronzeからSilverへの変換中に.MakeValid()を適用して修正しました — /sql/02_silver_schema_and_transform.sqlを参照してください。

2. 静かなデータマッピングバグ。 鉱物検索は当初、commodity_type（実際にそこで見つかったと記録されているもの）ではなくmineral_name（鉱山のサイト名、例：「Silver King Mine」）に対してマッチングしていました — 行数を比較することで発見された正確性のバグです：「Quartz」のサイト名マッチが11件だったのに対し、実際の商品マッチは82件でした。

3. 実際のパフォーマンス/クエリプランの問題。 単純なJOIN ... ON STDistance(...) < Xパターンでは、一般的な鉱物に対してクエリが静かに13分以上かかっていました。SQL Serverのオプティマイザがその結合形状に対して空間インデックスを使用していなかったためです — 実行プラン分析により、ネストループ結合で1億2400万以上の推定行操作が確認されました。CROSS APPLYを中心にクエリを再構成することで修正しました（最近傍検索で空間インデックスの使用を確実にトリガーする文書化されたパターン）。同じクエリが約36秒に短縮されました。/sql/04_example_queries.sqlを参照してください。

4. 全国規模のデータフィルタリング。 連邦政府ソースからのいくつかの「コロラド」データセットは実際には全国規模でした（ある有効な請求ファイルは、コロラド州の14,699行にフィルタリングする前は579,730行でした）。ダウンストリームで読み込んで破棄するのではなく、取り込み中にバウンディングボックス交差でフィルタリングしました — /python/load_bronze.pyのCOLORADO_BBOX_WKTを参照してください。

5. MCPクライアント統合。 対象のMCPクライアントのリモートコネクタフローが、認証なしのローカルサーバーでもOAuthクライアント登録を期待していることが判明しました。HTTPS用のCloudflareクイックトンネルを使用してStreamable HTTP経由でサーバーを実行することで回避し、単一のコンシューマアプリの特定の認証要件ではなく、公式のMCP Inspectorツールを通じて機能を検証しました。

6. 反転したポリゴンリングの向き（3つの別々のテーブルに影響）。 ShapefileおよびFile-Geodatabaseソースのポリゴン（Counties、Cities、および大規模な歴史的Claimsデータセット）は、リングの巻き方向が反転して保存されることがありました — SQL Serverのgeography型はこれらを「X」ではなく「X以外のすべて」として解釈しました。.MakeValid()はこれを検出も修正もできません（自己交差のみを修復し、向きは修復しません）。STArea()で非現実的に大きな値をチェックして診断しました（正しく向き付けられた本物のコロラド州の郡が、地球の総表面積である約5億1,000万平方kmに近づくことは決してありません）。面積しきい値に基づく条件付き.ReorientObject()で修正しました。この修正の最初の試みでは誤った単位を使用しました（STArea()は平方キロメートルではなく平方メートルを返します）。そのため、実際に大きく正しく向き付けられたいくつかの郡が誤って反転しました — コロラド州の実際の64郡すべてに対して再検証することで発見・修正されました。

7. 繰り返し発生するパラメータ数バグのクラスと構造的修正。 単一のクエリ内でgeography::Point(?, ?, 4326)をインラインで複数回繰り返すと、必要なパラメータリストを数え間違えやすくなり、2つの別々の「パラメータ数が正しくない」実行時エラーが発生しました。SQLのDECLARE @searchPoint GEOGRAPHY = ...変数を使用して座標ポイントを一度計算し、クエリ全体で参照することで構造的に修正しました。これにより、ほとんどのクエリが実質2つのパラメータに削減され、当面のインスタンスを修正するだけでなく、今後このバグクラスを排除しました。

8. バグではなくデータ完全性の設計ギャップ。 check_land_accessは当初、明示的な順序なしでTOP 1を使用して単一の任意の請求を返していました。実際の既知の請求（友人の実際の鉱業請求データに対して検証した「Rocket Six」）に対してテストしたところ、14の別々の請求（6件が有効、8件が空き）がその1つの座標と正当に重なっていることが明らかになりました。これは密集した歴史的なコロラド鉱業地区では正常です。修正はバグパッチではなく、意図的な設計上の決定でした：有効な請求をすべて名前でリストし（いずれか1つでも「掘るな」を意味するため）、空き請求は1つを黙って選んで残りを隠すのではなく、件数として要約します。

9. 行ごとのエンリッチメントルックアップに関する多段階のパフォーマンス調査。 鉱物検索結果をエンリッチするための郡ルックアップを追加した後、一般的な鉱物（Quartz：約24,570件の生マッチ）がMCPツール呼び出しでタイムアウトし始めました。デバッグにより、いくつかのもっともらしい原因が順に除外されました：SQLレベルでのTOP (N)によるキャッピングは、高コストな計算列でTOPとORDER BYを組み合わせた場合のSQL Serverオプティマイザのリグレッションにより、実際には劇的に悪化しました（キャップなしの約6秒に対して4分以上）。フェッチ後にPythonでキャッピングしても役に立ちませんでした。実際のコストは結果が返される前にSQL Server内で支払われていたためです。また、郡ルックアップを相関サブクエリ、JOIN、OUTER APPLYとして書き直してもすべて同様に遅く（約4分）、ボトルネックがクエリ構文ではなく、空間ルックアップの数（生マッチごとに1回）であることが証明されました。実際の修正：2フェーズのクエリ — まず高速な距離のみのマッチングとキャッピングを行い、次にすべての生マッチではなく、小さな最終結果セット（<=50行）に対してのみ空間郡ルックアップを実行します。これは、もっともらしいが誤った仮説を体系的に排除することが、すぐに見つかる単一の巧妙な修正ではなく、パフォーマンスデバッグの実際の作業であることを示す良い例です。

出力例

> 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

リポジトリの内容

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

ロードマップ（フェーズ2 / フェーズ3、スコープは定義済みだが未構築）

• フェーズ2： 河川/小川（漂砂鉱床の可能性）、温泉（鉱物形成地質）、基盤岩/地質層データ（Macrostrat） — 同じBronze-to-Silver空間パターン、新しいソース。

• フェーズ3： トレイルヘッド/駐車場の入口、標高データ、および車両固有の道路アクセスマッチング（最低地上高/4WD要件と特定の車両プロファイルの比較）。

データ帰属

データは土地管理局（BLM）および米国地質調査所（USGS）によって提供され、その公開データ利用規約に従って使用されています。これは個人プロジェクトであり、BLMまたはUSGSとは提携しておらず、その承認も受けていません。データは「現状のまま」提供され、誤りや欠落が含まれる可能性があります — 現地を訪問する前に、必ず請求ステータスと土地へのアクセスを独立して確認してください。

その他のプロジェクト

• Data Engineering & Systems Architecture Portfolio — Microsoft Fabric上に構築された本番グレードのMedallion Architectureプラットフォーム。PySpark/Delta Lakeパイプライン、Copilot Studio AIエージェント、KQL Eventhouse分析、Azure DevOpsによる完全なCI/CDを含みます。