Forum Discussion

JeffPeterson1's avatar
JeffPeterson1
Qrew Captain
5 years ago

Finding the closest major metro to a city

Some similar questions have been asked but I can't find exactly what I'm after.

Here's the problem:

I have a list of sites in one table called 'Sites'. 

In another table I have a list of 50 'Major Cities'.   Both tables contain only the city name and the latitude and longitude.

What I want to do is compare each site to the list of major cities to determine which one is the closest. 

I can see how to measure the distance between 2 sets of coordinates,  but how would I compare to an entire table list and only return the closest Major City to each Site? 
 
EDIT:  I believe the found my answer here:  https://community.quickbase.com/communities/community-home/digestviewer/viewthread?GroupId=43&MessageKey=2ea7d1af-9374-42a6-8182-6f1f7a545547&CommunityKey=157a0aac-5622-4134-8b0a-ae35c9b68e53&tab=digestviewer

------------------------------
Jeff Peterson
------------------------------
APIs and custom code
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    I recognize that distance formula.  It's mine from this old post from 2014 here.
    https://community.quickbase.com/communities/community-home/digestviewer/viewthread?GroupId=103&MID=30357&CommunityKey=d860b0f8-6a48-487b-b346-44c47a19a804&tab=digestviewer

    I watched Kirk's video and it only works for one Home Record at a time.  It does not work for a set of sites.

    I believe what you will need to do is to create an Automation to copy in all 50 Major cities as child records to each site.  In fact they could be a join table between Sites and Major cities.   

    Then lookup the geocode of the Site down to that child join table and do you Pythagoras math on the children.

    Then you can make a summary of the minimum distance and if you like you can also float up the combined text of the city name where the minimum distance is the distance of that city.

    Post back if you get stuck on how to create all those children records, but you can get the concept working to prove it will work by copying across the 50 cities to one representative site.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------