RockHound — Colorado Rockhounding Intelligence Platform

Управляемая, пространственно-ориентированная платформа данных, отвечающая на реальный вопрос: «Где я могу легально заниматься поиском камней в Колорадо и что я, вероятно, найду?» Создана полностью — от необработанных федеральных и государственных данных через архитектуру Medallion (слои Bronze/Silver) до управляемого MCP-сервера (Model Context Protocol) — позволяя ИИ-агенту отвечать на вопросы о поиске камней, опираясь на реальные, курируемые, достоверные пространственные данные, а не на необработанные или непроверенные источники.

Структура репозитория: SQL-скрипты в /sql, Python-код в /python — см. эти папки для фактической реализации.

Цель

Объединить несколько независимых публичных наборов данных — статус горных заявок, землевладение и исторические записи о минеральных проявлениях — в единую запрашиваемую платформу, а затем предоставить эти данные ИИ-системе через управляемый интерфейс, который предоставляет только конкретные, безопасные, предварительно одобренные запросы, а не прямой доступ к базе данных. Это отражает тот же паттерн «готового к ИИ, управляемого продукта данных», который всё чаще требуется в современных ролях инженера данных.

Конкретный вопрос, на который это отвечает: «Найти свободные/просроченные горные заявки рядом с задокументированными проявлениями минерала и сказать, разрешено ли мне там находиться».

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 (например, «Government Agency») для полной прослеживаемости и происхождения данных — паттерн управления, встроенный намеренно, а не как запоздалая мысль.

Технологический стек

• SQL Server — нативный пространственный тип данных geography, пространственное индексирование, STDistance/STIntersects/STContains, MakeValid()

• Python — geopandas, pandas, pyodbc, shapely

• MCP Python SDK (mcp.server) — транспорт Streamable HTTP

• MCP Inspector — официальный инструмент для тестирования/проверки MCP-серверов

• Cloudflare Tunnel — локальное HTTPS-подключение для удалённого тестирования MCP-клиентов

Инструменты и платформы

Подробное описание того, что для чего использовалось, поскольку фактическая среда разработки — часть реальной истории.

Источники данных (откуда взялись необработанные данные)

Разработка базы данных и запросов

Разработка на Python

Инструменты, специфичные для MCP

Контроль версий и хостинг

Два управляемых инструмента, намеренно ограниченных по объёму, а не предоставляющих ИИ-системе необработанный доступ к SQL:

find_vacant_claims_near_mineral(mineral_name, max_distance_miles) Находит свободные/просроченные заявки рядом с задокументированными историческими проявлениями заданного минерала, помечая, какие заявки закрылись недавнее всего (самые свежие возможности), в каком округе каждая находится, и сортируя по близости.

check_land_access(latitude, longitude, mineral_search_radius_miles) По заданным координатам возвращает полный отчёт о местности: тип землевладения, покрывает ли какую-либо горную заявку эта точка (и если да, активную или свободную), округ, ближайший город и расстояние до него, а также любые минералы, задокументированные в пределах настраиваемого радиуса поиска.

Оба инструмента запрашивают только курируемый слой Silver через фиксированные параметризованные запросы — ИИ никогда не получает произвольный доступ к базе данных, только эти конкретные, безопасные, целевые ответы.

Реальные инженерные проблемы, которые были решены

Этот раздел существует потому, что процесс отладки, пожалуй, самая показательная часть всего проекта — реальная инженерия данных — это не чистый первый проход. См. /sql/04_example_queries.sql для фактических диагностических запросов, использованных для поиска и исправления этих проблем.

1. Недопустимая пространственная геометрия. Реальные правительственные ГИС-полигональные данные содержали самопересекающиеся/недопустимые геометрии, что вызывало ошибки выполнения в строгом типе geography SQL Server (24144: instance is not valid). Исправлено с помощью .MakeValid(), применённого во время преобразования Bronze-to-Silver — см. /sql/02_silver_schema_and_transform.sql.

2. Тихая ошибка сопоставления данных. Поиск полезных ископаемых изначально сопоставлялся с mineral_name (название участка шахты, например, «Silver King Mine»), а не с commodity_type (что фактически задокументировано как найденное там) — ошибка корректности, обнаруженная при сравнении количества строк: 11 совпадений по названию участка для «Quartz» против 82 реальных совпадений по товарному типу.

3. Реальная проблема производительности/плана запроса. Простой шаблон JOIN ... ON STDistance(...) < X приводил к тому, что запросы для распространённых полезных ископаемых молча выполнялись 13+ минут, потому что оптимизатор SQL Server не использовал пространственный индекс для такой формы соединения — подтверждено анализом плана выполнения, показавшим 124M+ оценочных операций со строками на вложенном цикле соединения. Исправлено путём перестройки запроса вокруг CROSS APPLY (документированный шаблон для надёжного использования пространственного индекса в поиске ближайших соседей), что сократило время выполнения того же запроса до ~36 секунд. См. /sql/04_example_queries.sql.

4. Фильтрация данных национального масштаба. Несколько наборов данных «Colorado» из федеральных источников на самом деле были общенациональными (один файл активных заявок содержал 579,730 строк до фильтрации до 14,699 строк Колорадо). Фильтрация выполнялась через пересечение ограничивающего прямоугольника при загрузке, а не путём загрузки и отбрасывания на последующих этапах — см. COLORADO_BBOX_WKT в /python/load_bronze.py.

5. Интеграция MCP-клиента. Обнаружено, что процесс удалённого подключения целевого MCP-клиента ожидает регистрации OAuth-клиента даже для неаутентифицированных локальных серверов. Обходное решение заключалось в запуске сервера через Streamable HTTP с быстрым туннелем Cloudflare для HTTPS, а функциональность проверялась с помощью официального инструмента MCP Inspector, а не через специфические требования аутентификации отдельного приложения-потребителя.

6. Инвертированная ориентация колец полигонов, затрагивающая три отдельные таблицы. Полигоны из шейп-файлов и файловых баз геоданных (Counties, Cities и большой исторический набор данных Claims) иногда хранились с обратным порядком обхода колец — тип geography в SQL Server интерпретировал их как «везде, кроме X», а не как «X», что .MakeValid() не обнаруживает и не исправляет (он только исправляет самопересечения, а не ориентацию). Диагностировано путём проверки STArea() на нереалистично большие значения (настоящий, правильно ориентированный округ Колорадо никогда не должен приближаться к ~510,000,000 кв. км — общей площади поверхности Земли). Исправлено с помощью условного .ReorientObject() на основе порога площади. Первая попытка исправления использовала неправильную единицу измерения (STArea() возвращает квадратные метры, а не квадратные километры), что ошибочно перевернуло несколько действительно больших, правильно ориентированных округов — обнаружено и исправлено путём повторной проверки по всем 64 реальным округам Колорадо.

7. Повторяющийся класс ошибок с количеством параметров и структурное исправление. Повторение geography::Point(?, ?, 4326) встроенно несколько раз в одном запросе облегчало ошибку в подсчёте необходимого списка параметров, что вызывало две отдельные ошибки выполнения «неверное количество параметров». Исправлено структурно: координатная точка вычисляется один раз через SQL-переменную DECLARE @searchPoint GEOGRAPHY = ... и используется во всём запросе, что сокращает большинство запросов до всего 2 реальных параметров и устраняет класс ошибок на будущее, а не просто исправляет конкретный случай.

8. Пробел в полноте данных, а не ошибка. check_land_access изначально возвращал одну произвольную заявку через TOP 1 без явной сортировки. Тестирование на реальной известной заявке («Rocket Six», проверенной по фактическим данным о горнодобывающих заявках друга) показало, что 14 отдельных заявок — 6 активных, 8 свободных — законно перекрывают эту одну координату, что нормально для плотного исторического горнодобывающего округа Колорадо. Исправление было не патчем ошибки, а осознанным проектным решением: перечислить каждую активную заявку по имени (поскольку любая из них означает «не копайте»), а свободные заявки обобщить как количество, а не молча выбирать одну и скрывать остальные.

9. Многоэтапное исследование производительности для обогащения по каждой строке. После добавления поиска округа для обогащения результатов поиска полезных ископаемых распространённые минералы (Quartz: ~24,570 необработанных совпадений) начали выходить за пределы времени ожидания при вызове через MCP-инструмент. Отладка последовательно исключила несколько правдоподобных причин: ограничение с помощью TOP (N) на уровне SQL на самом деле сделало ситуацию значительно хуже (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

Дорожная карта (Фаза 2 / Фаза 3, определена, но ещё не реализована)

• Фаза 2: Реки/ручьи (потенциал россыпных месторождений), горячие источники (минералообразующая геология), данные о коренных породах/геологических формациях (Macrostrat) — та же пространственная схема Bronze-to-Silver, новые источники.

• Фаза 3: Точки входа на тропы/парковки, данные о высоте и сопоставление доступа по дорогам с учётом конкретного автомобиля (дорожный просвет / требования 4WD против конкретного профиля транспортного средства).

Атрибуция данных

Данные предоставлены Бюро землеустройства (BLM) и Геологической службой США (USGS) и используются в соответствии с их условиями публичных данных. Это личный проект, не связанный с BLM или USGS и не одобренный ими. Данные предоставляются «как есть» и могут содержать ошибки или пропуски — всегда самостоятельно проверяйте статус заявки и доступ к земле перед личным посещением любого объекта.

Другие проекты

• Портфолио по инженерии данных и системной архитектуре — производственная платформа Medallion Architecture, построенная на Microsoft Fabric, включая конвейеры PySpark/Delta Lake, AI-агентов Copilot Studio, аналитику KQL Eventhouse и полный CI/CD через Azure DevOps.