Download the GeoCorr2014 table via http://mcdc.missouri.edu/applications/geocorr2014.html
- Select a state.
- Select the "source" and "target" geographies. (For example, if you have demographic data at the tract level, and want to map it to Voting Tabulation Districts (VTD), set VTD as the source and tract as the target. (Yes, this seems counterintuitive.)
- Weight by population (2010 census)
- Generate CSV file. (The report is unnecessary.)
NOTE: Unfortunately, the output doesn't include the full tract FIPS codes, so we'll need to deal with that. Here are some rows showing the geocorr columns we'll be using:
| county | tract | vtdname | pop10 | afact |
|---|---|---|---|---|
| 23001 | 0101.00 | 23001150682 | 10 | 0.001 |
| 23001 | 0102.00 | 23001150682 | 1878 | 0.227 |
| 23005 | 0173.03 | 23005061272 | 1744 | 0.361 |
select
v.GEOID10 as vtd,
coalesce(sum(g.pop10), 0) as totalpop,
coalesce(sum(d.hs_count*g.afact), 0) as hs,
coalesce(sum(d.ba_count*g.afact), 0) as ba,
v.geometry as geometry
from votingdistricts as v
left join geocorr as g on g.vtdname=v.GEOID10
left join demodata as d on d.geoid2 = g.county || lpad(g.tract*100,6,'0')
group by vtdname