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 ability upon entering the customer location (Postal Code in the Location table record) getting back a list of the closest vendor locations (from the Vendor Location table) for service within 150 miles PLUS a list at the bottom for all National Vendors (which is a checkbox on the records in the Vendor table.

We have four key tables that I need to use for the solution:
  1)  Locations (which would be the customer location where service is required)
  2)  Vendor (which is the Parent table for Vendor Locations)
  3)  Vendor Location (which is the Child table for Vendors)
  4)  Postal Code (which is related to Locations and Vendor Location on Postal Code to get the latitude and longitude values)

The formula to calculate the distance is:
(CEILING(2 * 3961 * asin(sqrt(power(sin(radians((VLoc.[Latitude] - Loc.[Latitude]) / 2)),2) + cos(radians(Loc.[Latitude])) * cos(radians(VLoc.[Latitude])) * Power(sin(radians((VLoc.[Longitude] - Loc.[Longitude]) / 2)),2)))*1.15))

I was hoping that I could create a report to do this for me, but I am thinking there is more to this.  Even if I simplify the request to just calculating and pulling only the Vendor Location records in one report, then the National Accounts are just a basic report I can create on the Vendors table since they are not dependent on distance calculation.

 

Any ideas how we might do this within Quickbase if possible?  I am sure it could be done outside of Quickbase probably using Webhooks to bring back results.  I would like to avoid this if possible as I already have a tool users can use that runs a SP on SQL to get back results in an Excel file.



------------------------------
Philip Jones
------------------------------
APIs and custom code

3 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    5 years ago
    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
        Qrew Champion
        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
        ------------------------------