Hello, I want to know how I can access a table or set of tables at state - county - census track - block group level detail (and census blocks if possible) with information related to basic census stats such as total population, sex, age, income, (and retail sales if possible). My purpose is to ingest this information to BigQuery for different business and geographical analysis, so I would also want to know if it's possible to get the latitude and longitude at this level of detail.
I know having this level of detail would result in an insanely amount of data and it will probably require pulling the data from different tables and ingest it in many partitions, so in case it's not feasible to get the information I need in a decent amount of csv/excel files, and API requests are the best bet, I would like to know if there is a suggested methodology to run this heavy ETL, such as looping through geography codes or using * wildcard to select all codes for some specific geographies (that ones that apply), or a combination of both methodologies or any other additional approach.
My best guess is looping through geographic codes but I don't know the code ranges, so in case you guys suggest going to the API request with looping codes I would highly appreciate if you can share where to find that total code list for states, counties, census tracks, block groups and census blocks.
Thank you!
Ricardo.
Passing on a former colleague’s advice for familiarizing yourself with what’s available— look at the list of tables and then you’ll know the table ID which helps (no retail sales, sorry)…
The U.S. Census Bureau provides a wide range of demographic, economic, and housing data at the state, county, census tract, and block group levels through the American Community Survey (ACS) and the Decennial Census.
To access the data at the state, county, census tract, and block group levels, you can use the American FactFinder tool, which is available on the U.S. Census Bureau website.
Here's how you can use the American FactFinder tool to access data at the state, county, census tract, and block group levels:
Go to the American FactFinder website (https://factfinder.census.gov/)
Select the "Advanced Search" option.
Select the desired data set (i.e. American Community Survey or Decennial Census)
Select the desired geographic level (i.e. state, county, census tract, or block group)
Select the desired data tables or variables.
Select the desired time period
Click on "View" to see the data.
The American FactFinder tool also allows you to create custom tables and maps, and to download the data in various formats.
Factfinder was nice. It’s been gone for years now. Replaced by data.census.gov
www.census.gov/.../transition-from-aff.html