+ Reply to Thread
Results 1 to 12 of 12

Calculating distance between many GPS points and sorting by distance

  1. #1
    Registered User
    Join Date
    09-18-2013
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    6

    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
    =ACOS(COS(RADIANS(90-B2)) *COS(RADIANS(90-E2)) +SIN(RADIANS(90-B2)) *SIN(RADIANS(90-E2)) *COS(RADIANS(C2-F2))) *6371

    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
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    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.


    click on the * Add Reputation if this was useful or entertaining.

  3. #3
    Registered User
    Join Date
    09-18-2013
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    6

    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
    Attached Files Attached Files
    Last edited by jackalek; 09-24-2013 at 06:02 AM. Reason: spelling and clarification

  4. #4
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    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. #5
    Registered User
    Join Date
    09-18-2013
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    6

    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. #6
    Registered User
    Join Date
    09-25-2013
    Location
    PAKISTAN
    MS-Off Ver
    Excel 2003
    Posts
    1

    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. #7
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    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. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,081

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

    Pl see the atached file.
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

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

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

  10. #10
    Registered User
    Join Date
    09-18-2013
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    6

    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. #11
    Registered User
    Join Date
    09-18-2013
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    6

    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. #12
    Registered User
    Join Date
    09-18-2013
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    6

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [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
  2. [SOLVED] Calculating Distance between 2 points.
    By isdouble in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-20-2012, 07:17 AM
  3. Distance between two points
    By dinuhere in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2012, 06:42 AM
  4. Distance between 2 geo-points
    By bstubbs in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-19-2009, 07:53 AM
  5. 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1