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:
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.