Additional hospital data

[jan-campaignlab]

Have attached a file with some additional/missing hospitals to be added.

Can create a pull request instead if easier.

additional_hospitals_20250618.csv

[c6andy]

Hi @jan-campaignlab – huge apologies for the delay in responding to this.

Thanks for providing this extra data – that’s excellent. I have a couple of questions about it.

1. Would you be able to provide a source for this data? That way we can include it in the README
2. There are some duplicate rows in the data. E.g. Yeovil District Hospital is in there a few times (albeit with different lat/lon coordinates):
   

   constituency-explorer/database/fixtures/additional_hospitals_20250618.csv

   Lines 729 to 738 in 3cbf783

   	1289994765.0,way,50.9449955,-2.6329525,Yeovil District Hospital,Unknown,Unknown,Unknown,BA21 4AR,BA21,-2.633305,50.944967,Yeovil,Yeovil,E14001603,E14001603,Somerset,E06000066,Yeovil South,E05014392,Yeovil,E04008773,,E99999999,South West,Somerset,South West,England,South Somerset 015A,E01029232,South Somerset 015,E02006089,NHS Somerset,E38000150
   	1289994766.0,way,50.9450213,-2.6330038,Yeovil District Hospital,Unknown,Unknown,Unknown,BA21 4AR,BA21,-2.633305,50.944967,Yeovil,Yeovil,E14001603,E14001603,Somerset,E06000066,Yeovil South,E05014392,Yeovil,E04008773,,E99999999,South West,Somerset,South West,England,South Somerset 015A,E01029232,South Somerset 015,E02006089,NHS Somerset,E38000150
   	1289994767.0,way,50.9449007,-2.6329639,Yeovil District Hospital,Unknown,Unknown,Unknown,BA21 4AR,BA21,-2.633305,50.944967,Yeovil,Yeovil,E14001603,E14001603,Somerset,E06000066,Yeovil South,E05014392,Yeovil,E04008773,,E99999999,South West,Somerset,South West,England,South Somerset 015A,E01029232,South Somerset 015,E02006089,NHS Somerset,E38000150
   	1289994768.0,way,50.9450776,-2.6329368,Yeovil District Hospital,Unknown,Unknown,Unknown,BA21 4AR,BA21,-2.633305,50.944967,Yeovil,Yeovil,E14001603,E14001603,Somerset,E06000066,Yeovil South,E05014392,Yeovil,E04008773,,E99999999,South West,Somerset,South West,England,South Somerset 015A,E01029232,South Somerset 015,E02006089,NHS Somerset,E38000150
   	1289994769.0,way,50.9450709,-2.6327023,Yeovil District Hospital,Unknown,Unknown,Unknown,BA21 4AR,BA21,-2.633305,50.944967,Yeovil,Yeovil,E14001603,E14001603,Somerset,E06000066,Yeovil South,E05014392,Yeovil,E04008773,,E99999999,South West,Somerset,South West,England,South Somerset 015A,E01029232,South Somerset 015,E02006089,NHS Somerset,E38000150
   	1289994770.0,way,50.9448319,-2.6331218,Yeovil District Hospital,Unknown,Unknown,Unknown,BA21 4AR,BA21,-2.633305,50.944967,Yeovil,Yeovil,E14001603,E14001603,Somerset,E06000066,Yeovil South,E05014392,Yeovil,E04008773,,E99999999,South West,Somerset,South West,England,South Somerset 015A,E01029232,South Somerset 015,E02006089,NHS Somerset,E38000150
   	1289994771.0,way,50.9448732,-2.6325721,Yeovil District Hospital,Unknown,Unknown,Unknown,BA21 4AR,BA21,-2.633305,50.944967,Yeovil,Yeovil,E14001603,E14001603,Somerset,E06000066,Yeovil South,E05014392,Yeovil,E04008773,,E99999999,South West,Somerset,South West,England,South Somerset 015A,E01029232,South Somerset 015,E02006089,NHS Somerset,E38000150
   	1289994772.0,way,50.9447954,-2.632257,Yeovil District Hospital,Unknown,Unknown,Unknown,BA21 4AR,BA21,-2.633305,50.944967,Yeovil,Yeovil,E14001603,E14001603,Somerset,E06000066,Yeovil South,E05014392,Yeovil,E04008773,,E99999999,South West,Somerset,South West,England,South Somerset 015A,E01029232,South Somerset 015,E02006089,NHS Somerset,E38000150
   	1289994773.0,way,50.9443244,-2.6334922,Yeovil District Hospital,Unknown,Unknown,Unknown,BA21 4AR,BA21,-2.633305,50.944967,Yeovil,Yeovil,E14001603,E14001603,Somerset,E06000066,Yeovil South,E05014392,Yeovil,E04008773,,E99999999,South West,Somerset,South West,England,South Somerset 015A,E01029232,South Somerset 015,E02006089,NHS Somerset,E38000150
   	1295743726.0,way,50.9445609,-2.6331258,Yeovil District Hospital,Unknown,Unknown,Unknown,BA21 4AR,BA21,-2.633305,50.944967,Yeovil,Yeovil,E14001603,E14001603,Somerset,E06000066,Yeovil South,E05014392,Yeovil,E04008773,,E99999999,South West,Somerset,South West,England,South Somerset 015A,E01029232,South Somerset 015,E02006089,NHS Somerset,E38000150

   
   How should we handle this? E.g. we could import as is, or remove duplicates (with the same name and constituency)?
3. Similarly, some of the hospitals are already present in the data. E.g. Astley Ainslie Hospital:
   

   constituency-explorer/database/fixtures/additional_hospitals_20250618.csv

   Line 75 in 3cbf783

   4981914.0,way,55.9287569,-3.1978374,Astley Ainslie Hospital,Unknown,Unknown,hospital,EH9 2HL,EH9,-3.196904,55.928375,Edinburgh South,Edinburgh South,S14000080,S14000080,City of Edinburgh,S12000036,Southside/Newington,S13002933,,S99999999,,S99999999,Lothian,Edinburgh Community Health Partnership,,Scotland,Morningside - 01,S01008624,Morningside,S02001615,Edinburgh Community Health Partnership,S03000042

   
   …is already present in the Scottish hospital data:
   

   constituency-explorer/database/fixtures/hospitals-scotland.csv

   Line 211 in 10dc1a7

   S201H,Astley Ainslie Hospital, EH9 2HL,"Grange Loan, Edinburgh, EH9 2HL",EH9,-3.196904,55.928375,Edinburgh South,Edinburgh South,S14000024,S14000080,City of Edinburgh,S12000036,Southside/Newington,S13002933,,S99999999,,S99999999,Lothian,Edinburgh Community Health Partnership,,Scotland,Morningside - 01,S01008624,Morningside,S02001615,Edinburgh Community Health Partnership,S03000042

   
   Again, we could attempt to deduplicate instances like this. Or we can just import as-is, and accept that there may be some repeats.
4. Unlike the other hospital data, this CSV doesn’t include full address data. That’s not a problem – we can just populate the address field with the postcode. But just flagging that the rest of the address (e.g. street name and number; town) will be missing.

Let me know how you’d like to proceed – thanks in advance.

[jan-campaignlab]

Hi @c6andy , thank you, will double check and get back to you properly on each question. One clarification - would you prefer/need for the data to be separated between Scotland and England (I believe I purposefully just combined into one file, but can split them up if easier)?

[jan-campaignlab]

Follow up answers:

1. Yes, will double check (as it's a combination of at least two I believe).
2. I will also check this, I guess default functionality to do that would be good, but this should be something I think is caught on our side (and I had some code to do this, but maybe it was by lat/long and hence didn't catch them as they were different).
3. I think this is because I made a file to overwrite the existing data (so combined all previous data with some new scraped data), would you prefer that each additional data upload is a separate file (maybe can try and add some versioning, as we may at some point reupload same dataset which has been updated)?
4. I know one of the new sources didn't have full addresses, but let me check as I did try and retain the ones which did from before.

[c6andy]

Hi @jan-campaignlab – apologies once again for the delayed response.

One clarification - would you prefer/need for the data to be separated between Scotland and England (I believe I purposefully just combined into one file, but can split them up if easier)?

I don’t mind. I’ve written a new importer for the data anyway, so it doesn’t make a difference if it’s one file or two.

1. Would you be able to provide a source for this data? That way we can include it in the README

Yes, will double check (as it's a combination of at least two I believe).

Great, thank you. This would be extremely helpful – not least for making updates in future. The contents of the type column made me think this was perhaps derived from Open Street Map data? But I wasn’t sure.

2. There are some duplicate rows in the data […] How should we handle this? E.g. we could import as is, or remove duplicates (with the same name and constituency)?

I will also check this, I guess default functionality to do that would be good, but this should be something I think is caught on our side (and I had some code to do this, but maybe it was by lat/long and hence didn't catch them as they were different).

Okay, great – thank you. I think it’s totally fine to make removing duplicates the responsibility of the importer – especially if it means the data extraction process is simpler and therefore more reproducible. Ideally the data we import is as close to “source data” as possible, and the importer can be responsible for performing any necessary transformation steps.

3. Similarly, some of the hospitals are already present in the data […] Again, we could attempt to deduplicate instances like this. Or we can just import as-is, and accept that there may be some repeats.

I think this is because I made a file to overwrite the existing data (so combined all previous data with some new scraped data)

Hmm – I’m not sure if this is the reason. We currently have 990 hospitals in constituency explorer, and additional_hospitals_20250618.csv contains 791. So I don’t think the file can include all the existing ones. Of those 791 hospitals, it looks like somewhere between 60 and 110 hospitals are already present in the existing data.

would you prefer that each additional data upload is a separate file (maybe can try and add some versioning, as we may at some point reupload same dataset which has been updated)?

I think it’s okay either way. My preference would be that we overwrite the data with newer versions of the same data. Then we get versioning via git – e.g. here are the different versions of the scottish hospital data.

4. Unlike the other hospital data, this CSV doesn’t include full address data. That’s not a problem – we can just populate the address field with the postcode. But just flagging that the rest of the address (e.g. street name and number; town) will be missing.

I know one of the new sources didn't have full addresses, but let me check as I did try and retain the ones which did from before.

Ah okay. If you’re able to include addresses then that’s great, as we do display them in the map view. But if not, that’s also fine – we can just show postcode. Currently we have addresses for english hospitals, but not for scottish, and not for any of the hospitals in additional_hospitals_20250618.csv.