Help

Re: Calculate Distance Between Two Addresses

5786 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Derek_Clark
6 - Interface Innovator
6 - Interface Innovator

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.”

26 Replies 26

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?

Christiaan_Huiz
6 - Interface Innovator
6 - Interface Innovator

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
Christiaan_Huiz
6 - Interface Innovator
6 - Interface Innovator

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 :winking_face: 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. :winking_face:

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

two jails in Belgium

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 :winking_face: