1

I need census block group (CBG) shapefiles separately for each commuting zone (CZ) in the US (by the 2000 delineation, there were 709 of them).

I only found CBG shapefiles by state though, here.

By county-level FIPS, this can be linked to commuting zone delineations, e.g. from USDA ERS here.

So the logical steps would be the following:

  1. Append CBG shapefiles into one database, with county as an attribute.

  2. Join with CZ delineation by county FIPS.

  3. Export separate shapefiles for CBGs by CZs in the aggregate data.

I could not get this done in QGIS. Is it simple with PostGIS? How exactly?

1 Answer 1

1

You want to note the relationship between FIPS code in the Commuting zones and the GEOID in the Census Counties, then the Block Groups

For DeKalb County CZ, FIPS is 01049

For DeKalb County, the GEOID is 01049

In the Block Groups, features with GEOID starting with 01049 are all DeKalb County - similarily, the STATEFP is 01 and the COUNTYFP is 049

So what you need to do is create a new field in your Block Groups - OF TEXT TYPE, if not you lose the leading 0 and the join won't work - that either takes the left(GEOID, 5) of the Block Groups, or concatenates STATEFP and COUNTYFP - both cases return the same FIPS as the Commuting Zones

enter image description here

From there, you can use QGIS to join those fields together.

enter image description here

And the resultant attribute table:

enter image description here

In PostGIS, I would have skipped the creating of the new field and joined the two tables on CZ.FIPS = LEFT(BG.GEOID, 5)

6
  • Thanks, I could join the two sources in QGIS after generating the right keys, but if I filter the joined layers on a specific CV, the resulting virtual layer has an empty shape file when I try to save it. Let alone the fact that I'd need to filter 709 CZs manually. Or what am I missing in QGIS?
    – László
    Commented Feb 7, 2017 at 15:53
  • Not sure what you mean by 'joined layers on a specific CV' - if you joined the data on the two key fields, you should do a selection on the joined data and export from there... Commented Feb 7, 2017 at 15:58
  • Specific CZ, sorry. But have you tried the selection and exporting from there? Don't you get a virtual layer from the filter? Does it keep the geographies? I see all attributes, with the right field count, but no geography. Thus the shape file is also empty after the export.
    – László
    Commented Feb 7, 2017 at 16:11
  • @László let me try... Commented Feb 7, 2017 at 16:16
  • 1
    See my update - make sure the new JOIN field is of TEXT ... then you can do your selection / export and the data will be there... Commented Feb 7, 2017 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.