Map block is issuing excessive geocoding requests

[Anandaroop_Roy]

I have recently configured the Map block on a few tables with a total of couple of hundred rows between them.

A few days later I checked my Google Developers console to see how my geocoding API key was faring, and I was shocked to see ~18,000 requests for that key.

If I open a Map block and the associated table simultaneously, I can see the geocoding cache column get updated repeatedly as I interact with the map — even though no data in those rows is changing. The gif below shows cell updates being triggered just while viewing/panning/zooming the map…

Then, if I open up one of records in question, I can see a bunch of changelog entries of the form: “User X edited this record using Map block Y”…

Can anyone explain what’s going on here? I’d like to keep using the Map block, but not if it’s going to blow up my API quotas simply by viewing the maps :frowning:

[Anandaroop_Roy]

One more thing about this table, in case it’s relevant.

The column that I’ve configured as the the Map block’s “location field” is actually a formula field, so that I can concatenate a few things together (e.g. address, city, state) to form a geocodable string.

Is there something about using a formula as the location field that would cause this overzealous geocoding?

[Mohamed_Swella1]

Hi @Anandaroop_Roy,

Welcome to Airtable Community! :slightly_smiling_face:

I would definitely say yes it is the formula field that is causing this. As Airtable is kind of refreshing the formula field to make sure it is correct if you ever change anything in the fields that are used in the formula.

A quick fix would be using Zapier to copy the formula field into a text field, then using this text field as the address. Or you can also do it manually by duplicating the Formula field and changing it into a text field, this means that you will copy and paste the address manually every time you add a new one.

Hope this helps! If it does, please mark this as Solution so others can benefit from it.

BR,
Mo

[Bill_French]

Anandaroop_Roy:

Is there something about using a formula as the location field that would cause this overzealous geocoding?

Yes. It’s a field of type FORMULA which is very much unlike actual data fields. As such, it is prone to unanticipated behaviours including the recalculations that you are seeing. Unfortunately, there’s no way to control this and it creates a runaway quota attack when it really needs to remain quiet for any populated row.

This is precisely one of the many issues that arise with hybrid field types, recently discussed in depth over in this thread. Ideally, any field should be able to optionally apply a formula that is contingent on the underlying field value as being empty.

URL--Text to Display Feature Product Suggestions

In the recent examples presented, field1 and field2 are not the fields where the formula exists; rather, they are fields referenced BY the formula. Wherever there is a field, I think it makes sense to have the ability to apply attributes, and attributes should include many things that fields are capable of including but not limited to: Field name Description Data type Conditional and default formatting Grid validation rules Form validation rules Security rules Formula Formulas - like all fi…

Since that’s not possible, what is the remedy?

Short answer - you cannot use a formula field for direct geo-encoding. Rather, you must use real fields for lat/lon or any type of geo-location object. This is best achieved through a Script Block that you run from time-to-time or an API process that runs periodically.

Another idea to consider is to cache lat/lng or geo-encoded data or addresses to avoid excessive Map quota costs. I always create and sustain location data and pull from the cache rather than re-encoding locations that I’ve already encountered. Our platform for public transit has performed more than a half-million reverse geo-code address lookups in the LA area and we never once paid a dime to Google by using this cache approach.

[Kamille_Parks]

Bill can you explain how that applies in this case? If the field in question is concatenating an address, a city, and a state together as required by the Map Block, wouldn’t conditionally applying a formula to a Single Line field run into the same problem? Whether or not the formula is a standalone field, wouldn’t Airtable still be constantly checking if the address/city/state fields have changed or if the conditions under which the formula should be applied have been met?

I feel like for this case, a more targeted feature request would be to just to let people define multiple location fields in the map block instead of requiring the full location definition (be it an address or a set of coordinates) be in one field.

[Bill_French]

Kamille_Parks:

If the field in question is concatenating an address, a city, and a state together as required by the Map Block, wouldn’t conditionally applying a formula to a Single Line field run into the same problem?

I’m certainly no expert in matters of formula computation rules internal to Airtable but it seems that the one aspect missing from this particular solution is the ability of formula fields to be able to know if they have already performed the calculation at least once. Since a formula field – by definition – uses the formula results as a proxy for the “field” value, it has no way to recursively inspect itself, right?

Formula Fields Lack Inspectable State

Lacking an ability to inspect the value of itself nor the ability to write values into a real data field, it cannot process the logic required to know if it has already computed the needed geo-encoding value once before. Ergo, it keeps processing and running up a tab for this customer’s account. It is simply a dumb function that has no idea if it has previously executed once, 20 times, 2,000 times, or never before.

That said, I’m pretty sure (correct me if I’m missing something) you can’t create a formula scenario with logic that says simply -

If this formula has already computed a value, stop computing a value.

What if …

What if we hypothesize for a moment and imagine that formulas are simply attributes of actual data fields. A real field with an attached formula could inspect the value in the field and if NULL, execute the formula repeatedly and as the computation engine calls for, but update the field itself only once. A lovely benefit of this model also emerges because deleting any existing value in such a field would cause the formula to reassert its logic, but only once if it was created with the specified conditional logic as noted in the diagram.

The design might look something like this:

My observation is simple - formula fields are not able to leverage the current value of themselves; ergo - they lack the ability to leverage their own state. In cases like this – and I assert, there are many others – the ability to use the previous results of a formula in future formulaic processing makes it possible to address many complex requirements involving computations and formula logic.

Workaround?

I tried to imagine a number of workarounds for this user’s situation and all of them led [me] to the conclusion that I needed to include a real data field with some sort of external scripting process - Script Block or API. Perhaps I’m not clever enough to come up with an approach using formulas only, but it looks like users are in a box when depending on formula fields to create dynamic data values that must mimic actual field values or suffer some rather nasty consequences.

Got a better idea for this user? Would love to learn how to do this a better way - I just can’t envision one.

[Kamille_Parks]

I’ve seen plenty of uses cases where someone needs to calculate a value via a formula, but wanting to keep that value once it’s been computed static. So in this case we have a field for a full address, concatenating a street address, city, and state field together. Is a full address for any particular going to change often or at all? Probably not, so it make sense that this would be a “static calculation”, where the value is computed once and Airtable stops checking.

I like that idea, but if someone wants to refresh a particular record’s value for that field (to account for changes to another referenced field), there should be some recourse for that. Maybe there’s a toggle for formula/rollup fields to keep values “synched” or to do the one-and-done calculation method. If toggled, and a formula output is found to be “outdated”, maybe Airtable just applies some styling to that cell to give the user an indication they need to update it.

How would Airtable know to stop checking? Well, according to the Custom Blocks doc, a formula field “knows” which other fields it references, meaning an array could be returned of those referenced fields. Airtable formulas have the ability to check the LAST_MODIFIED_TIME() of any particular field. So, I think there is some wiggle room for Airtable to devise a way to more cleverly check when to recalculate a formula value.

[Bill_French]

Sorry - I forgot to address the other part of your question.

Kamille_Parks:

I feel like for this case, a more targeted feature request would be to just to let people define multiple location fields in the map block instead of requiring the full location definition (be it an address or a set of coordinates) be in one field.

I’m certainly not ruling this out; it’s a good question. However, like all feature requests that deal with a single missing dimension, we see products become woefully burdened and featured to death. Systems grow to become clunky - pretty soon you’re addressing interest that are increasingly narrow.

I see things like this similarly to the lack of a Split() function. Would you ask Airtable to create a new formula that parses addresses? Or, would you recommend a new formula function that parses anything, allowing users to apply the new function to dozens of parsing objectives? If you convince them to implement an address parser, the very next request will be for postal code parsing for the UK. And then one that supports German post codes, etc.

There’s nothing wrong with adding new and specific features for location science - we live in a location commerce world now, so that’s good. But I like to encourage teams to be careful about expanding features that do stuff versus features that help users do lots of stuff - even stuff we can’t imagine.

[ScottWorld]

I have an address formula field that is tied to the Maps block, but it doesn’t keep triggering my Google Maps API over & over again.

It only triggers the API when the address changes.

Is it possible that there is something else going on with the formula field that @Anandaroop_Roy is using?

Mine is just a simple formula field — concatenating 4 text fields (address, city, state, zip).

@Anandaroop_Roy, are you using any lookups or rollups in your calculation field? Or anything else that might be interesting about your formula?

If not, I’m not sure why there’s difference between my Maps block and your Maps block. Maybe it has something to do with the number of records — I only have a small handful of records in my table.

[Bill_French]

Kamille_Parks:

I like that idea, but if someone wants to refresh a particular record’s value for that field (to account for changes to another referenced field), there should be some recourse for that.

I agree, and that’s yet another problem that exists today and for which there is no clever answer with or without fields that support formulas.

Kamille_Parks:

So, I think there is some wiggle room for Airtable to devise a way to more cleverly check when to recalculate a formula value.

I agree - lots of possibilities. But one aspect of [real] fields that support formulas is the fields are allowed to participate in all aspects of the platform such as the schema, API, etc. Fields populated with links would transform properly in PDF documents - there’s a universe of ways that formula fields trip up users and surprise them with cul-de-sacs. :winking_face: