[insert witty tagline here]

pyHarmonySearch supports Python’s multiprocessing module



Yesterday, I added support for Python’s multiprocessing module to pyHarmonySearch. I use multiprocessing to run multiple harmony searches simultaneously. Since harmony search is stochastic, different results may be returned on each run. Some runs will be luckier than others, so I figured it makes sense to take advantage of multiple cores by running multiple search iterations. The resulting solution is the best one found across all iterations.

I don’t have a rigorous proof demonstrating this, but I’ve seen better results in the test objective functions I’ve included on GitHub. On machines with many cores, better results could come at a minimal extra wall time cost. Furthermore, instead of running a single HS instance with a very large number of improvisations, it may be beneficial to simultaneously run multiple HS instances, each with a smaller number of improvisations. This is all conjecture, though, and it likely depends on the objective function being studied.

Python

pyHarmonySearch now supports discrete variables



Last week, I introduced pyHarmonySearch, a Python implementation of the harmony search meta-heuristic search algorithm. The original implementation only supported continuous variables. However, I recently added support for discrete variables. I provide examples in the code of both cases.

If you find pyHarmonySearch useful, please let me know!

Python

Introducing pyHarmonySearch!



For a paper I’ve been working on, I implemented the harmony search (HS) global optimization algorithm in Python. HS is a cool evolutionary-like optimization algorithm that mimics jazz improvisation. Wiki says:

In the HS algorithm, each musician (= decision variable) plays (= generates) a note (= a value) for finding a best harmony (= global optimum) all together.

I’m currently a graduate research assistant at Los Alamos National Laboratory. While I’ve actually had the code finished for several months, I had to wait for approval before I could give it out. It got approved today, so it’s now officially live on GitHub.

Right now, my HS implementation only handles the continuous case. I’d like to add support for discrete variables, but it’s not clear how to handle pitch adjustment….

If you find this useful, drop me a note!

Python

ZIP codes shouldn’t be represented as integers!



This is just a quick post to point out that ZIP codes should never be stored or represented as integers. ZIP codes should always be stored and represented as strings. Take the ZIP code 07755 (Oakhurst, NJ), for example. Any reasonable parsing algorithm will correct read in 07755, but 07755 will be immediately converted to 7755 as leading zeros mean nothing in the context of integers. The problem doesn’t become apparent until it becomes time to output the ZIP code (for example, in a KML file). 7755 is not a valid ZIP code.

A common place where this problem pops up is in web apps where user ZIP codes are stored. In Django, for example, you may be tempted to add the following field to a model:

zip = models.IntegerField(max_length=5)

While this will certainly work, if you ever need to display user ZIP codes (e.g., to let a user view their current address), leading zeros won’t be displayed. Instead, the correct way to represent a ZIP code would be:

zip = models.CharField(max_length=5)

Now, leading zeros won’t get cut off.

Django, Python, SQL

How to Rename Columns in an SQLite Database



I just solved a problem where I needed to rename some columns in an SQLite3 database. Unfortunately, SQLite3′s ALTER TABLE command doesn’t have the ability to rename columns in tables. Most Google results offered solutions such as creating a new table, copying the data over, and then dropping the old table. While this is fine for probably 99% of SQLite3 databases, the table I’m dealing with has almost 3 billion rows (yes, I have an SQLite3 database with 3×109 rows). Recreating the table (specifically, the indices on the columns) would take too long. It took me about 24 hours to create the indices on a supercomputer with 512gb of RAM, and I didn’t want to go through that again.

I stumbled on two separate posts that seemed to offer solutions, and I got it working using them. I just wanted to write up this blog entry to clarify a few things and to show this in action.

Backup your database! Seriously, back it up. The first time I tried this, I corrupted mine because I forgot to update the corresponding index. Were it not for the backup, I’d be spending another several days recreating it from scratch.

I’m on a *nix box, so I’m interacting with the database using sqlite3 from the command line. I think this is simplest and safest because you can double-check your progress as you go before you commit the changes to the database. As an example, I’ll show you the table I was working with:

CREATE TABLE people (
	id INTEGER PRIMARY KEY,
	state_id INTEGER NOT NULL,
	county INTEGER NOT NULL,
	age INTEGER NOT NULL,
	gender TEXT NOT NULL, -- M or F
	income INTEGER NOT NULL
);
CREATE INDEX people_county_index ON people(county);

What I realized is that I incorrectly named the county column. It should’ve been county_id. In order to change the name of the column, I must change the index as well. Here is how it’s done:

1
2
3
4
5
6
7
8
9
10
11
12
13

BEGIN; --start a transaction; this ensures that your changes won't be written until you COMMIT
.schema --this will show you your current schema
PRAGMA writable_schema=1; --this enables you to edit the schema
SELECT * FROM sqlite_master; --show the raw data SQLite saves related to your schema
UPDATE sqlite_master SET SQL=REPLACE(SQL, 'county', 'county_id') WHERE name='people'; --simple string replace
.schema --verify CREATE TABLE is correct
UPDATE sqlite_master SET SQL='CREATE INDEX people_county_id_index on people(county_id)' WHERE name='people_county_index'; --update index SQL
UPDATE sqlite_master SET name='people_county_id_index' WHERE name='people_county_index'; --update index name
.schema --verify index is correct
SELECT * FROM sqlite_master; --double-check all raw data once more
PRAGMA writable_schema=0; --disable editing the schema
COMMIT; --save the changes to the database
.exit

After you verify that things look right, run ANALYZE to update the query planner’s statistics. This hack seemed to mess up the query planner for my database, but ANALYZE fixed it. If you’re still paranoid, run PRAGMA integrity_check; after ANALYZE.

That’s all there is to it. sqlite_master is just a regular SQL table, so you can modify it like any other. Note that the call to replace(x, y, z) will replace all occurrences of y with z in the string x. This means that if “county” existed anywhere else in the CREATE TABLE people statement, it would be replaced. As a result, be careful when using replace().

Also, if anyone has any questions regarding big/huge/gigantic SQLite3 databases, feel free to ask. Mine is 280gb and contains about 3.5 billion records split between 4 tables. Performance is quite good, but it took quite a bit of work to get there, and there were a lot of hard lessons learned along the way.

SQL

SQL Query for Finding ZCTAs Present in Multiple States



Using the previously described 2010 US Census SQLite database I created, there are a whole slew of neat things that can be learned fairly easily. For example, I was curious which ZCTAs are contained in more than one state. Hammerite from Something Awful created a really nice SQL query for solving this problem in this post.

1
2
3
4
5
6
7
8
9
10

SELECT DISTINCT
	zctas.zcta, states.state
FROM
	states_zctas
	JOIN states_zctas AS states_zctas_different ON
		states_zctas.zcta_id = states_zctas_different.zcta_id AND
		states_zctas.state_id <> states_zctas_different.state_id
	JOIN zctas ON states_zctas.zcta_id = zctas.id
	JOIN states ON states_zctas.state_id = states.id
ORDER BY zctas.zcta ASC;

The results look like this:

zcta state
02861 Rhode Island
02861 Massachusetts
03579 New Hampshire
03579 Maine
20135 West Virginia
20135 Virginia
21912 Maryland
21912 Delaware
24604 West Virginia
24604 Virginia
30165 Georgia
30165 Alabama

So, as it turns out, there are 103 ZCTAs that are present in more than 1 state. Unsurprisingly, there’s no ZCTA present in more than two states.

GIS, SQL

Update to 2010 Census SQLite Population Database



About a week ago, I posted an SQLite database containing 2010 US Census population data. However, I discovered a slight problem recently and am releasing an update today. The problem is that I made the assumption that there is a one-to-many relationship between states and ZCTAs. In other words, I made the assumption that states contain many ZCTAs. While this relationship certainly exists, it’s actually too simple. The real relationship is many-to-many. There are a few instances of ZCTAs that are actually contained in multiple states (the ZCTA 30165, for example, is located in both Georgia and Alabama).

This updated schema accounts for this many-to-many relationship:

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
175
176
177
178
179
180
181
182
183

CREATE TABLE states (
	id INTEGER NOT NULL,
	state TEXT NOT NULL,
	centroid_longitude REAL NOT NULL,
	centroid_latitude REAL NOT NULL,
	population_total INTEGER NOT NULL,
	population_male_total INTEGER NOT NULL,
	population_male_lt5 INTEGER NOT NULL,
	population_male_5to9 INTEGER NOT NULL,
	population_male_10to14 INTEGER NOT NULL,
	population_male_15to17 INTEGER NOT NULL,
	population_male_18to19 INTEGER NOT NULL,
	population_male_20 INTEGER NOT NULL,
	population_male_21 INTEGER NOT NULL,
	population_male_22to24 INTEGER NOT NULL,
	population_male_25to29 INTEGER NOT NULL,
	population_male_30to34 INTEGER NOT NULL,
	population_male_35to39 INTEGER NOT NULL,
	population_male_40to44 INTEGER NOT NULL,
	population_male_45to49 INTEGER NOT NULL,
	population_male_50to54 INTEGER NOT NULL,
	population_male_55to59 INTEGER NOT NULL,
	population_male_60to61 INTEGER NOT NULL,
	population_male_62to64 INTEGER NOT NULL,
	population_male_65to66 INTEGER NOT NULL,
	population_male_67to69 INTEGER NOT NULL,
	population_male_70to74 INTEGER NOT NULL,
	population_male_75to79 INTEGER NOT NULL,
	population_male_80to84 INTEGER NOT NULL,
	population_male_ge85 INTEGER NOT NULL,
	population_female_total INTEGER NOT NULL,
	population_female_lt5 INTEGER NOT NULL,
	population_female_5to9 INTEGER NOT NULL,
	population_female_10to14 INTEGER NOT NULL,
	population_female_15to17 INTEGER NOT NULL,
	population_female_18to19 INTEGER NOT NULL,
	population_female_20 INTEGER NOT NULL,
	population_female_21 INTEGER NOT NULL,
	population_female_22to24 INTEGER NOT NULL,
	population_female_25to29 INTEGER NOT NULL,
	population_female_30to34 INTEGER NOT NULL,
	population_female_35to39 INTEGER NOT NULL,
	population_female_40to44 INTEGER NOT NULL,
	population_female_45to49 INTEGER NOT NULL,
	population_female_50to54 INTEGER NOT NULL,
	population_female_55to59 INTEGER NOT NULL,
	population_female_60to61 INTEGER NOT NULL,
	population_female_62to64 INTEGER NOT NULL,
	population_female_65to66 INTEGER NOT NULL,
	population_female_67to69 INTEGER NOT NULL,
	population_female_70to74 INTEGER NOT NULL,
	population_female_75to79 INTEGER NOT NULL,
	population_female_80to84 INTEGER NOT NULL,
	population_female_ge85 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,
	population_total INTEGER NOT NULL,
	population_male_total INTEGER NOT NULL,
	population_male_lt5 INTEGER NOT NULL,
	population_male_5to9 INTEGER NOT NULL,
	population_male_10to14 INTEGER NOT NULL,
	population_male_15to17 INTEGER NOT NULL,
	population_male_18to19 INTEGER NOT NULL,
	population_male_20 INTEGER NOT NULL,
	population_male_21 INTEGER NOT NULL,
	population_male_22to24 INTEGER NOT NULL,
	population_male_25to29 INTEGER NOT NULL,
	population_male_30to34 INTEGER NOT NULL,
	population_male_35to39 INTEGER NOT NULL,
	population_male_40to44 INTEGER NOT NULL,
	population_male_45to49 INTEGER NOT NULL,
	population_male_50to54 INTEGER NOT NULL,
	population_male_55to59 INTEGER NOT NULL,
	population_male_60to61 INTEGER NOT NULL,
	population_male_62to64 INTEGER NOT NULL,
	population_male_65to66 INTEGER NOT NULL,
	population_male_67to69 INTEGER NOT NULL,
	population_male_70to74 INTEGER NOT NULL,
	population_male_75to79 INTEGER NOT NULL,
	population_male_80to84 INTEGER NOT NULL,
	population_male_ge85 INTEGER NOT NULL,
	population_female_total INTEGER NOT NULL,
	population_female_lt5 INTEGER NOT NULL,
	population_female_5to9 INTEGER NOT NULL,
	population_female_10to14 INTEGER NOT NULL,
	population_female_15to17 INTEGER NOT NULL,
	population_female_18to19 INTEGER NOT NULL,
	population_female_20 INTEGER NOT NULL,
	population_female_21 INTEGER NOT NULL,
	population_female_22to24 INTEGER NOT NULL,
	population_female_25to29 INTEGER NOT NULL,
	population_female_30to34 INTEGER NOT NULL,
	population_female_35to39 INTEGER NOT NULL,
	population_female_40to44 INTEGER NOT NULL,
	population_female_45to49 INTEGER NOT NULL,
	population_female_50to54 INTEGER NOT NULL,
	population_female_55to59 INTEGER NOT NULL,
	population_female_60to61 INTEGER NOT NULL,
	population_female_62to64 INTEGER NOT NULL,
	population_female_65to66 INTEGER NOT NULL,
	population_female_67to69 INTEGER NOT NULL,
	population_female_70to74 INTEGER NOT NULL,
	population_female_75to79 INTEGER NOT NULL,
	population_female_80to84 INTEGER NOT NULL,
	population_female_ge85 INTEGER NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (state_id) REFERENCES states(id)
);
 
CREATE TABLE zctas (
	id INTEGER NOT NULL,
	zcta TEXT NOT NULL,
	centroid_longitude REAL NOT NULL,
	centroid_latitude REAL NOT NULL,
	population_total INTEGER NOT NULL,
	population_male_total INTEGER NOT NULL,
	population_male_lt5 INTEGER NOT NULL,
	population_male_5to9 INTEGER NOT NULL,
	population_male_10to14 INTEGER NOT NULL,
	population_male_15to17 INTEGER NOT NULL,
	population_male_18to19 INTEGER NOT NULL,
	population_male_20 INTEGER NOT NULL,
	population_male_21 INTEGER NOT NULL,
	population_male_22to24 INTEGER NOT NULL,
	population_male_25to29 INTEGER NOT NULL,
	population_male_30to34 INTEGER NOT NULL,
	population_male_35to39 INTEGER NOT NULL,
	population_male_40to44 INTEGER NOT NULL,
	population_male_45to49 INTEGER NOT NULL,
	population_male_50to54 INTEGER NOT NULL,
	population_male_55to59 INTEGER NOT NULL,
	population_male_60to61 INTEGER NOT NULL,
	population_male_62to64 INTEGER NOT NULL,
	population_male_65to66 INTEGER NOT NULL,
	population_male_67to69 INTEGER NOT NULL,
	population_male_70to74 INTEGER NOT NULL,
	population_male_75to79 INTEGER NOT NULL,
	population_male_80to84 INTEGER NOT NULL,
	population_male_ge85 INTEGER NOT NULL,
	population_female_total INTEGER NOT NULL,
	population_female_lt5 INTEGER NOT NULL,
	population_female_5to9 INTEGER NOT NULL,
	population_female_10to14 INTEGER NOT NULL,
	population_female_15to17 INTEGER NOT NULL,
	population_female_18to19 INTEGER NOT NULL,
	population_female_20 INTEGER NOT NULL,
	population_female_21 INTEGER NOT NULL,
	population_female_22to24 INTEGER NOT NULL,
	population_female_25to29 INTEGER NOT NULL,
	population_female_30to34 INTEGER NOT NULL,
	population_female_35to39 INTEGER NOT NULL,
	population_female_40to44 INTEGER NOT NULL,
	population_female_45to49 INTEGER NOT NULL,
	population_female_50to54 INTEGER NOT NULL,
	population_female_55to59 INTEGER NOT NULL,
	population_female_60to61 INTEGER NOT NULL,
	population_female_62to64 INTEGER NOT NULL,
	population_female_65to66 INTEGER NOT NULL,
	population_female_67to69 INTEGER NOT NULL,
	population_female_70to74 INTEGER NOT NULL,
	population_female_75to79 INTEGER NOT NULL,
	population_female_80to84 INTEGER NOT NULL,
	population_female_ge85 INTEGER NOT NULL,
	PRIMARY KEY (id)
);
 
CREATE TABLE states_zctas (
	state_id INTEGER NOT NULL,
	zcta_id INTEGER NOT NULL,
	PRIMARY KEY (state_id, zcta_id),
	FOREIGN KEY (state_id) REFERENCES states(id),
	FOREIGN KEY (zcta_id) REFERENCES zctas(id)
);
 
-- need an index on (state_id, zcta_id) and (zcta_id, state_id)
CREATE INDEX states_zctas_index2 ON states_zctas(zcta_id, state_id);

I haven’t changed a lot, but a few things have changed. First, note the states_zctas table which now accounts for the many-to-many relationship between states and ZCTAs. I’ve also renamed the population fields in the tables. *_population is now population_* for clarity reasons.

Accessing data is a little trickier as an INNER JOIN is required. For example, to access the total population for each ZCTA in Iowa, this query does the trick:

1
2
3
4
5

SELECT zctas.zcta, zctas.population_total
FROM zctas, states
INNER JOIN states_zctas
	ON states_zctas.zcta_id=zctas.id AND states_zctas.state_id=states.id
WHERE states.state="Iowa";

Download: 2010CensusPopulation.7z (size: 3mb)

GIS, SQL

Note to self: Mercurial doesn’t like big files



I recently decided to try Mercurial for version control. I’m most familiar with Subversion, but I like the idea of a distributed version control system. I tried Git first, but my desktop runs Windows 7, and the Windows interface for Git (TortoiseGit) is a little wonky compared to its SVN brother (TortoiseSVN). I stumbled onto Mercurial as an alternative to Git, and I love it. The Windows interface (TortoiseHg) is great; I actually like it better than TortoiseSVN!

Anyway, my initial repo size was 3.8gb. I pushed it, and after several hours of uploading, I got this error message:

1
2
3
4
5
6

searching for changes
remote: adding changesets
remote: adding manifests
remote: adding file changes
abort: unexpected response: empty string
[command returned code 255 Mon Dec 12 07:19:00 2011]

I figured something happened during the transfer, so I wiped the repo and tried again but got the same error message. After some Google-fu attempts, I discovered that this error doesn’t seem very popular. I figured it might just be choking on the repo size, so I went through and deleted a bunch of data I didn’t need. I got it down to 1.9gb. I tried again and got the same error message.

At this point, I was starting to get pissed. Pushing a repo that large on a connection with 2mbit upload takes a while, and this was wasting a bunch of time (and bandwidth). I decided to try breaking down the push into several smaller pushes (each around 400-500mb). This worked for the first several pushes, but by push 4, I got the same error. Looking at the files I was pushing, I saw that one of the files was a 500mb ZIP file. I figured this might be the issue, and since I didn’t actually need the file, I decided to get rid of it and try again. I wiped the repo and tried once more to push the entire repo in one go. Voilà, it worked!

So what was the problem? After looking around, I stumbled onto http://mercurial.selenic.com/wiki/HandlingLargeFiles which describes how Mercurial handles large files. Specifically, this sentence described my issue:

all files are currently handled as single Python strings in memory

Well of course! I use Linode for hosting, and I have the Linode 512 option. Since Mercurial requires memory proportional the the size of the file it’s processing, I ran out of memory (both physical and swap). I’m certain a server with more memory could’ve handled the troublesome 500mb ZIP file with ease, but mine couldn’t.

So the moral of the story is this: Mercurial is awesome as long as you recognize its memory limits when it comes to large files. If you get the “empty string” error 255 on a push, make sure you have enough memory to handle the largest files in your repo.

Programming

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

logrotate: Use it!



I was digging through my virtual hosts looking at log files and noticed that a few of them had pretty massive access logs. One of the more popular sites I run for some friends, American K-Pop Fans, had an access log of 363mb, and I’ve only been running the site for a few weeks! That obviously wasn’t going to work, so I started looking up how to manage log files. I noticed that Apache seemed to do an awesome job of keeping logs organized in /var/log/apache2/ and figured I should be able to model my log cleanliness after them.

After some Googling, I stumbled onto rotatelogs. After fumbling with it, although it’s pretty cool, I discovered that this wasn’t quite what I wanted. I did some more Googling and discovered logrotate, a program built into Linux for managing large amounts of logs. The two almost identical names confused me at first, but the difference became clear pretty fast.

rotatelogs is a really simple program for automatically breaking log files apart when Apache adds an entry. It does this by piping the log file entry through the program which then decides if it needs to create a new log file or can use the existing one. You can choose when to create a new log file based on time or log size. logrotate, on the other hand, is a Linux command-line utility which runs as a cron job every day. It runs all scripts in /etc/logrotate.d/. Looking in that directory, there’s an apache2 script which keeps Apache’s log files nice and tidy. There are also a variety of others, depending on what’s installed.

Both Linode’s logrotate article and Slicehost’s logrotate article helped me setup logrotate for my virtualhosts. Here’s what mine looks like:

1
2
3
4
5
6
7
8
9
10

/srv/www/*/logs/*.log {
	rotate 14
	daily
	compress
	delaycompress
	sharedscripts
	postrotate
		/usr/sbin/apache2ctl graceful > /dev/null
	endscript
}

The idea is pretty simple. Line by line:

  1. I list all of my virtual host log paths. All of my virtual hosts follow the same directory structure, so I can get away with wildcard usage like this.
  2. I tell it that I want to keep 14 days of previous log files.
  3. I tell it that I want it to run daily.
  4. I tell it that I want old log files compressed to save space.
  5. I tell it that it should delay compressing the most recent archived log file.
  6. I tell it that all of the virtual hosts listed on line 1 should be processed before the following script runs.
  7. I tell it to restart Apache gracefully (no open connections will be closed, and old log files won’t be closed immediately). The reason we use delaycompress is because we don’t want to compress the most recent log file until we’re sure Apache is done with it.

That’s it! This simple script maintains all my log files for me so that I don’t have to worry about them growing out of control.

Linux, Website
← Older Entries