Forum Discussion

MikeKlausing's avatar
MikeKlausing
Qrew Assistant Captain
6 years ago

Calculate distance (in Miles) between two zip codes

I have two zip codes that I need to calculate, in miles, the total distance between and populate that in a field on my form. 
Our zip code will remain constant which I have a field for, "[address 1]
There is also a field on my form for an address of the ship to, [address 2] 

Right now I have a formula URL that looks like this: 

"https://www.google.com/maps/dir/"&URLEncode([Address1]) &"/"&URLEncode([Address 2])

This pulls up google maps and calculates distance. 

My question is can I get the 500 miles that google maps calculates to generate back onto my QB form. As of now it appears in google maps just fine, but then I have to manually type that number back into QB.
  • You can download free excels dumps of every zip code
    http://federalgovernmentzipcodes.us/

    That will allow you to geocode your zips.

    Then you can wake up sleeping Pythagoras from 2,500 year ago who will answer your question.

    // In the USA, the typical distance between integer Longitude lines is about 53 miles. (these are the east / west coordinate)   
    // They are further apart at the equator (69 miles) and approach zero at the North Pole.
    // but we have few courses at the north pole or the equator, so lets just call it 53.   
    // In the whole world, its 69 miles between integer Latitude lines.

    // With credit to Pythagoras, we know that for a right angle triangle A^2 + B^2 = C^2.  I will use (A^2 for A squared) 
    // We want to find the length of the C diagonal where A is the North South distance and B is the West East Distance
    // Let LA1 be the LAtitude 1
    // Let LO1 be the LOngitude 2
    // Let LA2 be the LAtitude 1
    // Let LA2 be the LOngitude 2


    // So C^2= A^2 + B^2
    // C = SQRT (A^2 + B^2)
    // C = SQRT ((69*(LA1-LA2))^2 + (53*(LO1-LO2))^2)
    // note that to take a square root you raise it to the power of 1/2 or 0.5

    var number OriginLat    = [Patient Zip Code - Latitude];
    var number OriginLong = [Patient Zip Code - Longitude];
    var number DestLat      = [Consultant Zip Code - Latitude];
    var number DestLong   = [Consultant Zip Code - Longitude];


    var number Distance =
    Round(
    ((69*($OriginLat - $DestLat))^2 + (53*($OriginLong - $DestLong))^2)^0.5
    );

    If($OriginLat =0 or $DestLat=0,0,$Distance)


    • SarahBurres's avatar
      SarahBurres
      Qrew Trainee
      I have attempted this but I must have made a mistake.  The miles should be 3 and I am getting 5000.

      var number OriginLat = [LatitudeVendor];
      var number OriginLong = [LongitudeVendor];
      var number DestLat = [LatitudePlant];
      var number DestLong = [LongitudePlant];
      var number Distance = Round(((69*($OriginLat - $DestLat))^2 + (53*($OriginLong - $DestLong))^2)^0.5);

      If($OriginLat =0 or $DestLat=0,0,$Distance)


      ------------------------------
      Sarah Burres
      ------------------------------
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      Thx for the thx but really we should give credit to where credit is due.  The purists would a say that to calculate the distance between points you need to use more advanced formulas using SIN and COSIN to account for the curvature of the earth, but for we members of the flat earth society, Pythagoras is surely good enough.