Summarizing Point Data on a Polygon Layer

Occasionally it is necessary to summarize the data from a layer of points based on containment within a polygon layer. This article presents a relatively straightforward way to do this with Cartographica. 

The solution to this problem requires the use of both an internal command and a small AppleScript, which we will provide. This simple script can be modified for your individual needs to apply basically any formula, but in the example case, it provides the sum of the values of a single column in the point dataset. At the end of this piece, we'll discuss some easy adaptations of this script.

Preparing the data

To perform this function, you'll need (a minimum of) two layers in your document, one Polygon layer, and one Point layer. Further, the example AppleScript requires that the data to be summed is a Number type. It is also required that the Polygon layer contain a column that provides unique identification of the polygon. This can be a String or Number type.

Preparing for the script

Before the script is run, we need to prepare the point data by having Cartographica determine which polygons they are enclosed by. To do this:

  1. Select the Point layer in the layer stack
  2. Choose Tools > Add Distance to Nearest Feature…
  3. Select the Polygon layer from the Caluculate Distance to: menu
  4. Type a name for the distance column in the Distance Column box
  5. Check the box next to the Polygon layer's unique identifier column (UID column, henceforth)
  6. Click Add…

After a few seconds, your Point layer will now have 2 new columns, one containing the distance (from the point object to the nearest edge of the nearest polygon, with the numbers negative if the points are within the polygons), and one containing the UID Column. Now, your data is ready to go.

Running the script

The script is available by following the link. It is relatively straightforward and can be modified for a variety of calculations.

To run the script:

  1. Make sure your Map window is frontmost in Cartographica
  2. Double-click on the script file to open AppleScript Editor
  3. When prompted for Container Layer select the Polygon layer
  4. When prompted for ID Column of Container select the UID Column (as mentioned above)
  5. When prompted for Feature Layer select the Point layer
  6. When prompted for ID Column of Features select the matching UID column created by the distance tool
  7. When prompted for Column to Sum select the column whose data you want summed

Once you have described the environment to the AppleScript, it will process the data. For large numbers of points, it may take some time, but we have found it very quick for thousands of features and tens to hundreds of polygons.

When it is finished, your Polygon layer will contain a column named "value SUM" if the original column to be summed were "value". (Note: if you run the script more than once, subsequent sum columns will be named "value SUM 2", etc...

Caveats

  • If you have trouble using the script as described above, double-check that the values columns are Numbers and that the unique id column is actually unique.
  • Very large numbers of points or polygons can make the script slow, although we have tuned it to use techniques to minimize the delays
  • Although you can perform other functions with Cartographica while the script is running, we would not suggest it.

Adapting the script to other functions

The script, as written, just creates a simple sum, although many other functions can be used with the basic structure. AppleScript takes a bit of getting used to, but in this particular script, most of the real work is done in the last few lines:

set containerMap to {}
repeat with container in every feature in containerLayer
	set containerMap to containerMap & 
	   {{containerID:(value of (field data containerIDColumn of container)),
	     containerPtr:container, sum:0}}
end repeat

-- loop through each feature in the feature layer	
repeat with addFeature in every feature of featureLayer
	-- get the current value from the feature
	set currentValue to value of field data sumColumn of addFeature
	-- get the container from the feature
	set featureContainerID to value of field data featureIDColumn of addFeature
	
	-- unfortunately, this needs to be done in a loop, 
	-- but it is all local, so at least it's not too slow.
	repeat with container in containerMap
		if containerID of container is featureContainerID then
			-- update the sum value
			set sum of container to (sum of container) + currentValue
			exit repeat
		end if
	end repeat
end repeat

-- now that we've applied the function, go ahead and 
-- store the final results in the containers
repeat with container in containerMap
	set containerFeature to containerPtr of container
	set value of field data finalSumColumnName of containerFeature to (sum of container)
end repeat

In particular, if you wanted to create an average as well as the sum, you would need to add a new count item to the containerMap, update that during the "update the sum value" step and then calculate the average before setting the finalSumColumnName value. To do this:

  • add itemCount:0 before the first "}" in the containerMap initialization
  • add set itemCount of container to (itemCount of container) + 1 on a new line after -- update the sum value
  • change (sum of container) to ((sum of container)/(itemCount of container)

Of course, you could both the average and sum, by creating a second column and duplicating the line that sets the value for finalSumColumnName and adding a new one for finalAvgColumnName.