Calculating Distance from 2 Address Fields

Question

I have seen a lot of posts that partially answer elements I want to do, and I am relatively new to QuickBase (not necessarily databases or analytics, though). I am trying to calculate the distance between a worksite and a contractor's address to determine reimbursed mileage. So a couple of questions/asks to help:

1) It appears there is not a function or API plug-in to do actual driving mileage. Is that correct?
1)a) If there is a function/API, I have not found it. Does anyone have a good resource for it?
2) If there is not a function/API, I noticed a few posts on JavaScript formulas to calculate a Haversine distance formula. I just need approximates for budgeting purposes, not actual reimbursements (since they do this through their contractor payroll and screenshots).
2)a) Is the Haversine relative accurate to driving mileage from anyone that has used it in the past?
2)b) How do you pull Latitude/Longitude from address fields to do the Haversine calculation? I think I am just missing how to do it from other posts in here.

------------------------------
Jeffrey Weide
------------------------------

SSNJeffP · Answer

There is a formula to do this but it's "As the crow flies" not driving mileage.

To do it, you need the Latitude and Longitude of both places and you can use this formula (Credit: Mark Shnier (YQC)) to get the distance:

// 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.
// We will use a formula to estimate that value
// 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 = [Origin Lat];
var number OriginLong = [Origin Long];
var number DestLat = [Destination Latitude];
var number DestLong = [Destination Longitude];

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

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

------------------------------
Jeff Peterson
------------------------------

Sign up

Login to the community