Updates to the 2025 Accessibility SQL - For Government Sites

[mgifford]

I don't want to add the PR yet, but do want to capture the changes requested.

https://github.com/HTTPArchive/almanac.httparchive.org/blob/main/sql/2024/accessibility/lighthouse_score_by_government.sql
https://github.com/HTTPArchive/almanac.httparchive.org/blob/main/sql/2024/accessibility/lighthouse_score_by_government_with_urls.sql

I was also interested in seeing if it would be possible to somehow consolidate these two queries since most of the guts of the two is the same.

Some UK government institutions to add:

• mod.uk (defence)
• nhs.uk (health)
• police.uk (police)
• gov.scot
• gov.wales
• llyw.cymru

Some state url don't use .gov state.mi.us, state.in.us, state.mn.us, etc.

• https://mvic.sos.state.mi.us
• https://dsvsesvc.sos.state.mi.us
• https://mdotjboss.state.mi.us
• https://cplrenewal.msp.state.mi.us
• https://housing.state.mi.us
• https://www.mcgi.state.mi.us/milocator

The .gov scan should be trimmed more as it is catching some mi.gov.br sites in this. I just wasn't able to write a better SQL than this. It is still mostly accurate, but includes some random sites.

Looking at the list of official French list of domains, we could probably extend it to include:
\b(?:www.)?grand[\w-]+.(fr|alsace|bzh)\b
\b(?:www.)?(?:region[\w-]+|[\w-]+region).(fr|alsace|bzh)\b
\b(?:www.)?(?:region[\w-]+|[\w-]+region).(fr|alsace|bzh)\b
\b(?:www.)?departement[\w-].(fr|alsace|bzh)\b
\b(?:www.)?cc-[\w-]+.(fr|alsace|bzh)\b
\b(?:www.)?[\w-]+.agglo.(fr|alsace|bzh)\b
\b(?:www.)?commune(?:-[\w]+).(fr|alsace|bzh)\b
\b(?:www.)?mairie(?:-[\w]+)*.(fr|alsace|bzh)\b
\b(?:www.)?[\w-]+.gouv.(fr|alsace|bzh|app)\b

USA

• si.edu
• usps.com

I will add more below.

[mgifford]

New Brunswick's government uses gnb.ca

It's in here:
WHEN REGEXP_CONTAINS(page, r'\.(gc\.ca|canada\.ca|alberta\.ca|gov\.ab\.ca|gov\.bc\.ca|manitoba\.ca|gov\.mb\.ca|gnb\.ca|gov\.nb\.ca|gov\.nl\.ca|novascotia\.ca|gov\.ns\.ca|ontario\.ca|gov\.on\.ca|gov\.pe\.ca|quebec\.ca|gouv\.qc\.ca|revenuquebec\.ca|saskatchewan\.ca|gov\.sk\.ca|gov\.nt\.ca|gov\.nu\.ca|yukon\.ca|gov\.yk\.ca)/') THEN 'Canada'

So it should be downloaded, but not in:

      '|\\.gc\\.ca/'  -- Canada and provinces
      '|\\.canada\\.ca/'
      '|\\.alberta\\.ca/'
      '|\\.gov\\.ab\\.ca/'
      '|\\.gov\\.bc\\.ca/'
      '|\\.manitoba\\.ca/'
      '|\\.gov\\.mb\\.ca/'
      '|\\.gnb\\.ca/'
      '|\\.gov\\.nb\\.ca/'
      '|\\.gov\\.nl\\.ca/'
      '|\\.novascotia\\.ca/'
      '|\\.gov\\.ns\\.ca/'
      '|\\.ontario\\.ca/'
      '|\\.gov\\.on\\.ca/'
      '|\\.gov\\.pe\\.ca/'
      '|\\.quebec\\.ca/'
      '|\\.gouv\\.qc\\.ca/'
      '|\\.revenuquebec\\.ca/'
      '|\\.saskatchewan\\.ca/'
      '|\\.gov\\.sk\\.ca/'
      '|\\.gov\\.nt\\.ca/'
      '|\\.gov\\.nu\\.ca/'
      '|\\.yukon\\.ca/'
      '|\\.gov\\.yk\\.ca/'

gnb.ca isn't included.

[mgifford]

I've also missed Liechtenstein https://www.llv.li

There may also be regions here that would be good to check on:

https://digital-strategy.ec.europa.eu/en/policies/web-accessibility-monitoring

[mgifford]

Luxembourg has a list of domains from:
https://data.public.lu/en/datasets/inventaire-des-sites-publics/

\b(?:https?://)?(?:www.)?(?:[a-zA-Z0-9-]+.)?(?:public|gov|etat|data|service|security|mfi|lux)(?:.public|.gov|.etat)?.lu\b

The US has a federal listing here:
https://github.com/cisagov/dotgov-data

France has this list:
https://gitlab.adullact.net/dinum/noms-de-domaine-organismes-secteur-public

The UK has:
https://www.gov.uk/government/publications/list-of-most-used-websites

Similar aggregations could be made to pull out more domains.

[mgifford]

These should be added for Estonia:

1. https://www.riigikantselei.ee/
2. https://valitsus.ee/
3. https://www.riigikogu.ee/
4. https://www.hm.ee/
5. https://www.mkm.ee/
6. https://www.siseministeerium.ee/
7. https://www.sm.ee/
8. https://kliimaministeerium.ee/
9. https://www.agri.ee
10. https://www.just.ee/
11. https://www.kaitseministeerium.ee/et
12. https://www.kul.ee/
13. https://www.fin.ee/
14. https://vm.ee/
15. https://www.eesti.ee/

[mgifford]

Catalunya's got:
.gencat.cat

Basque Government has:
.euskadi.eus
.irekia.euskadi.eus

Not sure where these would be placed, but likely under Spain.

[mgifford]

Costa Rica go.cr doesn't appear as a country summary, (It is categorized as Other), so this should be updated.