May 23, 2020 11:17 AM
I’m trying to solve for an automation that will lead me to finding physical mailing addresses. I’m mostly wondering if there are blocks or other ways to reach public latitude and longitude converters that will feed the address back to Airtable.
More specifically, I’m using the Clearbit block and their data only gives you latitude and longitude coordinates. I’m basically just looking to see what I can do automatically with those coordinates to populate the actual address in my table.
Super grateful for this community and any ideas that may help here.
May 23, 2020 11:47 AM
Welcome to the community, @Logan_Lenz! :slightly_smiling_face:
You can’t do this directly with Airtable on its own, but you could set this up via an Integromat automation with Google Maps.
Integromat can grab an address from your Airtable system, send it to Google Maps, retrieve geographic coordinates (latitude and longitude), and then place those coordinates into your Airtable system.
p.s. I’ve also sent you a private message, in case you need to hire an Airtable consultant like myself to help you set this up.
May 24, 2020 06:43 AM
@ScottWorld, I think @Logan_Lenz is looking for exactly the opposite of your suggestion. I believe the objective is –
… given a lat/lng, what is the nearest street/mailing address for that point?
At Stream It, we do this in real-time for transit vehicles which always know their lat/lng, but lack any reference to the street address which is a requirement for most first response systems. Our platform must be able to alert law enforcement and emergency response teams when our cameras detect a safety incident or when a driver presses the panic button.
And as @ScottWorld suggests, the Google Maps API is able to reverse the lookup polarity and provide you with the address based on the geo-location. It isn’t always precise, but it will likely get the job done.
In the Google Apps Script environment we use the reverseGeoCode() method to do this. Pass a lat/lng, get an address. Implementing this in Airtable is a bit tricky; it requires a call to the API from either an external app that reads your locations and writes back the address fields. Another approach - create a Script Block to perform these actions.
In my work, we perform this process repeatedly - almost 200,000 times per day, and this is where the Maps API quota gets ugly as the free tier allows only 500 calls per day (I think). We totally avoid this by caching into a compressed index all previous lat/lng values and addresses. Over a period of time - for example - we have accumulated about a half-million addresses via lat/lng in the LA basin area and not once did we have to write a check to Google for geo-encoding fees.
Building this in a Script Block would be a good exercise, although you’d have to run it manually from time-to-time to keep the data updated. Google Apps Script also provides a way to automate this - a process that involves a script that looks for any empty address fields in your table and then leaps into action to fill those empty fields. Such a process could run every five minutes to sustain a fully updated table of addressed based on lat/lng and you can implement a max transformation counter to avoid Maps API charges.
Feb 04, 2023 04:57 PM
I am curious if the OP solved their issue. If not I can also build an extension that can reverse decode lat/lng to address from any table and add a new column with the address