+ Reply to Thread
Results 1 to 11 of 11

GPS Distance

  1. 10-03-2014, 07:25 AM #1
    Muttly
    Muttly is offline
    Registered User
    Join Date
    06-11-2007
    Location
    Co Cork, Ireland
    MS-Off Ver
    2007, 2010
    Posts
    33

    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
    Reply With QuoteRegister To Reply
  2. 10-03-2014, 07:58 AM #2
    mcmahobt
    mcmahobt is offline
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: GPS Distance

    In which cell is your formula applied?
    Reply With QuoteRegister To Reply
  3. 10-03-2014, 08:02 AM #3
    Muttly
    Muttly is offline
    Registered User
    Join Date
    06-11-2007
    Location
    Co Cork, Ireland
    MS-Off Ver
    2007, 2010
    Posts
    33

    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.
    Reply With QuoteRegister To Reply
  4. 10-03-2014, 08:05 AM #4
    mcmahobt
    mcmahobt is offline
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: GPS Distance

    Quote Originally Posted by Muttly View Post
    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?
    Reply With QuoteRegister To Reply
  5. 10-03-2014, 08:20 AM #5
    mcmahobt
    mcmahobt is offline
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: GPS Distance

    Quote Originally Posted by Muttly View Post
    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.
    Reply With QuoteRegister To Reply
  6. 10-03-2014, 08:30 AM #6
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  7. 10-03-2014, 09:38 AM #7
    Muttly
    Muttly is offline
    Registered User
    Join Date
    06-11-2007
    Location
    Co Cork, Ireland
    MS-Off Ver
    2007, 2010
    Posts
    33

    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.
    Reply With QuoteRegister To Reply
  8. 10-03-2014, 10:02 AM #8
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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.
    Reply With QuoteRegister To Reply
  9. 10-03-2014, 10:06 AM #9
    Muttly
    Muttly is offline
    Registered User
    Join Date
    06-11-2007
    Location
    Co Cork, Ireland
    MS-Off Ver
    2007, 2010
    Posts
    33

    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.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 10-03-2014, 10:27 AM #10
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: GPS Distance

    Show some examples of desired output. They need not be correct mathematically, just correct in desired format.
    Reply With QuoteRegister To Reply
  11. 10-03-2014, 11:03 AM #11
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculating distance between many GPS points and sorting by distance
    By jackalek in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-14-2014, 08:27 AM
  2. [SOLVED] Google Distance Matrix API - Distance and Time
    By rtcwlomax in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-18-2014, 08:42 PM
  3. [SOLVED] Edit distance (Levenshtein Distance) using Excel
    By cool_anu4u in forum Excel General
    Replies: 4
    Last Post: 10-09-2013, 08:46 AM
  4. [SOLVED] retrive the distance between any two cities from distance table
    By rksundaram in forum Excel General
    Replies: 5
    Last Post: 08-22-2013, 11:46 PM
  5. Retrieve the Distance between Any Two Cities from Distance Table
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-25-2005, 02:58 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1