+ Reply to Thread
Results 1 to 6 of 6

Creating a route based on GPS proximity

  1. #1
    Registered User
    Join Date
    01-31-2018
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    3

    Creating a route based on GPS proximity

    I have a list of locations with GPS coordinates. From the GPS coordinates I found the distance between each point with respect to one another. Now I have been trying to sort the locations based on the next closest point. In the end I would like the formula to start at a location (i.e. 16-10-426-12-EXT Receiver) find the next closest point (16-11-488-12 Receiver) then find the next closest point from there but exclude any location that was previously listed. Excluding previously listed locations from the Index or lookup is what I can not figure out.

    Thanks ahead of time!!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,779

    Re: Creating a route based on GPS proximity

    Filter the table and sort any column smallest to largest.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    01-31-2018
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    3
    Quote Originally Posted by protonLeah View Post
    Filter the table and sort any column smallest to largest.
    That will only sort the distance from one point with respect to the rest. I need it to pick the closest point in relation to each individual location. For example if you have point A between B, C, and D; B 1m to the left of A, C 1m to the right of A, and D 3.1m to the right of A, sorting them will put the order A, B, C, D when the most efficient route should be something more like A, B, D, C

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,323

    Re: Creating a route based on GPS proximity

    So, is it really some kind of "shortest path" algorithm that you are trying to do here? http://www.excel-easy.com/examples/s...h-problem.html
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    01-31-2018
    Location
    Colorado
    MS-Off Ver
    2016
    Posts
    3
    Quote Originally Posted by MrShorty View Post
    So, is it really some kind of "shortest path" algorithm that you are trying to do here? http://www.excel-easy.com/examples/s...h-problem.html
    Itís a similar idea. Except I will have a lot of data and would need it listed based on the shortest path of the next closest point, excluding any duplicates already in the list

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,323

    Re: Creating a route based on GPS proximity

    So a "shortest path -- greedy algorithm style" type algorithm? Just trying to make sure I understand exactly what you are trying to do.

    In the other shortest path algorithms, the "don't repeat a node" seems to be handled by the constraint that "flow into a node"-"flow out of node"=0. I would probably expect that the "don't duplicate a node" part of the algorithm probably includes some formula to count flow in/flow out of each node.

    In searching for other sample spreadsheets, I came across this shortest path algorithm tutorial with a couple of sample sheets: https://networkmodels.weebly.com/excel-solver.html One of their examples looks more like the 2D distance matrix that you have, so it might be more helpful.

    I think if I were doing this, I would probably start with one of these shortest path problem spreadsheets/examples, and make sure I understand exactly how it works. Once I understand how those examples work, I would structure your data in a similar way -- with nodes and arcs and such. Once I have the spreadsheet set up (with or without the Solver model), then I can work on the "greedy algorithm" part of the problem.

    (FWIW, I started trying to develop the greedy algorithm and thought that it looked easier with the arcs in list format like the Excel Easy example rather than in the 2D matrix format, but I did not explore it in depth. Whichever approach is going to require some thought towards data structure.)

+ 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] Creating in Excel a hyperlink to a route made in Google maps
    By pgyselin in forum Excel General
    Replies: 39
    Last Post: 02-10-2016, 08:19 AM
  2. Linear Trendline Proximity Problem
    By ih8xc in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-09-2015, 07:54 PM
  3. Replies: 3
    Last Post: 01-09-2015, 02:57 PM
  4. proximity of coordinate data
    By MattRNR in forum Excel General
    Replies: 2
    Last Post: 07-18-2013, 02:41 AM
  5. VB or Macro? Advice on best route to creating web search data returns
    By harrysthorne in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-10-2012, 03:49 AM
  6. Route Run Distances based on postcode area
    By marklister in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-07-2012, 11:33 AM
  7. Reference one cell in proximity to another
    By fadeoutagain278 in forum Excel General
    Replies: 4
    Last Post: 10-07-2008, 08:31 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