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)…
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.
Thank you David, when I open the tiger file I see many files with weird extensions https://ibb.co/Jyt2rJb
Which one has the shape data? and how I can open it? Also, is it possible to have the state-county-track-block group details in a single file?
These files are all parts of a single shapefile. In your example, tl_2022_25_tract is the shapefile name, and the six items are pieces of it. The DBF is a database, the SHP contains the geometry, the PRJ is the projection data, and the others are various kinds of metadata.
You would use a GIS program to open a shapefile. ArcGIS, QGIS, etc.