[OBO MykoMap] Fix ObO airtable sync now that Airtable API has changed

[wu-lee]

Please track against the clockify project [OBO MykoMap]

Description

The data feed download from the OBO Airtable API seems to have stopped working.

It could be related to this email Airtable sent at the end of last year, although this is not slated to come into effect until Feb 1st 2024:

You’re receiving this update because you have used the Airtable API via an API key in the last six months.

At the start of this year, we launched new API authentication methods and announced the upcoming deprecation of API keys on February 1st, 2024: this email is a reminder about this change.

How does this impact you? API keys (and therefore, any integration that used them to connect with Airtable) will continue to work until February 1st 2024. However, to ensure continued access to the Airtable API, you will need to start using the new authentication methods before February 1st 2024.

If you have already stopped using your Airtable API key, no further action is required. For example, if you have previously provided your API key to Zapier, you will have previously received emails confirming that your Zaps have been automatically migrated, and no further action is required for your Zapier usage (but may be required if you also provided your API key to other integrations or services)

If you or a developer on your team has used an Airtable API key to build API integrations with Airtable, please migrate to using personal access tokens to authenticate your API requests before February 1st 2024. Your API key usage can be directly replaced with a personal access token provided as an Authorization: Bearer header.

If your API Key use is with a third-party integration, we recommend using OAuth for authentication. Many partners have already updated their integrations to support OAuth. If you are unsure how to set up OAuth for a specific partner or OAuth is not currently available for that partner, we recommend you contact their team for details on when it will be available.

To learn more about this change, please visit our support article, which contains recommendations about how to find what is using your API key, and how to migrate to personal access tokens and OAuth.

More details https://support.airtable.com/docs/airtable-api-key-deprecation-notice

In any case, this needs more investigation.

Acceptance Criteria

• The data is being downloaded when requested.
• The data is being processed for the map as before.
• Changes made to data within Airtable are appearing on the https://maps.solidarityeconomy.coop/obo-public/ map within 15 minutes.

[wu-lee]

The error is only evident when the download is actually triggered (not suppressed because it thinks the data is up to date). To force a download, I've moved the data aside and run the downloader. It gets a 403 unauthorised error.

$ bundle exec seod download
invoking command: download
Traceback (most recent call last):
        4: from /home/seopendata/working/owned-by-oxford/downloader:13:in `<main>'
        3: from /home/seopendata/working/owned-by-oxford/.gems/ruby/2.7.0/bundler/gems/se-open-data-d26ba8a077c1/lib/se_open_data/cli.rb:232:in `command_http_download'
        2: from /home/seopendata/working/owned-by-oxford/.gems/ruby/2.7.0/bundler/gems/se-open-data-d26ba8a077c1/lib/se_open_data/cli.rb:671:in `fetch'
        1: from /usr/lib/ruby/2.7.0/net/http/response.rb:133:in `value'
/usr/lib/ruby/2.7.0/net/http/response.rb:124:in `error!': 403 "Forbidden" (Net::HTTPServerException)
'downloader' command in current directory failed
/home/seopendata/working/owned-by-oxford/.gems/ruby/2.7.0/bundler/gems/se-open-data-d26ba8a077c1/lib/se_open_data/cli.rb:282:in `command_download'
/home/seopendata/working/owned-by-oxford/.gems/ruby/2.7.0/bundler/gems/se-open-data-d26ba8a077c1/bin/seod:128:in `<top (required)>'
/home/seopendata/working/owned-by-oxford/.gems/ruby/2.7.0/bin/seod:23:in `load'
/home/seopendata/working/owned-by-oxford/.gems/ruby/2.7.0/bin/seod:23:in `<main>'

[lin-d-hop]

Feels to me like this would benefit from an initial timeboxed investigation to understand:

1. Is there a simple fix? If so just fix it.
2. If not, are there different potential solutions to chat through.
3. Does the auth update require a separate issue?

[ColmDC]

Spend a couple of hours to see if there is a straight forward fix.

[ColmDC]

Definitley seem to be some changes on

https://maps.solidarityeconomy.coop/obo-public/

Can you have a quick check that the obvious new geo-coding issues are not related to these fixes you've made, and I'll raise a seperate ticket to look into them. @wu-lee

[wu-lee]

Looking at the standard.csv file, which now has a confidence field, and includes the geocoded address verbatim, the least confident entries are the following. Note that Oxford is at about (51.7, 1.2)

h2i4cyH1O1Gr5g  0   40.291622   -79.470059  Rose Hill Community Association Rose Hill Community Centre
The Oval, Oxford, Great Britain, OX4 4UY
s8LsVr0nRLIgRf  19  52.889444   -122.377572 Flo's Social Enterprise Incubation Kitchen  see Flo's, The Place in the Park, Oxford, Great Britain, OX4 3JZ
7KtFzRMbiT8NST  22  24.3151 103.314 Solidarity Economy Association  fake address, Oxford, Great Britain, OX2 6TP
xKT58EV0NLUsom  25  51.746972   -1.228659   Tap Social  Tap Social, Unit 27 Curtis Industrial Estate;North Hinksey Lane, Oxford, Great Britain, OX2 0LX
QY380nl58zadvN  39  39.503704   -84.749449  PodTech Oxford, Great Britain
TeJKSbxVwWUbeS  39  39.503704   -84.749449  The Project PT  Oxford, Great Britain
gIEccwWrfUcMah  39  39.503704   -84.749449  Oxfordshire Cooperative Training Scheme Oxford, Great Britain
oT1GG0G8wjSLhl  39  39.503704   -84.749449  Wellbeing Cooperative   Oxford, Great Britain
pd5qxwT6bxE8R4  39  39.503704   -84.749449  RAW Workshop    Oxford, Great Britain

Sorting by longitude catches some slightly different cases - confident but whack nevertheless:

s8LsVr0nRLIgRf  19  52.889444   -122.377572 Flo's Social Enterprise Incubation Kitchen  see Flo's, The Place in the Park, Oxford, Great Britain, OX4 3JZ
QY380nl58zadvN  39  39.503704   -84.749449  PodTech Oxford, Great Britain
TeJKSbxVwWUbeS  39  39.503704   -84.749449  The Project PT  Oxford, Great Britain
gIEccwWrfUcMah  39  39.503704   -84.749449  Oxfordshire Cooperative Training Scheme Oxford, Great Britain
oT1GG0G8wjSLhl  39  39.503704   -84.749449  Wellbeing Cooperative   Oxford, Great Britain
pd5qxwT6bxE8R4  39  39.503704   -84.749449  RAW Workshop    Oxford, Great Britain
h2i4cyH1O1Gr5g  0   40.291622   -79.470059  Rose Hill Community Association Rose Hill Community Centre
The Oval, Oxford, Great Britain, OX4 4UY
XN5mfIZo0feAMY  90  42.09865913041304   -71.86402457635508  Damascus Rose Kitchen   Old Fire Station Cafe, 40 George Street, Oxford, Oxford, Great Britain, OX1 2AQ

Of these, problems I can see are:

• including the org name in the address (Rose Hill, Tap Social, Flo's)
• Not including anything much in the address (PodTech, The Project, OCTS, Wellbeing Coop, RAW Workshop)
• A "fake address" (SEA)

Not sure why Damascus Rose is off. The GeoAPIfy playground for that address:

https://apidocs.geoapify.com/playground/geocoding/?params=%7B%22query%22:%22Old%20Fire%20Station%20Cafe,%2040%20George%20Street,%20Oxford,%20Oxford,%20Great%20Britain,%20OX1%202AQ%22,%22filterValue%22:%7B%22radiusMeters%22:1000%7D,%22biasValue%22:%7B%22radiusMeters%22:1000%7D%7D&geocodingSearchType=full

Fiddling with that, the answer seems to be

• including the cafe name in the address
• using "Great Britain" instead of "UK" (although all the addresses have that, in this case using UK instead of Great Britain helps get it out of Canada)
• maybe doubling up "Oxford" to some extent?

[ColmDC]

Okay, clearly not related to Airtable issue, we should close this, but I think we should start a wiki page noting known weaknesses with our geocoder, and recommendations on how to feed it to get the best results.