Mike's Wiki

DRAFT

Placeholder - Download the 2023 5 Year ACS. Variables are statefips, puma, household Income and foodstamps. Puma/State are the bridge between the shapefiles and the database

create database puma_db;

create user 'puma_user'@'%' identified by 'xxxxx';
grant all privileges on puma_db.* to 'puma_user'@'%';

mysql> create table  temp
( 
 year int(4),
 multi_year int(4),
 sample int(8),
 serial int(12),
 cbserial int,       -- Serial number of the sample
 hh_weight float,    -- number of households represented by this . This is the actual number of households   represented
 cluster int, 
 statefip int(6),    -- state code
 puma int(8),        -- pumary region. State + puma = usable puma code
 strata int,
 gq    int,          -- Group quarters
 hh_income int,      -- Income for this household
 foodstamps int(3)   -- 0 na, 1 no, 2 yes
);

-- ----------------------------------------------------------------------------------------------------
-- Notes: if you sum the hh_weight for the entire set where gq = 1, then you get a value of about 127MM
-- This matches what the ACS says about the number of households
-- ----------------------------------------------------------------------------------------------------
-- GQ
-- 0	Vacant unit	
--	Households:	
-- 1	Households under 1970 definition
-- 2	Additional households under 1990 definition
-- 3	Institutions
-- 4	Other group quarters
-- 5	Additional households under 2000 definition
-- 6	Fragment

create index ie1_temp on temp (statefip, puma);
-- ----------------------------------------------------------------------------------------------------

fdsfdsfsdfsd

drop table if exists meta_values;

create table meta_values
(
  id integer not null auto_increment,
  name varchar(64),
  code integer,
  value integer,
  primary key (id)
 );

insert into meta_values (name, code, value)
select 'total occupied US households', 1, sum(hh_weight) from temp where gq = 1;

insert into meta_values (name, code, value)
select 'households by state' as household, statefip, sum(hh_weight) 
from temp 
where gq = 1 
group by household, statefip;

insert into meta_values (name, code, value)
select 'households by puma' as household, concat(statefip, lpad(puma,5,0)), sum(hh_weight) 
from temp 
where gq = 1 
group by household, statefip, puma;