CODEWITHSUNDEEP
google-sheetscoordinatesformularadians
Marc Lonsain
Marc Lonsain

Reputation: 37

Get closest coördinates from a list based in Google Sheets

I have seen a similar question here, but that answer did not to the job for me yet. We have a Google Sheets doc with 46 adresses, and converted longitude and latitude from that location.

Now we would like to be able to give in a location, from which we also get the longitude and latitude and for which we find the closest address based on the coördinates in the list.

Our document is here: https://docs.google.com/spreadsheets/d/1fKBGbRlOX6gw77Nk0S_9ZmsJ-fefM4mA_Ys-Mu-QHIA/edit?usp=sharing

In this file the addresses are shown in Column A, the longitude in column B and the latitude in column C. In Column E we will enter the location and in column F and G the longitude and latitude will be shown. Now in column H it should show the nearest location from the list in Column A.

We have tried:

ZOEKEN(1;1/INTERVAL(0;SIN((RADIALEN(B$2:B$47-F2))/2)^2+SIN((RADIALEN(C$2:C$47-G2))/2)^2*COS(RADIALEN(B$2:B$47))*COS(RADIALEN(G2)));A$2:A$47)

But the location it gives is not the closest upon checking in Google Maps.

Anyone able to help?

Upvotes: -1

Views: 702

Answers (1)

doubleunary
doubleunary

Reputation: 18784

To get the real driving or commuting distances between two addresses, use the GoogleMapsDistance custom function. Note that a driving distance is different from a bee line distance. Your existing formula appears to calculate the latter:

=LOOKUP(1;1/FREQUENCY(0;SIN((RADIANS(B$2:B$47-F2))/2)^2+SIN((RADIANS(C$2:C$47-G2))/2)^2*COS(RADIANS(B$2:B$47))*COS(RADIANS(G2)));A$2:A$47)

The formula seems overly complex. When you are dealing with distances of max a couple hundred kilometers, you can use the Pythagorean theorem instead:

distance² = (startLat - endLat)² + (startLong - endLong)²

A trickier problem is that the custom function you are using does not return numeric latitudes and longitudes. It returns text strings that use period as the decimal mark. Your spreadsheet's locale does not use periods but commas as decimal mark. You should replace those periods with commas with substitute(), like this:

=arrayformula( 
  lambda( 
    addressStart; latStart; longStart; latEnd; longEnd; 
    map( 
      latEnd; longEnd; 
      lambda( 
        latThis; longThis; 
        if( 
          latThis * longThis;
          sortn( 
            { addressStart \ sqrt( (latStart - latThis) ^ 2 + (longStart - longThis) ^ 2 ) }; 
            1; 0; 2; true 
          ); 
          iferror(1/0) 
        ) 
      ) 
    ) 
  )( 
    A2:A; 
    substitute(B2:B; "."; ","); 
    substitute(C2:C; "."; ","); 
    substitute(F2:F10; "."; ","); 
    substitute(G2:G10; "."; ",") 
  ) 
)

See your sample spreadsheet.

Upvotes: 1

Related Questions