Skip to content

DAAF-Contribution-Community/daaf / .claude/skills/education-data-source-nhgis

education-data-source-nhgis

Verified

NHGIS — census geography crosswalks via Portal: links schools (ncessch) and colleges (unitid) to census tracts, block groups, CBSAs, and regions (census-geography vintages 1990-2020; mirror file-year/linkage coverage 1980, 1984-2023). Portal provides geography linkage tables ONLY; browse census demographics in the public NHGIS Data Finder, then sign in with a free IPUMS NHGIS account to submit, manage, or download extracts. API extract requests also need an API key. Use for linking education or institutional data to census geography.

references/school-geography-links.md

View on GitHub
# Linking Schools to Census Geography

Connecting schools to census demographics requires establishing geographic relationships between school locations and census units.

## Linking Methods Overview

| Method | Input Required | Output | Best For |
|--------|----------------|--------|----------|
| Point-in-polygon | School coordinates | Tract/BG assignment | Custom analysis |
| NCES EDGE files | NCES school ID | District-to-tract relationships | District-level work |
| Education Data Portal | NCES school ID | Pre-linked tract data | Quick lookups |
| SABINS | SABINS boundary ID | Attendance area demographics | 2009-10 and 2010-11 (2011-12 Minnesota only) |
| Geocoding | School address | Coordinates or tract | When coordinates missing |

## Method 1: Point-in-Polygon Spatial Join

Most direct method when you have school coordinates.

### Process

1. Obtain school coordinates (latitude/longitude)
   - Source: CCD directory via Education Data Portal
   - Fields: `latitude`, `longitude`

2. Obtain census boundary files
   - Source: NHGIS Data Finder
   - Select: Census Tract or Block Group shapefiles
   - Match year to your analysis period

3. Perform spatial join
   - Tool: GIS software or Python/R spatial libraries
   - Operation: Point-in-polygon
   - Result: Each school gets census unit ID

### Python Example

```python
import geopandas as gpd
import polars as pl

# Load school points (from CCD via Portal mirror)
schools = pl.read_parquet("data/raw/ccd_schools.parquet")
schools_pd = schools.select(["ncessch", "longitude", "latitude"]).to_pandas()
schools_gdf = gpd.GeoDataFrame(
    schools_pd,
    geometry=gpd.points_from_xy(schools_pd.longitude, schools_pd.latitude),
    crs="EPSG:4326"
)

# Load tract boundaries (from NHGIS direct download)
tracts = gpd.read_file("nhgis_tracts_2020.shp")

# Spatial join
schools_with_tracts = gpd.sjoin(
    schools_gdf,
    tracts[["GISJOIN", "geometry"]],
    how="left",
    predicate="within"
)
```

```r
library(sf)
library(arrow)

# Load school points (from CCD via Portal mirror)
schools <- read_parquet("data/raw/ccd_schools.parquet")
schools_sf <- st_as_sf(schools, coords = c("longitude", "latitude"), crs = 4326)

# Load tract boundaries (from NHGIS direct download)
tracts <- st_read("nhgis_tracts_2020.shp")

# Spatial join
schools_with_tracts <- st_join(
  schools_sf,
  tracts[, "GISJOIN"],
  join = st_within,
  left = TRUE
)
```

### Considerations

- **Coordinate quality**: CCD coordinates are generally accurate but verify edge cases
- **Boundary vintage**: Match boundary year to data year
- **Projection**: NHGIS uses Albers Equal Area; transform if needed
- **Edge cases**: Schools exactly on boundaries may need manual review

## Method 2: NCES EDGE Geographic Relationship Files

NCES provides pre-computed relationships between school districts and census units.

### Available Files

| File | Content | Source |
|------|---------|--------|
| School District-Tract | District to tract relationships | NCES EDGE |
| School District-County | District to county relationships | NCES EDGE |
| School-Block | Individual schools to blocks | NCES EDGE |

### District-to-Tract Relationship

The EDGE School District Geographic Relationship Files show how census tracts relate to school districts.

**Key fields** (`grfxx_lea_tract`, per the NCES GRF technical documentation):
- `LEAID`: NCES district ID (7 characters)
- `NAME_LEAXX`: District name
- `TRACT`: Census tract ID (11 characters: state + county + tract)
- `COUNT`: Total number of tracts in the LEA
- `LANDAREA`: Land area in square miles

**Types of relationships**:
1. **Tract fully in district**: Tract entirely within district boundaries
2. **Tract partially in district**: Tract split across districts
3. **Multiple tracts in district**: Most districts contain many tracts

### Using EDGE Files

```python
import polars as pl

# Load the EDGE GRF LEA-to-tract file (grfxx_lea_tract; xx = vintage; NCES download, not Portal)
edge = pl.read_csv("grfxx_lea_tract.csv", schema_overrides={"LEAID": pl.Utf8, "TRACT": pl.Utf8})  # placeholder name/format: match your download; keep IDs as strings

# Filter to specific district
district_tracts = edge.filter(pl.col("LEAID") == "0622710")

# Get unique tracts in district
tracts_in_district = district_tracts.select("TRACT").unique()
```

```r
library(dplyr)
library(readr)

# Load the EDGE GRF LEA-to-tract file (grfxx_lea_tract; xx = vintage; NCES download, not Portal)
edge <- read_csv("grfxx_lea_tract.csv", col_types = cols(LEAID = col_character(), TRACT = col_character()))  # placeholder name/format

# Filter to specific district
district_tracts <- edge |> filter(LEAID == "0622710")

# Get unique tracts in district
tracts_in_district <- district_tracts |> distinct(TRACT)
```

### Limitations

- Relationships based on boundaries, not student residence
- Doesn't account for open enrollment or choice programs
- Updated annually; verify vintage matches your data

## Method 3: Education Data Portal NHGIS Data

The Urban Institute provides pre-linked school/college-to-tract data via the Education Data Portal mirrors. **No registration required.**

### Canonical Paths

See `datasets-reference.md` for all canonical paths. Key paths:

| Dataset | Path |
|---------|------|
| Schools Census 1990 | `nhgis/schools_nhgis_geog_1990` |
| Schools Census 2000 | `nhgis/schools_nhgis_geog_2000` |
| Schools Census 2010 | `nhgis/schools_nhgis_geog_2010` |
| Schools Census 2020 | `nhgis/schools_nhgis_geog_2020` |
| Colleges Census 1990 | `nhgis/colleges_nhgis_geog_1990` |
| Colleges Census 2000 | `nhgis/colleges_nhgis_geog_2000` |
| Colleges Census 2010 | `nhgis/colleges_nhgis_geog_2010` |
| Colleges Census 2020 | `nhgis/colleges_nhgis_geog_2020` |

Each file contains ALL data years (schools: 1986-2023, colleges: 1980, 1984-2023). The census year in the file name determines which boundary vintage is used for geographic assignment.

The `year` column follows the CCD/IPEDS directory year (a 2016 NHGIS schools record carries the same coordinates as the 2016 CCD directory record; the 2017 D.C. colleges NHGIS records match the 2017 IPEDS directory institution set, not the 2016 set; Portal API, observed 2026-10-19).

### Key Variables (Schools)

| Variable | Description | Type |
|----------|-------------|------|
| `ncessch` | NCES school ID | Int64 |
| `leaid` | NCES district ID | Int64 |
| `tract` | Census tract number | Int64 |
| `block_group` | Block group number (1-9; 0 = unassigned) | Int64 |
| `geoid_block` | 12-digit block-group GEOID in the 2010/2020 files; 11-digit tract GEOID in the 1990 file; unidentified encoding in the 2000 file (do not treat as a block or block-group GEOID). Never a 15-digit block ID; Int64, so zero-pad before use (pinned mirror, probed 2026-10-27) | Int64 |
| `census_region` | Census region (1-4, 9 for territories) | Int64 |
| `census_division` | Census division (1-9) | Int64 |
| `cbsa` | CBSA code (2000+ census files only) | Int64 |
| `cbsa_type` | 1=Metropolitan, 2=Micropolitan (2000+ only) | Int64 |
| `geocode_accuracy` | Match score up to 100 (observed 4-100); -2 = not applicable, which is every row from 2017 onward in the school files (pinned mirror, probed 2026-10-27) | Float64 |

### Querying via Mirror

```python
import polars as pl

# Uses fetch_from_mirrors() — tries each mirror in priority order per mirrors.yaml.
# See fetch-patterns.md for the fetch_from_mirrors() function.

# Load from Portal mirror
df = fetch_from_mirrors("nhgis/schools_nhgis_geog_2020")

# Filter to specific school and year
school_data = df.filter(
    (pl.col("ncessch") == 10000201704) & (pl.col("year") == 2023)
)
print(school_data.select(["ncessch", "tract", "block_group", "census_region"]))
```

```r
library(arrow)
library(dplyr)

# fetch_from_mirrors() is a Python helper; in R, build the URL from the mirror root.
# Mirror failover: see `education-data-query/references/fetch-patterns.md` (R pattern)
mirror <- yaml::read_yaml("mirrors.yaml")$mirrors[[1]]

# Load from Portal mirror
# NOTE: illustrative only — mirror parquet files are Polars-written and may
# declare string_view columns, so a plain read can fail under R arrow
# ("cannot handle Array of type <utf8_view>"). Real fetch scripts must use the
# view-safe parquet read from `education-data-query/references/fetch-patterns.md`.
df <- read_parquet(paste0(mirror$root_url, "/", mirror$vintage$hf_revision, "/", "nhgis/schools_nhgis_geog_2020", ".", mirror$format))

# Filter to specific school and year
school_data <- df |>
  filter(ncessch == 10000201704, year == 2023) |>
  select(ncessch, tract, block_group, census_region)
cat(format(school_data), "\n")
```

### Note on Integer Encodings

Portal data uses integer encodings for categorical variables. See `variable-catalog.md` for complete encoding tables.

| Variable | Code | Meaning |
|----------|------|---------|
| `census_region` | 1 | Northeast |
| `census_region` | 2 | Midwest |
| `census_region` | 3 | South |
| `census_region` | 4 | West |
| `census_region` | 9 | Territories |
| `cbsa_type` | 1 | Metropolitan |
| `cbsa_type` | 2 | Micropolitan |

## Method 4: SABINS School Attendance Areas

SABINS provides actual school attendance boundaries (catchment areas) for select areas and years.

### Coverage

| School Year | Status |
|-------------|--------|
| 2009-10 | Available |
| 2010-11 | Available |
| 2011-12 | Boundary files for Minnesota only |
| After 2011-12 | Not available |

### Geographic Coverage

- Not nationwide
- Varies by grade level
- Coverage maps: https://www.nhgis.org/sabins-data-availability

### Data Components

1. **Boundary files**: GIS shapefiles of attendance zones
2. **Census data**: 2010 Census data tabulated for attendance areas
3. **Crosswalk tables**: Link boundaries to CCD schools

### Using SABINS

```
1. Access via NHGIS Data Finder
2. Select "SABINS" in Geographic Levels filter
3. Choose grade level (K-12)
4. Download boundary files and/or tabular data
```

### SABINS-CCD Crosswalk

Attendance boundaries don't always match 1:1 with schools:
- Some schools share attendance areas
- Some attendance areas feed multiple schools

Crosswalk tables available for each grade:
- `sabins_ccd_crosswalk_grade{N}_{year}.csv`

### Limitations

- **Historical only**: No boundaries after 2011-12, and 2011-12 covers Minnesota only
- **Incomplete coverage**: Many districts not included
- **Grade-specific**: Boundaries vary by grade level
- **No current equivalent**: No national source for current attendance boundaries

## Method 5: Geocoding Addresses

When coordinates are unavailable, geocode school addresses to census units.

### Services

| Service | Output | Cost |
|---------|--------|------|
| Census Geocoder | Coordinates + tract | Free |
| FCC Area API | Tract from coordinates | Free |
| Google Geocoding | Coordinates | Paid |
| ArcGIS Geocoding | Coordinates + tract | Varies |

### Census Geocoder

```python
import requests

address = "1600 Pennsylvania Avenue NW, Washington, DC 20500"
url = f"https://geocoding.geo.census.gov/geocoder/geographies/onelineaddress"
params = {
    "address": address,
    "benchmark": "Public_AR_Current",
    "vintage": "Census2020_Current",
    "format": "json"
}
response = requests.get(url, params=params)
result = response.json()

# Extract tract
tract = result["result"]["addressMatches"][0]["geographies"]["Census Tracts"][0]["GEOID"]
```

```r
library(httr2)

address <- "1600 Pennsylvania Avenue NW, Washington, DC 20500"
resp <- request("https://geocoding.geo.census.gov/geocoder/geographies/onelineaddress") |>
  req_url_query(
    address = address,
    benchmark = "Public_AR_Current",
    vintage = "Census2020_Current",
    format = "json"
  ) |>
  req_perform()
result <- resp_body_json(resp)

# Extract tract
tract <- result$result$addressMatches[[1]]$geographies$`Census Tracts`[[1]]$GEOID
```

### Batch Geocoding

For many addresses, use Census Batch Geocoder:
- Upload CSV with addresses
- Returns coordinates and census geography
- Limit: 10,000 addresses per batch

## Aggregating Census Data to School Districts

When NHGIS doesn't provide school-district-level data for your variables:

### Method A: Tract-Weighted Aggregation

1. Get all tracts in district (via EDGE files)
2. Get tract-level data from NHGIS
3. Sum counts or compute weighted averages

```python
import polars as pl

# Example: Total population in district
district_population = tracts_in_district.select(pl.col("total_pop").sum()).item()

# Example: Weighted average median income
weighted_income = (
    tracts_in_district.select(
        (pl.col("median_income") * pl.col("households")).sum()
        / pl.col("households").sum()
    ).item()
)
```

```r
library(dplyr)

# Example: Total population in district
district_population <- tracts_in_district |> summarise(total = sum(total_pop)) |> pull(total)

# Example: Weighted average median income
weighted_income <- tracts_in_district |>
  summarise(wtd_income = sum(median_income * households) / sum(households)) |>
  pull(wtd_income)
```

### Method B: Block-Level Aggregation

More precise but more complex:

1. Get block-to-school-district crosswalk
2. Get block-level data
3. Aggregate blocks to districts

### Handling Partial Tracts

When tracts cross district boundaries:

**Option 1**: Area-based apportionment
- Pro: Simple
- Con: Assumes uniform distribution

**Option 2**: Use block data
- Pro: Exact for 100% count variables
- Con: No sample data at block level

**Option 3**: Population-weighted
- Use NHGIS crosswalk weights (if available)
- Pro: More accurate allocation
- Con: Requires crosswalk data

## Best Practices

### Matching Vintages

| School Data Year | Census Boundary Year | Census Data Year |
|------------------|----------------------|------------------|
| 2022-23 | 2020 | ACS 2018-2022 or 2020 Census |
| 2019-20 | 2010 or 2019 | ACS 2015-2019 |
| 2014-15 | 2010 | ACS 2011-2015 or 2010 Census |
| 2009-10 | 2010 | 2010 Census or SABINS |

### Quality Checks

1. **Missing matches**: Flag schools not matching any tract
2. **Edge cases**: Verify schools near boundaries
3. **Zero population**: Some tracts are non-residential
4. **Coordinate errors**: Check for obvious outliers

### Documentation

Record your linking methodology:
- Boundary vintage used
- Join method (point-in-polygon, crosswalk, etc.)
- Handling of edge cases
- Match rate achieved

## Common Issues

| Issue | Cause | Solution |
|-------|-------|----------|
| School not in any tract | Coordinates error or offshore | Verify coordinates; manual fix |
| Multiple tracts per school | Point exactly on boundary | Pick one or nearest centroid |
| Tract spans districts | Census/district mismatch | Use blocks or apportion |
| Historical mismatch | Boundary changes | Use standardized time series |
| Missing demographics | Low population tract | Aggregate to larger area |
finishing-a-development-branchobra/superpowersUse when implementation is complete, all tests pass, and you need to decide how to integrate the workStars: 322kUpdated Claude CodeCodexVerifiedInstall
systematic-debuggingobra/superpowersUse when encountering any bug, test failure, or unexpected behavior, before proposing fixesStars: 322kUpdated Claude CodeCodexVerifiedInstall
test-driven-developmentobra/superpowersUse when implementing any feature or bugfix, before writing implementation codeStars: 322kUpdated Claude CodeCodexVerifiedInstall
skill-creatoranthropics/skillsCreate new skills, modify and improve existing skills, and measure skill performance. Use when users want to create a skill from scratch, edit, or optimize an existing skill, run evals to test a skill, benchmark skill performance with variance analysis, or optimize a skill's description for better triggering accuracy.Stars: 198kUpdated Claude CodeCodexVerifiedInstall