Cheking for close geographical points by geographical coordinates

Hi all,

I have a data set made of a number of geographical points some of which are pretty close one another.
I would like to pool observations which are very close one another and attribute them another value for latitude and longitude that are at an intermediate point between them. In case this might be made too hard by the existence of more than two observations which are close among them, I would have no problem in just maintaining the coordinates of one (chosen arbitrarily) and attribute this to the other close observations.

I was using this formula to calculate distance between points:
=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371
Taken from here.

The first problem I found is that I have 116 observations and I cannot find a "nice" way to measure distances among all the points.
Attached you can find the excel workbook.
Hope someone may find a solution to this.

Hi simone77,

Very interesting problem. See the attached workbook for the first attempt at solving your problem. I attempted to solve the problem by:
a. Putting the average lattitude in cell $B$118, and the average longitude in cell $C$118.
b. Added your equation in Column E. For lat2 and long2 I used the average values.
c. I sorted the data by Column 'E', the distance from the central point.

Since the sign of the longitudes are both + and -, my approach breaks down when there are Plus longitude points and Minus Longitude points approximately equidistant from the average longitude of -1.97 degrees.

As a second attempt, before doing any math, I would eliminate any obvious outlier points from the average calculations such as
Point 01 = [10.30, -61.00]

Another possibility is to separate the data into two or more groups:
Group A - Longitude less than 7.00 degrees
Group B - Longitude more than 7.00 degrees

I hope this helps

Lewis

Hi Lewis,

Thank you. Your answer is interesting but it does not seem to fit what I want to do. I will try to explain better.
I am going to plot this coordinates in a geographical map, however if I do it by using the database without any modification, I will have several partially overlapping point that does not provide any useful information in the map and just make the map appearing quite "ugly".
It would much better if I established a threshold distance between two observations above which I consider them as different points and below which they are treated as having the same coordinates (hence two completely overlapping point in the map).
Before establishing a threshold I need to know the distance of observation 1 with any other, of observations 2 with any other etc.

My original file was sorted by longitude, and should have been sorted by mileage from the central point. See the attached file which is:
a. Sorted by distance from the central point
b. Adds an additional column which indicates distance from the previous value

Lewis

Hi Marvin,

Thank you for your answer, I arrived to a very similar solution (identical except for the formula used to estimate distances). Yes, I cannot think of a way to make this another way (even though for sure there are many others).

Originally Posted by MarvinP

Hi simone,

I used the old (flat world) Pythagorean formula to calculate distance between points. Then did a conditional formatting to show distances less than one. See if you can put your formula in for mine and understand the reasoning.

I am not sure to understand the meaning of using the central point for what I wanted to do.
Anyway, thank you very much.

Originally Posted by LJMetzger

My original file was sorted by longitude, and should have been sorted by mileage from the central point. See the attached file which is:
a. Sorted by distance from the central point
b. Adds an additional column which indicates distance from the previous value

Lewis