0

I know, one of the best scripts to calculate the distance between two locations is the VBA-Script I have found here....

Now I have tried to "convert" this script from VBA to Google Apps Script. Even I have tried really to convert it step by step but I get a completely different (wrong) result and I do not see the reason for that. What is wrong with the code? I know there are other functions delivering the result. But none of these is as accurate as this one.

function toRad(degree){
return degree * Math.PI / 180;
}

function quadrat(quadrat){
return Math.pow(quadrat,2);
}


function distances(lon1, lat1, lon2, lat2) {  

  var EPSILON = 0.000000000001;
  var low_a = 6378137;
  var low_b = 6356752.3142;
  var f = 1 / 298.257223563;
  var L = toRad((lon2 - lon1));
  var U1 = Math.atan((1 - f) * Math.tan(toRad(lat1))); //Atn = Atan!!!
  var U2 = Math.atan((1 - f) * Math.tan(toRad(lat2))); //Atn = Atan!!!

  var sinU1 = Math.sin(U1);
  var cosU1 = Math.cos(U1);
  var sinU2 = Math.sin(U2);
  var cosU2 = Math.cos(U2);

  var lambda = L;
  var lambdaP = 2 * Math.PI;
  var iterLimit = 100; // can be set as low as 20 if desired.

  while ( (Math.abs((lambda - lambdaP)) > EPSILON && iterLimit > 0)){
    iterLimit = (iterLimit - 1);
    var sinLambda = Math.sin(lambda);
    var cosLambda = Math.cos(lambda);
    var sinSigma = Math.sqrt( (quadrat((cosU2 * sinLambda))  + quadrat(((cosU1 * sinU2) - (sinU1 * cosU2 * cosLambda)))) );

    if (sinSigma == 0){
      var distances = 0; //co-incident points
      break;
    }
    var cosSigma = ((sinU1 * sinU2) + (cosU1 * cosU2 * cosLambda));
    var sigma = Math.atan2(cosSigma, sinSigma);
    var sinAlpha = ((cosU1 * cosU2 * sinLambda) / sinSigma);
    var cosSqAlpha = (1 - (sinAlpha * sinAlpha));

    if (cosSqAlpha == 0){ //check for a divide by zero
      var cos2SigmaM = 0; //2 points on the equator
    }
    else{
      cos2SigmaM = (cosSigma - ((2 * sinU1 * sinU2) / cosSqAlpha));
      }
    var c = f / 16 * cosSqAlpha * (4 + f * (4 - 3 * cosSqAlpha));
    lambdaP = lambda;
    var P1 = (-1 + (2 * quadrat(cos2SigmaM)));
    var P2 = (sigma + (c * sinSigma * (cos2SigmaM + (c * cosSigma * P1))));
    lambda = (L + ((1 - c) * (f * sinAlpha * P2)));
    }

  var uSq = cosSqAlpha * (quadrat(low_a) - quadrat(low_b)) / quadrat(low_b);

  P1 = (4096 + uSq * (-768 + uSq * (320 - 175 * uSq)));
  var upper_A = 1 + uSq / 16384 * P1;
  var upper_B = uSq / 1024 * (256 + uSq * (-128 + uSq * (74 - 47 * uSq)));
  P1 = (-3 + 4 * quadrat(sinSigma)) * (-3 + 4 * quadrat(cos2SigmaM));
  P2 = upper_B * sinSigma;
  var P3 = (cos2SigmaM + upper_B / 4 * (cosSigma * (-1 + 2 * quadrat(cos2SigmaM)) - upper_B / 6 * cos2SigmaM * P1));
  var deltaSigma = P2 * P3;
  var S = low_b * upper_A * (sigma - deltaSigma); 
  var finalresult = +S.toFixed(3);

  return finalresult; 
}

By the way...this one is working but it is not as accurate as the VBA-script.

function distVincenty(lon1, lat1, lon2, lat2) {

  var R = 6371000; // radius of the earth in meters, https://en.wikipedia.org/wiki/Earth_radius
  var dLon = (lon2-lon1) * Math.PI / 180; // Convert degrees to radians
  var dLat = (lat2-lat1) * Math.PI / 180; // Convert degrees to radians
  var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
          Math.cos(lat1 * Math.PI / 180 ) * Math.cos(lat2 * Math.PI / 180 ) *
          Math.sin(dLon/2) * Math.sin(dLon/2);
  var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
  var d = R * c;

  // Distance in meters, rounded to an integer.
  return d
}
Dirk
  • 145
  • 1
  • 11
  • Possible duplicate of [Calculate distance between two latitude-longitude points? (Haversine formula)](https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula) – Cooper Oct 24 '19 at 05:46
  • I am not able to run VBA code - could you please describe more accurately which problem are you having? At what point is your script beginning to yield inaccurate results? Please consider checking out the following link, it may help you: https://developers.google.com/apps-script/guides/support/troubleshooting#debugging – carlesgg97 Oct 24 '19 at 07:23
  • It starts with ``var sinSigma = Math.sqrt( (quadrat((cosU2 * sinLambda)) + quadrat(((cosU1 * sinU2) - (sinU1 * cosU2 * cosLambda)))) );`` This delivers different results. – Dirk Oct 24 '19 at 15:24
  • When I put sinSigma outside the WHILE-Loop, the results do match. There seems to be something wrong with the WHILE-Loop but I do not know what. – Dirk Oct 24 '19 at 15:55
  • @Dirk Have you resolved your problem? – Tedinoz Nov 02 '19 at 23:06

0 Answers0