[GIS] Creating spatial-join which will calculate sum of values in certain field behind attribute in ArcMap

Question

When using the "Update Column" tool within MapInfo Pro, you have the ability to update a column in one table with the sum of values from a column in another table; joining them only where objects in the second table lie within objects from the first table. I've attempted to create an example to help me explain this…

Table 1 shows the total number of various sewer incidents reported, per day e.g. there were 3 blockages and 4 odours reported on 01/02/03.

The Geoplan displays this information visually and shows us the locations of these incidents within each of the three cities.

Table 2 is what I'm aiming to achieve. The sum of the total number of incidents reported for each separate category – based on their geographical location.

I know this is possible using MapInfo Pro – however, I'm finding more and more companies are beginning to use ArcGIS. I'm a lot more familiar with the ArcGIS software than I am with MapInfo, and am aware of the "Join Data" tool in ArcMap. However, from what I can see you can only join data either: based upon a common field within the two tables you're joining, or based upon the spatial location of data, joining where points fall within the boundary of a polygon.

The second method is more relevant for me, and it gives an option for the sum of the points within each polygon to be calculated. However, this will calculate the total number of attributes… not the sum of the values within a certain field behind those attributes e.g. so it would calculate 2 flooding incidents in City 1, when there are in fact 4.

Any ideas on how this would be possible?

I'm using ArcGIS 10.3.1.

Answer 1

This can be done by something that is called Merge rule - not very easy to find, I think.

Use the Spatial Join geoprocessing tool and choose the Merge rule to be Sum for the needed fields (right-click each field). In the image below, I am joining the cities to the states (and each city feature has an integer associated with each of those fields you mentioned).

Then the output fields will have a sum for all values in each field.

You can check the results by right-clicking a field in the point feature class and choosing Statistics to see the sum, pretty handy.

As a comment, you may consider redesigning your table with incidents. I would suggest keeping two fields: Date and Type. This is much better because if you would need to log a new type of incident, you wouldn't need to add a new field. Of course, you could also run Spatial Join just getting the total number of points within each polygon and since each point can only have single type, it would be a matter of running simple summary statistics on the output.