Lat/Long

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
I need to build a Heat Map for our executive team displaying saturation of certain geographically defined areas.  Sadly, QB doesn't allow for a heat map organically, and most tools require the use of Lat/Long in order to render correctly.
Does anyone know of a formula that would convert the organic "Address" field into a Lat/Long?
Thanks,

Mike
Photo of Mike

Mike

  • 554 Points 500 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
You are going to have to use script to do the geocoding of the address.

This code:
async function geocodeAddress(address, key) {
  let response = await fetch(`https://maps.googleapis.com/maps/api/geocode/json?address=${address}`);
  let data = await response.json();
  return data;
}
var address = "1600 Amphitheatre Parkway, Mountain+View, CA";
var key = "";
geocodeAddress(address)
  .then(data => console.log(JSON.stringify(data, null, "  ")));
Returns JSON which includes the lat/lon values::

https://pastebin.com/CZiAy9gd

Notes

(1) You may have to get a key from Google for your planned usage:.

https://developers.google.com/maps/documentation/geocoding/start

(2) If you use a key you have to add it the the URL being fetched
(Edited)
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
If you already have the Lat & Long, you can plug those into the address 1 and address 2 respectively, and qb will map it for you. 

As far as heat maps, they haven't added that update yet, but it is coming.  For now you can add a summary chart for the counts in certain regions if that helps.
Photo of Matthew

Matthew

  • 296 Points 250 badge 2x thumb
What would be the easiest way to run this on existing data in a table? A formula field?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Huh?  Are you asking about Dan's script posting?
Photo of Matthew

Matthew

  • 296 Points 250 badge 2x thumb
I am indeed.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
The only way I know who to do this is to use a 3rd party add on like Juiced who can produce maps and record the geocodes into Quick Base fields so they do not have to be re-geocoded each time. 

Maybe Dan has some other magic, but I thought that you now need to have a Subscription contract with Google to routinely do geocoding.  I thought is no longer a free service from Google although perhaps they allow small volumes for free.  But I think for the maps or the usage of the geocodes to be fast, they need to get calculated once and recorded in Quick Base as geocoding is relatively slow. My experience with Juiced is they are able to geocode at a rate of about 3 per second, which sounds fast, but if you have 1000 addresses on report, that would take 50 minutes! 

But in our use case, we just code Customer Accounts that are not already geocoded, so there may just be a few each day.  So we run a dummy map of accounts not geocoded each day and Juiced writes the missing geocodes into a pair of lat long fields of the customer Account record.
Photo of Matthew

Matthew

  • 296 Points 250 badge 2x thumb
I simply need to convert the address field into lat and lng every time one is added.
Photo of Matthew

Matthew

  • 296 Points 250 badge 2x thumb
I have a subscription to google cloud for many various projects I work on outside of quickbase. So the api key is no issue. I can export the data and re-import it with updated lng/lat information for all existing locations. But if I wanted to update a location as it comes in what would be the best route here without paying a fortune to have an addon installed to quickbase. 
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,242 Points 20k badge 2x thumb
My script does exactly what you want. There is a little additional code that is needed to grab the address from the open form and after geo-coding stuff the lat and lon into fields.

Also many Google services are free and/or don't require a key when used below a rather generous threshold.
Photo of Matthew

Matthew

  • 296 Points 250 badge 2x thumb
Thanks Dan, any guidance on how to get this rolling inside of quickbase? I am unfamiliar with some of the javascript practices and workarounds outside of making buttons inside of qb app.
(Edited)
Photo of Matthew

Matthew

  • 296 Points 250 badge 2x thumb
I suppose I could try the image onload technique or run every added location through zapier parsing the address with the js code to update the record.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,242 Points 20k badge 2x thumb
This is easy to do and I will create a demo to show you how. I just got in town but I will put this on my to do list and hopefully I will post by Friday (I have several demos and questions that I have solutions for but I have been busy with other work).

BTW this is the message you get if you exceed Google's threshold:

{
  "error_message": "You have exceeded your daily request quota for this API. We recommend registering for a key at the Google Developers Console: https://console.developers.google.com/apis/credentials?project=_";,
  "results": [],
  "status": "OVER_QUERY_LIMIT"
}
Photo of Matthew

Matthew

  • 296 Points 250 badge 2x thumb
Something to note, each row in our "locations" table that has an address field is automatically added via zapier already. So nobody is interacting with quickbase, opening pages, or editing to input this information, unless it is wrong or gets updated after the fact during our routine checks and surveys. 

So I may have to implement this in both our zap and on the quickbase application itself.

I would love to go through and batch these locations without exporting them, but if thats not possible its no problem.

Thank you for your assistance.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
Here is a full demo that uses the essential snippet of code I posted previously:


Client Side Snapshot Fields ~ Add New Location
https://haversineconsulting.quickbase.com/db/bnxecizd3?a=nwr

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=673

I would like to explain the name I choose for the application.The application takes a address entered as a single text field and uses Google's Geocoding service to look up the latitude and longitude. You can think of the geocoding process as a function or formula that takes the address value and calculates the latidude and longitude. However, this function is calculated client side (in the browser) once and only once at the time of interacting with the form. If by some other mechanism the address value was changed (say through grid edit) the geocoding process is not reevaluated. This may or may not be an issue depending on your expectations and workflow.

Notes:

1) The example demonstrates two different ways of invoking the geocoding - automatic and manual. However, if you examine the code you will see there is only one difference in the code, namely what event is being responded to (change versus event):
  $("#_fid_6").on("change", function() {...});
  $("a.QBU_Button").on("click", function(event) {...});
The form may appear to operate differently in the two cases but from a coding perspective the code is just responding to two different events (change and click). There are LOTS of event script can respond to so this is a very accommodating way of responding to user behavior

Wiki on HTML Events
https://en.wikipedia.org/wiki/DOM_events#HTML_events

2) To preserve the integrity of the geocoding against accidental change of the latitude and longitude, the latitude and longitude fields were made readonly at the HTML form level:
  $("#_fid_7").attr("readonly", true);
  $("#_fid_8").attr("readonly", true);
  $("#_fid_10").attr("readonly", true);
  $("#_fid_11").attr("readonly", true);
3) The general technique used in this example can be extended to an enormous number of problems and follows a simple three step process:

    a) Grab some field values out of the form in response to some event
    b) Calculate some quantities using script
    c) Paste the calculated quantities into some other fields on the form

Step b) above may involve a complex calculation or algorithm that can be implemented in the formula language or it may involve calls to web services such as Google's geocoding.

4) The dbid and apptoken parameters are in the code as boiler plate although they are not used. The key parameter for Google's Geocoding service is left blank as it is not needed for low volume usage. For high volume usage you will have to supply your own key.

5) When I initially answered this question I was intentionally used a new scripting feature called async / await. These advanced techniques will make you script solutions easier and very short. However, I am limited in the time I have to write up elaborate explanations to every question. Over time you should become comfortable taking the "essential code" of a solution and supplying the necessary boilerplate to form a complete solution.
(Edited)
Photo of Matthew

Matthew

  • 296 Points 250 badge 2x thumb
Thank you for the detailed explanation, for the time being I've configured a zapier task that will geocode using their built in code action to filter any added rows to the database, calculate their coordinates and send it along with the rest of the information since these get added via external forms anyways. Alongside using their coordinates to calculate the time zone with another external API. 

However, this will be extremely useful in updating existing locations within the database, if the address is incorrect or needs to be modified in any way.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,242 Points 20k badge 2x thumb
Keep in mind that Google's Geocoding service can geocode multiple addresses with one API call.