search

tomwhite / covid-19-uk-data

Coronavirus (COVID-19) UK Historical Data
http://tom-e-white.com/covid-19-uk-data/
The Unlicense
162 stars 79 forks source link

Find population and boundary data for local authorities and health boards #18

Open tomwhite opened 4 years ago

tomwhite commented 4 years ago

Wales Health Boards: https://statswales.gov.wales/Catalogue/Population-and-Migration/Population/Estimates/Local-Health-Boards/populationestimates-by-lhb-age

tomwhite commented 4 years ago

Scotland: https://www.nrscotland.gov.uk/statistics-and-data/statistics/statistics-by-theme/population/population-estimates/mid-year-population-estimates/mid-2018

May need some work to map from LA to HB.

tomwhite commented 4 years ago

UK: https://www.ons.gov.uk/peoplepopulationandcommunity/populationandmigration/populationestimates

Again may need some work to map from lower to upper LA or HB.

timday commented 4 years ago

I picked up a file census.csv from https://www.kaggle.com/electoralcommission/brexit-results#census.csv (free sign-in required) which at first glance looked like it contains the right area codes, at least for England. Usefully bins population into 5 year age intervals. Although have to bear in mind it's 2011 census data so a bit out of date (and folks are almost a decade older now!)

timday commented 4 years ago

UK: https://www.ons.gov.uk/peoplepopulationandcommunity/populationandmigration/populationestimates

Again may need some work to map from lower to upper LA or HB.

The Scotland and Wales links seem obviously useful, but I'm a bit puzzled by this England one. It seems to be the "front door" to a vast quantity of good stuff... but amongst it I do not see anything with lower or upper tier area population/demographic data in a csv/spreadsheet file. There's certainly an obvious csv file with a whole of UK (no areas) population estimate timeseries, and there's a page with some maps which look like they have been produced from regional data (e.g figure 4 on https://www.ons.gov.uk/peoplepopulationandcommunity/populationandmigration/populationestimates/bulletins/annualsmallareapopulationestimates/mid2018 )... I'm somewhat mystified where that data is downloadable from though.

Ah hang on, that page has a link to "All data related to Population estimates by output areas, electoral, health and other geographies, England and Wales: mid-2018" which takes me to https://www.ons.gov.uk/peoplepopulationandcommunity/populationandmigration/populationestimates/bulletins/annualsmallareapopulationestimates/mid2018/relateddata?sortBy=title&query=&size=50 which looks like it might have something useful. (Wish I knew the terminology and hierarchy of all these different area types better!)

tomwhite commented 4 years ago

Useful thread here (also has links to geo info): https://twitter.com/geogale/status/1243173072196247557

bheather commented 4 years ago

https://www.ons.gov.uk/peoplepopulationandcommunity/populationandmigration/populationestimates/datasets/populationestimatesforukenglandandwalesscotlandandnorthernireland

Does this help?

Excel file with matching LA codes for England Scotland and NI - wales seems a bit broken

tomwhite commented 4 years ago

Resources for NI would be useful too, now that we have per LGD (local government district) breakdowns for confirmed case numbers.

Jcamain commented 4 years ago

We've managed to do the mapping and I've managed to map it to both Health Board at a Scottish Level and lower levels in England, Wales, and NI and it works in my dashboard - it's currently proprietary format - but will try and save it off into excel and also will detail where it came from - pretty sure it is all ONS data ..

nanjizal commented 4 years ago

ONS data is not MIT? - This is why I have avoided using or trying to parse. Need to check license and make any shape data clear in this respect.

Jcamain commented 4 years ago

Ok, so as a first step of tidying up my notes and doing documentation, here are the files I have used:

GeoJson Local_Health_Boards_December_2016_Full_Clipped_Boundaries_in_Wales.geojson Shape files for the Welsh Local Health Boards Open Government Licence v3.0 ONS http://geoportal.statistics.gov.uk/datasets/ons::local-health-boards-december-2016-super-generalised-clipped-boundaries-in-wales/data
Shapefile NHS_England_Regions_April_2019_EN_BUC.zi[ Shape files for the English NHS Regions Open Government Licence v3.0 ONS https://data.gov.uk/dataset/ece9c976-2f14-4cbb-b12c-866104413834/nhs-england-regions-april-2019-en-buc
Shapefile dohtrustboundary.zip Shape files for the Department of Health Boundaries Northern Ireland Open Government Licence v3.0 OpenDataNI https://data.gov.uk/dataset/0b04b46c-49af-45d5-b277-91b10937a01b/department-of-health-trust-boundaries
Shapefile SG_NHS_HealthBoards_2019.zip Shape files for the Scottish Health Boards Open Government Licence v3.0 Scottish Government SpatialData.gov.scot https://data.gov.uk/dataset/27d0fe5f-79bb-4116-aec9-a8e565ff756a/nhs-health-boards

and I have used our software to smooth the edges (polygons were massive), by using a simplify routine and then used simple names, NHSCode, NHSName, NHSPolygon, and created a simplified central point for the polygons where they weren't in existence. Then saved it out to csv (we use a proprietary format normally), hope it works! Am documenting everything else, so will do the same with my mapping from UTLA to that file. UKNHSRegions.zip

robchallen commented 4 years ago

UK_covid_reporting_regions.zip

This is the shapefile I assembled from UK UTLAs, Wales LHB, Scotland LHB, and whatever NI felt like using.

also from: https://github.com/terminological/uk-covid-datatools/blob/master/data-raw/UK_covid_reporting_regions.zip

tomwhite commented 4 years ago

Thanks for sharing the shapefile @robchallen!

downFast commented 4 years ago

Well, it is probably the longest way to do it but from this link we can download the boundaries then create a loop matching areacodes http://martinjc.github.io/UK-GeoJSON/ then once downloaded use this to convert topo to geojson https://mygeodata.cloud/conversion and finally https://mygeodata.cloud/map/#12191541-topo_eer

tada

Jcamain commented 4 years ago

I've kept mine at Health Board, but then allowed for drill downs to UTLA's and STP's in England - Can finally share - I've brought in the daily death data for England as well .. Much more interesting when you also bring in Population data - some of the data is in proprietary format, but apart from the polygons, I can always export other data into excel / csv - double and triple checked the different data quality and mapping pieces - I've also posted links to where I got the relevant files on the final sheet with hyperlinks: https://cr.qlik.com/sense/app/6e00e04e-570f-4a4c-998d-673105e6243f/sheet/5b806fd9-40f1-47a0-9a18-8b50690c0d03/state/analysis

downFast commented 4 years ago

I've kept mine at Health Board, but then allowed for drill downs to UTLA's and STP's in England - Can finally share - I've brought in the daily death data for England as well .. Much more interesting when you also bring in Population data - some of the data is in proprietary format, but apart from the polygons, I can always export other data into excel / csv - double and triple checked the different data quality and mapping pieces - I've also posted links to where I got the relevant files on the final sheet with hyperlinks: https://cr.qlik.com/sense/app/6e00e04e-570f-4a4c-998d-673105e6243f/sheet/5b806fd9-40f1-47a0-9a18-8b50690c0d03/state/analysis

my idea is to go here and grab the json https://coronavirus.data.gov.uk/

then the geojson will be created only once using the method above in my comment, but it's nice what you did tho

gbugmann commented 4 years ago

in https://geoportal.statistics.gov.uk/datasets/ons-postcode-directory-latest-centroids you will find the centroid lat and lon for each postcode in the UK. For each postcode, the csv file also gives the AreaCode. So, you can do some searching through the file to find lat and lon. I attach a result for the 183 area codes found in covid-19-cases-uk.csv.

AreaCode2lonlat.zip

Note that several postcodes will fall into a given area. I just picked the first I found. This may not be actually in the centre of the area. A better work would entail finding all postcodes in an area, then calculating their centre of gravity...

jsavinc commented 3 years ago

UK_covid_reporting_regions.zip

This is the shapefile I assembled from UK UTLAs, Wales LHB, Scotland LHB, and whatever NI felt like using.

also from: https://github.com/terminological/uk-covid-datatools/blob/master/data-raw/UK_covid_reporting_regions.zip

Hi @robchallen, I've been trying to find a low-resolution shapefile for Scottish Health Boards and came up blank - is it okay to use your compiled file for some research using data from National Records of Scotland? I'm looking at deaths at home in 2020 in Scotland.