# GPS Distance

1. ## GPS Distance

Hi. I have a list of gps cordinates in deciaml format where lat is in column a nad lon is in column b. I have the formula below to determine how far 1 of co-ords is from another. What I need is to get is for column c to say yes if any other location is within 50m. Ideally I'd like for column d to say which row was within the 50m range.

Can anyone help me out please.

=ACOS(SIN(A3)*SIN(A4)+COS(A3)*COS(A4)*COS(B4-B3))*3443.89849

2. ## Re: GPS Distance

In which cell is your formula applied?

3. ## Re: GPS Distance

I haven't applied it as it won't calculate what I need. It will only compare rows next to each other which isn't what I need. I need each row to compare to all rows and if it's less than 50m say so.

4. ## Re: GPS Distance

Originally Posted by Muttly
I haven't applied it as it won't calculate what I need. It will only compare rows next to each other which isn't what I need. I need each row to compare to all rows and if it's less than 50m say so.
Okay then...in your formula, which cell is your "reference" cell - that is, which cell is meant to stay static, while you cycle the other cells through the list?

5. ## Re: GPS Distance

Originally Posted by Muttly
Hi. I have a list of gps cordinates in deciaml format where lat is in column a nad lon is in column b. I have the formula below to determine how far 1 of co-ords is from another. What I need is to get is for column c to say yes if any other location is within 50m. Ideally I'd like for column d to say which row was within the 50m range.

Can anyone help me out please.

=ACOS(SIN(A3)*SIN(A4)+COS(A3)*COS(A4)*COS(B4-B3))*3443.89849
Also, if you're in degree form in regards to your decimal values for your lat and lon values, the formula you have won't work because it is measuring distance in Radians. You'll need to convert, or use a different equation.

6. ## Re: GPS Distance

One way:

 A B C D E F G H I J K L M 3 Pt Lat Lon 2 3 4 5 6 7 8 9 10 4 1 97.00186 36.00274 36 60 42 121 119 187 92 95 160 5 2 97.00218 36.00248 96 72 88 154 152 126 130 195 6 3 97.00132 36.00281 37 180 60 247 37 35 101 7 4 97.00156 36.00090 161 85 219 56 64 126 8 5 97.00292 36.00487 240 86 213 215 281 9 6 97.00080 36.00163 304 30 25 42 10 7 97.00353 36.00096 275 281 345 11 8 97.00106 36.00109 18 70 12 9 97.00101 36.00236 66 13 10 97.00043 36.00122

The formula in E4 and copied right and down is

=IF(E\$3 <= \$A4, "", 6371000*CentralAngle(\$B4, \$C4, INDEX(\$B\$4:\$B\$13, E\$3), INDEX(\$C\$4:\$C\$13, E\$3)))

And CentralAngle is a user-defined function:

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

7. ## Re: GPS Distance

Sorry for the delay.

Thanks shg that looks great however I could be dealing with a few hundred gps cordinates so a formula like that could end up being impossible to see anything useful. THe current sheet I have is 18000 rows long.

mcmahobt, I don't know what you mean by reference cell? Each row is has only 2 columns and each is either a lat or lon.

That formula is just one I found online when trying to work this out but I couldn't get it to work, in that I couldn't work out how to check each of the 18000 rows with it. It was fine to check row 1 against 2 and 2 against 3 etc etc but that is still of no use for what I need.

Thanks for your help so far eveyone.

8. ## Re: GPS Distance

That's 324 million iterations of a non-trivial function.

You aren't providing much information that anyone could use to help you.

9. ## Re: GPS Distance

I don't really know what else to provide. I've attached the file so you can see what data I have.

10. ## Re: GPS Distance

Show some examples of desired output. They need not be correct mathematically, just correct in desired format.

11. ## Re: GPS Distance

Like this?

 B C D E F G H 1 Within 2 50 3 Line Lat Lon x y z 4 1 52.279 490 -9.859 650 0.602 773 894 -0.104 763 511 0.791 004 576 1,6671,6672,6673,14292,14293,14294 5 2 52.140 218 -9.553 718 0.605 218 967 -0.101 862 333 0.789 515 083 2,3,4,7235,7236,7237,14843,14844,14845 6 3 52.140 233 -9.553 725 0.605 218 752 -0.101 862 369 0.789 515 244 2,3,4,7235,7236,7237,14843,14844,14845 7 4 52.140 287 -9.553 688 0.605 218 092 -0.101 861 860 0.789 515 815 2,3,4,7235,7236,7237,14843,14844,14845 8 5 52.141 237 -9.554 022 0.605 204 590 -0.101 863 208 0.789 525 991 5,6,7238,7239,14846,14847 9 6 52.141 233 -9.554 025 0.605 204 630 -0.101 863 251 0.789 525 955 5,6,7238,7239,14846,14847 10 7 52.142 653 -9.553 795 0.605 185 743 -0.101 857 574 0.789 541 165 7,8,7240,7241,14848,14849 11 8 52.142 652 -9.553 788 0.605 185 777 -0.101 857 507 0.789 541 147 7,8,7240,7241,14848,14849 12 9 52.153 455 -9.553 663 0.605 039 182 -0.101 831 477 0.789 656 848 9,10,7242,7243,14850,14851 13 10 52.153 473 -9.553 665 0.605 038 930 -0.101 831 452 0.789 657 045 9,10,7242,7243,14850,14851 14 11 52.162 288 -9.547 565 0.604 929 955 -0.101 746 884 0.789 751 430 11,12,7244,7245,14852,14853 15 12 52.162 283 -9.547 557 0.604 930 037 -0.101 746 808 0.789 751 377 11,12,7244,7245,14852,14853 16 13 52.168 277 -9.548 785 0.604 846 387 -0.101 746 072 0.789 815 539 13,14,15,7246,7247,7248,14854,14855,14856 17 14 52.168 295 -9.548 805 0.604 846 102 -0.101 746 241 0.789 815 735 13,14,15,7246,7247,7248,14854,14855,14856 18 15 52.168 253 -9.548 852 0.604 846 585 -0.101 746 829 0.789 815 289 13,14,15,7246,7247,7248,14854,14855,14856 19 16 52.169 708 -9.549 398 0.604 825 835 -0.101 749 272 0.789 830 865 16,17 20 17 52.169 715 -9.549 395 0.604 825 751 -0.101 749 222 0.789 830 936 16,17 21 18 52.170 512 -9.549 702 0.604 814 376 -0.101 750 637 0.789 839 464 18,19,20,21,7249,7250,7251,14857,14858 22 19 52.170 518 -9.549 695 0.604 814 297 -0.101 750 551 0.789 839 535 18,19,20,21,7249,7250,7251,14857,14858 23 20 52.170 520 -9.550 097 0.604 813 561 -0.101 754 788 0.789 839 553 18,19,20,21,7249,7250,7251,14857,14858 24 21 52.170 520 -9.550 093 0.604 813 567 -0.101 754 752 0.789 839 553 18,19,20,21,7249,7250,7251,14857,14858 25 22 52.171 347 -9.550 272 0.604 802 012 -0.101 754 744 0.789 848 402 22,23 26 23 52.171 343 -9.550 272 0.604 802 058 -0.101 754 752 0.789 848 366 22,23

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1