R2 Miljø (Environment) Data Catalog

Environmental data including pesticides, nitrogen leaching, protected areas, and soil.

Frontend Metrics Supported

Available Datasets

Silver Layer

Pesticides (316K rows)

Path: r2://landbruget-data/silver/pesticides/*/data.parquet

BNBO Status (5.4K rows)

Path: r2://landbruget-data/silver/bnbo_status/*/data.parquet

Schema (introspected):

geometry: binary (WKB)
area_ha: double
temanavn: string
status_bnbo: string
kommunenav: string
anlaegsnav: string
dgunr: string
[30 columns total]

Wetlands (1.7M rows)

Path: r2://landbruget-data/silver/wetlands/*/data.parquet

Soil Types

Path: r2://landbruget-data/silver/soil_types/*/data.parquet

Slurry Leaks (Incidents)

Path: r2://landbruget-data/silver/slurry leaks/*/data.parquet

Gold Layer

Pesticide Disaggregation (1.52M rows)

Path: r2://landbruget-data/gold/pesticidedisaggregation{year}/*/data.parquet

Schema (introspected):

DisaggregatedID: int64
cvr_number: string
PesticideName: string
PesticideRegistrationNumber: string
DosageQuantity: double
DosageUnit: string
MatchedFieldID: string
MatchedBlockID: string
AllocatedArea: double
field_uuid: string
municipality: string
[17 columns total]

NLES5 Nitrogen Estimates (500K rows)

Path: r2://landbruget-data/gold/nles5nitrogen{year}/*/data.parquet

Schema (introspected):

field_id: string
field_uuid: string
block_id: string
cvr_number: string
year: int64
area_ha: double
crop_type: string
soil_code: string
soil_description: string
clay_content: double
nitrogen_washout_kg_ha: double
percolation_mm: double
data_quality_score: double
[20 columns total]

Field Intersections (Environmental Overlaps)

Path: r2://landbruget-data/gold/fieldanalysis{year}intersections//data.parquet

Common Queries

Get Pesticide Usage by CVR

import duckdb
from common.storage.filesystem import setup_duckdb_cloud_auth

conn = duckdb.connect()
setup_duckdb_cloud_auth(conn)

# Read pesticide disaggregation data
df = conn.execute("""
    SELECT * FROM read_parquet('r2://landbruget-data/gold/pesticide_disaggregation_2024/2025-01-10/data.parquet')
""").df()

# Filter by CVR
company_pesticides = df[df['cvr_number'] == '31373077']
print(f"Total pesticide records: {len(company_pesticides)}")
print(f"Unique pesticides used: {company_pesticides['PesticideName'].nunique()}")

# Summarize by pesticide
usage_summary = company_pesticides.groupby('PesticideName').agg({
    'DosageQuantity': 'sum',
    'AllocatedArea': 'sum'
}).reset_index()

Find Fields in BNBO Protection Zones

# Read BNBO zones
bnbo = conn.execute("""
    SELECT * FROM read_parquet('r2://landbruget-data/silver/bnbo_status/2025-01-10/data.parquet')
""").df()

# Read field boundaries
fields = conn.execute("""
    SELECT * FROM read_parquet('r2://landbruget-data/silver/fvm_marker_2024/2025-01-10/data.parquet')
""").df()

# For spatial joins, convert to GeoDataFrames
import geopandas as gpd
bnbo_gdf = gpd.GeoDataFrame(bnbo, geometry=gpd.GeoSeries.from_wkb(bnbo['geometry']), crs='EPSG:4326')
fields_gdf = gpd.GeoDataFrame(fields, geometry=gpd.GeoSeries.from_wkb(fields['geometry']), crs='EPSG:4326')

fields_in_bnbo = gpd.sjoin(fields_gdf, bnbo_gdf, how='inner', predicate='intersects')
print(f"Fields overlapping BNBO zones: {len(fields_in_bnbo)}")

Calculate Nitrogen Leaching by Municipality

# Read nitrogen data
nitrogen = conn.execute("""
    SELECT * FROM read_parquet('r2://landbruget-data/gold/nles5_nitrogen_2024/2025-01-10/data.parquet')
""").df()

# Read field data with municipality
fields = conn.execute("""
    SELECT * FROM read_parquet('r2://landbruget-data/silver/fvm_marker_2024/2025-01-10/data.parquet')
""").df()

# Join and aggregate
nitrogen_with_muni = nitrogen.merge(
    fields[['field_id', 'municipality']],
    on='field_id',
    how='left'
)

muni_stats = nitrogen_with_muni.groupby('municipality').agg({
    'nitrogen_washout_kg_ha': 'mean',
    'area_ha': 'sum'
}).reset_index()
muni_stats.columns = ['municipality', 'avg_n_leaching', 'total_area']

Glyphosate Usage Analysis

# Filter for glyphosate products
glyphosate = df[
    df['PesticideName'].str.contains('glyph|roundup', case=False, na=False)
]

# Summarize by municipality
glyph_by_muni = glyphosate.groupby('municipality').agg({
    'DosageQuantity': 'sum',
    'AllocatedArea': 'sum'
}).reset_index()

# Calculate intensity
glyph_by_muni['dose_per_ha'] = glyph_by_muni['DosageQuantity'] / glyph_by_muni['AllocatedArea']

Wetland Overlap Analysis

# Read wetlands
wetlands = conn.execute("""
    SELECT * FROM read_parquet('r2://landbruget-data/silver/wetlands/2025-01-10/data.parquet')
""").df()

# High peat content areas
high_peat = wetlands[wetlands['toerv_pct'] > 50]
print(f"High peat wetland polygons: {len(high_peat)}")

Join Keys

Data Quality Notes

Pesticide Disaggregation

• Update frequency: Annual (after reporting deadline)
• Coverage: Disaggregated to field level from company-level reports
• Caveat: Allocation is modeled based on crop types

NLES5 Nitrogen

• Update frequency: Annual
• Model: NLES5 model output from Aarhus University
• Coverage: All agricultural fields
• Quality: dataqualityscore indicates confidence

BNBO Status

• Update frequency: Quarterly
• Coverage: All designated drinking water protection zones
• Source: Danish EPA (Miljøstyrelsen)

Wetlands

• Update frequency: Annual
• Source: Danish Environmental Portal
• Note: toerv_pct indicates peat soil percentage

Related Skills

• landbrugsareal/ - Field boundaries for spatial joins
• okonomi/ - Subsidies potentially affected by environmental compliance
• husdyr/ - Livestock density affecting nitrogen loading
• medarbejdere/ - Environmental compliance inspections

R2 Paths Reference

# List pesticide disaggregation years
rclone lsd r2:landbruget-data/gold/ | grep pesticide

# List NLES5 nitrogen years
rclone lsd r2:landbruget-data/gold/ | grep nles5

# List BNBO status snapshots
rclone lsd r2:landbruget-data/silver/bnbo_status/

# List wetland data
rclone lsd r2:landbruget-data/silver/wetlands/

# List field intersection analyses
rclone lsd r2:landbruget-data/gold/ | grep intersections

Spatial Analysis Tips

CRS Handling

All geometry stored in EPSG:4326 (WGS84). For Danish projections:

# Convert to Danish UTM
gdf = gdf.to_crs('EPSG:25832')

# Calculate areas in meters
gdf['area_m2'] = gdf.geometry.area

Buffer Analysis

# Create 100m buffer around BNBO zones
bnbo_buffered = bnbo.copy()
bnbo_buffered = bnbo_buffered.to_crs('EPSG:25832')  # UTM for meters
bnbo_buffered['geometry'] = bnbo_buffered.buffer(100)
bnbo_buffered = bnbo_buffered.to_crs('EPSG:4326')  # Back to WGS84