[insert witty tagline here]

2010 Census SQLite Database



In a previous post, I described an SQLite database I generated containing 2000 US Census data population info. Now that the 2010 Census data are out, I decided to update this database. The 2010 Census data are nice and clean (much better than the 2000 data!), but in order to get access to all of the data I’ve generated here, you need lots of different files, and it’s kind of a pain to combine it all into a simple useable format (I’ve spent the better part of an entire day doing it, and I’m working on a PhD in computer science…). Hopefully, this’ll make someone’s life simpler.

First, the data I used to generate this database can be found in two places:

The data I present here contain state, county, and ZCTA (which is mostly the same as ZIP code) population info for every state in the United States plus Puerto Rico and Washington DC. I provide centroids (latitude, longitude) and population by age/sex for each geographic unit. This is the schema I used to create the database:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174

CREATE TABLE states (
	id INTEGER NOT NULL,
	state TEXT NOT NULL,
	centroid_longitude REAL NOT NULL,
	centroid_latitude REAL NOT NULL,
	total_population INTEGER NOT NULL,
	male_total_population INTEGER NOT NULL,
	male_lt5_population INTEGER NOT NULL,
	male_5to9_population INTEGER NOT NULL,
	male_10to14_population INTEGER NOT NULL,
	male_15to17_population INTEGER NOT NULL,
	male_18to19_population INTEGER NOT NULL,
	male_20_population INTEGER NOT NULL,
	male_21_population INTEGER NOT NULL,
	male_22to24_population INTEGER NOT NULL,
	male_25to29_population INTEGER NOT NULL,
	male_30to34_population INTEGER NOT NULL,
	male_35to39_population INTEGER NOT NULL,
	male_40to44_population INTEGER NOT NULL,
	male_45to49_population INTEGER NOT NULL,
	male_50to54_population INTEGER NOT NULL,
	male_55to59_population INTEGER NOT NULL,
	male_60to61_population INTEGER NOT NULL,
	male_62to64_population INTEGER NOT NULL,
	male_65to66_population INTEGER NOT NULL,
	male_67to69_population INTEGER NOT NULL,
	male_70to74_population INTEGER NOT NULL,
	male_75to79_population INTEGER NOT NULL,
	male_80to84_population INTEGER NOT NULL,
	male_ge85_population INTEGER NOT NULL,
	female_total_population INTEGER NOT NULL,
	female_lt5_population INTEGER NOT NULL,
	female_5to9_population INTEGER NOT NULL,
	female_10to14_population INTEGER NOT NULL,
	female_15to17_population INTEGER NOT NULL,
	female_18to19_population INTEGER NOT NULL,
	female_20_population INTEGER NOT NULL,
	female_21_population INTEGER NOT NULL,
	female_22to24_population INTEGER NOT NULL,
	female_25to29_population INTEGER NOT NULL,
	female_30to34_population INTEGER NOT NULL,
	female_35to39_population INTEGER NOT NULL,
	female_40to44_population INTEGER NOT NULL,
	female_45to49_population INTEGER NOT NULL,
	female_50to54_population INTEGER NOT NULL,
	female_55to59_population INTEGER NOT NULL,
	female_60to61_population INTEGER NOT NULL,
	female_62to64_population INTEGER NOT NULL,
	female_65to66_population INTEGER NOT NULL,
	female_67to69_population INTEGER NOT NULL,
	female_70to74_population INTEGER NOT NULL,
	female_75to79_population INTEGER NOT NULL,
	female_80to84_population INTEGER NOT NULL,
	female_ge85_population INTEGER NOT NULL,
	PRIMARY KEY (id)
);
 
CREATE TABLE counties (
	id INTEGER NOT NULL,
	state_id INTEGER NOT NULL,
	county TEXT NOT NULL,
	centroid_longitude REAL NOT NULL,
	centroid_latitude REAL NOT NULL,
	total_population INTEGER NOT NULL,
	male_total_population INTEGER NOT NULL,
	male_lt5_population INTEGER NOT NULL,
	male_5to9_population INTEGER NOT NULL,
	male_10to14_population INTEGER NOT NULL,
	male_15to17_population INTEGER NOT NULL,
	male_18to19_population INTEGER NOT NULL,
	male_20_population INTEGER NOT NULL,
	male_21_population INTEGER NOT NULL,
	male_22to24_population INTEGER NOT NULL,
	male_25to29_population INTEGER NOT NULL,
	male_30to34_population INTEGER NOT NULL,
	male_35to39_population INTEGER NOT NULL,
	male_40to44_population INTEGER NOT NULL,
	male_45to49_population INTEGER NOT NULL,
	male_50to54_population INTEGER NOT NULL,
	male_55to59_population INTEGER NOT NULL,
	male_60to61_population INTEGER NOT NULL,
	male_62to64_population INTEGER NOT NULL,
	male_65to66_population INTEGER NOT NULL,
	male_67to69_population INTEGER NOT NULL,
	male_70to74_population INTEGER NOT NULL,
	male_75to79_population INTEGER NOT NULL,
	male_80to84_population INTEGER NOT NULL,
	male_ge85_population INTEGER NOT NULL,
	female_total_population INTEGER NOT NULL,
	female_lt5_population INTEGER NOT NULL,
	female_5to9_population INTEGER NOT NULL,
	female_10to14_population INTEGER NOT NULL,
	female_15to17_population INTEGER NOT NULL,
	female_18to19_population INTEGER NOT NULL,
	female_20_population INTEGER NOT NULL,
	female_21_population INTEGER NOT NULL,
	female_22to24_population INTEGER NOT NULL,
	female_25to29_population INTEGER NOT NULL,
	female_30to34_population INTEGER NOT NULL,
	female_35to39_population INTEGER NOT NULL,
	female_40to44_population INTEGER NOT NULL,
	female_45to49_population INTEGER NOT NULL,
	female_50to54_population INTEGER NOT NULL,
	female_55to59_population INTEGER NOT NULL,
	female_60to61_population INTEGER NOT NULL,
	female_62to64_population INTEGER NOT NULL,
	female_65to66_population INTEGER NOT NULL,
	female_67to69_population INTEGER NOT NULL,
	female_70to74_population INTEGER NOT NULL,
	female_75to79_population INTEGER NOT NULL,
	female_80to84_population INTEGER NOT NULL,
	female_ge85_population INTEGER NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (state_id) REFERENCES states(id)
);
 
CREATE TABLE zctas (
	id INTEGER NOT NULL,
	state_id INTEGER NOT NULL,
	zcta TEXT NOT NULL,
	centroid_longitude REAL NOT NULL,
	centroid_latitude REAL NOT NULL,
	total_population INTEGER NOT NULL,
	male_total_population INTEGER NOT NULL,
	male_lt5_population INTEGER NOT NULL,
	male_5to9_population INTEGER NOT NULL,
	male_10to14_population INTEGER NOT NULL,
	male_15to17_population INTEGER NOT NULL,
	male_18to19_population INTEGER NOT NULL,
	male_20_population INTEGER NOT NULL,
	male_21_population INTEGER NOT NULL,
	male_22to24_population INTEGER NOT NULL,
	male_25to29_population INTEGER NOT NULL,
	male_30to34_population INTEGER NOT NULL,
	male_35to39_population INTEGER NOT NULL,
	male_40to44_population INTEGER NOT NULL,
	male_45to49_population INTEGER NOT NULL,
	male_50to54_population INTEGER NOT NULL,
	male_55to59_population INTEGER NOT NULL,
	male_60to61_population INTEGER NOT NULL,
	male_62to64_population INTEGER NOT NULL,
	male_65to66_population INTEGER NOT NULL,
	male_67to69_population INTEGER NOT NULL,
	male_70to74_population INTEGER NOT NULL,
	male_75to79_population INTEGER NOT NULL,
	male_80to84_population INTEGER NOT NULL,
	male_ge85_population INTEGER NOT NULL,
	female_total_population INTEGER NOT NULL,
	female_lt5_population INTEGER NOT NULL,
	female_5to9_population INTEGER NOT NULL,
	female_10to14_population INTEGER NOT NULL,
	female_15to17_population INTEGER NOT NULL,
	female_18to19_population INTEGER NOT NULL,
	female_20_population INTEGER NOT NULL,
	female_21_population INTEGER NOT NULL,
	female_22to24_population INTEGER NOT NULL,
	female_25to29_population INTEGER NOT NULL,
	female_30to34_population INTEGER NOT NULL,
	female_35to39_population INTEGER NOT NULL,
	female_40to44_population INTEGER NOT NULL,
	female_45to49_population INTEGER NOT NULL,
	female_50to54_population INTEGER NOT NULL,
	female_55to59_population INTEGER NOT NULL,
	female_60to61_population INTEGER NOT NULL,
	female_62to64_population INTEGER NOT NULL,
	female_65to66_population INTEGER NOT NULL,
	female_67to69_population INTEGER NOT NULL,
	female_70to74_population INTEGER NOT NULL,
	female_75to79_population INTEGER NOT NULL,
	female_80to84_population INTEGER NOT NULL,
	female_ge85_population INTEGER NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (state_id) REFERENCES states(id)
);

Previously, I included boundary data (done very poorly as I made several mistakes regarding ZCTAs with multiple boundaries and ZCTAs present in multiple states). I also provided KML files containing this data. I’ve chosen not to do either of these with the 2010 data. All I provide here are population stats and the centroid for each geographic unit. It’s trivial to convert TIGER/Line® shapefiles to KML using free tools (such as QuantumGIS), and using the SQLite database I provide, it’d be fairly simple to add population attributes. Plus, it’s not super practical to use a KML file for attribute accesses; an SQLite database makes much more sense (and is much faster).

Without further ado:
Download: 2010CensusPopulation.7z (size: 2.9mb)

You’ll need a program like 7-Zip to extract the database. You can use a number of free programs to view the data (my favorite for Windows is SQLite Expert).

Feel free to contact me with any questions or comments you may have regarding this database. Hope you find it useful!

GIS, SQL

Leave a Reply