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