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.
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)…
Factfinder was nice. It’s been gone for years now. Replaced by data.census.gov
Generally the geoid (fips) codes can be found in the tiger shape files. They contain data fields with the geocodes and longitude and latitude for the "internal point" of the geographic polygon INTPTLAT INTPTLON .https://www.census.gov/geographies/mapping-files/time-series/geo/tiger-line-file.2022.html technical documentation https://www.census.gov/programs-surveys/geography/technical-documentation/complete-technical-documentation/tiger-geo-line.2022.html#list-tab-TN2BGQZWFO8ATUC9LB I don't believe that there is an API for the TIGER LINE files. They can be downloaded as a zip file from the Census website https://www2.census.gov/geo/tiger/TIGER2022/ for the most recent files (post 2020 census).
For example for Massachusetts census tracts: /https://www2.census.gov/geo/tiger/TIGER2022/TRACT/tl_2022_25_tract.zip (25 is the Massachusetts FIPS code).
The smallest geography is the "block group." A census tract might have 1-4 block groups or so.
Jose might find it easier to get internal point ("centroid") coordinates from the Bureau's gazetteer files. These are just flat files and don't require a GIS program to access.
We also have these at MCDC: https://mcdc.missouri.edu/cgi-bin/uexplore?/data/gazeteer