Plotting Data to Census Tracts

[JSprouls]

I work at a small, grassroots non-profit and I am trying to make some data-driven tree planting decisions for Nashville, TN. I am trying to identify food deserts to pilot some edible food forestry projects, and wanted to take advantage of the data from the USDA Food Access Research Atlas

In the data download page it says all of data included is aggregated into an Excel spreadsheet for easy download and includes:

Note for GIS users: The Atlas is based on 2010 census tract polygons. To use the underlying Atlas data in a GIS, the data from this spreadsheet needs to be joined to a census tract boundary file. With ESRI software, users should have access to the tract layer on ESRI's "Data and Maps" data distribution.

When I open the attribute table for the atlas data, the field CensusTract is a 10-digit code, whereas the census tract layer (downloaded from US Census Bureau Cartographic Boundary Files website ) has a GEOID for census tract that is 11-digits long (based on my research, that is how census tracts are designated ubiquitously).

Does anyone have any suggestions how to use this dataset, or am I missing something obvious?

PS: There is a previous post  that asks a similar question a decade ago, but the answer isn't helpful to me.

[jcarlson]

Sounds like a victim of Excel stripping the leading zero from a field. The datasets line up over here, if you assume there should be a "0" in front of the rest of the GEOID.

Over here, I am using LibreOffice Calc and QGIS, admittedly, but I can load the spreadsheet and boundary features and join them without issue.

[marksm_macomb]

I just took a really quick look at the data, and it looks like the tract ID is different lengths depending on the state! I think if you look at only Tennessee, you will find that the IDs in both the excel table and the tract polygon table will be 11 digits. 

When I opened a tract polygon layer from Esri, the first record is a tract in Texas, with an 11 digit ID, but when I opened the table from USDA, the first record is from Alabama with a 10 digit ID. So based on first glace they might not seem to match. I would go ahead and try the join anyways to see what happens 🙂 

[JSprouls]

You're right - and I cleaned the data in USDA Food Access Research Atlas spreadsheet and kept only the features with the Tracts I need to work with.

Now when I perform the Add Join, the feature runs and returns null values in each field from the CSV. Any idea what may be causing this? The data in the input join field isn't indexed, if that matters...

The only other ideas I have is that the Census Tracts from the USDA Food Access Research Atlas CSV are from 2010 and I am trying to join to more recent data - does that matter?

[JSprouls]

Also: the Tract info in the CSV is 'double' whereas the Tract ID is 'text' in the shapefile.

[jcarlson]

CSVs and other spreadsheets don't have field types, they just get interpreted by the program that loads them. If they see numbers, they assign a type of "number", and leading zerods get dropped.

[jcarlson]

I stand somewhat corrected. The CSV they provide has the 0 trimmed off already! Sounds like a bad export on their end. The XLSX, on the other hand, does have the leading 0 on the tract ID field.

Excel file:

Raw text of the CSV:

[JSprouls]

From what previous user  marksm_macomb posted; the TN data has the correct # of digits but the join returns null values. I offered a few ideas of what I guess may be causing the issues but not sure.

[jcarlson]

The join works just fine. Here's TN, symbolized by one of the Food Atlas fields:

And in Pro, too: