I have a census blockgroup shapefile of my local tri-county area with the original fields and a numeric version of the Geographic ID field (data type is double), and I have a census blockgroup data/dbf table of poverty levels for the same geographic area, with a matching numeric Geographic ID field (exported from excel to gdb). When I join the shapefile to the table, there is a 100% match rate, no problems. When I join the table to the shapefile, only about 10% of the records match. For fun, I highlighted 1 row of the table and applied a join validation for table to shapefile. The validation said that 10% of the shapefile records matched even though there was only 1 record selected on the joining table. I need to join the table to the shapefile so I can display the poverty levels in a map. What is happening here? Why do these tables join 1 way perfectly, but not the reverse?

Table to shapefile fail: Table to Shapefile Join

Shapefile to table success: Shapefile to Table join

  • I suspect the problem is because of the Double data type. I would strongly recommend converting your GEOIDs to Text/String instead (or, if you really want them as numbers, as Long -- but be aware that numeric GEOIDs will lose their leading 0s in states with a single-digit state FIPS code).
    – nmpeterson
    Aug 1 at 17:42
  • A text based join results in only 71 matches. I've used this same method and double datatype for other census data (2018, 2017, 2016) with no issues...
    – Tiffany
    Aug 1 at 19:20
  • Double is an unusual field format for joining data as you might get some sort of rounding error even though your number looks like an integer. I would do as @nmpeterson suggests and convert them to Long which is a integer format.
    – Hornbydd
    Aug 1 at 20:48
  • Are you using the correct vintage of block group polygons to go with your data? The 2020 ACS and 2020 Census data use different block groups than earlier ACS/Census data. The Census Bureau updates the boundaries at the beginning of each decade. If you're unsure, I'd recommend trying the latest (2021) TIGER/Line shapefiles.
    – nmpeterson
    Aug 1 at 20:50
  • I have tried Long and text format to the same effect, and have ensured that I am using the same blockgroup year as the data (2019). I selected a blockgroup at random and tried to select by attribute in the data table for the same ID, but it did not work. However a manual search through the table showed that the ID does exist and is in the same Long integer format. So idk why the joins are still not working. Is there an alternative workflow to join these tables together so I can display the data?
    – Tiffany
    Aug 4 at 16:49


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.