DAAF-Contribution-Community/daaf / .claude/skills/education-data-source-nhgis
education-data-source-nhgis
VerifiedNHGIS — 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.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 23.6 KB |
| references/boundary-changes.md | markdown | 9.1 KB |
| references/data-access.md | markdown | 17.0 KB |
| references/geographic-units.md | markdown | 8.6 KB |
| references/school-geography-links.md | markdown | 14.5 KB |
| references/time-series.md | markdown | 10.9 KB |
| references/variable-catalog.md | markdown | 18.4 KB |
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 |
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| finishing-a-development-branchobra/superpowersUse when implementation is complete, all tests pass, and you need to decide how to integrate the work | Stars: 322k | Updated | Claude CodeCodex | Verified | Install |
| systematic-debuggingobra/superpowersUse when encountering any bug, test failure, or unexpected behavior, before proposing fixes | Stars: 322k | Updated | Claude CodeCodex | Verified | Install |
| test-driven-developmentobra/superpowersUse when implementing any feature or bugfix, before writing implementation code | Stars: 322k | Updated | Claude CodeCodex | Verified | Install |
| 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: 198k | Updated | Claude CodeCodex | Verified | Install |