Community

Problem joining Census Block datafile to TIGER shapefile in ArcMap

3740
2
10-30-2017 04:38 PM
JohnTrawick
by
New Contributor
‎10-30-2017 04:38 PM

Hello ... I don't know if this is the right place for the question, but here goes ... I'm using ArcMap 10.5.1 for a project I'm doing on behalf of a non-profit housing development organization, and I've been attempting to join a Census table to a TIGER shapefile, without luck. In this particular case, I'm attempting to join a table of data at the Census Block level to the appropriate corresponding TIGER shapefile, but I have repeatedly found that the "geoid" numbers fail to match owing to their length (15 numerical digits) and the related tendency of both the shapefile and csv data tables to convert the numbers into exponential versions. This problem only occurs at the Census Block level, perhaps because the Census Block Group geoid number is only 12 digits, versus 15 (without decimals) for Blocks. I've spent hours trying to bypass the obstacle, or to resolve it, with no luck.

0 Kudos
2 Replies
AdrianWelsh
by MVP Honored Contributor
MVP Honored Contributor
‎10-31-2017 10:36 PM

Hi John,

Could you essentially create a new field that will display only the first 12 digits of the 15 digit number in order to match up with the other 12 digit field? Would this make the two fields match up and joinable at that point?

0 Kudos
DougOetter
by
New Contributor III
‎03-07-2018 01:05 PM

We had the same problem, and I didn't find an adequate solution on the forum, but finally got it to work:

  • Select the table you want from the Census Bureau (factfinder.census.gov), at the Block level
  • Unzip the archive file, and Open the .CSV file in Excel
  • Delete the Annotated Header (Row 1)
  • Here's where the problem occurs- Both Excel and ArcMap read the 15-digit GEO.id2 as Double Precision Float, but they BOTH display the numbers in Exponential Format, even though we didn't ask for that.  If you Declare the Format in Excel, it will change back to 15-digit, and that will save in the .XLSX format, but ArcMap will still read it and display it as Exponential.  You can change the Field format (Custom, 0) for the Table Properties, and it will display correctly, BUT since the GEOID10 is a string value, you need to conduct the Join using String.  When you Add Field and use the Field Calculator, the string value still comes in as exponential. 
  • So, instead, Add Field in the feature polygon layer, and use the Field Calculator to put single quotes around the 15-digit string
    • = "'"+GEOID10+"'"  (there is a single quote inside both of those double quotes)\
    • The new String field looks like this: '130099707022052'
  • In Excel, do the same for the GEO.id2 field using the Text function Concatenate:
    • =CONCATENATE("'",TEXT(B3,0),"'")
    • Export the Excel file to .CSV or tab-delimited .TXT
  • The two string fields will now join
  • Repeat as necessary for additional tables

#census #block #attribute #join