Create clusters/groups with Find Nearest but excluding the groups already created

[Harvey_H]

Dear All,

I'm having trouble trying to create clusters or groups of data points (e.g. store locations) when the universe and the target are the same with the Find Nearest tool.

I have 1,300 stores that I need to group by the closest 25 in order to create 52 clusters. The thing is that I am not being able to exclude the stores already assigned to a group from the rest of the universe. 

Imagine that the 1,300 stores are located in Portugal. I want them to be grouped in 52 clusters based on the 25 nearest each without intersection between groups.

I have tried solutions like K-Centroids and appending clusters afterwards but the clusters that they create are not exactly composed by 25 stores.

Do you have any ideas?

Best,

[PhilipMannering]

Did you try Ignore 0 distance Matches?

[Harvey_H]

Hey! Thanks for the quick reply.

Yep! Although that is not the problem... The thing is that the find nearest tool returns the 25 nearest for each store (creating 25 new rows per store) and I just want to group the stores by the 25 nearest stores, excluding the ones that are already grouped

[AngelosPachis]

Hi @Harvey_H,

I think your question can be answered via using an iterative macro. I've created one that does something similar to what you are looking for. So in my example I have 15 different store locations in Portugal and I'm going to find the 3 nearest stores for each location.

Firstly, the macro keeps the first record in each iteration and for that particular store, is looking for the 3 nearest stores that will fall out of the "M" anchor. I've set the number of stores to find to be 3, but you can change that in your case.

So for example, Store1 (which is the first record) is nearest to stores 3, 15 and 2.

Once I've found the 3 nearest stores, I want to output that information in every iteration so I connected the "M" output of the "Find Nearest" tool to a "Macro Output" tool. Then I have to remove those 4 stores (Store 1, 2, 3 and 15) from the remaining dataset.

I did that with a "Join" tool and the field I used to join on was Record ID. So records not contained in the Left Input anchor of the Join tool , will fall out of the Right output anchor of the Join, as shown below:

As a last step, I counted the number of records falling out of the right output anchor of the Join tool, as I want to make my macro stop iterating once there are no more records left to be processed, so essentially I want my macro to stop when the record count reaches 0.

To make it easier for you to use the macro, I've used a couple of interface tools, one allowing you to change the number of stores you are looking for in each iteration and another enabling you to change the radius in miles, without having to open the macro every time you need to configure it.

Let me know if that worked for you or you have any further questions.

Regards,

Angelos

[Harvey_H]

Hi @Angelos,

Thank you very much for your explanation. I think that this is what I need! 

Just one thing, I'm not being able to open the file... May you please re-upload in another file format? I have v. 2020.2 x64 and it says that I need an updated version but there are no updates pending...

Thanks again!! 

[AngelosPachis]

Hi @Harvey_H ,

Yes of course, I will attach the workflow and the macro separately.

Let me know if it worked for you.

Angelos