Auto-geocode Airtable addresses (latitude, longitude, country, state)

Build a code workflow that geocodes addresses landing in an Airtable table and writes the results back onto the same row.

Trigger: poll Airtable for new records (event type new_record) on a single configured base and table. At setup, ask me which base and table to watch, and which field on that table holds the street address.

For each new record, run these steps in order:

1. Read the address field from the triggering record. If it is empty, skip the record entirely (do not update it).

2. Call OpenStreetMap Nominatim Search (Forward Geocode) with q = the address, format = jsonv2, addressdetails = 1, limit = 1. Send a descriptive User-Agent header identifying this workflow and include an email query parameter so OSM operators can reach us. Sleep at least 1100ms before any subsequent Nominatim call in the same run to stay under their 1 request per second fair-use cap.

3. Handle the response:

- If the response is an empty array, call Airtable Update Record on the triggering record with geocode_status = no_match and leave the coordinate fields blank. This prevents the row from being re-attempted on every poll.

- If Nominatim returns HTTP 429, wait 2 seconds and retry the same request once. If it still fails, update the record with geocode_status = rate_limited so a human can re-run it later.

- If the response has at least one hit, take the first result and map it as follows: lat -> latitude (number), lon -> longitude (number), address.country -> country, address.state -> state (fall back to address.region or address.province if state is missing), address.postcode -> postcode, display_name -> display_name, and set geocode_status = ok.

4. Call Airtable Update Record (PATCH) on the triggering record with only the mapped fields. Do not touch the original address field or any column the workflow did not produce.

Configuration to expose to the user at setup: the Airtable base id, the table id or name, the address field name, and the destination field names for latitude, longitude, country, state, postcode, display_name, and geocode_status. Default the destination names to those exact strings but let me override them in case my columns are named differently.

Important constraints: this is a deterministic code workflow with no LLM step. The Nominatim service is no-auth. Airtable uses OAuth and needs data.records:read and data.records:write scopes plus schema.bases:read so the workflow can resolve field ids. Respect Airtable's 5 requests per second per base limit by processing one record per trigger fire; do not batch.

Additional information

What does this prompt do?

Watches your Airtable table for new rows that have an address.
Looks each address up against OpenStreetMap and pulls back the canonical place.
Writes latitude, longitude, country, state, postcode, and a cleaned display name back onto the same row.
Marks rows that have no match so they don't keep getting retried.

What do I need to use this?

An Airtable base with a table of addresses you want enriched.
A column on that table that holds the street address (any format works, free-form is fine).
Columns ready to receive latitude, longitude, country, state, postcode, a cleaned display name, and a small status field.
Nothing on the map side. OpenStreetMap's geocoder is free and needs no sign-up.

How can I customize it?

Pull more fields from the lookup (city, county, neighborhood, road) into extra columns.
Filter so only rows where the address is filled in and the status column is empty get processed.
Add a country or region bias if your data is mostly from one place, to improve match quality.

Frequently asked questions

Do I need a Google Maps or Mapbox account?

No. This uses OpenStreetMap's public geocoder, which is free and does not require a key. You just need Airtable connected.

Will it work for international addresses?

Yes. OpenStreetMap covers the whole world. Coverage and precision vary by country, but most addresses with a street, city, and country resolve well.

What happens if an address can't be matched?

The row still gets updated. The latitude and longitude columns stay empty and the status column is set to no_match, so the workflow does not retry it on every pass.

How fast does this run?

OpenStreetMap asks for roughly one address per second, so a few thousand rows takes a few hours. For bigger backlogs we recommend running it overnight or in smaller batches.

Will it overwrite rows I've already geocoded?

No. By default the workflow only touches new rows where the coordinates and status columns are still empty, so existing data is left alone.