Ingest USFS FACTS Common Attributes (national, all regions) → public-facts

[kpdavi]

Dataset

USFS FACTS Common Attributes — the comprehensive all-activities record from the Forest Service Activity Tracking System (FACTS), polygon geometry, published per-region on the EDW geodata portal.

Scope (source of truth — do not infer from artifacts)

• Spatial extent: Full national / upstream coverage. No clip. All 9 USFS regions: 01, 02, 03, 04, 05, 06, 08, 09, 10 (there is no Region 07). Merge into a single national dataset.
• Region queryability: The merged dataset MUST carry a region column (values 01..10) so consumers can filter/aggregate by USFS region. Preserve any native region attribute from the source schema as well; if the source lacks a reliable region code, derive region from the source file each feature came from during the preprocess/merge step.
• Source product/version: Actv_CommonAttribute_PL_Region{NN} GeoPackage, EDW snapshot as of 2026-06 (download date 2026-06-24). Record exact per-file Last-Modified in the README at build time.
• Geometry: Polygon (PL).
• H3 resolution: native 10, parent resolutions 9,8,0 (activity/treatment-unit polygons are mostly small; outliers handled by chunking).
• Reducer: N/A (vector — features are indexed, not value-aggregated).
• Bucket / naming: public-facts (new bucket). Snapshot pinned in the path: --dataset common-attributes-2026-06 → S3 prefix public-facts/common-attributes-2026-06, PMTiles source-layer = common-attributes-2026-06, k8s/job prefix common-attributes-2026-06.
• License: public-domain (US federal government work). Add a license link to the FS EDW terms.

Source files (all verified live 2026-06-24, GeoPackage, ~7.2 GB zipped total)

Region	Size (zip)	URL
01	2564.8 MB	https://data.fs.usda.gov/geodata/edw/edw_resources/gpkg/Actv_CommonAttribute_PL_Region01.zip
02	731.0 MB	https://data.fs.usda.gov/geodata/edw/edw_resources/gpkg/Actv_CommonAttribute_PL_Region02.zip
03	260.2 MB	https://data.fs.usda.gov/geodata/edw/edw_resources/gpkg/Actv_CommonAttribute_PL_Region03.zip
04	408.9 MB	https://data.fs.usda.gov/geodata/edw/edw_resources/gpkg/Actv_CommonAttribute_PL_Region04.zip
05	915.2 MB	https://data.fs.usda.gov/geodata/edw/edw_resources/gpkg/Actv_CommonAttribute_PL_Region05.zip
06	1127.8 MB	https://data.fs.usda.gov/geodata/edw/edw_resources/gpkg/Actv_CommonAttribute_PL_Region06.zip
08	492.5 MB	https://data.fs.usda.gov/geodata/edw/edw_resources/gpkg/Actv_CommonAttribute_PL_Region08.zip
09	634.4 MB	https://data.fs.usda.gov/geodata/edw/edw_resources/gpkg/Actv_CommonAttribute_PL_Region09.zip
10	64.8 MB	https://data.fs.usda.gov/geodata/edw/edw_resources/gpkg/Actv_CommonAttribute_PL_Region10.zip

Deliverables

• common-attributes-2026-06.parquet (GeoParquet, merged national, with region column) — --row-group-size 2000 to stay under the DuckDB httpfs stoi large-chunk cliff (geometry-heavy national data).
• common-attributes-2026-06.pmtiles (with tile-accurate table:columns; include region as a filterable field).
• common-attributes-2026-06/hex/h0=*/data_0.parquet (H3 res 10; parents 9,8,0).
• README.md (MapLibre example w/ source-layer: common-attributes-2026-06; DuckDB example showing a WHERE region = '05' filter).
• stac-collection.json under public-facts/, registered as a child of a new public-facts bucket-level collection, which is linked from the root catalog.

Build approach (guidance, not scope)

• Preprocess/merge: download 9 zips in parallel (~7.2 GB), unzip, convert+merge to one national GeoParquet (multi-.zip not allowed by cng-convert-to-parquet — use the preprocess-job pattern). Stamp the region column per source file during merge. Raw zips → s3://public-facts/raw/ first.
• Armada backend recommended given volume; tune chunk size from final feature count. The k8s backend 200-completion cap is likely insufficient.
• Document the coded categorical columns (activity codes, method/equipment, fund codes, etc.) with full CODE=Definition value lists in STAC asset-level table:columns — discover actual values via the DuckDB MCP after the parquet lands.

New-bucket chores

• Add catalog/sync/k8s/sync-public-facts.yaml (MinIO mirror).
• Classify license for source.coop mirror (public-domain → eligible; add to gen-source-sync.sh scope + regenerate the source-sync-scope ConfigMap).

Acceptance criteria

• National merged GeoParquet on public-facts; feature count == sum of the 9 regional source counts (verify each region's count via ogrinfo/DuckDB MCP).
• region column present and correct (distinct values exactly 01,02,03,04,05,06,08,09,10; counts per region match sources).
• PMTiles renders in MapLibre with source-layer: common-attributes-2026-06; region is filterable.
• Hex parquet present for all populated h0 cells; res 10 with parents 9,8,0.
• STAC collection valid, license = public-domain, asset-level table:columns with coded-value definitions, hex asset carries h3:native_resolution/h3:parent_resolutions + per-feature dedup warnings.
• STAC registered in public-facts collection and reachable from root catalog.
• MinIO sync job added.

[kpdavi]

Pre-build verification complete (2026-06-24)

Ran a schema-consistency + inventory job over all 9 region GeoPackages (mirrored to s3://public-facts/raw/). Results:

Schema consistency: all 9 regions are IDENTICAL — same 109 fields, same types, same layer name Actv_CommonAttribute_PL. The UNION ALL merge is safe.

National feature count: 7,324,720 polygons

R01	R02	R03	R04	R05	R06	R08	R09	R10
2,834,442	605,986	231,633	331,945	748,608	1,199,122	464,516	845,750	62,718

Source CRS: NAD83 / EPSG:4269 (reprojected to EPSG:4326 at convert time). Geometry column geom, mixed polygon/multipolygon.

Region-handling decision (refines the original "add a region column" scope)

The native ADMIN_REGION column is verified reliable: exactly one distinct value per source file, zero-padded, matching the file (R01→01 … R10→10), never null. It already provides the exact region-queryability requested (values 01–10).

Decision: use the native ADMIN_REGION as the region field — do NOT stamp a separate region column. Stamping would require a full ogr2ogr rewrite of 7.3M geometries for zero benefit. ADMIN_REGION will be documented prominently as the region field in the STAC collection and README.

Acceptance criterion updated accordingly: "region queryability satisfied via native ADMIN_REGION (distinct values exactly 01,02,03,04,05,06,08,09,10)".