Group of decimal coordinates - how to pool those which are close one another

[simone77]

Hi,

I first posted a question yesterday which asked how to set a formula to calculate distances (by using a function I provided) among all the observations (each one with a latitude and longitude value expressed in decimals) in an excel sheet. Here it is the previous thread.

I slightly modified the solution MarvinP suggested to me by using my own function to calculate distances among coordinates.
Attached you can find the excel with this. You will see that on the right side of the sheet, in yellow background, there is a matrix whose non-blank cells each one reporting a case of close coordinates (e.g.: 4-5 means that observations 4 and 5 are less than 15 Km apart one from another). Cells of the left-side matrix contains the function used to calculate distances among each observation with any other. Matrices are 116X116.

However, I am now jammed because I don't find an easy way to pool the observations found to be close each other (I have used a distance threshold of 15 Km - you can see it in the formula inside each cell of the "yellow" matrix").
The ideal way to establish new coordinates values for observations close one another would be by using coordinates which are at an intermediate position, however, this is not my priority, it would be OK also using one arbitrary value for all of them, i.e. picking up one pair of coordinates of one of the close observations and attribute this to the other. I have seen that, by excluding nonsensical similarities (e.g. 1-1, 2-2, etc.), there are groups of close observations of size 2 (e.g. 4-5) up to 10 (nine observations very close to a given one).

I have thought it for a while but it doesn't seem I am too inspired.
Anyone of you willing to solve this puzzle?

Simone

[shg]

You might start with having a read here: http://en.wikipedia.org/wiki/Cluster...sed_clustering

[MarvinP]

Hi Simone,

Here is another approach to this problem. Let Excel guess some coordinates and see how many other points are within the guess. Do this 1000 times and see what you get. In the attached I've created a few formulas.
1. The guess is a RandBetween the low and high Lon and Lat numbers
2. I used my old Pythagorean distance formula
3. I added a cell in E1 which will be your distance criteria
4. I keep the best answer (the count of other coordinates that are closer than E1)

Change the value in E1 to some max distance you want to cluster. Change H3 to zero.
Click the button for 1000 guesses. Change the VBA code to do 10,000 guesses or more....

See if this works for you...

[simone77]

Hi,

I apologize for the delay, I really appreciate your help but I am afraid it is not clear what I am looking for.
MarvinP, I have been looking at your solution and I have found it very interesting, I have learned quite a few things by it. Thanks for that. However, the final answer it gives does not fix my purpose.

I try to explain it better and simple:
1. I have 116 observations each one with a given latitude and longitude pair of coordinates.
2. Some observations are close among them, how close? it depends on my criterion. Since my final aim is to plot this points at European scale, a distance of, say, 15Km between two observations may be negligible and I would pool them as a unique observation.
Question: How can I reduce the set of 116 observations to a set of x observations based on their closeness?

Any idea on how to do this?

[MarvinP]

Hi simone,

I'm starting to like shg's answer now. You have to determine what criteria you are going to use to determine how to "cluster". His link points to a large sample of different methods for lots of different sciences.