Results 1 to 4 of 4

Thread: Extract informations if distance (lat,long) between two lists of places is lower than 50km
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Extract informations if distance (lat,long) between two lists of places is lower than 50km

    Hello,

    I have two lists of places (in two Excel sheets) : cities and swimming pools.
    I have the lat and long of all the places (for the cities it's the center).

    I want to extract a list of all the pools nearby every cities.
    So I would have a final list like this :
    - Name of the city / CityLat / CityLong / Name of a pool closer than 50km
    - Name of the city / CityLat / CityLong / Name of an other pool closer than 50km
    - etc.

    For now I have a formula (that seems accurate) to calculate the distance between two points :
    =ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371

    But I have no clue how to adapt it to my needs.

    I also try this VBA fonction that don't seems to work : http://www.codecodex.com/wiki/Calcul..._a_Globe#Excel

    Can someone help me on this one.

    Thanks

  2. #2
    Board Regular Rijnsent's Avatar
    Join Date
    Oct 2005
    Location
    Utrecht, Holland
    Posts
    1,079
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract informations if distance (lat,long) between two lists of places is lower than 50km

    Hi Matthieu,
    if you want to get that list you basically need a massive cross table with e.g. all cities as rows and all pools as columns. What you could do is use column A,B,C for the city (starting at row 4, so the first goes in A4,B4,C4), lat, lon and row 1,2,3 for Pool, lat, lon (starting at column D, so the first goes in D1,D2,D3). In D4 your formula would be =ACOS(COS(RADIANS(90-D$2)) *COS(RADIANS(90-$B4)) +SIN(RADIANS(90-D$2)) *SIN(RADIANS(90-$B4)) *COS(RADIANS(D$3-$C4))) *6371
    You can copy-paste that formula down/right.
    Hope that works,
    Koen
    Last edited by Rijnsent; Jul 16th, 2019 at 06:19 AM.
    You can't post attachments here, but you can help me helping you by posting a screen shot directly in your post with any of those tools.
    Otherwise use dropbox/google drive/etc to get your file accross (not preferred). For code, put it inside these tags: [ CODE][/CODE]. Do check the forum rules.
    Finally, please show that you made an effort to solve your problem: Yes, I like to help, but am not going to do your job.

  3. #3
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,173
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Extract informations if distance (lat,long) between two lists of places is lower than 50km

    this reminds me of some old threads

    https://www.mrexcel.com/forum/excel-...-formulas.html

    seems very similar. from what I recall, that query solution took seconds whereas formulas took 6 hours
    Last edited by Fazza; Jul 16th, 2019 at 08:03 PM.
    To receive a better answer, put more work into asking the question.


  4. #4
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    642
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract informations if distance (lat,long) between two lists of places is lower than 50km

    I did a distance calculation some time ago for zip codes in the US (the UK database of post code is too large for Excel).
    My calculations were based on distance "as the crow flies" not as you drive (as shown in Google).
    I have a sheet with zip codes and their corresponding Long and Lat values (approx 33000 of them).

    Code:
    Function CentralAngle(ByVal lat1 As Double, ByVal lon1 As Double, _
                          ByVal lat2 As Double, ByVal lon2 As Double) As Double
        ' shg 2008-1111
        
        ' Returns central angle between two point in RADIANS
        ' using Vincenty formula
    
        Const pi    As Double = 3.14159265358979
        Const D2R   As Double = pi / 180#
    
        Dim dLon    As Double
        Dim x       As Double
        Dim y       As Double
    
        ' convert angles from degrees to radians
        lat1 = D2R * lat1
        lon1 = D2R * lon1
        lat2 = D2R * lat2
        lon2 = D2R * lon2
    
        dLon = lon2 - lon1  ' delta lon
    
        x = Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(dLon)
        y = Sqr((Cos(lat2) * Sin(dLon)) ^ 2 + (Cos(lat1) * Sin(lat2) - Sin(lat1) * Cos(lat2) * Cos(dLon)) ^ 2)
        CentralAngle = WorksheetFunction.Atan2(x, y)
    End Function
    was/is the UDF I used to calculate.
    Last edited by kweaver; Jul 16th, 2019 at 08:16 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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