1

I have a point shapefile (points.shp) with fields [owner], [name], etc.. [owner] and [name] are both of type string. The owner contains a single string, the unique owner. Name contains a list of names, seperated by ";".

[owner]     [name]
   a     ";wood;grass;"
   a     ";grass;house;"
   a     "house"
   b     ";wood;tree;"
   b     ";grass;house;"
   c     ";tree;"

Now, what I am currently doing is aggregating all points of each unique name-string in [name] by a distance and Spatial Joining those back to count the amount of points containing this string within the Aggregated Polygon. The following script is running in Model Builder.

  1. Select all [name] LIKE '%;wood;%'
  2. Aggregate Points by distance z = agg_wood.shp
  3. Spatial Join agg_wood.shp with points.shp (selection still alive) = adds a field "Join_Count" to agg_wood.shp
  4. Select [name] LIKE '%;house;%' etc.

The aggregated Polygon Shapes (about 7000) are merged into a final Shapefile. So, my final, merged shapefile will have all counts of [name] occurrences:

shape_wood.shp:
[FID]  [Join_Count]
  1         10        
  2         11        
  3         2         
  4         1         
  5         13        
  6         4         

Finally, I merge all aggregated polygon shapefiles, which works. The point file has something about 800,000 points, about 5,000 unique name strings, and about 20,000 unique owners.

My problem is I would like to add a field to agg_wood.shp during the process that sums the number of unique occurrences for the field [owner] for each aggregated polygon. Lets say there are 5,000 points in an area and 500 of those contain "wood" in the list of strings from the field [name]. These are aggregated into one or more polygons. However, there may be 1 up to 500 different owners who generated "wood". I want to add a field [occ_own] with the number of unique owner-strings who added "wood" in the field [name]. So my intermediate output file (agg_wood.shp) would look like this:

shape_wood.shp:
[FID]  [Join_Count]  [Occ_Own]
  1         10        1
  2         11        1
  3         2         2
  4         1         1
  5         13        13
  6         4         1

shape_house.shp:
[FID]  [Join_Count]  [Occ_Own]
  1         23        3
  2         10        5
  3         3         3
  4         1         1
  5         150       1
  6         2         1

Then I would merge all of those to a single shapefile of all [name] polygons. But I can't imagine how to modify my process so it can calculate the number of unique owners for each polygon area and [name].

Does anyone have an idea?

  • 1
    Do you need to modify the model/code or just need a process for afterwards to summarize? If afterwards, you can look into summarizing it in the attribute table. – Branco Jul 2 '14 at 14:27
  • 1
    I'm having trouble following what you're trying to do. You select points with same name, create polygons enclosing clusters of them. Then you join the points to the polygons to see how many are in each polygon and get that as an attribute. But you also want unique owner count by name. What if you join the polygons to the points instead? Then use Summary Statistics to get a count of points by owner name by polygon, which you then Summary again to get owner count and total point count by polygon? – Chris W Jul 3 '14 at 4:29
  • First, I am sorry for my late reply - I was not able to respond sooner. Thank you, Branco, for your hint! It would be totally sufficient to add information afterwards. However, I do not understand how I can summarize unique owners from the point file (with 800.000 points and about 20.000 unique owners) for each polygon in my aggregated and merged polygon file. Could you specify what steps I should take? – Alex Jul 6 '14 at 7:42
  • I'm struggling to follow what you are trying to do. Would you perhaps be able to focus on providing us with what the input(s) to your process (tables and pictures) are and what the desired output(s) are (tables and pictures) before going into any detail of how you are trying to do it now, please? – PolyGeo Jul 6 '14 at 8:23
  • Of course, PolyGeo, I am trying to describe it again, more exactly: – Alex Jul 6 '14 at 13:25
2

The problem in your current method, and the reason summarizing afterward as @Branco suggests would not work, is that your spatial join operation creates the first attribute you want (total points per poly) while it destroys/eliminates the second variable (owner) you want to summarize. In order to summarize, you need whatever variables you want in the same dataset. Right now your points have owners and names, and your polygons get a count. You'd need your points to have a polygon name and then you could get owners by name by polygon.

Your data format also introduces a problem because name contains multiple values in a single field and summarizing on that will treat each unique field value as what it counts. In other words, woods;house and house;woods are two different things. So is house and ;house; for that matter. To avoid this, you'll have to use a selection as an input to summarize and not include that field as a case.


Start by modifying and reversing your current spatial join. Instead of points being join features they will be target. Polygons will be the join features. The output of that join will be points with an attribute that is [polygon ID] they fall in.

Now we add some steps to the process. Your spatial join output will become the input for a Summary Statistics tool. But in order to solve the multi-name issue mentioned above, first you'll need to put in/repeat a selection (possibly make feature layer) step to once again grab all points with the desired name string (note now you're working in a new dataset - the spatial join output, not your original point file).

Now you plug that selection/feature layer into a Summary Statistics tool. In there you will add [polygon ID] and [owner] as case fields (note you must add them in that order). You can add any valid statistic field/type you want - we don't need the results of that. The table that is output should then have a list of every unique [owner] and [polygon id] combination along with the [frequency] (or number of times) it occurs. Note the sum total of that frequency column should be the total number of points - so Polygon A has Owner Q frequency three (one row in table), Owner P frequency one (second row in table), and Owner R frequency six (third row in table), and 3+1+6=10 total points in Polygon A.

But you want to collapse that down to one record per polygon, so that output table will now become the input for a second Summary Statistics tool (no selection needed). This time [polygon ID] will be the case field and you'll have two statistics fields - [owner] with type count and [frequency] with type sum. The resulting table should have [polygon ID], [count owner], [sum frequency] and [frequency] (which should equal [count owner]).

That table now gives you the statistics you want for a single name. If you want them as attributes of the polygons, you can join that second Summary Statistics table to the polygons based on [polygon ID] and export the result or use a Join Field tool to append those attributes directly to the original polygon file.

You'll then repeat the entire process for the next [name] string selection, just as in the current step 4 you have. At the end, you'll merge all your polygon shapefiles to a single file.

You could build that all into the model with an iterator and submodel, collect values, and perhaps a dictionary because of that multi-value single-attribute condition of [name]. Otherwise you may want to consider cleaning up that point data so that each point only has a single name value (and those with more than one become stacked points). This could allow direct use of Summary Statistics without any selections, but a selection would still be needed for your aggregate to polygons tool.

  • Thank you very much for your detailed answer! I will go through it today and get back to you. I think it should be possible to adjust my Model (there, I am using already an iterator/dictionary). – Alex Jul 7 '14 at 8:46
  • I am a little bit worried that this extra step will slow down my process quite a bit, but it seems there is no other way than summary statistics. Yesterday, I was experimenting with Spatial Join and Field map, similar to your previous comment, but realized the same: count will only return total numbers, not unique counts. I wonder if it would be possible to add a field map option like "unique" within the spatial join tool (by ESRI, at some point). Nevertheless, again, thanks! – Alex Jul 7 '14 at 8:46
  • @Alex I was thinking the same thing when I was testing the Field Map option myself - why not have a 'unique count' option. Turns out there is an ArcGIS Idea for that functionality in some parts of the software if you want to vote for it. – Chris W Jul 7 '14 at 17:37
  • wow, it worked. I just tried it manually and followed your description and it was actually pretty fast. I got confused with the summary statistics tool and need to read into Case Fields and Statistic Fields, but it makes sense at the end. It should really be no problem adjusting my Model with that, as I already have the Dictionary/Selection loops and variables. Finally, just one reply to your Hint with separating the point values: this seems like a straight forward solution, however, I would end up with about 10 Million points - so in my case, I need to go all the way! Thanks so much! – Alex Jul 7 '14 at 18:43
0

After working with this solution for a while, I really found it useful and thought I'd share it here. This is a simplified version compared to my original question, but it does count unique occurrences (e.g. UserID):

1st Input is a Shapefile with different polygons where we need statistics for 2nd Input is a Pointfile, which need to be counted on a per polygon basis; there is also a UserID field - each User should only be counted once per Polygon (means there are multiple points from one UserID which only need to be counted once per polygon)

The following Model Builder Script does this automatically. This is a direct implementation of Chris W answer. ArcGISModel Builder

The script can be downloaded here for inspection: Model Builder Toolbox ArcGIS 10.4.1

Initial Spatial Join for Counting all Occurences

Initial Spatial Join for Counting all Occurences

Final resulting Table with overall occurences and distinct UserID counts (COUNT_user column)

Final resulting Table with overall occurences and distinct UserID counts (COUNT_user column)

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.