Calculate Distance Between Two Addresses

I categorized this post under “support” because I suspect it can be accomplished without any additional functionality from Airtable but, if not, it may be more appropriate as a “feature request.”

Is it possible to have a field that calculates the distance (in miles/kil) between two address fields? I am trying to create a base to log miles driven for business since the IRS requires a milage log to take advantage of the “standard mileage deduction.”

5 Likes

I don’t know any formula function to to this. Also, I think it can’t be done with Blocks.

1 Like

It appears to be a simple Zapier task but requires a paid Zapier account. (I say ‘appears’ because I was able to build a three-stage Zap — trigger on new Airtable record; issue request to Google Maps Distance Matrix API and receive response; update Airtable record. Component testing worked fine, but to perform an end-to-end systems test, I need a paid Zapier account.)

Essentially, I defined an Airtable table with three fields: {Origin}, {Destination}, and {Distance}. ({Origin} and {Destination} are single-line text; {Distance} is number.)

I then configured a three-step Zapier webhooks zap:

Trigger

Airtable account, ‘Distance test’ base, [Test] table
Trigger on new record

Action

Zapier webhook Get action.
URL: https://maps.googleapis.com/maps/api/distancematrix/json
Query string parameters:
units | imperial
origins | {Origin}
destinations | {Destination}
key | [My Google API key]

Send as JSON: no
Unflatten: yes

Action #2

Airtable account, ‘Distance test’ base, [Test] table
Action: Update record
Record: Use a custom value
Airtable: New record: Record ID
Set {Distance} to Zapier ‘Get’: Rows elements distance value

This sets {Distance} to the distance between the origin and destination addresses in meters; you’ll need to convert to miles for the IRS.

3 Likes

Hmmm, ok thanks for the detailed response! I also don’t have a paid Zappier account and this issue isn’t quite worth it at this time.

That’s what I thought. I think it would require a custom function option.

Does Zapier allow you to share zaps? Because I would love to test this but all that code scares me :rofl:

1 Like

I don’t think so – well, except among members of a workgroup. I think I later built and tested such a Zap – you might find it documented in another post of mine. I’m in the [slow] process of sorting through the 300+ bases in my workspace and 1200+ Community posts and attempting to put the useful ones into some sort of resource base I can publish. I’ll see if I can find the distance code – which could easily be tied to one of the 60+ tables in one of my ‘scratch’ code testing bases – and share it.

Thanks a lot for the instructions. I used it and tested it (I took a paid zapier account to test it) and it seems to work, however I wonder if I understood right that entering two addresses in AirTable should result in the distance and the duration (I added the duration).

It does so for the tests generated by Zapier, but when I type an address, nothing happens. I am assuming something incorrectly? Please see below.

Any suggestions that might solve this issue are very welcome!

I came back to check and ‘it worked’, however the address are rewritten. That is a bit confusing. And there is something wrong with the distance and the timing (a small village in The Netherlands to Brussels in Belgium). Any the conclusion is that setting up a link between Google Maps and AirTable with Zapier works, but for the moment not as I had in mind.

The issue about the address (to avoid it gets rewritten) I solved by adding a few extra steps, but the distance and timing problems remain.

question
I wonder how we can check the correctness of the distance and duration provided by the API? What extra steps do I need ?

As you can see the Royal houses of The Netherlands (The Hague) and Belgium (Brussels) only need one hour. Even for a royal that is fast…

That delay is the latency in Zapier. Airtable does not support webhook events, so Zapier must poll for changes in your data before it can make a computation and update the record.

Which API specifically are you referring? Google Maps (via Zapier)?

My API framework shows 2hrs 46 minutes and 263 km. Note - this was computed at approx 1a UTC. The start and end addresses I fed it are as follows but it [apparently] extrapolated a deeper address meaning automatically:

  • Start: “Voorzittersstraat 34, Brussel, Belgium”
  • End: “Fortstraat 14, The Netherlands”

image

Thanks @Bill.French for your input, it is a great start of the day to have valuable feedback coming in!

I was not aware of the AirTable limits related to webhook events. Is there a way we can speed up the process?

I use the API as advised by @W_Vann_Hall:

URL: https://maps.googleapis.com/maps/api/distancematrix/json

Which one did you use?

Is it this one you @Bill.French used : https://maps.googleapis.com/maps/api/directions/json

Incorrectly, both Airtable and Zapier refer to their integration glue as a “webhook”. The correct way to describe these processes is “polling”. Airtable does not support events and webhook posts - two things that make it possible to build near-real-time integration processes. Zapier (and other integration platforms) are forced to mimic webhooks by constantly polling for changes and that’s where it gets dicey.

Detecting changes in records is not always a perfect science. As such, the only way to reduce the latency is to speed up the polling process. With Airtable this can also get you into a little bind because of the API request limitations.

I believe we are using the same API, but I suspect its outcomes are being applied differently.

I use the integrated Google Maps SDK in Google Apps Script - see code below. From this, I can extract vastly more data about geo-processes than you probably have available using Zapier.

Plus, I can also perform geometry computations in javascript with ease - example:

function getDistance(c1, c2, opt)
{
  
  var lat1 = rad(c1.lat), lat2 = rad(c2.lat);
  var lng1 = rad(c1.lng), lng2 = rad(c2.lng);
  var dLng = (lng2-lng1), dLat = (lat2-lat1);
  var R = 6371/1.6;
  var a = Math.sin(dLat/2) * Math.sin(dLat/2) + 
    Math.sin(dLng/2) * Math.sin(dLng/2) * 
    Math.cos(lat1) *  Math.cos(lat2); 
  var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a)); 
  return parseInt(R * c);
}

function rad(degrees) {
  return degrees * Math.PI/180;
}

The following function computes the driving directions for the stated addresses. I reviewed the outcome on the map and the distances and times all seem reasonable, but I let you be the judge of the accuracy. I’ve published a log of the computed directions.

//
// get directions
//
function getDirections()
{
  var directions = getDirections_("Voorzittersstraat 34, Brussel, Belgium", "Fortstraat 14, The Netherlands");
  var steps = directions.steps;
  for (var i in steps)
  {
    Logger.log(steps[i].duration.text + " :: " + steps[i].distance.text);
    Logger.log(steps[i].html_instructions.replace(/<[^>]+>/g, ""));
    Logger.log("");
  }
  return(true);
}

function getDirections_(city1, city2) {
  var directions = Maps.newDirectionFinder()
    .setOrigin(city1).setDestination(city2)
    .setMode(Maps.DirectionFinder.Mode.DRIVING)
    .getDirections();
  var d = directions.routes[0].legs[0];
  return(d);
}

Driving directions…

1 min :: 0.1 km
Head northeast on Rue du Président/Voorzittersstraat toward Keienveldstraat/Rue Keyenveld

1 min :: 84 m
Turn right onto Keienveldstraat/Rue Keyenveld

1 min :: 0.2 km
Turn right onto Rue de l'Arbre Bénit/Gewijde-Boomstraat

1 min :: 0.2 km
Turn right onto Rue Mercelis/Mercelisstraat

1 min :: 0.1 km
Turn right onto Avenue Louise/Louizalaan

1 min :: 61 m
Slight left toward Stefaniatunnel/Tunnel Stéphanie/N24

1 min :: 0.6 km
Merge onto Stefaniatunnel/Tunnel Stéphanie/N24

1 min :: 0.3 km
Take the ramp to Port de Namur/Naamsepoort

1 min :: 0.6 km
Merge onto R20

1 min :: 0.3 km
Exit onto Avenue des Arts/Kunstlaan

2 mins :: 0.6 km
Turn right onto Rue Belliard/Belliardstraat

1 min :: 0.1 km
Keep left to continue on Rue Belliard/Belliardstraat/Belliardtunnel/Tunnel belliardContinue to follow Rue Belliard/Belliardstraat

1 min :: 0.6 km
Continue onto Kortenbergtunnel/Tunnel de Cortenbergh/N23/N3

3 mins :: 2.4 km
Continue straight onto Kortenbergtunnel/Tunnel de Cortenbergh/N23

1 min :: 93 m
Keep left at the fork to continue toward E40

2 mins :: 3.9 km
Continue onto E40

1 min :: 1.1 km
At the interchange Sint.-Stevens-Woluwe, keep right and follow signs for E19/Luchthaven Zaventem/Gent/Antwerpen/E40

1 min :: 0.2 km
Merge onto R0

2 mins :: 3.6 km
Merge onto E40/R0

1 min :: 0.6 km
At the interchange Machelen, keep right and follow signs for Antwerpen/Mechelen

19 mins :: 34.0 km
Merge onto E19

1 min :: 0.7 km
At the interchange Antwerpen-Zuid, keep right and follow signs for Breda/Luik/Haven 1-999

2 mins :: 3.3 km
Merge onto E19/E34

4 mins :: 5.9 km
Keep left at the fork to continue on E19, follow signs for Ring Antwerpen/Breda/Haven

6 mins :: 10.7 km
Keep left at the fork to stay on E19

14 mins :: 24.3 km
Keep left to stay on E19Entering the Netherlands

2 mins :: 4.1 km
Continue onto A16/E19

1 min :: 0.7 km
At the interchange Breda-Oost-Knooppunt Galder, keep right and follow signs for E312/A58 toward A27/Breda-Oost/Utrecht/Tilburg

15 mins :: 25.8 km
Continue onto A58/E312

3 mins :: 5.9 km
Keep left at the fork to continue on A65, follow signs for Tilburg-Noord/Oisterwijk/'s-Hertogenbosch

12 mins :: 12.7 km
Continue onto Bosscheweg/N65Continue to follow N65

1 min :: 1.7 km
Continue onto A65 (signs for 's-Hertogenbosch/Eindhoven/Nijmegen)

1 min :: 1.6 km
At the interchange Knooppunt Vught, follow signs for A2/E25 toward Nijmegen/Utrecht/Ring 's-Hertogenbosch

1 min :: 0.3 km
Take the N279 exit toward St.Michiels-gestel/Schijndel/Waalwijk/Rosmalen/Nijmegen/Veghel

1 min :: 2.0 km
Keep left to continue toward A59

1 min :: 1.7 km
Keep left, follow signs for Nijmegen/A59/Rosmalen

10 mins :: 17.4 km
Continue onto A59

21 mins :: 37.7 km
Merge onto A50

4 mins :: 6.4 km
At the interchange Knooppunt Grijsoord, keep right and follow signs for E35/A12/A50 toward Arnhem/Apeldoorn/Zwolle/Doetinchem

12 mins :: 20.0 km
Keep left at the fork to continue on A12

1 min :: 1.2 km
At the interchange Knooppunt Oud-Dijk, keep right and follow signs for A18 toward Doetinchem/Enschede

12 mins :: 21.7 km
Continue onto A18

1 min :: 1.2 km
Turn right onto Twente-Route/N18

5 mins :: 5.1 km
Turn left onto Zelhemseweg/N330Continue to follow N330

1 min :: 0.5 km
Turn right onto Fortstraat

1 min :: 0.1 km
Turn left to stay on Fortstraat

thanks @Bill.French, it is very accurate. I often took these roads many years ago while living in Brussels.

you wrote;

I use the integrated Google Maps SDK in Google Apps Script - see code below. From this, I can extract vastly more data about geo-processes than you probably have available using Zapier.

I don’t have a clue how to set this up :wink: That is why I use Zapier, but it seems it comes with a price.

You obviously have a GMail account which means you also have a vast infrastructure of serverless containers running at Global scale. :wink:

Every Google document (spreadsheet, presentation, or doc) are functionally [each] Docker containers running on servers that you needn’t even think about. Under each of these files is Google Apps Script (and 18 SDKs) and this environment requires no setup, no deployment process, no dev-ops skills to take full advantage of. Read about it here.

Zapier and Integromat provide a lot of useful integration capabilities and they make it possible for great innovative thinking. But they have ceilings like any technology. My work is biased because I tend to help businesses get to integration levels that are far beyond basic exchanges of data.

I also work for clients who have intellectual property requirements - they must own their tech stack because they regard these integrations as central to their value. Another aspect to consider - instrumenting business logic external to a given IT solution (or product) is not ideal for organsations where the makers of these solutions may change jobs. Imagine you’re the new guy and you have no clue that Zapier is a critical cog in a solution for which you are now the custodian.

For the moment Zapier should do the job with AT seen my kind of work, I am not on the tech level you @Bill.French work and I am afraid it is not for soon. My conclusion for the moment is that the proposed solution by @W_Vann_Hall works but does not provide good enough outcomes for me. It could be that my config fails somewhere or that we should use https://maps.googleapis.com/maps/api/directions/json instead of ‘distancematrix’

My last test was between two jails, one in Ghent and one in Brussels:

Nieuwewandeling 89, 9000 Gent, Belgium
Ducpétiauxlaan 106, 1060 Sint-Gillis, België

And below the outcome (…), my gut feeling is that I do something wrong seen the outcome that is not just a bit out of scope

I show this as ~53 minutes and 67km so yeah - this doesn’t seem to be working well in Zapier. I’ll bet there’s a way to tweak it though. Have you tried this one?

1 min :: 0.4 km
Head northeast on Nieuwewandeling/N430 toward Elsa Guyssenspad

1 min :: 59 m
Turn left onto Jan van Hembysebolwerk

1 min :: 59 m
Make a U-turn

2 mins :: 0.7 km
Turn right onto Nieuwewandeling/N430

1 min :: 0.5 km
Turn right onto Einde Were/R40Continue to follow R40

2 mins :: 1.8 km
Turn left onto Drongensesteenweg/N466Continue to follow N466

1 min :: 0.2 km
Turn right onto the R4 ramp

2 mins :: 2.4 km
Keep left and merge onto R4

1 min :: 0.7 km
Take the exit toward E40/E17/N43

1 min :: 0.8 km
Merge onto Adolphe Pégoudlaan/B402

25 mins :: 45.5 km
Keep left at the fork, follow signs for E40/Brussel/E17 and merge onto E40

5 mins :: 8.6 km
At the interchange 11-17-Groot-Bijgaarden, keep right and follow signs for E19 toward Bergen/Charleroi

1 min :: 1.6 km
Take exit 17-Brussel-Centrum-Bruxelles-Centre toward Bruxelles-Centre/Brussel-Centrum

1 min :: 0.2 km
Merge onto Rue de la Bienvenue/Verwelkomingsstraat

1 min :: 0.6 km
Continue onto Boulevard Industriel/Industrielaan/N266

1 min :: 0.8 km
Slight right onto Boulevard de l'Humanité/Humaniteitslaan

1 min :: 0.4 km
Turn right onto Rue du Charroi/Gerijstraat

1 min :: 0.4 km
Turn left onto Avenue du Pont de Luttre/Luttrebruglaan

2 mins :: 0.6 km
Continue onto Avenue Wielemans Ceuppens/Wielemans Ceuppenslaan

1 min :: 0.4 km
At the roundabout, take the 2nd exit onto Avenue Reine Marie-Henriette/Kon. Maria-Hendrikalaan

1 min :: 0.4 km
At the roundabout, take the 3rd exit and stay on Avenue Reine Marie-Henriette/Kon. Maria-Hendrikalaan

1 min :: 0.5 km
At the roundabout, take the 3rd exit onto Avenue Besme/Besmelaan/N241Continue to follow N241

1 min :: 0.1 km
Turn left onto Rue Eugène Verheggen/Eugène Verheggenstraat

1 min :: 0.2 km
Turn right onto Avenue Ducpétiaux/Ducpétiauxlaan/N242Destination will be on the right

thanks again @Bill.French, I’ll check this one out one the days to come. First I’ll test if ZAP that rewrites the address as coordinates might be a solution AND / OR I’ll check if an other API might not provide a more accurate outcome. A lot to do :wink: