TIGER/ACS Blockgroup Join in PostGIS

[Craig Miller]

Looking for some assistance.

I’m working on a side-project that works with US Census Data.  I have downloaded and imported into PostGIS both the TIGER data and the ACS Block Group data for 2022 (actually all years since 2013 if it matters).  I’m attempting to join an ACS table to the geometry in the TIGER data by GEOID, but not having any luck.

One thing I noticed is that the TIGER data has a GEOID that is 12 chars long, vs the ACS tabular data which has a GEOID that is 40 chars long.  I’ve also seen brief mention of the “Geoheader” file, but no clear documentation on how this relates to either the TIGER data or the ACS data.

I’m working strictly in PostGIS on this project.

If anyone has suggestions on a helpful tutorial or even better has some sample SQL to do the join it would definitely be appreciated!

Thanks!

—Craig

🚀 nextidea.io

[Benjamin Weiser]

With census data it’s a tiered ID structure. So if you want a census block, track, incorporated or unincorporated area, state etc, you need to match it with that ID.

See this and and scroll down to Hierarchy Diagrams:

https://www.census.gov/programs-surveys/geography/guidance/geo-identifiers.html

If you are using Tiger data, you need to find out which hierarchy layer you are using, then join based on that.

Lots of cool things you can do with census and tiger data. Feel free to message me if you need help.

~Benjamin Weiser

--
You received this message because you are subscribed to the Google Groups "Cascadia Users of Geospatial Open Source" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cugos+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/cugos/5AF51C0A-0E70-4F0D-B042-B57474F0917D%40nextidea.io.