Skip to main content

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

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


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, sTest] 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 | kMy 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.


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, sTest] 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 | kMy 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.


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.


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


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


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, sTest] 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 | kMy 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.


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


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


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.


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, sTest] 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 | kMy 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.


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!


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.


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…




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)?


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…






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 tapparently] extrapolated a deeper address meaning automatically:





  • Start: “Voorzittersstraat 34, Brussel, Belgium”


  • End: “Fortstraat 14, The Netherlands”









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)?


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(stepsei].duration.text + " :: " + steps:i].distance.text);

Logger.log(stepsei].html_instructions.replace(/<e^>]+>/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.routest0].legsu0];

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 :winking_face: That is why I use Zapier, but it seems it comes with a price.


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 leach] 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.




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 yeach] 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



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:


I checked with the Latitude and Longitude coordinates and that provides better results, but not good enough. I guess it shows the distance from point to point as a straight line and thus not via the routes we use to take.





So the next step is to replace the API, I am curious if this might work.


me gustaría que me ayudaran a desarrollar este flujo con zapier. ya que necesito medir dos puntos de coordenadas y obtener la distancia.


Hello,



I am trying to do the same thing. I want to calculate the distance between 2 addresses and I don’t have the coordinates (lat,long)





@Bill.French : Should I copy what you wrote and paste it somewhere ? By any chance would you have a tuto or video of the steps?


You mention the word ‘script’. Should I build a custom app based on what you wrote?


If so, which type of app should it be?




Thanks in advance for your help ! 🙂


Hello,



I am trying to do the same thing. I want to calculate the distance between 2 addresses and I don’t have the coordinates (lat,long)





@Bill.French : Should I copy what you wrote and paste it somewhere ? By any chance would you have a tuto or video of the steps?


You mention the word ‘script’. Should I build a custom app based on what you wrote?


If so, which type of app should it be?




Thanks in advance for your help ! 🙂




To do this without lat/lng coordinates you can use the Google Map API that allows you to provide the two addresses and Google will figure out the distance for you.





Um, yes, but only if you have the lat/lng coordinates. This code would need to be modified significantly to accommodate your requirements.





Unfortunately, no.





Probably not. A custom app requires extensive React javascript experience and I get the sense this would be outside your wheelhouse. I recommend you find a good developer on this community to build what you need.


I have a database with many places and their coordinates and I’m looking for a script to know all the places within x kilometers from one place.



Do you know if this is possible with Airtable?



Thanks in advance.



Thibaud -






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(stepsei].duration.text + " :: " + steps:i].distance.text);

Logger.log(stepsei].html_instructions.replace(/<e^>]+>/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.routest0].legsu0];

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



Hello it would be helpful if you can tell me what ‘opt’ value is. Thanks in advance.


Reply