TableIDs in the ACS 2015-2019 metadata file named 2019_DataProductList.xlsx

Hello all!

This is my first post, and I am seeking some guidance

The ACS 2015-2019 metadata file named 2019_DataProductList.xlsx states that there are 57 separate TableIDs for subject "X_26" (GROUP QUARTERS)

But how do I know in advance at what geographic region this information is avaialble? I did a test with "census tracts" for the state of Hawaii (State FIPS 15)

But if one actually downloads the pre-joined TIGER/Line shapefiles in geodatabase and shapefile format with “Selected Demographic and Economic Data” for census tracts in the state of Hawaii for the 2015-2019 American Community Survey (ACS) 5-Year Estimates there is only ONE TableID (B26001) with only one field/variable called B26001e (and the associated measurement error B26001m)!!

https://www.census.gov/geographies/mapping-files/time-series/geo/tiger-data.html

1. So I am so curious what happened to all other 56 TableIDs for the state of Hawaii at the census tract level in the 2015-2019 American Community Survey (ACS) 5-Year Estimates

2. How do I know in advance what TableIDs are provided for each subject area at the census tract level, census subdivision (CSD), county and state level  in Hawaii?

3 There appear to be 29 subject areas in the 2019 American Community Survey (ACS) 5-Year Estimates. Fortunately 23 of the 29 subject areas had all of the TableIDs advertised in the 2019_DataProductList.xlsx metadata

But what about the remaining six subject areas?  How does one know how many Table IDs are actually in the “Selected Demographic and Economic Data” for the state of Hawaii for the 2015-2019 American Community Survey (ACS) 5-Year Estimates for each Table ID at each level of analysis

I manually checked each of the table IDs in each of the 29 subject areas for the census tract data in Hawaii, but surely there is a summary list somewhere?

cheers, Jason

  •  I teach GIS and my students use the twenty-nine topic/subject areas found in the 2015-2019 Selected Demographic and Economic Data for ArcGIS Pro 

    It is easy to find the availability of data tables for the 5-year and 1-year ACS releases as you mentioned

    However, my students are only using the prejoined "Selected Demographic and Economic Data” (Arcgis Pro geodatabase files with 29 standalone subject tables)i for the 2015-2019 American Community Survey (ACS) 5-Year Estimate

    How would we find the metadata for that?

    Consider subject/topic area 26 (Group Quarters):  there is only ONE TableID (B26001) found in the Selected Demographic and Economic Data” but there are 57 TableIDs found in the regular census

    So I had to create my own metadata tables for this so that our students would know what they are downloading

  • Hey Jason - here is the Census Bureau documentation on the pre-joined geodatabases: https://www.census.gov/programs-surveys/geography/technical-documentation/records-layout/tiger-line-demo-record-layouts.html 

    As for the SQL files - that could be a permission issue. I had a research assistant use these files and he said he had to save them to his root C drive to access them and run SSMS as admin to import them. Also please note that the data only includes the estimates and does not have the margin of error (MOE) tables.

    I have to ask, though, is it possible for you to modify your curriculum to work with only the selected demographic data provided by the Census Bureau?  


  • Also - I assume you are in Hawaii - if so,  have you reached out to someone in the DBEDT office for assistance? Perhaps they can better guide you to what you are looking for. I feel I've only added confusion. 

    Here is some information from them:

    https://census.hawaii.gov/home/geography/

    Statewide GIS office:

    http://planning.hawaii.gov/gis/

    Also - a lot of folks connect to the census api to get data, and that may be better, I'm just not adept at using those. You might want to check out https://github.com/hrecht/censusapi

  • Dear JamiRae

    Yes, in Hawaii and we are aware of those sites and agencies.

    1. You have already provided valuable help!

    2.  Our assignment deals with the 151 Census Designated places in Hawaii. On this site I could not find the 5 year ACS 2015-2019 place tables broken down into state TIGER/Line with Selected Demographic and Economic Data (census.gov) (although there was an option to select state for Block Group, Census Tract and County Subdivision)

    Therefore, I downloaded the Place geodatabase for the entire country and am extracting out the Hawaii records

     ACS_2019_5YR_PLACE.gdb.zip 02-Jun-2021 06:56 2.9G

    Index of /geo/tiger/TIGER_DP/2019ACS (census.gov)

    3. All of the above links deal seem to contain 5 year ACS 2015-2019 data (exclusively). Is there an area to download the 1 year ACS 2019 data as a zip file, rather than use data.census.gov and download them one at table at a time?

    thank you so much again, Jason

    p.s. Since I am using the latest Microsoft SQL tool and your dataset has been tested, theoretically there should be a solution?

    I would be very interested in the future if anyone would be able to help me for a few moments by zoom? to help me access your files in Microsoft SQL (restore your BAK file, etc). We spent time with our tech folks but have the issues you mentioned Even just opening the tiny Maine dataset would be great. I am trying to learn the SQL approach

  • I also did check the documentation on the 5 year ACS 2015-2019 pre-joined geodatabases. I believe there are 29 main subject tables for the following geographies: Tract, Place, Block, County SubDivision etc

    Also when I downloaded each of the 29 subject tables as CSV files for the 5 year ACS 2015-2019 they seem to have EXACTLY the same number of columns in excel (and exactly the same fields) in the Tract, Place, Block, County SubDivision data

    Is that what you expected? 

    cheers, jason

    Hey Jason - here is the Census Bureau documentation on the pre-joined geodatabases: https://www.census.gov/programs-surveys/geography/technical-documentation/records-layout/tiger-line-demo-record-layouts.html