Help with combining data within grids and buffers

Question

So basically, I've got employee data within quarter-mile grids for different states. So there is a grid covering the entire state, each grid 1/4 mile apart, with a employee data within each of those 1/4 squares. The data represents the entire 1/4 mile, it's not individual points.

I then also have 1 mile buffers around different cities throughout those states.

I need to determine how many employees are within each of those buffers. However, whenever I append the grids to the buffers, it counts the ENTIRE grid, even if a portion of it goes outside that buffer. I need it so it does not include any data outside of the buffer.

I have tried to use both the clip and intersect tool. Both tools did clip the grids spatially, but they did not change the data in terms of numbers of employees whatsoever.

Any ideas?

DanLee · Answer

Can you share some sample data, perhaps data of a small state?

The basic steps are (I might miss some details):

1. Intersect the employee polygons with the buffers. The output contains portions of employee polygons in the buffers.
2. Run Join Field to join the above output with the employee polygons to get the employee polygon shape_area field.
3. Add a field and than Calculate the field by dividing the intersected employee polygon shape_area by the original employee polygon shape_area. This gives you the ratio (or percentage) of the area. 
4. Add another field and calculate it by the employee number x the area ratio.
5. Use Join Field or Spatial Join tool to link/find where the buffers are related to the intersected employee polygons, and then transfer the calculated fields over.

Hope that gives you some lead. Sorry I don't have time to verify the steps.

AndrewRowlands · Answer

This is a tough one, because it is hard to determine how the employee numbers are distributed throughout the grid polygon (i.e. are they spread equally across the polygon, are should some areas of the polygon be weighted differently than others?). Off the top of my head, the only thing that comes to mind is creating a custom BDS layer in the Business Analyst Desktop extension. You would use the Custom Data Setup Tool and select the proper apportionment method. You could then create your trade areas of 1 mile buffers around your cities and use the Spatial Overlay (Append) tool to grab the values from the Grid. Then, depending on what apportionment settings you have set up in BA, you should see different numbers. The Advanced Apportionment Settings article discusses the different styles available.

Unfortunately, all of this will only be doable with Business Analyst desktop. That's not to say there may not be some other way to get a similar result using ArcGIS without any extensions...

Yeah, I've tried all of that, including creating a custom BDS layer and then doing the append with that. None of that has worked either.

Assuming the data is evenly distributed throughout each grid, is there a way I can calculate how much of each grid is within the 1 mile trade area?

Lucas Danzinger · Answer

This is a tough one, because it is hard to determine how the employee numbers are distributed throughout the grid polygon (i.e. are they spread equally across the polygon, are should some areas of the polygon be weighted differently than others?). Off the top of my head, the only thing that comes to mind is creating a custom BDS layer in the Business Analyst Desktop extension. You would use the Custom Data Setup Tool and select the proper apportionment method. You could then create your trade areas of 1 mile buffers around your cities and use the Spatial Overlay (Append) tool to grab the values from the Grid. Then, depending on what apportionment settings you have set up in BA, you should see different numbers. The Advanced Apportionment Settings article discusses the different styles available.

Unfortunately, all of this will only be doable with Business Analyst desktop. That's not to say there may not be some other way to get a similar result using ArcGIS without any extensions...