DataBaseZone.com
Conrad Muller
Juneau, Alaska

Email: conrad at
databasezone
dot com

Obtaining Census Bureau data directly from the Census Bureau APIs


Data now available from the Census Bureau through the Web API

Returning the data requires a carefully crafted URL that includes the database table and column information of the data you want, plus a user "KEY" that is free online from the Census API site: http://www.census.gov/developers/.

I have included Web pages with dozens of links to get you started, but you will need to add your own Census API Key to the URLs in the pages to make them work. Just do a replace all on "YourKeyGoesHere" with your key.

All of the demo pages use the state code 02 for Alaska. You will probably want to change the code to a state that is relevent to you.

You can retrieve up to 50 data columns at a time with a single URL.

How to retrieve and import the American Community Survey (ACS) data
 

Web pages that provide the required HTML query strings as links can be used to retrieve the data.

I have made a set of Web pages available that cover some basic ACS data and some Census data. You should open the pages to see what I have made available. You can save the data in Internet Explorer to a file, but you cannot see it in a page.

You MUST obtain a free user "KEY" online from the Census API site: http://www.census.gov/developers/, and add it to these pages to actually retrieve the data.

Copy these HTML documents to your computer and add your key, then run them in a browser to return the data in JSON format.

In FireFox or Chrome the data will open in a new page, and you can copy it into a spreadsheet to clean it up and analyze it, or you can save it as a file and consume it as JSON.

Internet Explorer will not open the data in a new Web page due to the way IE treats returned JSON data, however, IE will let you save the data to a file.

Retrieving the data

Open 2011ACS5_Links&MOE.html and it looks like this:

 

Each link has a list of the data columns it returns below it.

To see the actual request URL, look at the source code. For a general idea of how the URLs work, I have included two below.

I know the image above is hard to read, so I have provided this detail of the 2011 ACS 5 year Web page.

As you can see, there are links followed by descriptions of the data returned by choosing the link.

The URL for the Population16AndOver link on this Web page looks like this:

http://api.census.gov/data/2011/acs5?key= b48301d897146e8f8efd9bef3c6eb1fcb864cf&get=B23001_001E,NAME&for=place:*&in=state:02

The key in these URLs is for demonstration and is not valid. Please get your own key and replace the invalid key with your own. You will also need to replace the state code for Alaska (02) with the state code of your state.

If you click on the Population16AndOver link, you get a JSON format data dump in a new browser tab that looks like this:

[["B23001_001E","NAME","state","place"],
["92","Adak city","02","00065"],
["78","Akhiok city","02","00650"],
["403","Akiachak CDP","02","00760"],
["217","Akiak city","02","00870"],
["939","Akutan city","02","01090"],
["339","Alakanuk city","02","01200"],
["5","Alatna CDP","02","01305"],
["24","Alcan Border CDP","02","01390"],
["198","Aleknagik city","02","01420"],
["19","Aleneva CDP","02","01560"],
["59","Allakaket city","02","01860"],
["176","Ambler city","02","01970"],
["289","Anaktuvuk Pass city","02","02080"],

In this case, the data in each row is: Population over 16, Census Place, State code (02 for Alaska), FIPS code.

The first row is the header.

         B23001_001E is the Census designation for the table and column for Population 16 and over.

         The second column is the NAME of the City or CDP.

         The third column is State code (02 for Alaska).

         The final column is Place designated by its FIPS code.

The URL for the Unemployed link looks like this:

http://api.census.gov/data/2011/acs5?key=b48301d897146e8f8efd9bef3c6eb1fcb864cf&get=B23001_008E,B23001_015E,B23001_022E,
B23001_029E,B23001_036E,B23001_043E,B23001_050E,B23001_057E,B23001_064E,B23001_071E,B23001_076E,B23001_081E,B23001_086E,
B23001_094E,B23001_101E,B23001_108E,B23001_115E,B23001_122E,B23001_129E,B23001_136E,B23001_143E,B23001_150E,B23001_157E,
B23001_162E,B23001_167E,B23001_172E,NAME&for=place:*&in=state:02

Using the 2011_ACS_API_Links.html Web page

There are two columns of links on the 2015_ACS_API_Links.html Web page. The links in the left column retrieve data for Places, and the links in the right column retrieve data for Counties.

         Place is Census code for a City or for a Census Designated Place (CDP). If you choose a link in the left hand column, you will return data for Cities or for CDPs.

         County is Census code for Boroughs and Census Areas. If you choose a link in the right hand column, you will return data for Boroughs and Census Areas.

        Data from both columns needs to be retrieved and combined for complete coverage.

There should be about 255 places and 29 counties in Alaska. Most states will have a lot more. The Census Bureau combines and splits areas from time to time, so the count could change a bit.


My (Conrad Muller's) work on this page is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 2.5 License.

Home | Resume | Project Portfolio | Writings | Developer Resources | Contact Form