Notes: US Drinking Water Quality

Project Core Vision & Strategy

• The Problem: Public drinking water quality data is highly fragmented, often trapped in community-specific PDFs or semi-accessible federal databases. Furthermore, while there is massive tracking infrastructure at the water source level, downstream consumer-level tap water data is incredibly fragmented. The public generally operates on a baseline assumption that if the source or treatment facility is fine, the tap water is fine, but infrastructure degradation, local plumbing variables, and private well discrepancies mean this isn't always true.
• The Goal: Build a centralized, user-friendly portal (potentially featuring a chloropleth map) that makes water quality data accessible at the consumer/downstream level. By making this data visible, the portal will drive public education and awareness, improve community health, and serve niche water enthusiasts (like coffee and tea brewers) looking to maximize their water enjoyment.
• Scope Reduction: Need to make a choice between the municipal/city PDF data pipeline or aggregating into WQX data for wells.

Data Sources & Tools Evaluation

High Relevance

• EPA Water Quality Exchange (WQX): Universal format for sharing water quality data. This is a primary asset for aggregating non-city/individual well data.
  1. Resource: EPA WQX Framework
• USGS dataRetrieval R Package: Highly useful for programmatically pulling hydrologic and discrete water quality data from the Water Quality Portal (WQP) and EPA endpoints into an R environment.
  1. Documentation: dataRetrieval Package Guide
• EPA Drinking Water Data and Tools / SDWIS (Safe Drinking Water Information System): The federal data warehouse. Bypassing the slow UI is possible via the EPA Envirofacts Data Service API, which allows custom RESTful queries targeting specific tables (like public water system locations and violations) and returns clean JSON/XML payloads.
  1. Developer Resource: Envirofacts Data Service API Documentation
• San Diego Water Quality Reports: Confirmed to be trapped in PDFs (2007–2025). Good proof-of-concept for the problem, but manual scraping will take a while so we'll have to settle on either one or two cities and/or one city and all years (i.e., format and schema structures will vary wildly by municipality).
  1. Data Source: City of San Diego Annual Water Quality Reports

Lower Relevance

• EPA Water Data and Tools (Ambient/Models/Algae Dashboards): Useful for context, but features like harmful algae bloom predictions or surface water modeling are likely too broad for a one-month drinking water MVP.
• USGS Estimated Groundwater Withdrawals (2015): County-level water quantity (extraction volume) rather than quality. Confirmed as a different project idea.

Context: How Water Moves & Is Tracked in the US

Here is a quick breakdown of how water extraction and quality tracking:

[Water Source] ──> [Extraction: Well / Surface Intake] ──> [Treatment Facility] ──> [Distribution / Tap]
       │                                                          │
       └─► Tracked by WQX / USGS (Raw/Ambient)                    └─► Tracked by SDWIS / Local PDFs (Finished)

1. Public/Municipal Systems: Water is imported or drawn from surface/groundwater, treated centrally, and distributed. Quality is highly regulated under the Safe Drinking Water Act (SDWA) and logged in SDWIS, but consumer-facing data usually ends up in municipal PDF reports.
2. Private Wells / Springs: Homeowners or small rural communities extract directly from aquifers. Testing is private and voluntary (is this true?). This is where the crowdsourced portal idea fits, as there is no central repository for private wells.

Next Steps

1. Text Extraction Exploration (Municipal Pipeline Proof-of-Concept)

• Determine if it works: Download 3 sample PDFs (e.g., San Diego 2025, San Diego 2015, and one external city like Austin or Flint) to test text extractability.
  1. Resource: City of San Diego Annual Water Quality Reports
• Evaluate programmatic libraries: Test Python’s pdfplumber or pypdf first. If the PDFs are digital-native (text is selectable), pdfplumber can target precise table cell coordinates without requiring heavy computation.
• Assess OCR framework necessity: Traditional Deep Learning OCR frameworks (like LayoutLM or fine-tuning models) are massive time sinks for a 4-week timeline. If any documents are scanned images, route them through pre-trained, out-of-the-box engines like PaddleOCR or Tesseract (pytesseract) rather than trying to train or fine-tune a model.

2. Well Data Retrieval Test (Non-Municipal Pipeline Proof-of-Concept)

• Test the R package: Run a quick script with the USGS dataRetrieval package to pull discrete ground water quality samples from a local county to see how clean the return data is.
  1. Resource: dataRetrieval Package Guide
• Test the EPA API: Query the EPA Envirofacts SDWIS API for a specific region (like San Diego County) to evaluate how easily you can extract public/community well locations and active system data.
  1. Resource: Envirofacts Data Service API Documentation

3. Evaluate Results & Choose Path

• Analyze constraints vs. payoff: Evaluate the technical lift of both proof-of-concepts. Decide whether to build the pipeline around the municipal PDF extraction (historical, rich city-level trends) or the well-water dataset (WQX standard/crowdsourced portal).

4. Define the Schema

• Draft the baseline: Use the EPA WQX format to define a minimal, simplified data schema for the selected portal database (e.g., targeting core parameters like Total Dissolved Solids, pH, Lead, and Nitrates).
  1. Resource: EPA WQX Framework

5. Bootstrap the Map & Build Ingestion MVP

• Seed the visualization: Map the localized datasets pulled during your proof-of-concept tests into an initial chloropleth map.
• Create the entry portal: Build a simple, lightweight user interface that allows manual input of water test results (either by well-owners or by pasting parsed municipal table data) to update the map visualization in real-time.