On Monday we wrote a SQL for determining the most culturally diverse neighborhoods in Chicago as an exercise to demonstrate the potential and functionality of SQL in PGAdmin.
As per Kearns’ request here is the pdf
Census data is accessible in two forms on the US Census site: Tiger/Line Geographic Shapefiles and as Tabulated Data (CSV) from the census proper, including a range of demographic, housing, and economic data. Follow these steps to join these two data types to generate geolocated maps of this data in QGIS (for a slightly outdated, but very detailed step-by-step tutorial, go here):
- find and download the census regions Shapefiles you are interested in from the Tiger/Line site. Choose appropriate region and granularity – census blocks are the smallest geographical unit used by the US census. Make sure to download the tiger regions from the year that corresponds to the tabular data you are getting (i.e. 2010, 2000, etc.)
- find and download the Tabulated datasets you are interested in from the US census factfinder2 site. (Click on the “Advanced Search” tab; Click the “Geography” tab; Click the “Name” tab; Enter the Name of the Region you are interested in – “Wayne County, Michigan” for instance; In the “Geography Filters” Column pick the appropriate geographical unit – e.g. “block”. After all of this, it should give you a list of regions that meet the search criteria. Select the region(s) you want and press “Add”. Close the Geographies pop-up, and you should see all of the tabulated datasets for this region and geographical unit. Select the dataset you want to download and press “View.” Verify the data is what you want and Press “Download.” In the download dialogue box, check the “Data and Annotations in Separate Files” box and press “OK” to start the download. This will bring up one more dialogue window with a progress bar. Once the progress is complete, press “download” to finally download the Zip file.
- Follow this tutorial to prep and import the two files to QGIS and join them based on the appropriate geometryID column (e.g. the block ID #) – You may want to do some cleaning of the csv file to remove any unwanted columns and to ensure the geoID column data matches with the shapefile. Use the find/replace command in Excel (CTRL-F) to facilitate this work.
Tables: 2006-2010 5-year ACS http://www.census.gov/hhes/migration/data/acs/county_to_county_mig_2006_to_2010.html
Data are based on a sample and are subject to sampling variability. The degree of uncertainty for an estimate arising from sampling variability is represented through the use of a margin of error. The value shown here is the 90 percent margin of error. The margin of error can be interpreted roughly as providing a 90 percent probability that the interval defined by the estimate minus the margin of error and the estimate plus the margin of error (the lower and upper confidence bounds) contains the true value. In addition to sampling variability, the ACS estimates are subject to nonsampling error (for a discussion of nonsampling variability, see Accuracy of the Data). The effect of nonsampling error is not represented in these tables.
- County-to-County Migration Flows
- County-to-County Migration Flows by Sex
- County-to-County Migration Flows by Age
- County-to-County Migration Flows by Race
- County-to-County Migration Flows by Hispanic or Latino Origin
- County/MCD-to-County/MCD Migration Flows
- County/MCD-to-County/MCD Migration Flows by Sex
- County/MCD-to-County/MCD Migration Flows by Age
- County/MCD-to-County/MCD Migration Flows by Race
- County/MCD-to-County/MCD Migration Flows by Hispanic or Latino Origin
Sample size and data quality measures (including coverage rates, allocation rates, and response rates) can be found on the American Community Survey website in the Methodology section.
Estimates based on a sample of households over a 5-year period
Demographic profile from the 2010 Census:
Estimates based on a sample of households over a 5-year period: