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)…
IPUMS NHGIS provides online and API access to nationwide data files at the block group and block level. You can find and select any set of available summary tables and download them all in one file (per source dataset). Block data are available only for decennial censuses, most recently the 2020 census, and only for a limited set of subjects, so you can't, for example, get income info at the block level. The U.S. Census Bureau collects information on income and many other characteristics through the ACS, and block groups are the lowest level for which ACS data are reported. NHGIS also has the ACS block group data, and you can get it in nationwide files for multiple tables without having to loop through geographies or merge tables together.I don't know of any data source for retail sales for small areas.For geographic coordinates for block groups, I recommend using the Census Bureau's centers of population. NHGIS also provides these centers as shapefiles. For blocks or other levels, if you get NHGIS tables for a decennial census (not ACS), the data file should include longitude and latitude for a central point within each area. NHGIS also provides nationwide shapefiles of block group polygons and state-extent shapefiles of block polygons.
Thanks Jonathan, your information has been very useful for what I really need
Jonathan Schroeder said:I don't know of any data source for retail sales for small areas.
Some states report taxable sales. At least, I know Missouri does, by city and Standard Industry Codes:
https://dor.mo.gov/public-reports/#pubtax
But I don't know of a central national source for such data.