+ Reply to Thread
Results 1 to 5 of 5

Grouping latitude Longitude coordinates into groups of 30 nearest locations

  1. #1
    Registered User
    Join Date
    02-22-2020
    Location
    Dubai, UAE
    MS-Off Ver
    14.7
    Posts
    2

    Grouping latitude Longitude coordinates into groups of 30 nearest locations

    Hi guys,

    new member, i have a problem that builds on an existing thread on this forum "Finding the nearest location from list of latitude Longitude"

    i'm trying to split 372 location coordinates into 31 groups based on nearest locations.

    my file currently uses the below 2 formulas to get the 30 nearest locations for each location.

    =SMALL(ACOS(SIN(RADIANS(B2))*SIN(RADIANS($B$2:$B$27))+COS(RADIANS(B2))*COS(RADIANS($B$2:$B$27))*COS(RADIANS($C$2:$C$27-C2))-1*10^-14)*6371,2)
    =INDEX($A$2:$A$27,MATCH(E2,ACOS(SIN(RADIANS(B2))*SIN(RADIANS($B$2:$B$27))+COS(RADIANS(B2))*COS(RADIANS($B$2:$B$27))*COS(RADIANS($C$2:$C$27-C2))-1*10^-14)*6371,0))
    However, How do i ensure each group has a unique set of nearest locations only and locations do no repeat in different groups ?

    If anyone can help me adjust the formula to avoid repetition amongst groups ? I can provide the sample data if needed.


    Thanks

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Grouping latitude Longitude coordinates into groups of 30 nearest locations

    Maybe I am overthinking this, but this begins to feel like a clustering problem https://en.wikipedia.org/wiki/Cluster_analysis . We have talked about clustering problems before (a couple of links at end), but, as the Wikipedia page explains, there is no one correct clustering algorithm and the choice of algorithm depends on exactly how you want to detect clusters.

    My first introduction to clustering algorithms: https://www.excelforum.com/excel-gen...ml#post3996836
    A sample file implementing k-means clustering for a 1D set (would need to expand to 2D): https://www.excelforum.com/excel-gen...ml#post3996836

    Questions at this point: Am I overthinking this? Do you need one of these more complex clustering algorithms, or did you have something simpler in mind? Where do you want to go from here?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    02-22-2020
    Location
    Dubai, UAE
    MS-Off Ver
    14.7
    Posts
    2

    Re: Grouping latitude Longitude coordinates into groups of 30 nearest locations

    Hi yes i need a basic clustering function to groups points that are closest to each other.

    i checked the links you've sent, however im still unsure how to apply the same on the locations.

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

    Re: Grouping latitude Longitude coordinates into groups of 30 nearest locations

    This isn't the kind of programming that I do, either, so I'm not quite sure exactly how to implement it. Is there something about clustering algorithms that you are having trouble understanding? Do you know which algorithm you want to utilize?

    From Wikipedia's clustering algorithms page, I click through to the k-means clustering page and find a description of a naive k-means clustering algorithm ( ). Basically it boils down to:
    1) Choose 30 "mean points".
    2) Compute distance from each point to each mean point.
    3) Assign each point to the closest mean point.
    4) For each group/cluster, compute a new mean point.
    5) Go back to step 2 and repeat steps 2 to 4 until the algorithm stops changing.

    Which of those steps do you have trouble with? Do you have a sample data set (preferably in a spreadsheet) that you can share with us?

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Grouping latitude Longitude coordinates into groups of 30 nearest locations

    I don't know if this will help, but I quickly put together this naive k-means spreadsheet. It uses randomly generated x,y points and assigns the points to 3 different clusters.
    1) Column F and G are the main x,y input columns
    2) H7:J8 are the main "centroid" points.
    3) columns H:J compute the distance from each point to each centroid.
    4) column L:M determine the closest centroid, then assign the point to that centroid.
    5) L6:N7 compute the new centers for each centroid.

    I have not programmed this very carefully, but it should illustrate the basic steps and one possible way to program those steps into a spreadsheet.
    Attached Files Attached Files

+ 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] Finding the nearest location from list of latitude Longitude
    By spike4848 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-16-2019, 11:45 PM
  2. comparing 2 lists of Latitude Longitude coordinates
    By polarbear467 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-11-2013, 11:00 AM
  3. Replies: 0
    Last Post: 09-19-2012, 09:04 PM
  4. Splitting large numbers into groups (LATITUDE AND LONGITUDE)
    By craighaylett in forum Excel General
    Replies: 5
    Last Post: 11-16-2009, 12:19 PM
  5. Latitude/Longitude Porblem
    By edwardtong694 in forum Excel General
    Replies: 3
    Last Post: 05-20-2009, 12:23 PM
  6. [SOLVED] How do I enter/format latitude and longitude coordinates in Excel
    By Servant_ in forum Excel General
    Replies: 1
    Last Post: 02-10-2006, 10:55 AM
  7. [SOLVED] Help with Latitude and Longitude.
    By LadiFireBug in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-22-2005, 05:09 AM

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