Forum Discussion

PhilipJones's avatar
PhilipJones
Qrew Member
5 years ago

Find Vendors within a set distance of a Customer Postal Code on the fly

In one of our apps, we receive calls from customer(s) who need some kind of service which could be anything from equipment to pick up service where we act as the broker.  What we would like is the ab...
APIs and custom code
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
I can help you set this up, but the scope of what is required is too much to explain every click.  My method uses Pythagoras https://community.quickbase.com/communities/community-home/digestviewer/viewthread?GroupId=103&MID=30357&CommunityKey=d860b0f8-6a48-487b-b346-44c47a19a804&tab=digestviewer

The essential formula is here.

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)


I can help you one on one if you need help to set a "focus" on a Postal code to then rank up the Vendors by distance.  Contact me by email or my website.  ( I happen to be located in Canada).  We would need to locate a source for the lat long geocodes by postal code or at least the Forward Sortation Area ("FSA") - 1st three characters of the post code.  


------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
PhilipJones's avatar
PhilipJones
Qrew Member
5 years ago
My formula is a derivative of the Haversine formula written using SQL functions.  Our vendor locations are US covering areas from Puerto Rico to Alaska.  Since we are looking for locations within 150 miles or 240 km, then error due to longitudinal changes moving closer or father from the poles or equator are minimized.  This is the most accurate method for distances over 20km and accounts for the curvature more accurately than the pythagorean method.  Nice article here.

I digress, my problem is not so much in how to calculate the distance as to how I would invoke using this formula within Quickbase.  Assume Vendor Location and Location tables have these columns (plus others) where Lat and Long are in degrees or radians and use the appropriate formula for the conversion:

Record_ID      City                State      Postal_CD    Latitude                     Longitude

What I do not know is when I am looking at a Location Record and want to see a report out of Vendor Locations having all records within 150 miles, how would I generate this report.  I will have many Locations in the Locations table that would need results related to the Location Zip.

------------------------------
Philip Jones
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    I  can only help you with native Quick Base and at this time there is no support for the Haversine functions.   But I believe that you could come up with a lookup table to take the Longitude of the Location record and then adjust the "69" in the formula.

    The way I would implement this would be to have a URL formula button to Edit or Create a record in a user focus table fo the current user where the Key field is the current user.   It would set the geocode focus for the current user based on the Location they launched off of as their Focus Postal code.

    Then that Focus can be looked up down to the Vendors table based on a relationship where the reference field is the field

    [Current User]

    with a formula user of

    User()  // ie the current user.



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------