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
------------------------------

markshnier__you · Answer

Jeff, thx for the mention.
There is not a native QuickBase function to geocode an address. So I know of two methods. One is lookup in the Internet and enter manually. &nbsp;The other would be to install a 3rd party app from Juiced for their Maps add on, since when you draw the map, it will write any missing geocodes to a pair of Lat Long fields in the Record. So that's an option where there is a steady flow if new addresses to geocode.&nbsp;If you can find some free service which would respond to an Web Service &nbsp;call, a Pipeline can send it and listen for the answer.Now, having said all that, I recall an app I recently did where employees need to enter mileage for expense reimbursements &nbsp;I use this URL formula in Add mode on a record to open a new window to show the exact driving distance the user enters the addresses and then clicks the button to visually see the distance and then keys it into the record while still in add mode.&nbsp;List("/",&nbsp;"https://www.google.com/maps/dir",URLEncode([From Address]),URLEncode([To Address]))
------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

scottburday__tr · Answer

In case you are interested, Trinity develops custom processes to work with Quickbase using actual distance and time calculations. This includes:

- Simple point-to-point distance calculations
- Lots of different types of mapping processes
- Actual routing and scheduling of teams for efficient driving
...and lots more

Here is a quick video of a recent implementation of distance and time calculation: https://www.screencast.com/t/hVkEIPf0
Here is a video of a more complex routing and scheduling demo. While you are looking at a custom interface, all the functionality is developed right into the Quickbase app with API calls to Google Maps: https://www.screencast.com/t/xTWOgyb5t

If you are interested in discussing further I can be reached at sburday@trinityis.com

Thanks.

------------------------------
Scott Burday, Trinity
------------------------------

jeffpeterson1 · Answer

We use a pipeline and the API to a service called Geocod.io to automatically geocode new records in our system.&nbsp; &nbsp;Free and pretty easy to setup.&nbsp; &nbsp;
Of course, the same problem exists, which is that you can really only get 'as the crow flies' measurements without using an add-on.------------------------------Jeff Peterson------------------------------

jeffpeterson1 · 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
------------------------------

jeffreyweide · Answer

Thanks, Mark and Jeff. I think that the URL formula might be something good to try out because that will just allow my logistics coordinator to click a link to get the mileage for reimbursements instead of having to have two screens open and trying to copy/type addresses. I did see Mark's original formula that you included, Jeff. I think that will be the way to go if they want to do the straight-line (as the crow flies) distance. It will be helpful to see if they qualify for reimbursement, but I can see that not being beneficial if it is over Colorado mountains were it is far from a straight-line :)------------------------------Jeffrey Weide------------------------------

Forum Discussion

Calculating Distance from 2 Address Fields