Calculating max and average distance from point to points in ModelBuilder?

Question

I have two point layers. One contains nest locations and the ID of the individual using that nest. The second point layer contains movement points for all individuals in the area.

I would like my ModelBuilder model to generate a table with a row for each nest showing the MAX and AVERAGE distance from the nest to the movement point which are from the individual ID using the nest.

The images below are a mockup of the basic idea of what the input looks like and what I'd like the results to contain.

So far, and with some help on this board, I've used Point Distance to output a table which has the distance from each nest point to all other points. Then I've been playing with Summary Statistics to try to generate the output table. The problem is that the Point Distance tool, I believe, is creating distance from the nest to ALL points and not just the ones that match the ID. I need to put a filter into the SurveyPoints at the beginning to iterate through the nest IDs. But ideally I want this all to wrap into a single final table.

My untried idea is to try to do a iterate through SurveyPoints using a filter based on the nest id (I don't know how to do this). Then somehow append the results of the Point Distance or the Summary Statistics to get a final table.

---UPDATE--- I tried the approach as suggested which looked like it was going to work. However, I'm getting a weird snag. Let me know if this should be a new question.

My Iterate Field Values is setup just like the suggestion with the Iterator using the nests UnitID field which is a string.

When I run the model (after validating) it gives me an error because the Value = 1 for some reason. Actually if I click on the Value circle before I run the model the greyed-out value in the Value box is '1'. None of the UnitID fields are 1.

So when I run the model I get the error below. "Nests_meter" is the true name of the "Nests" labelled circle in my model.

ERROR 000358: Invalid expression UnitID = 1 An invalid SQL statement was used. An invalid SQL statement was used. [Nests_meter] Failed to execute (Make Feature Layer).

The weird thing is that the first time I added the iterator, it did contain a valid value. But after that it just starts with a value of 1. I recreated the model from scratch but still get the '1'.

Answer 1

You need to add a feature selection iterator. Set the 'group by fields' to the nest id. This will iterate through the survey points selecting each nest id.

UPDATE

I think it will be easier for you to work with two feature layers. Implement this for the start of your model, then connect the two feature layers to the point distance tool.

Model:

Iterate field values settings (Note that if your nest id is a string, change the data type to 'string'):

Make feature layer settings (Note that if your nest id is a string, change the expression to NESTID = '%Value%'):

UPDATE 2

As OP noted, the 'Value' field from the iterator has a value of 1 before running the model. This is just a dummy value that should not be used. However, the make feature layer tools were failing because they were running before the iterator started running, and they grabbed the dummy value.

To fix this, make 'Value' a precondition to the make feature layer tools. To do this, use the connector to connect 'Value' to both make feature layer tools. When you connect them, select 'Precondition' from the popup menu.

This will ensure that 'Value' has actually received a value before passing the value to the make feature layer tool.

Info on preconditions: http://desktop.arcgis.com/en/arcmap/10.3/analyze/modelbuilder/a-quick-tour-of-using-precondition.htm

Answer 2

First off, the way you have described the nest to individual relationship is 1 to 1. Any nest is only used by one individual and any one individual only uses one nest. Therefor there is no need for two point layers in your system. We can simplify this to one layer by joining the attributes using 'individual'. (Even if the 1:1 relationship isn't true and you have M:M, this will still work but will get complicated if you care which nest we are referring to for a given individual.)

Anyway, first create two new attributes (type="double") in the nest table (call them 'x1' and 'y1') and populate them with the geometry (calculate geometry x and y in the respective column). Do the same for the individual movements point data (name the columns 'x2' and 'y2').

OK, now join the nest data set to the movement data set on the 'individual' attribute. Right click the movement data in the table of contents and select join.

We have a table that is now something like:

INDIVIDUAL | x1 | x2 | NEST | INDIVIDUAL(2) | x2 | y2 

Create another attribute in the movement table called 'distance' and populate it using the field calculator using the distance between two points formula:

GENERIC: SQRT((x2-x1)^2+(y2-y1)^2)

Stab at ArcMap: math.fabs( math.sqrt( math.pow((!x2! - !x1!),2) + math.pow((!y2! - !y1!),2) ) )

Now you need to get the MAX and MEAN of those distance values for each individual. There are a few ways to do this and without ArcMAp in front of me I can't think of the most efficient way right now (helpful edits welcome):

-There is probably a statistics tool in the toolbox somewhere that gets the MAX and MEAN grouped by an attribute.

-You can split the table by individual and then calculate new attributes for MAX and MEAN, or right click the attribute and look at the summary statistics.

-If not too many different individuals you could use select by attribute to select each individual in turn and then do a field calculate for MAX and MEAN (remember to create the new attributes first). calculations should only be done on the selected features.

-[EDIT] It looks like the summary statistcs tool you currently have in your model is what you need for this part LINK.

See example below

If the data set is not too big then I would probably have exported the data after the join and worked in excel.

P.S. make sure the two data sets are in the same cartesian coordinate system.

N.B. I don't have ArcMap anymore so the instructions are by memory - please feel free to edit with correct ArcMap syntax and instructions.