new-orphan-wells/docs/chapter4-variables.md

Chapter 4 Variable Reference

The Geography of Transition: Distribution and Consequences of Orphaned Wells

---

Research Questions

1. How are orphaned wells spatially distributed and do they concentrate in historically fossil-dependent communities?
2. Do states with strong fossil industry dependence have formal energy transition governance mechanisms?
3. Do states frame orphaned well remediation as an engineering problem or a justice problem?
4. How does spatial distribution relate to political tensions over responsibility and funding?

---

Core Analytical Variables

Well Distribution

Variable	Source	DB Location	Notes
Well count by state	USGS DOW	v_wells_by_state.well_count	27 states
Well count by county	USGS DOW	v_wells_by_county.well_count	5-digit GEOID
Well count by tract	USGS DOW	v_wells_by_tract.well_count	11-digit GEOID
Well density (wells/km²)	Calculated	v_wells_by_tract.wells_per_km2	Land area only
Well type (normalized)	USGS DOW	wells.well_type_normalized	12 categories
Well status	USGS DOW	wells.status	State-specific terminology

State Governance Framework (RA-coded)

Variable	Source	DB Location	Values
Transition office count	Climate Policy Dashboard	v_state_governance.transition_office_count	0, 1, 2+
Office fossil language	Climate Policy Dashboard	state_transition_offices.code_fossil	0/1
Office equity language	Climate Policy Dashboard	state_transition_offices.code_equity	0/1
Prioritization system type	IOGCC 2023	state_prioritization.system_type	Text description
Technical factors used	IOGCC 2023	state_prioritization.tech_factors	Semicolon list
Rural/urban in scoring	IOGCC 2023	state_prioritization.code_rural_urban	0/1
Vulnerability/EJ in scoring	IOGCC 2023	state_prioritization.code_vuln	0/1
Surface land use in scoring	IOGCC 2023	state_prioritization.code_surface	0/1

Derived Classification

Variable	DB Location	Logic
framework_type	v_state_governance	Justice if code_vuln=1; Mixed if code_rural_urban=1; Engineering if system documented but no EJ/density; Unclassified otherwise
office_language_type	v_state_governance	Fossil + Equity / Fossil only / Equity only / Office exists no language / No transition office

Environmental Justice Indicators (requires ACS join)

Join on wells.tract_geoid = ACS geoid:

Variable	ACS Table	Description
Median household income	B19013	Tract-level; proxy for economic vulnerability
% Non-white	B03002	Calculated from race/ethnicity totals
% Below poverty line	B17001	Federal poverty threshold
Median housing age	B25035	Proxy for legacy industrial neighborhood
% Unemployed	B23025	Labor market conditions

---

Key Queries

State governance summary (activate after RA data loaded)

SELECT state, state_name, well_count_dow,
       framework_type, office_language_type,
       code_vuln, code_rural_urban, code_fossil, code_equity,
       est_liability_mid_usd
FROM v_ch4_state_analysis
ORDER BY well_count_dow DESC;

Engineering vs. justice states, well count comparison

SELECT framework_type,
       count(DISTINCT state)              AS state_count,
       sum(well_count_dow)                AS total_wells,
       round(avg(well_count_dow))         AS avg_wells_per_state
FROM v_ch4_state_analysis
GROUP BY framework_type
ORDER BY total_wells DESC;

Highest-density tracts (for mapping)

SELECT tract_geoid, tract_name, county_name, state_usps,
       well_count, wells_per_km2, tract_land_km2
FROM v_highest_density_tracts
LIMIT 50;

Wells in tracts below median income (EJ analysis — requires ACS)

SELECT w.state, count(*) AS wells_in_low_income_tracts
FROM wells w
JOIN acs_b19013 a ON w.tract_geoid = a.geoid
WHERE a.median_hh_income < 50000
GROUP BY w.state
ORDER BY wells_in_low_income_tracts DESC;

State transition office presence vs. well burden

SELECT sg.framework_type,
       sg.office_language_type,
       count(DISTINCT sg.state) AS states,
       sum(sg.well_count_dow) AS total_wells,
       avg(sg.well_count_dow) AS avg_wells
FROM v_state_governance sg
GROUP BY sg.framework_type, sg.office_language_type
ORDER BY total_wells DESC;

---

Analytical Strategy (Chapter 4)

Section 1: Mapping the Distribution

• National map: well locations by well_type_normalized
• State-level choropleth: well count and well density
• County-level choropleth: v_wells_by_county joined to TIGER county boundaries
• Key finding to highlight: OH + PA + OK = 47% of all documented orphaned wells

Section 2: Fossil Dependence and Governance

• Crosstab: states by framework_type × well count
• Test: Do high-burden states have transition offices? (office_count > 0 vs. well_count)
• Key contrast: PA (no transition office, engineering frame) vs. CO (Just Transition Office, equity language)

Section 3: The Justice Dimension

• Map: well density by tract overlaid with % non-white or % below poverty
• Identify tracts where both are elevated — the "double burden"
• Use v_highest_density_tracts for case study selection

Section 4: Political Tensions

• Connect framework_type to state political context (add state_politics table if needed)
• Argument: justice framing is not randomly distributed — correlates with state political economy

---

Data Limitations for Chapter 4

1. DOW dataset is documented wells only. True orphaned well count is almost certainly higher. API estimates 2 million+ undocumented orphaned wells nationally.

2. Definitional inconsistency. California "idle" wells differ legally from other states' "orphaned" definition. Flagged in data_file_notes.

3. Type field missingness (59.3% Unknown). Major states (OH, PA, KY) did not classify type. Limit type-based analysis to states with complete type data or use normalized categories cautiously.

4. Snapshot data. Data collected 2019–2022; plugging programs have been active since, so current counts are lower.

5. Spatial precision. No formal accuracy tests. Some coordinates converted from PLSS — precision is lower for KS and MT wells.