Skip to main content

County Search and Aggregation - Status Summary

Issue Identified​

County search filtering is not working because:

  1. ❌ The county field in jurisdictions_search table is NULL for most records
  2. ❌ Cities don't have county data (Census gazetteer files don't include it)
  3. ⚠️ Townships have county data but database update failed due to transaction errors

What We Have Now​

✅ Created​

  1. ZIP Code to County Mapping (data/gold/reference/zip_county_mapping.parquet)

    • 33,791 ZIP codes mapped to counties
    • Downloaded from Census Bureau 2020 ZCTA-to-County relationship file

  2. Scripts:

    • scripts/data/download_county_mappings.py - Downloads Census relationship files
    • scripts/data/update_jurisdiction_counties.py - Updates database with county data

  3. Documentation: website/docs/guides/county-aggregation.md

    • Complete guide on county-level aggregation
    • Examples of queries and API usage
    • Future enhancement roadmap

⚠️ Partially Working​

  • Township County Mapping: Code works (infers from GEOID) but needs database fix
  • Search API: Already supports county parameter, just needs data

❌ Not Yet Available​

  • City to County Mapping: Census doesn't provide this in gazetteer files
    • Need geocoding API OR state-specific Census relationship files
    • Affects 32,333 cities

Quick Fix: Update Townships (23,318 records)​

The update script has a bug where database transaction errors cause rollback. Fix:

# In update_township_with_counties(), add error handling per row:
for _, row in townships_df.iterrows():
    try:
        # Update code...
        conn.commit()  # Commit each row individually
    except Exception as e:
        conn.rollback()  # Rollback only this row
        logger.error(f"Error: {e}")

Then run:

python scripts/data/update_jurisdiction_counties.py

This will populate county data for townships (64% of non-county jurisdictions).

Complete Fix: Add City-County Mapping​

Three options:

Option 1: Use Geocoding (Recommended for now)

  • Use city lat/lon coordinates (already in data)
  • Call geocoding API (Nominatim, Google, etc.)
  • Free tier available

Option 2: Download Census Relationship Files

  • Download state-by-state from Census Bureau
  • URL: https://www2.census.gov/geo/docs/maps-data/data/rel2020/place/
  • Process each state file

Option 3: Use OpenStreetMap

  • Query OSM Nominatim API for each city
  • Extract county from administrative boundaries

Current Database State​

Type              Total    With County   Percent
-------------------------------------------------
city             32,333            0      0.0%
county            3,222            0      0.0%
school_district  13,326            0      0.0%
township         36,421            0      0.0%  (should be 64% after fix)

API Already Works​

The Search API in api/routes/search_postgres.py already has county filtering:

# City filter
if city:
    where_clauses.append(f"LOWER(name) LIKE LOWER(${param_idx})")
    params.append(f"%{city}%")

Just missing: county filter (which would be trivial to add once data exists)

Frontend Already Uses It​

The Home.tsx component already passes county to the API:

if (searchScope === 'county' || searchScope === 'city') {
  if (location.county) params.set('county', location.county)
}

Next Steps (Priority Order)​

1. Fix Township Update (10 minutes)​

Edit scripts/data/update_jurisdiction_counties.py to commit per-row instead of in batch. This will populate 23,318 township records with county data.

2. Add County Filter to Search API (5 minutes)​

Add to api/routes/search_postgres.py:

# County filter
if county:
    where_clauses.append(f"county = ${param_idx}")
    params.append(county)
    param_idx += 1

3. Add City-County Geocoding (1-2 hours)​

Create script to geocode all 32,333 cities using Nominatim:

from geopy.geocoders import Nominatim

geolocator = Nominatim(user_agent="open-navigator")
# Geocode each city, extract county, update database
# Add rate limiting (1 request/second for free tier)

Once townships have county data:

  1. Search for "Boston" with scope="county"
  2. Should return all jurisdictions in the county
  3. Verify heatmap/stats aggregate correctly

Files Created​

  1. /home/developer/projects/open-navigator/scripts/data/download_county_mappings.py
  2. /home/developer/projects/open-navigator/scripts/data/update_jurisdiction_counties.py
  3. /home/developer/projects/open-navigator/website/docs/guides/county-aggregation.md
  4. /home/developer/projects/open-navigator/data/gold/reference/zip_county_mapping.parquet

Summary​

Problem: County field is empty in database → county filtering doesn't work

Root Cause: Census gazetteer files don't include county for cities/places

Solution Created:

  1. ✅ Downloaded ZIP→County mapping (33,791 records)
  2. ✅ Created scripts to update database
  3. ⚠️ Township updates ready but transaction error needs fix
  4. ❌ City→County needs geocoding or additional Census files

Impact: Once township fix is applied, 64% of jurisdictions will have county data. City geocoding will bring it to 100%.