Housing Permits vs School Enrollment Change

What follows is the transcript of this chat as State Planner generated it. It is here to show how the answer above was arrived at: every query State Planner ran, what each one returned, and where the data came from.

transcript.md

↑ Contents

State Planner chat · https://stateplanner.com

State: Tennessee (TN)
Started: October 22, 2026, 12:35 AM UTC
Runtime: 1 min 47 sec
Status: completed

Prompt

Which Tennessee school districts gained or lost the most students since 2017–18? Map the schools by enrollment change, and say whether the gains are in the counties permitting the most new homes.

Model: Anthropic: Claude Opus 5.5 (anthropic/claude-opus-5.5, via openrouter)

Response

Now a county map setting permits beside enrollment change.

District enrollment change, 2017‑18 to 2024‑25 (NCES Common Core of Data)

Statewide, public school enrollment barely changed: 1,001,967 students in fall 2017 and 1,002,363 in fall 2024. Seven schools in 2017‑18 and 15 in 2024‑25 reported no enrollment and are left out of the totals. Underneath that flat total, students shifted between districts.

Districts with the largest gains

District	2017‑18	2024‑25	Change	%
Tennessee Public Charter School Commission	–	6,979	+6,979	new
Rutherford County	45,292	52,127	+6,835	+15.1%
Montgomery County	34,841	39,385	+4,544	+13.0%
Johnson County*	2,110	4,952	+2,842	+134.7%
Wilson County	18,343	20,703	+2,360	+12.9%
Williamson County	39,322	41,593	+2,271	+5.8%
Lewis County*	1,725	3,731	+2,006	+116.3%
Memphis‑Shelby County Schools	109,591	111,125	+1,534	+1.4%
Union County*	4,207	5,514	+1,307	+31.1%
Hamilton County	44,822	45,981	+1,159	+2.6%

Districts with the largest losses

District	2017‑18	2024‑25	Change	%
Achievement School District	11,419	1,412	−10,007	−87.6%
Davidson County (Metro Nashville)	84,728	81,022	−3,706	−4.4%
Sullivan County	9,758	7,878	−1,880	−19.3%
Tipton County	11,114	9,975	−1,139	−10.2%
Lauderdale County	4,159	3,277	−882	−21.2%
Carter County	5,290	4,450	−840	−15.9%
Cheatham County	6,277	5,451	−826	−13.2%
Greene County	6,783	6,088	−695	−10.2%
Hawkins County	6,705	6,038	−667	−9.9%
Fayette County	3,560	2,909	−651	−18.3%

* Johnson, Lewis and Union counties grew because of full‑virtual schools they host. Tennessee Connections Academy has about 3,000 students in Johnson County, Volunteer State Virtual Academy about 2,000 in Lewis, and Tennessee Virtual Academy and its junior high about 2,700 in Union. Those students live all over the state. Leave the virtual schools out and all three counties are roughly flat or down.

Two of the biggest moves are students changing district rather than arriving or leaving:

The Achievement School District's large loss mostly matches the new Charter Commission district and Memphis‑Shelby's gain.
Counted by where the schools are, Davidson County lost only about 700 students, not 3,700, because the Charter Commission's schools sit mostly in Nashville.

Schools by enrollment change

School Enrollment Change 2017-18 to 2024-25

Map legend (each school is matched to itself by its NCES school ID):

Dark blue: gained 200 or more students (68 schools)
Light blue: gained 50–199 (202)
Grey: changed by fewer than 50 (804)
Orange: lost 50–199 (483)
Dark red: lost 200 or more (95)
Green: school ID new since 2017‑18 (263 schools, about 82,500 students)
Black: school ID not listed in 2024‑25 (124 schools that had about 36,200 students)
White: enrollment not reported in one of the two years

A school's ID changes when it moves to another district, as when schools left the Achievement School District, so some green and black dots are the same building under a new operator. Many of the gains among existing schools are in Rutherford, Montgomery, Wilson and Williamson counties. Losses are spread widely across rural counties.

Are the gains where the most homes are being permitted?

Mostly yes, but the three largest counties don't fit. I compared each county's school enrollment, counted by where its schools are and leaving out full‑virtual schools, with the housing units permitted there in 2017–2024 (Census Building Permits Survey).

Housing Permits vs School Enrollment Change

Map legend:

Purple shading: housing units permitted from 2017 to 2024 per 1,000 residents in 2017. The darkest counties permitted 80 or more.
Green dots: county enrollment rose.
Orange dots: county enrollment fell.

What the comparison shows:

Seven of the 10 counties with the largest enrollment gains are also among the 10 that permitted the most homes: Rutherford (28,300 units), Montgomery (22,900), Williamson (18,400), Hamilton (17,500), Wilson (16,400), Sumner (14,800) and Maury (14,000). Most of these are around Nashville or Clarksville.
The other three of the top 10 permitting counties lost students:
- Davidson permitted the most homes in the state, 86,300 units, yet lost about 700 students (−0.8%).
- Knox permitted 24,300 units and lost about 1,000 (−1.6%).
- Shelby permitted 15,000 units and lost about 3,900 (−2.5%).
Counties grouped by permits per resident (about 24 counties each):
- Top quarter: enrollment up 2.9%, with 12 of 24 counties gaining.
- Second quarter: down 3.4%.
- Third quarter: down 4.7%.
- Bottom quarter: down 6.7%, with only one county gaining.
Overall the link is moderate. The rank correlation between permits per resident and enrollment change is 0.45. Building a lot of housing is no guarantee of more students: Maury (149 units per 1,000), Sevier (100) and Fayette (76) all permitted heavily, and Sevier and Fayette lost students.

Caveats:

Permits authorize construction; they don't count completed homes, and some permitted units are never built. Pickett County has no permit record in these years.
Enrollment is the official fall count. It covers public schools only, so it doesn't capture moves to private school or homeschooling.
Districts aren't the same as counties. City districts such as Murfreesboro, Lebanon and the Shelby suburbs are inside their counties' totals.

I can also compare against 2017–2023 permits to allow for the time between a permit and families moving in, or look only at single‑family units.

Exploratory Queries

Query 1 — Check years, school counts and enrollment coverage per year.

   SELECT school_year,
          count(*) n,
          count(enrollment) with_enr,
          sum(enrollment) enr,
          count(DISTINCT leaid) districts
     FROM tn_nces_public_schools
 GROUP BY 1
 ORDER BY 1

school_year	n	with_enr	enr	districts
2017-18	1789	1782	1001967	147
2018-19	1862	1862	1006309	147
2019-20	1894	1878	1014744	147
2020-21	1907	1880	984066	148
2021-22	1931	1906	995549	149
2022-23	1932	1900	1005626	148
2023-24	1924	1905	1003495	148
2024-25	1928	1913	1002363	149

data/turn-1-query-1.csv

Query 2 — Check permit years available.

   SELECT min(YEAR),
          max(YEAR),
          count(DISTINCT geoid)
     FROM tn_bps_county_permits
    WHERE YEAR >= 2017

min	max	count
2017	2025	94

data/turn-1-query-2.csv

Query 3 — Enrollment change by district, 2017-18 to 2024-25.

     WITH a AS (
             SELECT leaid,
                    max(district_name) dn,
                    sum(enrollment) e,
                    count(*) s,
                    count(enrollment) se,
                    mode() WITHIN GROUP (
                     ORDER BY county_name
                    ) cty
               FROM tn_nces_public_schools
              WHERE school_year = '2017-18'
           GROUP BY 1
          ),
          b AS (
             SELECT leaid,
                    max(district_name) dn,
                    sum(enrollment) e,
                    count(*) s,
                    count(enrollment) se,
                    mode() WITHIN GROUP (
                     ORDER BY county_name
                    ) cty
               FROM tn_nces_public_schools
              WHERE school_year = '2024-25'
           GROUP BY 1
          )
   SELECT coalesce(b.leaid, a.leaid) leaid,
          coalesce(b.dn, a.dn) district,
          coalesce(b.cty, a.cty) county,
          a.e e17,
          b.e e24,
          coalesce(b.e, 0) - coalesce(a.e, 0) chg,
          round(100.0 * (b.e - a.e) / nullif(a.e, 0), 1) pct,
          a.s s17,
          b.s s24
     FROM a
FULL JOIN b USING (leaid)
 ORDER BY chg DESC

leaid	district	county	e17	e24	chg	pct	s17	s24
4700157	Tennessee Public Charter School Commission	Davidson County		6979	6979			17
4703690	Rutherford County	Rutherford County	45292	52127	6835	15.1	47	55
4703030	Montgomery County	Montgomery County	34841	39385	4544	13.0	39	46
4702160	Johnson County	Johnson County	2110	4952	2842	134.7	7	8
4704550	Wilson County	Wilson County	18343	20703	2360	12.9	21	25
4704530	Williamson County	Williamson County	39322	41593	2271	5.8	44	52
4702430	Lewis County	Lewis County	1725	3731	2006	116.3	4	7
4700148	Memphis-Shelby County Schools	Shelby County	109591	111125	1534	1.4	201	223
4704290	Union County	Union County	4207	5514	1307	31.1	10	11
4701590	Hamilton County	Hamilton County	44822	45981	1159	2.6	78	82
4704020	Sumner County	Sumner County	29636	30719	1083	3.7	49	53
4704551	University Schools	Shelby County		957	957			3
4700154	Lakeland	Shelby County	1652	2538	886	53.6	2	2
4702370	Lebanon	Wilson County	3773	4628	855	22.7	6	7
4703240	Oak Ridge	Anderson County	4440	5078	638	14.4	7	8
4700990	DeKalb County	DeKalb County	3041	3644	603	19.8	5	7
4700149	Collierville	Shelby County	8671	9263	592	6.8	8	10
4703150	Murfreesboro	Rutherford County	8841	9342	501	5.7	12	13
4702700	Maryville	Blount County	5349	5717	368	6.9	7	8
4703480	Putnam County	Putnam County	11421	11776	355	3.1	20	22
4702400	Lenoir City	Loudon County	2297	2638	341	14.8	3	4
4702760	Maury County	Maury County	12791	13098	307	2.4	21	24
4700180	Bedford County	Bedford County	8754	9042	288	3.3	14	15
4702610	Manchester	Coffee County	1400	1628	228	16.3	3	3
4700060	Alcoa	Blount County	2047	2270	223	10.9	4	4

25 out of 151 are listed here.

data/turn-1-query-3.csv

Query 4 — Check for virtual schools driving big percent gains in small districts.

   SELECT school_year,
          district_name,
          school_name,
          virtual,
          enrollment
     FROM tn_nces_public_schools
    WHERE leaid IN ('4702160', '4702430', '4704290', '4700990')
      AND school_year IN ('2017-18', '2024-25')
      AND (
          virtual ILIKE '%full%'
       OR school_name ILIKE '%virtual%'
       OR school_name ILIKE '%online%'
       OR enrollment > 1000
          )
 ORDER BY 2,
          3,
          1

school_year	district_name	school_name	virtual	enrollment
2024-25	DeKalb County	DeKalb County Schools Adult Education Virtual School	Full virtual	784
2024-25	Johnson County	Tennessee Connections Academy Johnson County 4-8	Full virtual	1471
2024-25	Johnson County	Tennessee Connections Academy Johnson County 9-12	Full virtual	1543
2024-25	Lewis County	Volunteer State Virtual Academy Elementary School	Full virtual	614
2024-25	Lewis County	Volunteer State Virtual Academy High School	Full virtual	943
2024-25	Lewis County	Volunteer State Virtual Academy Middle School	Full virtual	421
2017-18	Union County	Tennessee Virtual Academy	Full virtual	1216
2024-25	Union County	Tennessee Virtual Academy	Full virtual	1867
2024-25	Union County	Tennessee Virtual Junior High School	Full virtual	856

data/turn-1-query-4.csv

Query 5 — County-level school enrollment change (non-virtual) versus housing units permitted 2017-2024.

     WITH e AS (
             SELECT county_fips,
                    sum(enrollment) FILTER (
                        WHERE school_year = '2017-18'
                    ) e17,
                    sum(enrollment) FILTER (
                        WHERE school_year = '2024-25'
                    ) e24
               FROM tn_nces_public_schools
              WHERE school_year IN ('2017-18', '2024-25')
                AND coalesce(virtual, '') <> 'Full virtual'
           GROUP BY 1
          ),
          p AS (
             SELECT geoid,
                    sum(units_total) u
               FROM tn_bps_county_permits
              WHERE YEAR BETWEEN 2017 AND 2024
           GROUP BY 1
          ),
          pop AS (
             SELECT geoid,
                    pop_estimate
               FROM tn_census_county_pop_est
              WHERE YEAR = 2017
          )
   SELECT c.name,
          e.e17,
          e.e24,
          e.e24 - e.e17 chg,
          round(100.0 * (e.e24 - e.e17) / e.e17, 1) pct,
          p.u units,
          round(1000.0 * p.u / pop.pop_estimate, 1) per1k,
          rank() OVER (
           ORDER BY p.u DESC NULLS LAST
          ) units_rank,
          rank() OVER (
           ORDER BY e.e24 - e.e17 DESC
          ) chg_rank
     FROM tn_census_counties c
LEFT JOIN e ON e.county_fips = c.geoid
LEFT JOIN p ON p.geoid = c.geoid
LEFT JOIN pop ON pop.geoid = c.geoid
 ORDER BY units DESC NULLS LAST

name	e17	e24	chg	pct	units	per1k	units_rank	chg_rank
Davidson County	85583	84879	-704	-0.8	86283	122.6	1	87
Rutherford County	54133	61904	7771	14.4	28327	88.6	2	1
Knox County	60937	59932	-1005	-1.6	24337	52.3	3	92
Montgomery County	34841	38634	3793	10.9	22928	111.7	4	2
Williamson County	43025	44637	1612	3.7	18391	80.2	5	4
Hamilton County	44794	45727	933	2.1	17518	48.9	6	6
Wilson County	22061	25216	3155	14.3	16401	119.6	7	3
Shelby County	153052	149173	-3879	-2.5	15030	16.1	8	95
Sumner County	29636	30671	1035	3.5	14826	80.0	9	5
Maury County	12791	13098	307	2.4	13985	149.0	10	9
Sevier County	14512	14408	-104	-0.7	9675	99.7	11	33
Blount County	18166	18195	29	0.2	6869	52.6	12	17
Robertson County	11331	11201	-130	-1.1	6041	85.3	13	37
Washington County	16388	15750	-638	-3.9	6001	46.2	14	83
Loudon County	7184	7723	539	7.5	4763	90.8	15	7
Putnam County	11416	11776	360	3.2	4681	61.1	16	8
Bradley County	15984	15960	-24	-0.2	4639	44.0	17	23
Sullivan County	20840	18818	-2022	-9.7	3728	23.8	18	94
Dickson County	8387	7737	-650	-7.8	3499	66.2	19	85
Cumberland County	7419	6970	-449	-6.1	3491	59.3	20	72
Fayette County	3560	2941	-619	-17.4	3067	75.7	21	82
Bedford County	8754	9042	288	3.3	3004	62.2	22	10
Anderson County	11961	12027	66	0.6	2588	34.1	23	15
Franklin County	5876	5355	-521	-8.9	2558	61.1	24	76
Cheatham County	6277	5381	-896	-14.3	2454	60.7	25	91

25 out of 95 are listed here.

data/turn-1-query-5.csv

Query 6 — Correlation between permits per 1,000 residents and non-virtual enrollment change by county, plus permit-tier summaries.

     WITH e AS (
             SELECT county_fips,
                    sum(enrollment) FILTER (
                        WHERE school_year = '2017-18'
                    ) e17,
                    sum(enrollment) FILTER (
                        WHERE school_year = '2024-25'
                    ) e24
               FROM tn_nces_public_schools
              WHERE school_year IN ('2017-18', '2024-25')
                AND coalesce(virtual, '') <> 'Full virtual'
           GROUP BY 1
          ),
          p AS (
             SELECT geoid,
                    sum(units_total) u
               FROM tn_bps_county_permits
              WHERE YEAR BETWEEN 2017 AND 2024
           GROUP BY 1
          ),
          pop AS (
             SELECT geoid,
                    pop_estimate
               FROM tn_census_county_pop_est
              WHERE YEAR = 2017
          ),
          j AS (
             SELECT c.geoid,
                    e.e17,
                    e.e24,
                    100.0 * (e.e24 - e.e17) / e.e17 pct,
                    coalesce(p.u, 0) u,
                    1000.0 * coalesce(p.u, 0) / pop.pop_estimate per1k
               FROM tn_census_counties c
               JOIN e ON e.county_fips = c.geoid
          LEFT JOIN p ON p.geoid = c.geoid
               JOIN pop ON pop.geoid = c.geoid
          ),
          r AS (
             SELECT *,
                    rank() OVER (
                     ORDER BY per1k
                    ) rp,
                    rank() OVER (
                     ORDER BY pct
                    ) re,
                    ntile(4) OVER (
                     ORDER BY per1k DESC
                    ) q
               FROM j
          )
   SELECT (
             SELECT round(corr(rp, re)::numeric, 2)
               FROM r
          ) spearman,
          (
             SELECT round(corr(per1k, pct)::numeric, 2)
               FROM r
          ) pearson,
          q,
          count(*) n,
          round(min(per1k), 1) min_per1k,
          sum(e24 - e17) chg,
          round(100.0 * sum(e24 - e17) / sum(e17), 1) pct,
          count(*) FILTER (
              WHERE e24 > e17
          ) gaining
     FROM r
 GROUP BY q
 ORDER BY q

spearman	pearson	q	n	min_per1k	chg	pct	gaining
0.45	0.54	1	24	51.4	13191	2.9	12
0.45	0.54	2	24	23.8	-7100	-3.4	5
0.45	0.54	3	24	6.1	-12432	-4.7	2
0.45	0.54	4	23	0.0	-4274	-6.7	1

data/turn-1-query-6.csv

Query 7 — Count schools in each change category for the legend.

     WITH a AS (
             SELECT ncessch,
                    enrollment e
               FROM tn_nces_public_schools
              WHERE school_year = '2017-18'
          ),
          b AS (
             SELECT ncessch,
                    enrollment e
               FROM tn_nces_public_schools
              WHERE school_year = '2024-25'
          )
   SELECT CASE
                    WHEN a.ncessch IS NULL THEN 'new'
                    WHEN b.ncessch IS NULL THEN 'gone'
                    WHEN a.e IS NULL
                           OR b.e IS NULL THEN 'na'
                              WHEN b.e - a.e <= -200 THEN 'l2'
                              WHEN b.e - a.e <= -50 THEN 'l1'
                              WHEN b.e - a.e < 50 THEN 'z'
                              WHEN b.e - a.e < 200 THEN 'g1'
                              ELSE 'g2'
          END cat,
          count(*),
          sum(coalesce(b.e, 0) - coalesce(a.e, 0)) net
     FROM a
FULL JOIN b USING (ncessch)
 GROUP BY 1
 ORDER BY 1

cat	count	net
g1	202	19019
g2	68	21833
gone	124	-36161
l1	483	-49501
l2	95	-28844
na	13	-4882
new	263	82539
z	804	-3607

data/turn-1-query-7.csv

Map Layer Queries

School Enrollment Change 2017-18 to 2024-25: Counties

   SELECT geom,
          name AS "County"
     FROM tn_census_counties

Picture: maps/turn-1-school-enrollment-change.png

School Enrollment Change 2017-18 to 2024-25: Schools by enrollment change — colored by "cat": Lost 200+ students (#b2182b), Lost 50–199 (#ef8a62), Within ±49 (#e0e0e0), Gained 50–199 (#67a9cf), Gained 200+ (#2166ac), New school ID since 2017-18 (#1b9e77), School ID not listed in 2024-25 (#000000), Enrollment not reported (#ffffff)

     WITH a AS (
             SELECT ncessch,
                    enrollment e,
                    school_name,
                    district_name,
                    geom
               FROM tn_nces_public_schools
              WHERE school_year = '2017-18'
          ),
          b AS (
             SELECT ncessch,
                    enrollment e,
                    school_name,
                    district_name,
                    virtual,
                    geom
               FROM tn_nces_public_schools
              WHERE school_year = '2024-25'
          )
   SELECT coalesce(b.geom, a.geom) geom,
          coalesce(b.school_name, a.school_name) AS "School",
          coalesce(b.district_name, a.district_name) AS "District",
          a.e AS "Enrollment 2017-18",
          b.e AS "Enrollment 2024-25",
          b.e - a.e AS "Change",
          b.virtual AS "Virtual",
          CASE
                    WHEN a.ncessch IS NULL THEN 'new'
                    WHEN b.ncessch IS NULL THEN 'gone'
                    WHEN a.e IS NULL
                           OR b.e IS NULL THEN 'na'
                              WHEN b.e - a.e <= -200 THEN 'l2'
                              WHEN b.e - a.e <= -50 THEN 'l1'
                              WHEN b.e - a.e < 50 THEN 'z'
                              WHEN b.e - a.e < 200 THEN 'g1'
                              ELSE 'g2'
          END AS cat
     FROM a
FULL JOIN b USING (ncessch)

Picture: maps/turn-1-school-enrollment-change.png

Housing Permits vs School Enrollment Change: Counties by housing units permitted per 1,000 residents, 2017–2024 — colored by "tier": 80+ units per 1,000 (#54278f), 40–79 (#9e9ac8), 10–39 (#dadaeb), Under 10 (#fcfbfd), No permit record (#ffffff)

     WITH e AS (
             SELECT county_fips,
                    sum(enrollment) FILTER (
                        WHERE school_year = '2017-18'
                    ) e17,
                    sum(enrollment) FILTER (
                        WHERE school_year = '2024-25'
                    ) e24
               FROM tn_nces_public_schools
              WHERE school_year IN ('2017-18', '2024-25')
                AND coalesce(virtual, '') <> 'Full virtual'
           GROUP BY 1
          ),
          p AS (
             SELECT geoid,
                    sum(units_total) u
               FROM tn_bps_county_permits
              WHERE YEAR BETWEEN 2017 AND 2024
           GROUP BY 1
          ),
          pop AS (
             SELECT geoid,
                    pop_estimate
               FROM tn_census_county_pop_est
              WHERE YEAR = 2017
          )
   SELECT c.geom,
          c.name AS "County",
          p.u AS "Housing units permitted 2017-2024",
          round(1000.0 * p.u / pop.pop_estimate, 1) AS "Units per 1,000 residents(2017 pop)",
          e.e17 AS "Enrollment 2017-18 (excl. virtual)",
          e.e24 AS "Enrollment 2024-25 (excl. virtual)",
          e.e24 - e.e17 AS "Enrollment change",
          round(100.0 * (e.e24 - e.e17) / e.e17, 1) AS "Enrollment change %",
          CASE
                    WHEN p.u IS NULL THEN 'q0'
                    WHEN 1000.0 * p.u / pop.pop_estimate >= 80 THEN 'q4'
                    WHEN 1000.0 * p.u / pop.pop_estimate >= 40 THEN 'q3'
                    WHEN 1000.0 * p.u / pop.pop_estimate >= 10 THEN 'q2'
                    ELSE 'q1'
          END AS tier
     FROM tn_census_counties c
LEFT JOIN e ON e.county_fips = c.geoid
LEFT JOIN p ON p.geoid = c.geoid
LEFT JOIN pop ON pop.geoid = c.geoid

Picture: maps/turn-1-permits-vs-enrollment.png

Housing Permits vs School Enrollment Change: County enrollment change (excl. virtual): gained — colored by "dir": Enrollment rose (#1a9850), Enrollment fell (#f46d43)

     WITH e AS (
             SELECT county_fips,
                    sum(enrollment) FILTER (
                        WHERE school_year = '2017-18'
                    ) e17,
                    sum(enrollment) FILTER (
                        WHERE school_year = '2024-25'
                    ) e24
               FROM tn_nces_public_schools
              WHERE school_year IN ('2017-18', '2024-25')
                AND coalesce(virtual, '') <> 'Full virtual'
           GROUP BY 1
          )
   SELECT ST_PointOnSurface(c.geom) geom,
          c.name AS "County",
          e.e24 - e.e17 AS "Enrollment change",
          round(100.0 * (e.e24 - e.e17) / e.e17, 1) AS "Change %",
          CASE
                    WHEN e.e24 > e.e17 THEN 'up'
                    ELSE 'down'
          END dir
     FROM tn_census_counties c
     JOIN e ON e.county_fips = c.geoid

Picture: maps/turn-1-permits-vs-enrollment.png

Housing Permits vs School Enrollment Change: Counties by housing units permitted per 1,000 residents, 2017–2024 — colored by "tier": 80+ units per 1,000 (#54278f), 40–79 (#9e9ac8), 10–39 (#dadaeb), Under 10 (#fcfbfd), No permit record (#ffffff)

     WITH e AS (
             SELECT county_fips,
                    sum(enrollment) FILTER (
                        WHERE school_year = '2017-18'
                    ) e17,
                    sum(enrollment) FILTER (
                        WHERE school_year = '2024-25'
                    ) e24
               FROM tn_nces_public_schools
              WHERE school_year IN ('2017-18', '2024-25')
                AND coalesce(virtual, '') <> 'Full virtual'
           GROUP BY 1
          ),
          p AS (
             SELECT geoid,
                    sum(units_total) u
               FROM tn_bps_county_permits
              WHERE YEAR BETWEEN 2017 AND 2024
           GROUP BY 1
          ),
          pop AS (
             SELECT geoid,
                    pop_estimate
               FROM tn_census_county_pop_est
              WHERE YEAR = 2017
          )
   SELECT c.geom,
          c.name AS "County",
          p.u AS "Housing units permitted 2017-2024",
          round(1000.0 * p.u / pop.pop_estimate, 1) AS "Units per 1,000 residents(2017 pop)",
          e.e17 AS "Enrollment 2017-18 (excl. virtual)",
          e.e24 AS "Enrollment 2024-25 (excl. virtual)",
          e.e24 - e.e17 AS "Enrollment change",
          round(100.0 * (e.e24 - e.e17) / e.e17, 1) AS "Enrollment change %",
          CASE
                    WHEN p.u IS NULL THEN 'q0'
                    WHEN 1000.0 * p.u / pop.pop_estimate >= 80 THEN 'q4'
                    WHEN 1000.0 * p.u / pop.pop_estimate >= 40 THEN 'q3'
                    WHEN 1000.0 * p.u / pop.pop_estimate >= 10 THEN 'q2'
                    ELSE 'q1'
          END AS tier
     FROM tn_census_counties c
LEFT JOIN e ON e.county_fips = c.geoid
LEFT JOIN p ON p.geoid = c.geoid
LEFT JOIN pop ON pop.geoid = c.geoid

Picture: maps/turn-1-permits-vs-enrollment-2.png

Housing Permits vs School Enrollment Change: County school enrollment change, 2017-18 to 2024-25 (excl. full-virtual) — colored by "dir": Enrollment rose (#1a9850), Enrollment fell (#f46d43)

     WITH e AS (
             SELECT county_fips,
                    sum(enrollment) FILTER (
                        WHERE school_year = '2017-18'
                    ) e17,
                    sum(enrollment) FILTER (
                        WHERE school_year = '2024-25'
                    ) e24
               FROM tn_nces_public_schools
              WHERE school_year IN ('2017-18', '2024-25')
                AND coalesce(virtual, '') <> 'Full virtual'
           GROUP BY 1
          )
   SELECT ST_PointOnSurface(c.geom) geom,
          c.name AS "County",
          e.e24 - e.e17 AS "Enrollment change",
          round(100.0 * (e.e24 - e.e17) / e.e17, 1) AS "Change %",
          CASE
                    WHEN e.e24 > e.e17 THEN 'up'
                    ELSE 'down'
          END dir
     FROM tn_census_counties c
     JOIN e ON e.county_fips = c.geoid

Picture: maps/turn-1-permits-vs-enrollment-2.png

Used Data Sets

tn_bps_county_permits — last synced 2026-10-14 · 3,288 rows
tn_census_counties — last synced 2026-09-27 · 95 rows
tn_census_county_pop_est — last synced 2026-10-15 · 5,320 rows
tn_nces_public_schools — last synced 2026-10-09 · 15,167 rows

data/turn-1-query-1.csv

↑ Contents

school_year	n	with_enr	enr	districts
2017-18	1789	1782	1001967	147
2018-19	1862	1862	1006309	147
2019-20	1894	1878	1014744	147
2020-21	1907	1880	984066	148
2021-22	1931	1906	995549	149
2022-23	1932	1900	1005626	148
2023-24	1924	1905	1003495	148
2024-25	1928	1913	1002363	149

8 rows.

data/turn-1-query-2.csv

↑ Contents

min	max	count
2017	2025	94

1 row.

data/turn-1-query-3.csv

↑ Contents

leaid	district	county	e17	e24	chg	pct	s17	s24
4700157	Tennessee Public Charter School Commission	Davidson County		6979	6979			17
4703690	Rutherford County	Rutherford County	45292	52127	6835	15.1	47	55
4703030	Montgomery County	Montgomery County	34841	39385	4544	13.0	39	46
4702160	Johnson County	Johnson County	2110	4952	2842	134.7	7	8
4704550	Wilson County	Wilson County	18343	20703	2360	12.9	21	25
4704530	Williamson County	Williamson County	39322	41593	2271	5.8	44	52
4702430	Lewis County	Lewis County	1725	3731	2006	116.3	4	7
4700148	Memphis-Shelby County Schools	Shelby County	109591	111125	1534	1.4	201	223
4704290	Union County	Union County	4207	5514	1307	31.1	10	11
4701590	Hamilton County	Hamilton County	44822	45981	1159	2.6	78	82
4704020	Sumner County	Sumner County	29636	30719	1083	3.7	49	53
4704551	University Schools	Shelby County		957	957			3
4700154	Lakeland	Shelby County	1652	2538	886	53.6	2	2
4702370	Lebanon	Wilson County	3773	4628	855	22.7	6	7
4703240	Oak Ridge	Anderson County	4440	5078	638	14.4	7	8
4700990	DeKalb County	DeKalb County	3041	3644	603	19.8	5	7
4700149	Collierville	Shelby County	8671	9263	592	6.8	8	10
4703150	Murfreesboro	Rutherford County	8841	9342	501	5.7	12	13
4702700	Maryville	Blount County	5349	5717	368	6.9	7	8
4703480	Putnam County	Putnam County	11421	11776	355	3.1	20	22
4702400	Lenoir City	Loudon County	2297	2638	341	14.8	3	4
4702760	Maury County	Maury County	12791	13098	307	2.4	21	24
4700180	Bedford County	Bedford County	8754	9042	288	3.3	14	15
4702610	Manchester	Coffee County	1400	1628	228	16.3	3	3
4700060	Alcoa	Blount County	2047	2270	223	10.9	4	4
4702550	Macon County	Macon County	4007	4216	209	5.2	8	8
4702520	Loudon County	Loudon County	4887	5085	198	4.1	9	9
4702580	Madison County	Madison County	12836	13017	181	1.4	23	26
4704170	Trousdale County	Trousdale County	1255	1411	156	12.4	3	3
4701500	Greeneville	Greene County	2846	2977	131	4.6	7	8
4703300	Oneida	Scott County	1284	1401	117	9.1	3	3
4701110	Elizabethton	Carter County	2566	2678	112	4.4	5	5
4700690	Cleveland	Bradley County	5792	5890	98	1.7	9	9
4702010	Huntingdon Special School District	Carroll County	1270	1364	94	7.4	3	4
4704200	Tullahoma	Coffee County	3546	3632	86	2.4	7	8
4701390	Bradford	Gibson County	555	630	75	13.5	2	2
4702910	Meigs County	Meigs County	1752	1797	45	2.6	4	4
4702460	Lexington	Henderson County	851	893	42	4.9	2	2
4702490	Lincoln County	Lincoln County	3863	3899	36	0.9	8	8
4700156	Department Of Children's Services Education Division	Fayette County		32	32			1
4703390	Perry County	Perry County	1075	1100	25	2.3	4	5
4702190	Kingsport	Sullivan County	7637	7658	21	0.3	12	12
4704320	Van Buren County	Van Buren County	739	756	17	2.3	2	2
4703210	Newport	Cocke County	713	730	17	2.4	1	1
4704100	Trenton	Gibson County	1361	1372	11	0.8	3	4
4700330	Bradley County	Bradley County	10192	10202	10	0.1	17	18
4700930	Dayton	Rhea County	884	891	7	0.8	1	1
4700120	Athens	McMinn County	1744	1748	4	0.2	5	3
		Shelby County			0		7
4703060	Moore County	Moore County	885	882	-3	-0.3	2	2
4700146	Tennessee Schools for the Deaf	Knox County	135	131	-4	-3.0	3	4
4700480	Carroll County	Carroll County	6	1	-5	-83.3	1	1
4700143	West Tennessee School for the Deaf	Madison County	42	33	-9	-21.4	1	1
4700720	Clinton	Anderson County	973	963	-10	-1.0	3	3
4700240	Benton County	Benton County	2160	2150	-10	-0.5	6	8
4704050	Sweetwater	Monroe County	1542	1532	-10	-0.6	4	4
4700030	Alamo	Crockett County	611	600	-11	-1.8	1	1
4704260	Union City	Obion County	1658	1647	-11	-0.7	3	3
4700210	Bells	Crockett County	419	407	-12	-2.9	1	1
4701230	Fentress County	Fentress County	2245	2229	-16	-0.7	5	6
4703900	South Carroll	Carroll County	361	343	-18	-5.0	1	1
4702970	Milan	Gibson County	2047	2026	-21	-1.0	3	4
4703660	Rogersville	Hawkins County	672	644	-28	-4.2	1	1
4701890	Hollow Rock - Bruceton	Carroll County	655	626	-29	-4.4	2	2
4700145	Tennessee School for Blind	Davidson County	131	100	-31	-23.7	1	1
4700150	Millington Municipal Schools	Shelby County	2632	2587	-45	-1.7	4	3
4700144	Alvin C. York Agricultural Institute	Fentress County	538	493	-45	-8.4	1	1
4701620	Hancock County	Hancock County	959	903	-56	-5.8	2	4
4702790	McKenzie	Carroll County	1356	1296	-60	-4.4	3	3
4700151	Germantown	Shelby County	6113	6053	-60	-1.0	5	7
4703420	Pickett County	Pickett County	670	605	-65	-9.7	2	2
4700660	Clay County	Clay County	1121	1054	-67	-6.0	3	4
4703870	Smith County	Smith County	3126	3056	-70	-2.2	9	9
4702280	Lake County	Lake County	817	743	-74	-9.1	3	3
4701080	Dyersburg	Dyer County	2663	2585	-78	-2.9	4	4
4701140	Etowah	McMinn County	411	332	-79	-19.2	1	1
4702340	Lawrence County	Lawrence County	6947	6867	-80	-1.2	12	15
4700600	Chester County	Chester County	2885	2801	-84	-2.9	6	6
4703540	Richard City	Marion County	272	183	-89	-32.7	1	1
4701800	Henderson County	Henderson County	3991	3900	-91	-2.3	9	10
4700360	Bristol	Sullivan County	4136	4044	-92	-2.2	8	9
4703960	Stewart County	Stewart County	2064	1972	-92	-4.5	4	6
4700850	Crockett County	Crockett County	2064	1969	-95	-4.6	5	5
4702070	Jackson County	Jackson County	1493	1389	-104	-7.0	4	4
4703780	Sevier County	Sevier County	14512	14408	-104	-0.7	28	33
4702130	Johnson City	Washington County	7925	7818	-107	-1.4	11	12
4701830	Henry County	Henry County	3034	2924	-110	-3.6	6	6
4703600	Robertson County	Robertson County	11381	11267	-114	-1.0	21	23
4701920	Houston County	Houston County	1383	1265	-118	-8.5	4	6
4701950	Humboldt City Schools	Gibson County	1179	1044	-135	-11.5	3	3
4703360	Paris	Henry County	1686	1538	-148	-8.8	3	3
4700450	Cannon County	Cannon County	1967	1813	-154	-7.8	7	5
4700155	Tennessee State Board of Education	Shelby County	156		-156		1
4700780	Coffee County	Coffee County	4474	4313	-161	-3.6	9	11
4704490	West Carroll Sp Dist	Carroll County	910	734	-176	-19.3	3	3
4700960	Decatur County	Decatur County	1672	1492	-180	-10.8	4	6
4703090	Morgan County	Morgan County	2980	2800	-180	-6.0	8	8
4700152	Arlington	Shelby County	4599	4403	-196	-4.3	4	4
4701400	Gibson Co Sp Dist	Gibson County	4024	3828	-196	-4.9	9	9
4704470	Weakley County	Weakley County	4193	3993	-200	-4.8	10	11
4703330	Overton County	Overton County	3132	2924	-208	-6.6	7	8
4700270	Bledsoe County	Bledsoe County	1780	1570	-210	-11.8	5	5
4702670	Marshall County	Marshall County	5542	5322	-220	-4.0	10	10
4703450	Polk County	Polk County	2390	2160	-230	-9.6	6	7
4704440	Wayne County	Wayne County	2316	2079	-237	-10.2	7	9
4700001	Hamblen County	Hamblen County	10543	10300	-243	-2.3	18	18
4703000	Monroe County	Monroe County	5257	5006	-251	-4.8	12	13
4702640	Marion County	Marion County	4105	3844	-261	-6.4	11	12
4704500	White County	White County	3997	3736	-261	-6.5	9	8
4701410	Giles County	Giles County	3861	3595	-266	-6.9	8	8
4700630	Claiborne County	Claiborne County	4235	3967	-268	-6.3	12	14
4704350	Warren County	Warren County	6520	6241	-279	-4.3	11	12
4700153	Bartlett	Shelby County	9134	8834	-300	-3.3	10	10
4703750	Sequatchie County	Sequatchie County	2230	1924	-306	-13.7	3	3
4701680	Hardin County	Hardin County	3619	3294	-325	-9.0	7	7
4704230	Unicoi County	Unicoi County	2405	2074	-331	-13.8	6	7
4700750	Cocke County	Cocke County	4535	4202	-333	-7.3	11	12
4701980	Humphreys County	Humphreys County	2980	2641	-339	-11.4	7	6
4701530	Grundy County	Grundy County	2059	1711	-348	-16.9	7	5
4702100	Jefferson County	Jefferson County	7302	6937	-365	-5.0	11	14
4701050	Dyer County	Dyer County	3974	3600	-374	-9.4	8	8
4703270	Obion County	Obion County	3348	2959	-389	-11.6	7	7
4701860	Hickman County	Hickman County	3392	3001	-391	-11.5	8	9
4701200	Fayetteville	Lincoln County	1476	1080	-396	-26.8	3	3
4703510	Rhea County	Rhea County	4307	3890	-417	-9.7	7	7
4700900	Cumberland County	Cumberland County	7419	6970	-449	-6.1	12	12
4703590	Roane County	Roane County	6633	6178	-455	-6.9	17	18
4701290	Franklin County	Franklin County	5428	4972	-456	-8.4	11	11
4701770	Haywood County	Haywood County	2904	2422	-482	-16.6	5	7
4700300	Blount County	Blount County	10770	10270	-500	-4.6	20	22
4704380	Washington County	Washington County	8479	7969	-510	-6.0	16	15
4700420	Campbell County	Campbell County	5638	5118	-520	-9.2	12	16
4701440	Grainger County	Grainger County	3503	2975	-528	-15.1	8	9
4701650	Hardeman County Schools	Hardeman County	3687	3157	-530	-14.4	9	9
4700090	Anderson County	Anderson County	6548	6018	-530	-8.1	16	19
4702880	McNairy County	McNairy County	4271	3733	-538	-12.6	8	10
4701260	Franklin SSD	Williamson County	3703	3161	-542	-14.6	8	8
4703720	Scott County	Scott County	2977	2423	-554	-18.6	7	7
4701020	Dickson County	Dickson County	8387	7777	-610	-7.3	16	17
4702220	Knox County	Knox County	60802	60185	-617	-1.0	87	95
4702820	McMinn County	McMinn County	5690	5051	-639	-11.2	9	9
4701170	Fayette County Public Schools	Fayette County	3560	2909	-651	-18.3	7	7
4701740	Hawkins County	Hawkins County	6705	6038	-667	-9.9	18	18
4701470	Greene County	Greene County	6783	6088	-695	-10.2	16	18
4700570	Cheatham County	Cheatham County	6277	5451	-826	-13.2	13	14
4700510	Carter County	Carter County	5290	4450	-840	-15.9	15	15
4702310	Lauderdale County	Lauderdale County	4159	3277	-882	-21.2	7	7
4704080	Tipton County	Tipton County	11114	9975	-1139	-10.2	14	14
4703990	Sullivan County	Sullivan County	9758	7878	-1880	-19.3	22	17
4703180	Davidson County	Davidson County	84728	81022	-3706	-4.4	161	162
4700147	Achievement School District	Shelby County	11419	1412	-10007	-87.6	32	12

151 rows.

data/turn-1-query-4.csv

↑ Contents

school_year	district_name	school_name	virtual	enrollment
2024-25	DeKalb County	DeKalb County Schools Adult Education Virtual School	Full virtual	784
2024-25	Johnson County	Tennessee Connections Academy Johnson County 4-8	Full virtual	1471
2024-25	Johnson County	Tennessee Connections Academy Johnson County 9-12	Full virtual	1543
2024-25	Lewis County	Volunteer State Virtual Academy Elementary School	Full virtual	614
2024-25	Lewis County	Volunteer State Virtual Academy High School	Full virtual	943
2024-25	Lewis County	Volunteer State Virtual Academy Middle School	Full virtual	421
2017-18	Union County	Tennessee Virtual Academy	Full virtual	1216
2024-25	Union County	Tennessee Virtual Academy	Full virtual	1867
2024-25	Union County	Tennessee Virtual Junior High School	Full virtual	856

9 rows.

data/turn-1-query-5.csv

↑ Contents

name	e17	e24	chg	pct	units	per1k	units_rank	chg_rank
Davidson County	85583	84879	-704	-0.8	86283	122.6	1	87
Rutherford County	54133	61904	7771	14.4	28327	88.6	2	1
Knox County	60937	59932	-1005	-1.6	24337	52.3	3	92
Montgomery County	34841	38634	3793	10.9	22928	111.7	4	2
Williamson County	43025	44637	1612	3.7	18391	80.2	5	4
Hamilton County	44794	45727	933	2.1	17518	48.9	6	6
Wilson County	22061	25216	3155	14.3	16401	119.6	7	3
Shelby County	153052	149173	-3879	-2.5	15030	16.1	8	95
Sumner County	29636	30671	1035	3.5	14826	80.0	9	5
Maury County	12791	13098	307	2.4	13985	149.0	10	9
Sevier County	14512	14408	-104	-0.7	9675	99.7	11	33
Blount County	18166	18195	29	0.2	6869	52.6	12	17
Robertson County	11331	11201	-130	-1.1	6041	85.3	13	37
Washington County	16388	15750	-638	-3.9	6001	46.2	14	83
Loudon County	7184	7723	539	7.5	4763	90.8	15	7
Putnam County	11416	11776	360	3.2	4681	61.1	16	8
Bradley County	15984	15960	-24	-0.2	4639	44.0	17	23
Sullivan County	20840	18818	-2022	-9.7	3728	23.8	18	94
Dickson County	8387	7737	-650	-7.8	3499	66.2	19	85
Cumberland County	7419	6970	-449	-6.1	3491	59.3	20	72
Fayette County	3560	2941	-619	-17.4	3067	75.7	21	82
Bedford County	8754	9042	288	3.3	3004	62.2	22	10
Anderson County	11961	12027	66	0.6	2588	34.1	23	15
Franklin County	5876	5355	-521	-8.9	2558	61.1	24	76
Cheatham County	6277	5381	-896	-14.3	2454	60.7	25	91
Jefferson County	7302	6882	-420	-5.8	2404	45.0	26	70
Coffee County	8972	9068	96	1.1	2337	42.2	27	14
Hamblen County	10543	10300	-243	-2.3	2172	34.1	28	52
Madison County	12878	13050	172	1.3	2146	21.9	29	12
Campbell County	5638	5118	-520	-9.2	1870	47.5	30	75
Marshall County	5542	5322	-220	-4.0	1869	57.2	31	49
Greene County	9629	8952	-677	-7.0	1749	25.2	32	86
Polk County	2390	2160	-230	-9.6	1658	95.8	33	50
Tipton County	11114	9975	-1139	-10.2	1606	26.5	34	93
Warren County	6520	6241	-279	-4.3	1571	39.0	35	57
Marion County	4366	3995	-371	-8.5	1472	51.9	36	65
White County	3997	3736	-261	-6.5	1363	51.3	37	53
Monroe County	6799	6265	-534	-7.9	1276	28.1	38	80
Carter County	7856	7005	-851	-10.8	1234	21.9	39	89
Macon County	4007	4216	209	5.2	1199	49.5	40	11
Gibson County	9166	8900	-266	-2.9	1172	23.4	41	54
Hawkins County	7844	7196	-648	-8.3	1170	20.7	42	84
Rhea County	5191	4781	-410	-7.9	1116	34.5	43	69
Roane County	6633	6100	-533	-8.0	1037	19.6	44	79
Giles County	3861	3595	-266	-6.9	989	33.0	45	54
DeKalb County	3041	2860	-181	-6.0	973	50.3	46	41
Union County	2991	2791	-200	-6.7	868	45.3	47	44
Trousdale County	1255	1411	156	12.4	857	78.0	48	13
Smith County	3126	3056	-70	-2.2	856	43.9	49	28
Hickman County	3392	3001	-391	-11.5	831	33.8	50	66
Claiborne County	4235	3967	-268	-6.3	716	22.6	51	56
Meigs County	1752	1797	45	2.6	630	51.4	52	16
Dyer County	6637	6185	-452	-6.8	532	14.3	53	73
Grainger County	3503	2975	-528	-15.1	515	22.3	54	77
Weakley County	4193	3993	-200	-4.8	468	14.2	55	44
McMinn County	7845	7131	-714	-9.1	391	7.5	56	88
Lincoln County	5339	4938	-401	-7.5	384	11.1	57	68
Cannon County	1967	1813	-154	-7.8	368	26.4	58	38
Lewis County	1725	1753	28	1.6	330	27.1	59	18
Hardeman County	3687	3157	-530	-14.4	286	11.0	60	78
Crockett County	3094	2976	-118	-3.8	236	16.6	61	35
Moore County	885	882	-3	-0.3	211	32.9	62	21
Lauderdale County	4159	3277	-882	-21.2	209	8.1	63	90
Obion County	5006	4606	-400	-8.0	191	6.2	64	67
Humphreys County	2980	2641	-339	-11.4	181	9.6	65	63
Clay County	1121	1054	-67	-6.0	180	23.6	66	27
Hardin County	3619	3294	-325	-9.0	170	6.4	67	61
Chester County	2885	2801	-84	-2.9	163	9.5	68	30
Henry County	4720	4399	-321	-6.8	158	4.9	69	60
Haywood County	2904	2397	-507	-17.5	151	8.3	70	74
Henderson County	4842	4744	-98	-2.0	149	5.4	71	32
Lawrence County	6947	6748	-199	-2.9	133	3.1	72	43
Unicoi County	2405	2074	-331	-13.8	110	6.1	73	62
McNairy County	4271	3700	-571	-13.4	109	4.2	74	81
Sequatchie County	2230	1924	-306	-13.7	108	7.1	75	58
Overton County	3132	2924	-208	-6.6	98	4.5	76	46
Benton County	2160	2150	-10	-0.5	96	6.1	77	22
Perry County	1075	1100	25	2.3	77	9.5	78	19
Grundy County	2059	1711	-348	-16.9	62	4.6	79	64
Carroll County	4558	4364	-194	-4.3	62	2.2	79	42
Stewart County	2064	1972	-92	-4.5	56	4.2	81	31
Scott County	4261	3824	-437	-10.3	50	2.3	82	71
Cocke County	5248	4932	-316	-6.0	29	0.8	83	59
Van Buren County	739	756	17	2.3	27	4.6	84	20
Lake County	817	743	-74	-9.1	26	3.5	85	29
Decatur County	1672	1459	-213	-12.7	25	2.2	86	48
Johnson County	2110	1938	-172	-8.2	23	1.3	87	39
Houston County	1383	1258	-125	-9.0	7	0.9	88	36
Fentress County	2783	2722	-61	-2.2	7	0.4	88	25
Wayne County	2316	2079	-237	-10.2	3	0.2	90	51
Hancock County	959	903	-56	-5.8	2	0.3	91	24
Morgan County	2980	2800	-180	-6.0	0	0.0	92	40
Jackson County	1493	1389	-104	-7.0	0	0.0	92	33
Bledsoe County	1780	1570	-210	-11.8	0	0.0	92	47
Pickett County	670	605	-65	-9.7			95	26

95 rows.

data/turn-1-query-6.csv

↑ Contents

spearman	pearson	q	n	min_per1k	chg	pct	gaining
0.45	0.54	1	24	51.4	13191	2.9	12
0.45	0.54	2	24	23.8	-7100	-3.4	5
0.45	0.54	3	24	6.1	-12432	-4.7	2
0.45	0.54	4	23	0.0	-4274	-6.7	1

4 rows.

data/turn-1-query-7.csv

↑ Contents

cat	count	net
g1	202	19019
g2	68	21833
gone	124	-36161
l1	483	-49501
l2	95	-28844
na	13	-4882
new	263	82539
z	804	-3607

8 rows.

Provenance: `tn_bps_county_permits`

This file describes how the rows in PostgreSQL table tn_bps_county_permits were obtained from an external source: where they were fetched and how fields were renamed and typed. Field meanings are in the mapping table below.

Identity

Table: tn_bps_county_permits
Family: BPS
Display name: Building Permits by County
Source URL: https://www2.census.gov/econ/bps/County/ (co<year>a.txt, 1990 on)
Geometry (source / stored): none / none
Update cycle: Yearly (each May)

Pipeline

The Building Permits Survey publishes one national county file per year, the annual summary (a), comma-delimited text with a two-row header. Every year from 1990 is loaded; the newest year is the one before the current, once the Bureau has published it. Each year's file is downloaded whole and kept; the sync keeps Tennessee's rows (FIPS State 47) and checks each file's survey date names its year (2024, or 9099 in the 1990s files).

A county listed twice in one year (the 2014 and 2015 files repeat four Alaska boroughs and one Maryland county under a second name with the same estimates) keeps the row with more reported units; two rows with different estimates fail the sync. The monthly and year-to-date files are not loaded.

Changed from the source: cut to Tennessee; reshaped to one row per county per year; the three totals added; the region, division and survey-date columns dropped (doc/BPS-SYNC.md).

No licence terms: a work of the U.S. Government, credited to the U.S. Census Bureau (doc/DATA-LICENSES.md).

Field → column mapping

Source field	Column	Type	Description
FIPS State, FIPS County	`geoid`	text	Two digits and three, joined
County Name	`name`	text	Trailing spaces trimmed
Survey Date	`year`	text → integer	Checked against the file's year
1-unit Bldgs, Units, Value	`bldgs_1`, `units_1`, `value_1`	integer	Estimates with imputation
2-units Bldgs, Units, Value	`bldgs_2`, `units_2`, `value_2`	integer
3-4 units Bldgs, Units, Value	`bldgs_3_4`, `units_3_4`, `value_3_4`	integer
5+ units Bldgs, Units, Value	`bldgs_5plus`, `units_5plus`, `value_5plus`	integer
1-unit rep … 5+ units rep	`rep_bldgs_1` … `rep_value_5plus`	integer	Reported only
(computed)	`bldgs_total`, `units_total`, `value_total`		The four estimate sizes added
Region Code, Division Code	(dropped)

provenance/tn_census_counties.md

↑ Contents

Provenance: `tn_census_counties`

This file describes how the rows in PostgreSQL table tn_census_counties were obtained from an external source: where they were fetched and how fields were renamed and typed. Field meanings are in the mapping table below.

Identity

Table: tn_census_counties
Family: Census
Display name: Census Counties (Tennessee)
Source URL: https://tigerweb.geo.census.gov/arcgis/rest/services/TIGERweb/tigerWMS_Current/MapServer/82
Also from: https://www2.census.gov/programs-surveys/cfs/technical-documentation/geographies/list2022.xlsx
Geometry (source / stored): polygon / geometry(MultiPolygon, 4326)
Update cycle: Yearly

Pipeline

Two Census Bureau sources, joined on the county's 5-digit FIPS code:

Boundaries: TIGERweb tigerWMS_Current layer 82 ("Counties"), where=STATE='47', GeoJSON, outSR=4326, keyset-paged on GEOID.
CFS areas: sheet CFSAreas of the 2022 Commodity Flow Survey county list, the Tennessee rows (ST = 47). Only the 2022 columns are kept.

The two must name the same counties, or the sync fails. FAF's tn_faf_county_xwalk is built from this table: each county's cfs_geoid is the CFS22_ID of one FAF6 zone.

Field → column mapping

Source field	Column	Type	Description
TIGER `GEOID`	`geoid`	varchar(5)	state + county FIPS
TIGER `GEOID`, last 3	`county_fips`	varchar(3)	county FIPS
TIGER `NAME`	`name`	varchar(60)	e.g. `Shelby County`
CFS `CFS22_AREA`	`cfs_area`	varchar(5)	CSA/MSA code, or `99999`
CFS `CFS22_TYPE`	`cfs_area_type`	varchar(1)	C, M or R
CFS `CFS22_NAME`, whitespace collapsed	`cfs_area_name`	varchar(120)	the workbook pads some names with doubled and trailing spaces
CFS `CFS22_GEOID`	`cfs_geoid`	varchar(19)	e.g. `E330000US4736800000`
TIGER `AREALAND`	`aland_m2`	bigint	square meters
TIGER `AREAWATER`	`awater_m2`	bigint	square meters
TIGER `INTPTLAT`	`intptlat`	double precision	internal point
TIGER `INTPTLON`	`intptlon`	double precision	internal point
geometry	`geom`	geometry(MultiPolygon, 4326)	`ST_Multi(ST_GeomFromGeoJSON)`

Geometry

SRID 4326 (WGS84). GIST index on geom.

Change detection

The Census sync's check compares the layer's vintage ("January 1, 2026 vintage") and Tennessee's row count, and the county list's Last-Modified and length. A change to either marks Census stale, and the next Census sync rebuilds this table, then FAF's crosswalk.

Attribution

US Census Bureau. TIGER products are free to use with acknowledgement of the U.S. Census Bureau as the source; the CFS county list states no terms.

provenance/tn_census_county_pop_est.md

↑ Contents

Provenance: `tn_census_county_pop_est`

This file describes how the rows in PostgreSQL table tn_census_county_pop_est were obtained from an external source: where they were fetched and how fields were renamed and typed. Field meanings are in the mapping table below.

Identity

Table: tn_census_county_pop_est
Family: Census
Display name: Annual Population Estimates by County
Source URL: https://www2.census.gov/programs-surveys/popest/datasets/2020-2025/counties/totals/co-est2025-alldata.csv (vintage 2025; each vintage <V> is 2020-<V>/counties/totals/co-est<V>-alldata.csv), for 2020 on
History (2000–2019): under the same datasets/ directory, 2000-2010/intercensal/county/co-est00int-tot.csv and 2010-2020/intercensal/county/asrh/cc-est2020int-agesex-all.csv (the revised population), 2010/2010-eval-estimates/co-est2010-alldata.csv and 2010-2020/counties/totals/co-est2020-alldata.csv (the components of change of the releases they revised)
History (1970–1999): under https://www2.census.gov/programs-surveys/popest/tables/, 1900-1980/counties/asrh/co-asr-7079.csv, 1980-1990/counties/asrh/pe-02-<year>.csv and 1990-2000/intercensal/st-co/stch-icen<year>.txt (the county age, sex and race estimates), and 1900-1980/counties/totals/e7079co.txt and 1980-1990/counties/totals/e8089co.txt (the decades' county totals tables, for their census counts)
Geometry (source / stored): none / none
Update cycle: Yearly (each March)

Pipeline

The Population Estimates Program's national county file, CSV, one row per county with a column per year for each figure. The sync keeps Tennessee's county rows (SUMLEV 050, STATE 47), dropping the state's own total (040), and turns each into one row per year, the years read off the file's POPESTIMATE<year> columns. Every county the file names must be one the county layer of TIGERweb draws, or the sync fails. A file that is not valid UTF-8 is read as Latin-1. These rows are series postcensal.

The years before 2020 are series intercensal, from four further national files that never change:

Population, 2000–2009: co-est00int-tot.csv, one row per county, codes written without leading zeros (SUMLEV "50", STATE "1"), which the sync pads. ESTIMATESBASE2000 and POPESTIMATE2000–2009; its POPESTIMATE2010 and CENSUS2010POP are not loaded. vintage 2010.
Population, 2010–2019: the Bureau's table of revised county totals for the decade is a spreadsheet that names counties without codes, so the sync reads the same figures from cc-est2020int-agesex-all.csv, one row per county and YEAR code, its POPESTIMATE the whole population: YEAR 1 is the April 1, 2010 base, 2–11 July 1, 2010–2019, and 12 (the April 1, 2020 census) is not loaded. vintage 2020. 2010 is taken from this file, not the 2000s one, which has its own figure for it.
Components: the original releases' files, as published, matched by county code: co-est2010-alldata.csv for 2000–2010 (2000 is April to June) and co-est2020-alldata.csv for 2011–2019. Their natural change column is NATURALINC<year>. A county with no row in the 2000s release (one coded anew in the 2010s) has empty 2010 components; any other missing county fails the sync.
pop_change is computed, not read: pop_estimate less the row before's (2010's less the 2000s file's 2009, 2000's less 1999's, or ESTIMATESBASE2000 for a county with no 1999 row). The releases' NPOPCHG_<year> describes their own population, not the revised one, and is not loaded.
1970–1999: each county's July 1 population is the sum of its age, sex and race estimates, which tn_census_county_pop_char loads from the same files (its provenance has their layouts): vintage 1980, 1990 and 2000 for the three decades. The Bureau's county totals tables for the 1970s and 1980s, e7079co.txt and e8089co.txt, are fixed-width text in blocks of five years, a long name wrapping onto a line of its own; only their first column, the decade's April 1 census, is loaded, into estimates_base on the 1970 and 1980 rows. Their July figures are not loaded: the 1970s table rounds counties to hundreds, and both are on the geography of their day as the age files are, but not always the same day's. No component of change is loaded before 2000. pop_change is computed as for the later decades, 1970's from the April 1970 count, and the 2000 row's is from July 1999.
A history row's county need not be one TIGER draws today (Connecticut's eight old counties, the 1970s' Alaskan census divisions and Virginia's Nansemond, and a handful elsewhere); the sync logs them.

Field → column mapping

Source field	Column	Type	Description
`STATE` + `COUNTY`	`geoid`	varchar(5)	county FIPS
the `<year>` suffix	`year`	smallint
the newest `<year>`	`vintage`	smallint	1980, 1990, 2000, 2010 or 2020 on history rows
	`series`	varchar(12)	`postcensal`, or `intercensal` on history rows
`POPESTIMATE<year>`	`pop_estimate`	integer
`ESTIMATESBASE2020`	`estimates_base`	integer	2020 row only (`ESTIMATESBASE2000`, `YEAR` 1 on the 2000 and 2010 rows; the decade tables' census column on the 1970 and 1980 rows)
`NPOPCHG<year>`	`pop_change`	integer	computed on history rows
`BIRTHS<year>`	`births`	integer
`DEATHS<year>`	`deaths`	integer
`NATURALCHG<year>`	`natural_change`	integer	`NATURALINC<year>` on history rows
`INTERNATIONALMIG<year>`	`international_mig`	integer
`DOMESTICMIG<year>`	`domestic_mig`	integer
`NETMIG<year>`	`net_mig`	integer
`RESIDUAL<year>`	`residual`	integer
`GQESTIMATES<year>`	`gq_estimate`	integer

The file's per-1,000 rate columns (R…) and GQESTIMATESBASE2020 are not loaded.

Geometry

No geometry column.

Change detection

The Census sync's check asks for the next vintage's file each time and moves to it once it is published; otherwise it compares the current file's Last-Modified and length, and each history file's. Any of them marks this table stale, and the next Census sync rebuilds it whole, every year from 2020 replaced by the new release.

Attribution

US Census Bureau, Population Estimates Program.

provenance/tn_nces_public_schools.md

↑ Contents

Provenance: `tn_nces_public_schools`

This file describes how the rows in PostgreSQL table tn_nces_public_schools were obtained from an external source: where they were fetched and how fields were renamed and typed. Field meanings are in the mapping table below.

Identity

Table: tn_nces_public_schools
Family: NCES
Display name: NCES Public Schools
Source URL: https://nces.ed.gov/opengis/rest/services/K12_School_Locations
Geometry (source / stored): esriGeometryPoint / Point, 4326
Update cycle: Annually

Pipeline (NCES family)

NCES publishes each school year as two national ArcGIS MapServer services in the K12_School_Locations folder: EDGE_GEOCODE_PUBLICSCH_<yy> (the EDGE geocoded school locations) and EDGE_ADMINDATA_PUBLICSCH_<yy> (the Common Core of Data administrative attributes, with their own points). Every year from 2017–18 with both services is loaded. Each state's rows are cut on the server (STATE on the locations, LSTATE on the administrative layer, both the school's physical state, so Bureau of Indian Education schools land in the state they stand in) and walked by keyset on OBJECTID, one gzipped GeoJSON page per stored chunk (doc/NCES-SYNC.md).

The two layers are joined on (NCESSCH, school year). A school in either is a row: the location's point, name, address and geography win where both have them, and a school only in the administrative layer is placed by that layer's own point. The locations service is used for a year only when its SCHOOLYEAR is that year; NCES's 2018–19 locations service carries the 2017–18 list, so 2018–19 is built from the administrative layer alone.

Changed from the source: cut to Tennessee; the two layers joined by school; NCES's missing, not-applicable and suppressed codes (−1, −2, −9) stored as NULL; status, school type, virtual status and charter recoded to one vocabulary across years (their wording changed between collections); SCHOOLYEAR / SURVYEAR shortened to 2024-25; phone numbers, sex-by-race counts, adult education counts and the mailing-address lines dropped.

Public domain: each layer's description states "All information contained in this file is in the public domain" (doc/DATA-LICENSES.md).

Field → column mapping

Source field	Column	Esri type	Description
NCESSCH	`ncessch`	String	Both layers
SCHOOLYEAR, SURVYEAR	`school_year`	String	`2024-2025` → `2024-25`
LEAID	`leaid`	String	Administrative layer, else locations (2019–20 on)
ST_LEAID	`st_leaid`	String	Administrative layer
NAME, SCH_NAME	`school_name`	String	Locations, else administrative
LEA_NAME	`district_name`	String	Administrative layer
STREET, LSTREET1	`street`	String	Locations, else administrative
CITY, LCITY	`city`	String
ZIP, LZIP	`zip`	String
CNTY	`county_fips`	String	Locations; administrative layer from 2022–23
NMCNTY	`county_name`	String
LOCALE, ULOCALE	`locale`	String	Locations' code, else the code before ULOCALE's `-`
(from `locale`)	`locale_name`		NCES's name for the code
CBSA, NMCBSA	`cbsa`, `cbsa_name`	String	Locations only
CD, SLDL, SLDU	`cd`, `sldl`, `sldu`	String	Locations only
STATUS	`status`	String	1 Open, 3 New, 4 Added, 5 Changed boundary or agency, 6 Inactive, 7 Future, 8 Reopened
SCHOOL_TYPE_TEXT	`school_type`	String	Recoded across years
SCHOOL_LEVEL	`school_level`	String	`Not Reported` / `Not Applicable`, in either case, → NULL
GSLO, GSHI	`grade_low`, `grade_high`	String	`M` / `N` → NULL
CHARTER_TEXT	`charter`	String → boolean	Yes / No (2019–20 `1-Yes` / `2-No`); `N`, `Missing` and `Not Applicable` → NULL
VIRTUAL	`virtual`	String	Recoded across years; `M`, `N`, `Missing`, `Not Reported`, `Not Applicable` → NULL
MEMBER	`enrollment`	Integer
PK, KG, G01–G13, UG	`enroll_pk`, `enroll_kg`, `enroll_g01`–`enroll_g13`, `enroll_ungraded`	Integer
FTE	`teachers_fte`	Double
STUTERATIO	`student_teacher_ratio`	Double
TOTFRL, FRELCH, REDLCH	`frl_eligible`, `free_lunch`, `reduced_lunch`	Integer
DIRECTCERT	`direct_cert`	Integer	2021–22 on
AM, AS, BL, HP, HI, TR, WH	`students_aian`, `students_asian`, `students_black`, `students_nhpi`, `students_hispanic`, `students_two_or_more`, `students_white`	Integer
PHONE, LSTREET2, LSTREET3, LZIP4, LSTATE, STABR, OPSTFIPS, STFIP, STATE, LAT, LON, LATCOD, LONCOD, CBSATYPE, CSA, NMCSA, NECTA, NMNECTA, TOTAL, TOTMENROL, TOTFENROL, AE, the `ALM` / `ALF` race-by-sex counts, MAGNET_TEXT, STITLEI, TITLEI, TITLEI_TEXT, SY_STATUS_TEXT	(dropped)

Geometry

Source points, outSR 4326 (the 2022–23 on services are stored in NAD83, EPSG 4269).

Housing Permits vs School Enrollment Change

District enrollment change, 2017‑18 to 2024‑25 (NCES Common Core of Data)

Schools by enrollment change

Are the gains where the most homes are being permitted?

Layers

Data sets used

Ask your own question about Tennessee

Transcript

State Planner chat · https://stateplanner.com

Prompt

Response

District enrollment change, 2017‑18 to 2024‑25 (NCES Common Core of Data)

Schools by enrollment change

Are the gains where the most homes are being permitted?

Exploratory Queries

Query 1 — Check years, school counts and enrollment coverage per year.

Query 2 — Check permit years available.

Query 3 — Enrollment change by district, 2017-18 to 2024-25.

Query 4 — Check for virtual schools driving big percent gains in small districts.

Query 5 — County-level school enrollment change (non-virtual) versus housing units permitted 2017-2024.

Query 6 — Correlation between permits per 1,000 residents and non-virtual enrollment change by county, plus permit-tier summaries.

Query 7 — Count schools in each change category for the legend.

Map Layer Queries

School Enrollment Change 2017-18 to 2024-25: Counties

Housing Permits vs School Enrollment Change: Counties by housing units permitted per 1,000 residents, 2017–2024 — colored by "tier": 80+ units per 1,000 (#54278f), 40–79 (#9e9ac8), 10–39 (#dadaeb), Under 10 (#fcfbfd), No permit record (#ffffff)

Housing Permits vs School Enrollment Change: County enrollment change (excl. virtual): gained — colored by "dir": Enrollment rose (#1a9850), Enrollment fell (#f46d43)

Housing Permits vs School Enrollment Change: Counties by housing units permitted per 1,000 residents, 2017–2024 — colored by "tier": 80+ units per 1,000 (#54278f), 40–79 (#9e9ac8), 10–39 (#dadaeb), Under 10 (#fcfbfd), No permit record (#ffffff)

Housing Permits vs School Enrollment Change: County school enrollment change, 2017-18 to 2024-25 (excl. full-virtual) — colored by "dir": Enrollment rose (#1a9850), Enrollment fell (#f46d43)

Used Data Sets

Provenance: tn_bps_county_permits

Identity

Pipeline

Field → column mapping

Provenance: tn_census_counties

Identity

Pipeline

Field → column mapping

Geometry

Change detection

Attribution

Provenance: tn_census_county_pop_est

Identity

Pipeline

Field → column mapping

Geometry

Change detection

Attribution

Provenance: tn_nces_public_schools

Identity

Pipeline (NCES family)

Field → column mapping

Geometry

Provenance: `tn_bps_county_permits`

Provenance: `tn_census_counties`

Provenance: `tn_census_county_pop_est`

Provenance: `tn_nces_public_schools`