Is there a formula within Quickbase that would calculate the miles from one point to another if the latitude and longitude were supplied for two locations?

You have to use JavaScript because the formulas involve trigonometric functions that are not supported in QuickBase's formula language. Here is one implementation of the Haversine formula:

http://stackoverflow.com/questions/27928/how-do-i-calculate-distance-between-two-latitude-longitude-...

Actually, I am a member of the flat earth society and hence I am not bothered by false rumors that the earth is round. It is, in fact flat, and all distances are traveled how crows fly. Hence we do not need a formula which deals with the purely fictional hypothesis that the earth is round.

So, we just need to recall our grade 10 math and wake up those sleeping memories of Pythagoras.


In addition, I'm assuming, Kristi, based on the odds of where QuickBase is used, that you are in the USA and based on the odds again of US businesses, you probably only have records relating to a portion of the USA. if you tell me your general business area (States) I can confirm the correct estimate for the distance between Longitude Lines based on how far north or south your locations generally are.




Here is a formula I used to calculate and hence be able to rank the distances from a Patient to various clinics which offered the tests needed. You should be able to paste this in and just edit the four formula variables for the origin and destination Lats and Longs.


// 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 Patients 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)

You got to be kidding me! If you want a flatland approximation this formula would have better results because you can specify the mean latitude (ie phim) and calculate the delta lat/lon between the two points:

Spherical Earth Projected To A Planehttp://en.wikipedia.org/wiki/Geographical_distance#Spherical_Earth_projected_to_a_plane

delta latitude (delta phi) delta longitude (delta lambda) mean latitude (phim)
Try this formula where phim it taken as the mean latitude of the continental United States:
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 cosphim = 0.76791099268; var number radius = 3963.1676;
$radius * (($OriginLat - $DestLat)^2 + ($cosphim * ($OriginLong - $DestLong))^2)^0.5
The GEOGRAPHIC CENTER of the UNITED STATES LAT. 39�50' LONG. -98�35'
39 degrees 50 arcminutes = 0.695222819 radians cosphim = cosine(0.695222819) = 0.76791099268
Radius of Earth in Miles: 3,963.1676

Right, if only QuickBase would come up with some new native functions like SIN and COS I would go that way with a more complicated formula.  I suppose that I could load up a table for the values of SIN and COS for each degree or radian but that is too much work for my needs.  For the two projects I did for clients it was all about ranking the options by distance to know which clinics were closest to the Patient or which Teachers were closest to the course locations.

>I suppose that I could load up a table for the values of SIN and COS for each degree or radian but that is too much work for my needs.

You could build a relationship between the table that contained the angle field and the sine and cosine tables.

On the other hand just replace the formula language with JavaScript and it will solve all these problems. Then you could freely calculate FFT, eigenvalues, splines, linear/quadratic programming problems etc:

http://www.numericjs.com/documentation.html

I recall eigenvalues from Engineering.  I also recall that they were really difficult.

Actually I re-read your post for the flat earth approximation and its cool. I like the concept of using the geographic center of the US as a starting point. Thank you.

>I like the concept of using ...

They are essential the same formulas perhaps with slightly different coefficients. If you wanted to you could evaluate the accuracy of these various formula by running sample points through the formulas and comparing them to the results of an online calculator:

 http://www8.nau.edu/cvm/latlongdist.html

FWIW here is a comparison of the coefficients when the formulas are aligned to the same structure:

((62.9536940934*($OriginLat - $DestLat)^2 + (48.3428337241*($OriginLong - $DestLong))^2)^0.5

((69*($OriginLat - $DestLat))^2 + (53*($OriginLong - $DestLong))^2)^0.5

Hmmmm, so my 69 figure is wrong?  I'm not understanding why  http://geography.about.com/library/faq/blqzdistancedegree.htm

You could check my calculations but the coefficients reflect a curve fit in two dimensions AND the fact that we are assuming "small" deviations from the center of the continental United States.

I calculate the first coefficient as sqrt(radius) =sqrt(3963.1676) = 62.9536940934

I calculate the second coefficient as sqrt(radius) * cosphim = 62.9536940934 * 0.76791099268 = 48.3428337241

I don't have time right now to check the math but there might be a discrepancy to check out with some test cases.

I'm sure that Kristi got more than she expected, in terms of discussion when she posted her question.

Mark, this solution worked great!   Thanks for providing this information to the QB community.   

Jim