Calculating distance between many GPS points and sorting by distance

1. Calculating distance between many GPS points and sorting by distance

Hello.
I've hit a problem with my set of data. I've a set of GPS coordinates in Decimal Degree format. I can calculate distance between any two given points by creating second set of GPS coordinates and using formula

My basic work is attached. As you can see I can only calculate distances between points in the same row.
What I need is to calculate distances between all the points stored in B2:C24 and select the one which is closest to each other.
This is proving difficult as I need to do calculation on the same data set 24 times (or more if data set is bigger) and compare the results and select two points closest to each other (and print the distance - this not an issue as I can do more processing when I get the main problem solved).
I've tried INDEX and MATCH but not succeed in getting it sorted.

Help would be much appreciated.

Regards
Sam

2. Re: Calculating distance between many GPS points and sorting by distance

The way I would approach this is to have set1 as rows (as is now) but change set to to go across the columns
Select Set2 copy and PasteSpecial ... transpose

Then change the formulae so that the references to E and F point to the right cells.

Note: in copying the formulae a reference such as C3 when copied will change the C and the 3 relative to the new position. Whereas \$C3 or c\$3 or \$C\$3 will only change the part not prefixed by a \$. This will help in copying your formulae.

The result will be a table of all values : you just have to find the smallest.

3. Re: Calculating distance between many GPS points and sorting by distance

Hello.

What you described may work, however I'm trying to tackle this form different angle. I've a set of CSV files with GPS points in columns.
Over the weekend I've done some calculations and I can create a formula and copy paste it. The problem is its okay for small set of data and but I've to do plenty of manual copy paste operations when data set is growing. My data would look like the one attached to this post, in columns. I need to calculate the distance of all points against the same column. So I have the points in B2. C2 and I can calculate distance between this point and all other points down the line.
My problem starts when I want to calculate B3. C.3 against ALL other points, including B2, C2
I can do it manually but I i'm ending up with two new columns of calculations per each row of points.
How do I create formula so I can calculate the distance of all of the points in columns ? It may be in a VBA as well, I'm learning this this so this would be very good starter

4. Re: Calculating distance between many GPS points and sorting by distance

As I susggested previously the easiest way is to copy and past(transpose) B:C to 1:2 as it will make the formulae quite straightforward.
If you just want to keep columns B:C then you could use =offset and use the column to calculate row_increment eg int((column()+n)/3)) where n is a correction value.

Using a custom formula in the obvious way won't make the application of the formula any easier.

You could use a macro which is runfrom a button or the menu. That would mean no copying of formulae

5. Re: Calculating distance between many GPS points and sorting by distance

Hello.
Thanks for the tips, I've decided to write a macro

I've function with does calculations of the great circle distance, and it is working when called from spreadsheet.
I can call it from sub with direct values hard coded as function parameter.
I have, however got into trouble with looping the function.

Lets paste some code:

``Please Login or Register  to view this content.``
So this is the function, which takes 4 double variables plus two boolean ones.
I've no problem with calling it directly from spreadsheet or by passing direct values to it.
But when I try to get it looping then I hit a problem of data type mismatch.

``Please Login or Register  to view this content.``
The error happens when I'm trying to compile the code as compiler is saying ByRef argument mismatch.
I think I know why is it happening, function wants type double as parameter, but array vntarray return something else.
How do I pass values from two dimension array to a function and convert it to double to match function declaration ?

Any help would be appreciated.

6. Re: Calculating distance between many GPS points and sorting by distance

really like looking at and I conceive this website got some genuinely useful stuff on it! .

7. Re: Calculating distance between many GPS points and sorting by distance

I thought by your posting that you generally know what you are doing so I include here a couple of illustrative bits of code:

1. The OPTION EXPLICIT must only exist once at the top of each module and ensure you declare each variable.
2. a modification to your calling routine. This prints out pertinent bits in your function call.

3. But I think you have the basic looping structure working incorrectly. So I have included a sample routine which loops through the same BC elements and outputs the results in the same format. If this does the right thing and you need help adapting it let me know.

Regards

``Please Login or Register  to view this content.``

8. Re: Calculating distance between many GPS points and sorting by distance

Pl see the atached file.

9. Re: Calculating distance between many GPS points and sorting by distance

Originally Posted by kvsrinivasamurthy
Pl see the atached file.
I suggested that originally but jackalec said he didn't want to do it that way

10. Re: Calculating distance between many GPS points and sorting by distance

Hello.
Thank you very much for the reply, I'm held up by work at the moment so had no chance to play with it. I'll post the outcome as soon as I had some time to play with it.

11. Re: Calculating distance between many GPS points and sorting by distance

So, welcome back.
I had some time to sit at the problem again and I've ditched the excel at all. Could not get it to work
Instead I've learned basic Python and came up with the following code, It is far from perfect, however it does what I wanted.
For future reference, code is attached below.

``Please Login or Register  to view this content.``
As you can see it is chaotic and basic but it calculate what I want.
Attached is sample of data I'm working on.

``Please Login or Register  to view this content.``
If someone wants to improve it or point to a better solution please fell free.
Shall I mark it as SOLVED ?

Best regards and thanks for the help and ideas!

Sławek aka Sam

12. Re: Calculating distance between many GPS points and sorting by distance

Hello.

Over the time the script received few improvements.

The current code can be seen below.

``Please Login or Register  to view this content.``

There are currently 1 users browsing this thread. (0 members and 1 guests)