England and Wales schools database: available here in SQL format
As part of the government’s Show Us A Better Way competition, it has made available all sorts of databases and datasets and APIs that haven’t previously been available – such as the list of all the schools in England and Wales.
Our only quibble with the latter was that it was only provided in Excel format – which as one commenter points out is a proprietary format (though free programs like OpenOffice will open it), and anyway to really begin doing useful things with such data you need to stuff it into a database; which calls for SQL format.
Never fear, Free Our Data is here. We’ve imported the data from the Excel file into a MySQL database and exported it as an SQL file which has all the required CREATE TABLE commands, with the data.
Grab a copy.
To make sure you’ve got the correct version (in case it gets copied and used elsewhere):
the MD5 checksum of the zip file is 3f46d71d84f6047ee0162d12a9456901
and of the SQL file itself (once unzipped) is 1021643b2c1c71773f20c7a4fbd1b8e1 .
- The following posts may be related...(the database guesses):
- None
July 4th, 2008 at 12:30 pm
Does the click-use license allow you to republish there data?
:)
July 4th, 2008 at 12:46 pm
Many thanks Charles!
July 4th, 2008 at 1:01 pm
[...] geeky girl wrote an interesting post today onHere’s a quick excerpt As part of the government’s Show Us A Better Way competition, it has made available all sorts of databases and datasets and APIs that haven’t previously been available – such as the list of all the schools in England and Wales. Our only quibble with the latter was that it was only provided in Excel format – which as one commenter points out is a proprietary format (though free programs like OpenOffice will open it), and anyway to really begin doing useful things with such data you need to stuf [...]
July 4th, 2008 at 1:19 pm
I tried looking at this but some of the fields have apostrophies in them so the formatting goes wrong when I try to access. It would be helpful if possible to have the character fields delineated by double apostrophies ” ”
Thanks
July 4th, 2008 at 1:44 pm
A verrry minor quibble, but while a MySQL dump is probably preferable to Excel’s binary blob, it’s not that useful if you don’t want your database tables to follow that schema or if you don’t use MySQL at all. Would exporting from Excel as CSV or tab-delimited not make a bit more sense in terms of usefulness?
July 4th, 2008 at 3:22 pm
If this data is publicly available, it might be worth putting it on DabbleDB, which lets you host public datasets for free. No idea about whether the licence mentioned above would permit this though.
July 4th, 2008 at 3:56 pm
@Richard: umm, how are you trying to access it? If you make a database and then treat it as a local file to be loaded it should be able to figure it out on its own.
@Mo: if you export it as CSV (or tab-delimited), it will still have the same schema. You then have to import it into a table and slice and dice it as you will. It’s not as if this is in TNF – if there’s only one table there’s only one step to TNF!
@Rich: I might look at DabbleDB, but the useful thing is having it on one’s own machine to play around with.
My own frustration is there isn’t a downloadable set of league tables.
July 4th, 2008 at 3:57 pm
(I would have explained @Richard above using more technical terms but comments using SQL commands are blocked – to prevent hackers.)
July 7th, 2008 at 3:35 pm
Of course, the big question is how do we plot these on a map (geocode) to really “exploit” the data. Looking at the XLS file (and yes they could have distributed as a CSV or perhaps the “open” OOXML, but then no one would have been able to use it!) the only real geocode information is the postcode. We could use OSs Code Point. Ahh, but then we wouldn’t be allowed to use it because the dataset would contain OS IPR… a government department, for a competition run by a government department. O what a tangled web….
September 6th, 2008 at 11:05 am
Is there a more updated version of this available, as all schools in England and Wales are not on it thank you.