RockHound — 콜로라도 암석 수집 인텔리전스 플랫폼

"콜로라도에서 합법적으로 암석 수집을 할 수 있는 곳은 어디이며, 무엇을 발견할 수 있을까?"라는 실제 질문에 답하는, 거버넌스가 적용된 공간 인지 데이터 플랫폼입니다. 연방 및 주 정부의 원시 데이터부터 Medallion Architecture(Bronze/Silver 레이어링)를 거쳐 거버넌스가 적용된 MCP(Model Context Protocol) 서버까지 엔드투엔드로 구축되었습니다. 이를 통해 AI 에이전트가 원시적이거나 검증되지 않은 소스가 아닌, 실제로 선별되고 신뢰할 수 있는 공간 데이터에 기반하여 암석 수집 질문에 답할 수 있습니다.

저장소 구조: SQL 스크립트는 /sql, Python 코드는 /python에 있습니다 — 실제 구현은 해당 폴더를 참조하세요.

목표

광업 권리(claim) 상태, 토지 소유권, 과거 광물 산출 기록 등 여러 독립적인 공개 데이터셋을 단일 쿼리 가능한 플랫폼으로 결합한 다음, 원시 데이터베이스 접근이 아닌 특정하고 안전하며 사전 승인된 쿼리만 노출하는 거버넌스 인터페이스를 통해 해당 데이터를 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

실제 데이터 소스 (모두 공개, 모두 무료)

모든 소스 레코드는 완전한 데이터 계보(linage)와 출처 추적을 위해 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 — 원격 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) 좌표가 주어지면 전체 현장 보고서를 반환합니다: 토지 소유권 유형, 해당 지점을 덮는 광업 권리 존재 여부(존재한다면 활성 vs. 공실), 카운티, 가장 가까운 도시와 그 거리, 그리고 구성 가능한 검색 반경 내에 문서화된 광물.

두 도구 모두 고정된 매개변수화된 쿼리를 통해서만 선별된 Silver 레이어를 조회합니다 — AI는 임의의 데이터베이스 접근 권한을 얻지 못하며, 오직 이러한 특정하고 안전하며 목적에 맞게 구축된 답변만 얻을 수 있습니다.

실제로 해결한 엔지니어링 과제

이 섹션이 존재하는 이유는 디버깅 과정이 이 프로젝트 전체에서 가장 대표적인 부분이기 때문입니다 — 실제 데이터 엔지니어링은 깔끔한 첫 시도가 아닙니다. 이러한 문제를 찾고 수정하는 데 사용된 실제 진단 쿼리는 /sql/04_example_queries.sql를 참조하세요.

1. 잘못된 공간 지오메트리. 실제 정부 GIS 폴리곤 데이터에는 자기 교차(self-intersecting)/잘못된 지오메트리가 포함되어 있어 SQL Server의 엄격한 geography 타입(24144: instance is not valid)에서 런타임 오류가 발생했습니다. Bronze-to-Silver 변환 중 .MakeValid()를 적용하여 해결했습니다 — /sql/02_silver_schema_and_transform.sql 참조.

2. 조용한 데이터 매핑 버그. 광물 검색이 처음에 commodity_type(실제로 그곳에서 발견된 것으로 문서화된 것)이 아닌 mineral_name(광산의 지명, 예: "Silver King Mine")을 기준으로 매칭하고 있었습니다 — 행 수를 비교하여 발견한 정확성 버그입니다: "Quartz"에 대한 지명 매칭 11건 vs. 실제 품목 매칭 82건.

3. 실제 성능/쿼리 계획 문제. 단순한 JOIN ... ON STDistance(...) < X 패턴은 일반적인 광물에 대해 쿼리가 조용히 13분 이상 걸리게 만들었습니다. SQL Server 옵티마이저가 해당 조인 형태에 공간 인덱스를 사용하지 않았기 때문입니다 — 중첩 루프 조인에서 1억 2,400만+ 건의 예상 행 연산을 보여주는 실행 계획 분석으로 확인했습니다. 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를 통해 서버를 실행하고 HTTPS용 Cloudflare 퀵 터널을 사용하여 우회했으며, 단일 소비자 앱의 특정 인증 요구사항이 아닌 공식 MCP Inspector 도구를 통해 기능을 검증했습니다.

6. 폴리곤 링 방향 반전, 세 개의 별도 테이블에 영향. Shapefile 및 File-Geodatabase 소스 폴리곤(Counties, Cities, 그리고 대규모 역사적 Claims 데이터셋)이 때때로 반전된 링 권선 순서로 저장되었습니다 — SQL Server의 geography 타입이 이를 "X"가 아닌 "X를 제외한 모든 곳"으로 해석했으며, .MakeValid()는 이를 감지하거나 수정하지 못합니다(자기 교차만 수리하고 방향은 수리하지 않음). STArea()에서 터무니없이 큰 값을 확인하여 진단했습니다(올바르게 방향이 지정된 실제 콜로라도 카운티는 지구 전체 표면적(~5억 1,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 도구 호출을 통해 타임아웃되기 시작했습니다. 디버깅 과정에서 여러 그럴듯한 원인을 차례로 배제했습니다: SQL 수준에서 TOP (N)으로 상한을 두는 것은 비싼 계산 열에 TOP과 ORDER BY를 결합할 때 SQL Server 옵티마이저 회귀로 인해 상황을 극적으로 악화시켰고(상한 없이 약 6초 vs. 4분 이상), Python에서 가져온 후 상한을 두는 것도 결과가 반환되기 전에 실제 비용이 SQL Server 내부에서 지불되고 있었기 때문에 도움이 되지 않았으며, 카운티 조회를 상관 서브쿼리, JOIN, OUTER APPLY로 다시 작성하는 것도 모두 동일하게 느렸습니다(약 4분). 이는 병목이 쿼리 구문이 아니라 공간 조회의 절대적 수(원시 매칭당 1회)임을 증명했습니다. 실제 수정: 두 단계 쿼리 — 먼저 빠른 거리 전용 매칭 및 상한 설정, 그 다음 모든 원시 매칭이 아닌 작은 최종 결과 집합(<=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와 제휴하거나 보증하지 않습니다. 데이터는 "있는 그대로" 제공되며 오류나 누락이 포함될 수 있습니다 — 현장을 직접 방문하기 전에 항상 클레임 상태와 토지 접근 권한을 독립적으로 확인하십시오.

기타 프로젝트

• 데이터 엔지니어링 및 시스템 아키텍처 포트폴리오 — Microsoft Fabric 기반의 프로덕션급 Medallion Architecture 플랫폼으로, PySpark/Delta Lake 파이프라인, Copilot Studio AI 에이전트, KQL Eventhouse 분석, Azure DevOps를 통한 전체 CI/CD를 포함합니다.