RockHound — 科罗拉多州岩矿采集智能平台

一个受治理、具备空间感知能力的数据平台，回答一个真实问题："在科罗拉多州，我可以在哪些地方合法地进行岩矿采集，以及我可能会找到什么？"平台从原始联邦和州政府数据端到端构建，通过 Medallion 架构（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 访问权限：

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-to-Silver转换期间应用.MakeValid()修复 — 参见/sql/02_silver_schema_and_transform.sql。

2. 一个静默的数据映射bug。 矿物搜索最初匹配的是mineral_name（矿场的场地名称，例如"Silver King Mine"），而不是commodity_type（实际记录为在那里发现的矿物） — 这是一个通过比较行数发现的正确性bug："Quartz"有11个场地名称匹配，而实际的commodity匹配有82个。

3. 一个真实的性能/查询计划问题。 直接的JOIN ... ON STDistance(...) < X模式导致常见矿物的查询静默耗时13分钟以上，因为SQL Server的优化器没有为该连接形状使用空间索引 — 通过执行计划分析确认，显示嵌套循环连接上有1.24亿+的预估行操作。通过围绕CROSS APPLY重构查询修复（这是在最近邻搜索中可靠触发空间索引使用的文档化模式），将同一查询降至约36秒。参见/sql/04_example_queries.sql。

4. 全国范围的数据过滤。 来自联邦来源的几个标注为"Colorado"的数据集实际上是全国范围的（一个活跃权利要求文件在过滤到科罗拉多州的14,699行之前有579,730行）。在摄取期间通过边界框相交过滤，而不是加载后在后续阶段丢弃 — 参见/python/load_bronze.py中的COLORADO_BBOX_WKT。

5. MCP客户端集成。 发现目标MCP客户端的远程连接器流程即使对于未经身份验证的本地服务器也要求OAuth客户端注册。通过使用Streamable HTTP运行服务器，并借助Cloudflare快速隧道提供HTTPS来规避该问题，并通过官方MCP Inspector工具验证了功能，而不是依赖单个消费者应用特定的认证要求。

6. 倒置的多边形环方向，影响三个独立的表。 源自Shapefile和File-Geodatabase的多边形（Counties、Cities以及大型历史Claims数据集）有时以反转的环缠绕顺序存储 -- SQL Server的geography类型将这些解释为"除X之外的所有地方"而非"X"，而.MakeValid()无法检测或修复这一点（它只修复自相交，不修复方向）。通过检查STArea()是否出现大得不合理的值来诊断（一个真实的、方向正确的科罗拉多县不应接近约510,000,000平方千米 -- 地球的总表面积）。通过基于面积阈值的条件.ReorientObject()修复。此修复的第一次尝试使用了错误的单位（STArea()返回的是平方米，而非平方千米），导致错误地翻转了几个真正大且方向正确的县 -- 通过对照科罗拉多州全部64个真实县重新验证而发现并纠正。

7. 一个反复出现的参数计数bug类别，以及一种结构性修复。 在单个查询中多次内联重复geography::Point(?, ?, 4326)，很容易数错所需的参数列表，导致两次独立的"参数数量错误"运行时错误。通过结构性方式修复：使用SQL DECLARE @searchPoint GEOGRAPHY = ...变量一次计算出坐标点，并在整个查询中引用它，将大多数查询减少到仅2个实际参数，从根源上消除该bug类别，而不仅仅是修复当前的实例。

8. 一个数据完整性设计缺口，而非bug。 check_land_access最初通过TOP 1返回单个任意权利要求，且没有显式排序。针对一个真实的、已知的权利要求（"Rocket Six"，对照一位朋友的实际采矿权利要求数据验证）进行测试后发现，有14个独立的权利要求 -- 6个活跃、8个空置 -- 合法地重叠于该坐标，这对于密集的历史科罗拉多矿区来说是正常的。修复不是bug补丁，而是一个深思熟虑的设计决策：按名称列出每个活跃权利要求（因为其中任何一个都意味着"不要挖掘"），并将空置权利要求汇总为计数，而不是静默地挑选一个并隐藏其余部分。

9. 一次针对逐行富化查找的多阶段性能调查。 在添加县查找以富化矿物搜索结果后，常见矿物（Quartz：约24,570个原始匹配）开始通过MCP工具调用超时。调试依次排除了几个看似合理的原因：在SQL层面使用TOP (N)限制实际上使情况显著恶化（4分钟以上，而无限定时约6秒），原因是SQL Server优化器在TOP与昂贵计算列上的ORDER BY组合时出现回归；在Python中获取后再限制也没有帮助，因为实际成本仍在SQL Server内部、在结果返回之前付出；将县查找重写为相关子查询、JOIN和OUTER APPLY都同样慢（约4分钟），证明瓶颈是大量的空间查找（每个原始匹配一次），而非查询语法。实际的修复：两阶段查询 -- 先进行快速的仅距离匹配和限制，然后仅在小的最终结果集（<=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

路线图（Phase 2 / Phase 3，已界定范围但尚未构建）

• Phase 2： 河流/溪流（砂矿沉积潜力）、温泉（成矿地质）、基岩/地质构造数据（Macrostrat）— 相同的Bronze-to-Silver空间模式，新数据源。

• Phase 3： 步道起点/停车场入口点、高程数据，以及针对特定车辆的道路通行匹配（离地间隙/4WD要求 vs. 特定的车辆配置文件）。

数据归属

数据由Bureau of Land Management（BLM）和U.S. Geological Survey（USGS）提供，并按照其公共数据条款使用。这是一个个人项目，与BLM或USGS无关联，也未获得其认可。数据按"原样"提供，可能包含错误或遗漏 -- 在亲自到访任何地点之前，请务必独立核实权利要求状态和土地通行情况。

其他项目

• Data Engineering & Systems Architecture Portfolio — 一个构建在Microsoft Fabric上的生产级Medallion Architecture平台，包括PySpark/Delta Lake管道、Copilot Studio AI代理、KQL Eventhouse分析，以及通过Azure DevOps实现的完整CI/CD。